Analysis Services 2008, the Next-Generation Analytics Platform

Since Microsoft’s 1996 acquisition of the Israeli-based Panorama Software, Microsoft’s Business Intelligence platform has been continually refined and is spearheaded by the later renamed Analysis Services analytical platform! Analysis Services continues to rank as the number one OLAP product according to such institutes as Gartner, IDC, and the OLAP Report. With each iteration that passes Analysis Services continues to ‘up the ante’ in the OLAP market.

Analytical platforms in general are growing and are realizing more mainstream use. Every potential business and industry can benefit from analytics and thus the larger field of Business Intelligence! Competition is fiercer today in business than ever before and the ability to reap more value from your existing corporate data then your competitors is one of the many ‘winning factors’ in business today!

Every major area of the SQL Server product has been enhanced in the 2008 release including Analysis Services. The new enhancements as described later touch both multidimensional and data mining objects. Analysis Services 2008 improvements are substantial and thus warrant an evaluation of Analysis Services 2008 and the potential benefits it may bring to your organization!

Why Upgrade from Analysis Services 2000?

The same reasons for upgrading to Analysis Services 2005 from 2000 apply to why you should consider upgrade to Analysis Services 2008. In addition to those original improvements found in the 2005 release, 2008 contains additional improvements which are listed below in the topic of Why Upgrade from 2005. Beyond the analytical platform improvements there is the end of mainstream support for SQL Server 2000 matter. Microsoft mainstream support for SQL Server 2000 ends on 4/8/2008, more information can be found on this at http://support.microsoft.com/lifecycle/?LN=en-us&p1=2852&x=17&y=10 .

Why Upgrade from Analysis Services 2005?

Like the DTS to SSIS 2008 migration, if you are already running on the 2005 platform you will realize less benefits when compared to those migrating from Analysis Services 2000. That being said, there are still a good amount of new features and improvements found in Analysis Services 2008 including but not limited to:

• MultiDimensional Data
  • Enhanced Cube Design
    • Simplified & Enhanced Cube Wizard ‘IntelliCube’
    • AMO & UI Best Practice Alerts
    • Aggregations Designer
    • Simplified & Enhanced Aggregations Designer & Usage-Based Optimizations Wizards
  • Enhanced Scalability
    • MOLAP-enabled write back
    • Scale-out databases
    • Backup Compression
  • Enhanced Dimension Design
    • Attribute Relationships Designer
    • Enhanced Dimension Wizard
    • Key Columns Dialog
  • Enhanced MDX
    • Dynamic Named Sets
    • Subspace Computations
• Data Mining
  • ARIMA Time Series Algorithm
  • Creation of Holdout Test Sets
  • Filtering on Model Cases
  • Cross-validation of multiple Mining Models
  • Drill though to Structure Cases & Columns
  • Aliasing Mining Model Columns

As you can see there are still several added benefits by migrating to Analysis Services 2008! Microsoft continues to devote a large amount of its resources to continual refining the coveted MSFT BI platform. So should you upgrade to Analysis Services 2008? Ultimately, this decision should be based on your organization’s analytical applications and use of Analysis Services and the degree you can benefit from the new capabilities of the 2008 platform.   

We are still running on OLAP Services (SQL 7.0), what do we do?

Things get a bit more difficult if your organization is still running on the original SQL 7.0 OLAP Services release! First, you will need to upgrade to one of the two supported SQL Server 2008 upgrade paths:

1.       SQL Server 2000 w/sp4

2.       SQL Server 2005 w/sp2

Once you have performed this preliminary upgrade of your SQL Server (and thus OLAP Services) server you can start planning your SQL Server 2008 upgrade if you desire to get the latest advances in the analytics platform! Note that mainstream support for SQL Server 7.0 concluded on 12/31/2005! For more information on Microsoft’s Support Lifecycle for SQL Server 7.0 please see http://support.microsoft.com/lifecycle/?p1=2862 .

Effort Estimation Phase

Yet another parallel with the DTS to SSIS 2008 migration, you should consider performing a migration effort inquiry phase. There are fairly large differences between the 2000 version of Analysis Services and those versions that followed thereafter. Some of the items you may want to consider when performing your Analysis Services migration effort estimation include but not limited to:

·         Cube & Mining Structure/Models Complexities

·         SQL Server Upgrade Advisor (SSUA) 2008’s alerts on your existing Analysis Services Installations

·         64-bit Environments

Summary & Part2

In this post we have reviewed why an organization should consider upgrading their existing Analysis Services (and OLAP Services) installations to Analysis Services 2008. Much like the DTS to SSIS 2008 migration, Analysis Services migrations (particularly from SQL Server 2000) are a somewhat difficult yet high value proposition. A second parallel is having a solid, well thought out migration plan and effort estimation phase completed. Part 2 of our Analysis Services 2008 migration topic will go into the actual migration itself, rather how we perform the migration once we have decided to go forward!

Cheers

Derek Comingore

Time to Upgrade…

Well, we’re back and it’s time to perform the actual DTS to SSIS migration. Before we get started let’s review a few key points of interest. First, the migration process from DTS to SSIS is the same regardless of which version of SSIS (2005 or 2008) you are migrating too. In the context of these blog posts we are discussing SSIS 2008, just keep in mind that these are the same processes to migrate to SSIS 2005 as well. Second, we need to review the available DTS to SSIS migration strategies as mentioned in my first post, The Key SQL Server 2008 BI Migration: DTS to SSIS 2008, Part 1 of 2:

·         Proactive (resolving DTS to SSIS issues prior to upgrade)

·         Reactive w/Package Migration Wizard

·         Reactive w/o Package Migration Wizard

There are various business and technical requirements that will assist you in this decision such as:

·         Can you still work in/edit the DTS Packages?

·         Can the majority of the DTS to SSIS issues be resolved in DTS or must you wait until the package is in SSIS format? (some DTS items do not have direct SSIS equivalents)

·         What is the average effectiveness of the Package Migration Wizard for your packages?

The DTS to SSIS Migration Process

At this point you have been given the ‘green light’ to perform the actual migration and you have selected a migration strategy. So now what? Well, first off don’t do anything on production! If you have not yet performed a DTS Package scan with SQL Server Upgrade Advisor (SSUA) for the effort estimation, that will be the first task to perform. Again, we want to minimize the load on the production server(s) as they still have to do their daily ‘jobs’. To perform a SSUA scan of your DTS Packages perform the following steps:

1.       Export the DTS Packages to the Structured Storage File Format.

a.       In SQL Server 2000 Enterprise Manager, open each package and do a Save As command to the Structured Storage File Format.

*If the production server has already been upgraded to SQL Server 2005 and is still running the DTS Packages you will need the SQL Server 2000 DTS Designer Components to save the package to a storage file.

*You can save each package to a new file or consolidate all of them into a single file. The SSUA facilitates scanning all files in a supplied directory so either method will work with the SSUA.

2.        Copy or Move the files to a SQL Server 2008 development server.

3.        Scan the DTS Packages w/SSUA 2008

*The SSUA requires the detection of the DTS runtime which gets installed as part of the SQL Server 2005|2008 backwards compatibility files prior to allowing you to scan individual .dts (structured storage) files.

4.        Note the SSUA scan results

With your SSUA scan results ‘in hand’ let’s move on. You should already have the DTS packages on a development server. At this point you should either use your current development server or another server to create a true test environment. By test environment I mean a SQL Server 2008 environment that mimics production. So items like files, directories, databases (with a sampling of data) etc are on the development server, this will facilitate the actual testing of your SSIS 2008 packages after being converted!

At this point our development environment has been built using SQL Server 2008. It is now time to migrate the packages in the development environment by leveraging your chosen migration strategy detailed above. Once you have your SSIS (.dtsx) equivalents of the DTS packages you need to perform testing of each individual SSIS package. Make sure they execute as expected producing the correct results. Once you are confident that the new SSIS packages are working as expected you should:

·         Enable SSIS Package Configurations for all external resources

·         Create a Package Deployment Utility

You are now ready to deploy, test, and ensure that the new SSIS packages run as expected in your production environment! Definitely coordinate the following steps with the server’s DBA. Depending upon the overall SQL Server 2008 upgrade strategy the DBA choose (in-place or side-by-side) you may or may not have both instances on the current production machine. If the production servers have not yet been upgraded then you must wait until they are upgraded to SQL Server 2008. Once the production server(s) are upgraded, deploy the packages with the previously created Package Deployment utility to the intended servers. Update any package configuration settings needed and test each individual package during periods of low activity on the production server (if possible). Once you have confirmed the new SSIS Packages work as expected your DTS to SSIS 2008 migration is complete!

Migrating from SSIS 2005 to 2008

If you are already running on SQL Server 2005’s Integration Services platform and you wish to get the new benefits of Integration Services 2008 you will still need to perform a migration (or upgrade), however it’s a relatively simple one. As of the time of this writing (SQL Server 2008 CTP5), SSIS 2008 does support being installed side-by-side with SSIS 2005. SSIS 2008 is for the most part a pure enhancement of the platform. I say for the most part because there are a few ‘gotchas’ (breaking changes) between the two builds:

·         SSIS 2008 uses Visual Studio 2005 Tools for Applications (VSTA) for its scripting environment

·         SSIS 2008 uses a newer build of the SQL Server Native Client

·         SSIS 2008 contains an updated OLEDB Provider for Analysis Services (10.0)

·         SSIS 2005 custom developed components will need to be altered to work in SSIS 2008

The script environment differences are the biggest change between the two builds of SSIS. When you open/add a SSIS 2005 package which contains a Script Task in SQL Server 2008 BIDS it will automatically attempt to convert the script for you. You can also attempt to execute the older SSIS 2005 package with the 2008 dtexec utility, this operation will also convert the script; however the script conversion is only temporary for the currently running instance of the package.

Removing the old DTS Packages & SQL Server Agent Jobs from Production

This is the step that is the easiest to forget about! You have now successfully migrated your older DTS packages to the SSIS 2008 format, we are all done right? No, that’s not right, we still have the original DTS packages running on our production server. To remove the older packages from a production environment perform the following steps (again make sure the DBA is aware of your actions):

·         Stop all corresponding SQL Agent jobs

·         Open each package and do a Save As command to export the production version to the structured storage file format (yes, I know we already did this, but you never know if any of them changed or not, good practice)

·         Backup, check in, etc the storage files just in case we ever need to refer to them again!

*Assuming the same schedules apply to the new packages you may want to just update each SQL Server Agent Job with the corresponding SSIS Package step replacing the older DTSRun steps. If you need to recreate the jobs too on a different server consider scripting out all of the jobs and then editing/recreating them in bulk with the updated job steps in the scripts.

Summary

In this post we have explored the various DTS to SSIS migration solutions and best practices processes. Once the migration is approved based upon the business and technical factors (see my prior post The Key SQL Server 2008 BI Migration: DTS to SSIS 2008, Part 1 of 2) you need to perform the actual migration. The DTS to SSIS migration summarized consists of:

1.       Choosing a DTS to SSIS Migration Strategy (Reactive/Proactive)

2.       Capturing SSUA DTS Package Alerts (all categories of notifications)

3.       Building a dev/test environment

4.       Migrating the packages using the selected DTS to SSIS Migration Strategy

5.       Testing/Correcting the resulting SSIS 2008 Packages in the dev/test environment

6.       Deploying and reconfirming the resulting SSIS 2008 Packages work in production as expected

7.       Removing the old DTS Packages from production w/optional SQL Server Agent Jobs

By using the prescriptive guidance found in both of my posts you will have a great head start on the DTS to SSIS migration process we all face as SQL Server customers!

Cheers

Derek Comingore

We have had a lot of great posts from contributing author Derek Comingore regarding application compatibility and upgrades of the key SQL Server 2008 components that sit outside the relational engine. In this entry, I’m posting a podcast interview we created with Derek that covers many of these points so our podcast subscribers can get the content as well.

Fire up those Zune’s and enjoy!

 

 SQL Server 2008: BI Stack Key App Compat Issues [16:58m]: Play Now | Play in Popup | Download

-Derick

The DTS to SSIS Dilemma…

SQL Server Integration Services or SSIS, saying those words around IT personnel who have a plethora of DTS packages will shiver! Naturally, they tremble because of two key reasons. First, SSIS is a completely different and more powerful tool, with power does come a certain degree of complexity. DTS has been used for a range of tasks beyond simple Extract, Transform, Load (ETL) purposes. Recently I was at a client’s site that entailed over 400 DTS packages, few of which were used for actual decision support purposes. Analyst, Information Workers, and others that are common non-IT personnel users of DTS are going to feel awkward (at least at first) using SSIS!

The second reason that folks get nervous about any potential DTS to SSIS migrations is because they are to some degree aware of the migration ‘cost’ or effort involved. DTS to SSIS is not a trivial migration, especially for those clients who have the 100s or even 1000s of production packages. There is a package migration wizard I will discuss in part 2 of this post that ships with SQL Server 2005 and 2008.  The package migration wizard attempts to migrate your DTS packages, however this wizard roughly migrates only around 40% of the various possible package configurations!

So Why Migrate Given the High Effort Required?

This is a natural question and the answer is also simple: power and speed. First, SSIS is a much more powerful data integration platform. SSIS contains several features that the older DTS platform did not have. Here are some of the features of SSIS that DTS did not have (or at least to the degree that SSIS contains):        

• Separation of control flow and data flow        
• Some connections have been optimized        
• Several additional various transformations        
• Wider range of data sources        
• Security enhancements        
• Toolset unification via BIDS with the other BI projects, source control integration
• Checkpoints & in place restarts        
• Dynamic runtime configurations        
• Superior logging capabilities 

The second reason to migrate is speed or rather performance. SSIS entails two different yet cooperating engines, the Run-Time Engine and the Data Flow Engine. SSIS’s Data Flow Engine leverages a buffer-oriented architecture to load and transform data in memory. As a result, SSIS is fairly fast compared to DTS, especially in terms of transformations. If you are performing simple data loads without many transformations involved then there will be a small delta in performance leaning towards SSIS (SSIS’s SQL Server destination component is faster than the older DTS connections). That said, as you increase package transformation complexity, SSIS performance will continue to increase over DTS. In fact, on average SSIS is believed to be roughly 7 times as fast as DTS. Your organization may experience a lesser increase in performance or even higher but this is the average gain seen.

A third reason though it is not a prevalent one yet (which is why I did not mention it before) is because the DTS product is an official deprecated feature of SQL Server. What this means is that Microsoft is delivering a message to its customer that they need to migrate to the SSIS platform sooner than later. Some would argue with me that this should be the main reason why organizations should migrate but let’s be honest about the matter. SQL Server 2008 is the last version to support DTS (as of now) and thus as a customer you will have a long time until you are absolutely forced into this migration but note that the day will come.

We Are Already Running on SSIS 2005, Do We Need SSIS 2008?

This is where things get a bit more difficult. Do you really need to perform an upgrade to SQL Server 2008 just for the SSIS enhancements? Like most things in life, that will depend on your organization’s usage of SSIS and the larger SQL Server product. Product enhancements beyond SSIS are out of scope for this blog post but suffice it to say there are several BI related enhancements in the 2008 product beyond those found in SSIS. That said, below are the enhancements from SSIS 2005 to 2008:        

• New Data Profiling Task & Viewer        
• Enhanced Performance and Caching for the Lookup Transformation        
• Enhanced Data Flow Engine Tuned for Parallelism       
• Various smaller enhancements for supporting new data types and TSQL capabilities

OK, I Want SSIS…How Do We Migrate?

There are three core approaches to the DTS to SSIS 2005|2008 migration. I will be listing these solutions in order of ease. Also, only the last two are possible solutions if you must migrate immediately for some reason.·        

• Proactive

SolutionBased on the SQL Server Upgrade Advisor’s results, take corrective action on the existing DTS packages then re-scan them w/Upgrade Advisor. Once the Upgrade Advisor reports no outstanding issues leverage the 2005 Package Migration Wizard to automate the package migration process. Any packages that cannot have their upgrade issues resolved will need to be rebuilt from scratch in SSIS. 

• Reactive Approach w/Package Migration Wizard

Disregard the Upgrade Advisor’s alerts and use the Package Migration Wizard to perform the migrations. Review each new resulting SSIS package to ensure it is built correctly. For those packages which did not migrate correctly either modify the resulting SSIS package or create it from scratch in the SSIS designer. 

• Reactive Approach w/o Package Migration Wizard

Disregard the Upgrade Advisor’s alerts and rebuild all of the packages from scratch in the SSIS designer. 

Scalability Experts DTS to SSIS Effort Estimation Framework & Considerations

The first step in performing a DTS to SSIS migration project is the effort estimation itself. This is not a trivial migration process and thus warrants a short yet focused study on the effort required. At Scalability Experts we have developed a DTS to SSIS Effort Estimation Framework for such projects. The framework assists us in both the time and accuracy of our DTS to SSIS effort estimations. When you are performing a DTS to SSIS effort estimation some of the items you want to consider include:

• Package Complexities
• Package Upgrade Advisor Alerts
• Parent/Child Package Environments
• Package External Dependencies
• 64-Bit Environments

Summary & Part 2

In conclusion, DTS to SSIS is a somewhat difficult yet highly valuable migration for those organizations that have come to rely on the Microsoft data integration platform(s). With the arrival of SSIS 2008 there are even more reasons to perform the migration. Having a solid migration planned out and effort estimation phase completed will be key steps to ensuring a smooth migration. In part 2 of this blog post we will discuss the actual migration process itself (How to perform the upgrade). This is the next logical step in the process once management has given the thumbs up for the migration effort to continue. Cheers
Derek Comingore

In SQL Server Reporting Services (SSRS) the Report Server is the core component of the product. In the 2000 & 2005 product, the Report Server consisted of both a Windows & Web service. The web services facilitate the programmatic interfaces (APIs) into the Report Server. The windows service provides initialization, scheduling, delivery, and maintenance tasks. Together, the services represent a single instance of a SSRS Report Server. The Report Server is also where you can plug in your custom developed Data Processing, Delivery, Rendering, and Security extensions.

A SSRS Report Server relies on backend databases for both its permanent and temporary requirements. By default, the two Report Server databases are called reportserver and reportservertempdb. These databases can reside on either a local or remote instance of SQL Server 2000 or 2005. Furthermore, you should not directly change the data or structure in these databases; only work through the SSRS various interfaces that will in turn update the data in these databases.

When you intend to upgrade a SSRS 2000 or 2005 instance that has been configured with any of the following Report Server database configurations your upgrade will be blocked:

·         SQL Server 2000 in a different instance

·         SQL Server 2000 on a remote instance

·         SQL Server 2005 on a remote instance running in a 2000 compatibility mode

If you leverage the SQL Server 2008 Upgrade Advisor (SSUA) to advise you of your database servers upgrade issues the tool will report back a warning informing you of the issue. To correct the issue and thus allow you to upgrade your SQL Server 2000 or 2005 instance to the 2008 product you have one of two choices:

·         Upgrade (and ensure the compatibility mode is set to 2005) your remote SQL Server 2000 server to 2005 that houses the Report Server databases

·         Move the Report Server databases to a SQL Server 2005 or 2008 instance and do not forget to use the SSRS Configuration tool to connect the Report Server to the new database server instance

Derek Comingore