1

Summary :I m involved in a project that requires us to update/upgrade a existing cube programmatically. Is this even possible (apart from using AMO)?

Details: We have a cube that deploys to all client environments via a installer. Now as we continue to develop, we make changes to the cube, like changes in calculated measures, adding a new level to a dimension or editing an existing level/measure. we need to deploy these changes to client environments in the form of an updates.

Now these environments are not directly accessible by us nor do they have bids installed, meaning, we cant use bids to make changes and deploy it to the prod environment. Hence the requirement of a script/s to accomplish it.

Is there an approach that can enable to release these updates to the cube programmatically (not via AMO)? eg: a reporcess of a cube can be triggered in the form of a xmla statement.

We also need to be considerate of any customization/s that the client would have made (like addition of measures or levels for a given dimension) and preserve them.

Please let me know if i have clearly explained the issue at hand.

Thanks Srikanth

user3198708
  • 37
  • 1
  • 9

1 Answers1

1

Instead of AMO, you can also directly issue XMLA ALTER statements. Actually, AMO converts everything to low level XMLA as well, which are then sent to the Analysis Services server. However, the official documentation of the XMLA ALTER statement at http://msdn.microsoft.com/en-us/library/ms186630.aspx is difficult to read. It would be easier to capture the XMLA statements resulting from the AMO issued by BIDS when you click deploy. You can do this via SQL Server Profiler as documented here: http://technet.microsoft.com/en-us/library/ms174946.aspx.

And, as soon as you have more than a few trivial changes, it may be much easier to re-deploy the complete Analysis Services database instead of capturing just the changes and trying to create ALTER statements.

FrankPl
  • 13,205
  • 2
  • 14
  • 40
  • Thanks for the response. I did consider the xmla via the trace option, however, however running the alter xmla statements would wipe out any customizations that the client has made ( in terms of new levels, measures added), would it not? how do i account for those? – user3198708 May 02 '14 at 17:55
  • You can issue `alter` statements on different levels of objects. However, if you need to merge independent changes from two sides, I would not think there is any simple solution. That will be a lot of effort in any case. – FrankPl May 05 '14 at 14:33
  • Thanks Frank. From what i have searched, the amo calls are the best way to go about it. Using XMLA can work only in certain cases and this is considering the customization's at clients. – user3198708 May 20 '14 at 12:28