Chpater 3: Migrating to SQL Server 2005 BI Components

Definitions
Measures - the numbers that are analysed. E.g. sales amts, inventory counts, account balance amts.
Dimensions - qualify whats being analysed. E.g. if analysing sales amts, then the following dimensions will relate to each sale: pdt, customer, sale date & store.
Attributes - Dimensions are made up of attributes. They provide detailed descriptors about the dimension. e.g pdt dimension will have attributes: Category, Model, Size & Pdt Name. You can summarize/analyse data at an attr lvl.
Hierarchies - attr's combine together to make hierarchies. (Yr -> Qrtr -> Mnth -> Day).
Cubes - contain dimensions, attributes, measures.

The 2 features with changes that have the largest impact on migrating are attributes and cubes.


Managing SSAS

• In-place migration - when install 2005 on same server on which def. instance of 2000 already installed. The 2005 installation process wil auto invoke the in-place upgraqde, thus existing cubes will be auto migrated.
• side-by-side migration - install 2005 on a new server OR same server as named instance. (TIP: install a named 2005 instance alongside an unnamed 2000 instance. Then after successful auto migration of cubes uninstall 2000 and rename 2005 to default instance).

Migrate from SSAS 2000 to SSAS 2005 ~ Migration Wizard

• Attribute Changes - in 2000 a dim. cld only contain 1 hierarchy. In 2005 a dim. can have >=0 hierarchies.
• Cube changes - In 2000 only 1 'fact' tbl. In 2005 >=1 fact tbl. In 2000 whilst cubes can be combined into virtual cubes, there are limitations on how the fact tbls can relate to one another.

1. Run on server hosting 2005. Need lcl or network access to 2000 cube.
2. IDs incompatibilities.

Post Migration considerations -

Migrate to SSIS packages

• DTS & SSIS - diffs -
• DTS is workflow engine with a few data transform fntns. SSIS - enterprise-lvl ETL tool with richer workflow engine, better tranform engine, + ability to perform data operations.
• Workflow-drive Vs Dataflow driven.
• synchronous data processing Vs asynch data-processing.
• one-to-one data copies Vs many-to-many data processing.
• Scripting for advanced operations Vs minimal scripting due to broad capabilities
• all or nothing Vs package restartability + txns support

• Executing & Managing DTS in SQL 2005 -
• Installation Runtime - when installing 2005, the Advanced Options allow one to choose to include the DTS Runtime Engine as part of the 2005 installation.
• SSMS - Can manage + design + edit (install DTS Designer components) DTS packages in 2005 Mgmt Studio. Store DTS pckgs in msdb - Mgmt\Legacy\DTS.
• Execute Package Task - exec DTS pckg from within SSIS pckg.

• Running Package Migration Wizard -
• can select mult. packs to migrate.
• Wizard logs progress to a flat file.
• If invoked wizard from within BIDS, the output SSIS pckgs will be auto added to the existing project.

• Post-Migration Steps -
• Some DTS func. does not migrate well:
• Dynamic Property tasks in DTS do not get migrated. Instead the details of the dynamic property are entered into a Script task as comment lines.
• Analysis Services 2000 Processing Task not supported in SSIS. Will need to call an .exe or use Script Task.
• UDL (Universaal Data Link) files not supported in SSIS - so if DTS pckg contains a conn. pointing at UDL file conn. in SSIS will need changing.
• Data Pumps that involve scripting or certain conns (e.g. Excel) will get embedded in an Execute DTS 2000 Package Task.
• Taking Advantage of SSIS features -
• Convert any Exec DTS 2000 Tasks -> Data Flow Task.
• Change Exec SQL Tasks -> Data Flow Tasks - DTS relies heavily on relational db engine to perform transforms, and thus you can reduce need for staging tbls.
• Add error handling.
• Add checkpoints - i.e. built-in restart features, which allow you to start a package fro ma prev. point of failure.

Create a package

Use the Package Migration Wizard