Analysis Services 2005 migration tool: Custom member formula issues in migrated database

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.

1 comment:

Ritesh said...

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.


My Articles


Cube structure optimization for MDX query performance in Analysis Services 2005 SP2: Tips for Parent Child Hierarchies usage

Fact table design for “State Workflow Analysis”: Analysis Services Dimensional modeling

Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip

Identifying intra-dimensional members relationship and reducing cube sparsity in Analysis Services 2005 SP2 : Cube design tip

Leaves() : An example to understand it for both regular hierarchies as well as parent child hierarchies

Aggregation design: useful tips

Level based attribute hierarchy: MDX query performance woes in SQL Server 2005 SP2: Is it fixed in post SP2 hotfix?

Parent child hierarchy to level base hierarchy conversion: hiding placeholder dimension members in client application

Trouble / Troubleshooting

Aggregate(), Sum() functions using calculated members does not work in Analysis Services 2005 SP2 (9.00.3042.00 version) but works in Analysis Services 2000 SP4

Analysis Services 2005 migration tool: Custom member formula issues in migrated database

Cube Partitions: Fact table not listing in Business Intelligence Development Studio in partition wizard

Analysis Services 2005: Many-to-Many relationship does not support unary operators with parent-child dimension


NextAnalytics and MDX : Part 1 - Swap Cells with Row Labels

Selecting dimension's default member based on a member property

Sorting members on member codes / member properties

Time Dimension: How to set Default Member to Current Month

Setting dynamic default member in dimension X based on the current member of dimensions Y


Code : utility code for converting cellset to a data table


Google specialized search for Analysis Services and MDX web resources integrated in my blog

Art of reading MDX articles

MDX Expression Builder : Need for a tool making it easier for functional users to write MDX expressions, queries.