Steps for Seamless Migration of SSAS 2000 to 2005 Database

"Migration wizard is plagued. it's really a big job. We have to always find the loose ends and tie them. Sometimes it's really better to go for redesigning entirely from scratch." -Lin, Li (OLAP Consultant, HP)

It's really a patch work. Major part here include summary of Microsoft BI Webcast by Mike Young, Proclarity and Dave Wickert, Microsoft Corporation. After this, key lies in incorporating or adopting the OLAP Design best practices for SSAS 2005.

What Upgrade Advisor will do ?

This is the first step you must do prior to Migration / Redesign. Take a look at the article by Adam Machanic about its importance.Before getting your hands into this we must be aware of all migration issues (if migrating).

What the Migration Wizard will do?

1. First performs validation on your selected database
2. Next allows to produce a report of all known issues and suitable actions taken.
3. Finally allows to Start migrating all the objects When Migration process starts, it :Does the best it can do to recreate the same cube in SSAS 2005

• Cubes are migrated to new cubes, not perspectives
• The end result is not a “best practice” AS 2005 cube
• Cube Metadata is migrated, not the data
  Does not take advantage of the most new features:
  –Perspectives
  –KPIs
  –Translations
• Virtual Cubes are migrated into Normal Cubes, but it duplicates the cubes partly or completely and may require some attention. Clean up is essentially required.
• Named Calculations are created automatically in DSVs
• DSVs are automatically created as all tables in use from underlying data sources
• Partitions are magic, they appear to work just like we want them.
  
• Items that are not migrated include: Drill through settings, remote partitions, linked cubes
• Scripting Option ( you have flexibility to modify the scripts before migrating that can take effect after migration)

Decide whether you actually require migration or redesign based on the following questions

1. Is the migration the end result what you want? (yes, put full stop here) or else
2. Is it a starting point that you can alter later? (this’ll give rise to further questions)

• What about my existing reports (you want some sophistication? go for that)
• What key definitions I already have in place ?
• Do I have multiple source to incorporate into the process ? ( leverage multiple sources
• Collate from the migration wizard and later decide to redesign) (Yes – better go for redesign)
• Have I considered distinct count measures for design? (Too much of distinct means more patch work, so go for redesign)
• Do I have multiple virtual cubes for granularity, security and / or data set size issues ? (yes, this indicates chances of duplications, so do necessary clean up either fusing the functionality into the actual cube (in case of only single cube used for creating virtual cube) or redesign a new cube making union of fact tables and inner join with required dimensions in case of Virtual cube linking several cubes)
• What is the current relationship between my DW/ DM and number of Cubes
• How many member properties do I currently use? (Do we need extra ones, go for it?)

Get to know Common Issues known after Migration

• Additional attribute relationships are added for the next level up in the hierarchy. Don’t delete them; they are required for aggregations
• Multiple hierarchies translate to multiple dimensions and are named differently (eg: Time.calendar and Time.fiscal will most likely to turn into timeDim1 and timeDim2, (Manual work required is that we should do clean up by renaming them in BIDS)
• A prefix 'Dim' may be added to deal with duplicate name issues. Eg: Say if there were two dimensions (One Private and the other Shared) having a name Called Time in the MSAS 2000 , then the first would be named Time, the hierarchies would translate to TimeDim and TimeDim1
• Virtual Dimensions are subject to dimension merging rules (If we don’t use virtual dimensions, then migrations are going to be fantastic, these are going to be added back into parent dimensions as a named query that represents logical table representation resembling like a merging role)
• Virtual Cubes will duplicate underlying cubes. Some Cleaning is required (Refer the section below for more details how clean up can be done)
  
• All columns are automatically are created as attributes.
• Named calculations are automatically created, but named Column x which you probably want to rename (Manual work required here will be proportionally equal to the number of calculated members available)
• The Drill through settings are not migrated as the architecture in SSAS2005 has changed.
  

Top 10 Steps after Migration:

1. Import the newly migrated SSAS 2005 database (Specify a TEST 2005 SSAS Instance while migrating from SSAS 2000, Which we can use to import into BIDS Analysis Services 9.0 Project) into Analysis Services 9.0 database project in BIDS.
2. Configure your datasource to point to a test environment. Are you facing connectivity issues ? Take a look at this article, it can help find a quick resolution.
3. On the DSV convert all the Dimensions and Facts preferably into a Named query to avoid addition of unnecessary columns during refresh of the DSV. Now refresh of the DSV Metadata against the datasource show no changes, this help us to continue using the current set of columns. Make sure you make exact relations. Create one DSV per logical schema or a Mining Model . Virtual Dimensions can be identified and rebuilt as a named query here.
   
4. Since all the dimensions become shared, duplicates (Identical Copies with Array numbering on their Names) can be identified and deleted. and Those Some Array Numbered dimensions can be renamed to its meaningful name suitable for the context where it is used.
5. Make sure the properties of the dimensions and facts match with that of SSAS 2000. If there are changes in structure of dimensions or facts in underlying data marts, quick solution is to replace all of them with the named queries wherever applicable. Browsing the dimensions must show exact hierarchy as it was in legacy.
   
6. You can switch between direct edit mode connecting to the Migrated Cube lying in the Server or Importing the object offline and edit them using BIDS. You have a greater flexibility and a rapid approach if you know how to replace/ modify the legacy Object names using the Code View of every Dimensions and Cubes and check whether they use the metadata of DSV or not. More the modifications you do to DSV. More you'll be required to change here.
   
7. Dealing with Virtual cubes: If a virtual cube in the legacy points to a Single Cube but provide further role restrictions, then we can simply delete this Cube and add a new role restricting the access to required objects. If a virtual cube in the legacy still points to a single cube having additional calculated members, then we can fuse all the caculated members into one cube and provide a different perspective to the user. If a virtual cube links several cubes, then it's better to rebuild this virtual cube into a new physical cube with suggestions given by Andrew. Here Perspectives play an important role in reducing the complexity by selecting those objects that are part of a meaninful context / subject area.
   
8. Recreate the partitions if they do not work properly. Design aggregations with performance gain of not more than 30% since increasing the number of aggregations aggravates the performance.
9. Applying Drill through actions, if Set in Legacy Cubes: Create Drill through actions for those Drill through enabled Cubes in Legacy. Follow the steps for the drill through:

• For the drill through on Underlying Facts, Create a Fact Dimension, Set: “Storage Type= ROLAP”, For Every attribute except the Key attribute, Set: AttributeHierarchyVisibility = False” and include it in the cube. Note: If the reports are very slow, Set it Storage Type=MOLAP.
• For the Drill through on Underlying Dimensions, Add the additional attributes, For each one of the newly added attributes, , Set: AttributeHierarchyVisibility = False”. Here MOLAP settings is obvious, as it is already set default when dimension was created initially.

1. Following enhancements can be added : Partitioning, Proactive Caching, Translations, KPIs, Perspectives, Use of new Data mining algorithms etc. Further to incorporate best practices design, let's be aware of the steps here.
2. Deploying the validated SSAS 2005 into a Sql Server Analysis services 2005 using one of the following ways:

• MTP (Move to Production) Process: Deploying into a Test Server, Making a back up and restoring this copy into Production Analsysis Server Instance and finally processing.
• Direct deployment and processing via BIDS (using the Instance name of the Analysis Server 2005 under Project Properties -> Deployment, Specify the Server Insance)
• Deployment wizard that guides you through easy steps of Deployment.

11. Finally Process the SSAS Database pointing to the legacy datasource which helps to validate and compare the Data by browsing every Dimensions in SSAS 2000 as well as SSAS 2005. If they do not match, then we can troubleshoot based on the section we browse (say whether hierarchy of dimension goes to its lowest level and check if any related settings were missing.