Packaging changes to a cube - what are my options?

I have some changes I want to make to an existing cube - that is, I'd like to add some Measures and Dimensions to the Team Foundation Server cube. I would like these changes to be applied in one quick process, where the system administrator can just run an MSI that makes the changes.

I looked at XMLA but there doesn't seem to be much support for making incremental changes to the cube. Is AMO the way to go? How have people dealt with this in the past?

Suggestions appreciated,

Paul

Hey Paul,

AMO is probably the way to go. It still uses XMLA when it talks to SSAS, but it will handle getting the current structure from SSAS and building the appropriate scripts. You're right, there is not much in the way of incremental support. SSAS seems to have taken the approach of saying "send me how you want the database to look and I will figure out what needs to be changed".

If you are adding brand new dimesions you might be able to use a hybrid approach. You could script the new dimensions as XMLA and execute them via AMO using the server.Execute() method. But as far as adding these dimensions and new dimensions to existing cubes - I would definitely use AMO, this should cater for cases where they may have made slight changes to the cubes (eg. service packs?) so you can apply your new stuff without regressing the cubes.

A couple of things to consider:

The person running the msi would need to be in the administration role for SSAS or be in a role that has Full Control of the TFS SSAS database. I'm not sure off the top of my head how you test for this, but if you need help, let me know and I'm sure I can figure it out. If you alter the cubes like this you will need to consider the effect it will have on the aggregations, altering a cubes structure will cause any existing aggregations to be dropped and you will most likely need to re-design these.