SQL Server Management Studio (SSMS) 2005 has a great feature for migrating the Analysis Services (AS) database from AS 2000 to 2005.
In AS 2000, since there is only one hierarchy per dimension and if the member name unique property is set to false, each member is represented by "[Dimension Name].&[Member Key]" notation.
When the database is migrated from 2000 to 2005, the migration wizard creates the dimension as well as the hierarchy with the same name as well as the ID. (In AS 2005, each object has a name as well as in internal ID).
However, when you create the dimensions manually, or deleted and recreate the dimension, the ID is different for the hierarchy. Now, all the calculated members whose formula is stored in the dimension table stop working since for calculation, the formula needed by AS 2005 is [Dimension Name].[Hierarchy Name].&[Member Key].
So, for the migrated AS database, we need to insert the hierarchy name in the custom member formula in the dimension table.
It is a big work when a lot of custom member formulas are existing.
Posts
1 comment:
Hi do you have any examples for this.
We have lot of dimension level MDX formulas and currently if we are migrating them as it is from 2000 to 2005 it is taking more than 48 hours to process the dimension.
Same dimension processes in less than 5 minutes in SQL 2000.
Any help is welcome.
Thanks
Post a Comment