At the MTC’s we’ve worked with a few customers interested in discussing how they could migrate existing cubes from Essbase to SQL Server Analysis Services.  In one particular instance we did a proof of concept that involved manually converting one of the customer’s more complex cubes.

It’s worth noting that there is at least one tool on the market from a Microsoft partner called Exologic that will automate the migration process and it seems to do a good job of converting objects over.  However, in this particular case the customer wanted to understand the complexities involved and see how difficult the process would be to rebuild the cube from the ground up in Analysis Services.

One of the difficulties that we initially encountered was in implementing the shared members from Essbase in Analysis Services.  A shared member essentially means that the dimension member needs to appear in more than one place within the hierarchy.  One example of this would be an organization where employees have one manager but may have a “dotted-line” relationship to another manager as well.

The traditional way to accomplish this has been to use the Custom Rollup feature in SSAS.  In this particular POC though, we discovered that due to the design of the cube, amount of data, and number of shared members the performance of the custom rollup was not acceptable.  It was taking nearly a minute to run one particular query that used the custom rollup dimension.

Instead, we ended up using a many-to-many dimension to implement the shared members.  That improved performance of the query in question significantly - with the new design it ran in less than one second.

The SQL Customer Advisory Team has done a detailed write-up describing the approach from a more general standpoint.  It’s a good article and should be helpful if you have a situation where you need to migrate Essbase shared members to SSAS.

-Scott Hulke, Microsoft Technology Center – Dallas