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.

Package changes made and saved revert to prior state when package closed and re-opened

I copied and added an existing package as a new package to a project and have been having trouble with settings reverting to those for the original package after I modify and save the changes for the new package. Sometimes happens with the save itself, other times it happens when I close and re-open the package. Most cases are with connections that revert back to the original file reference, but there are also control flow and data flow elements that keep reverting back to either settings from the original package or defaults that result in the re-opened package being in error. Not sure how to get around this issue short of developing the new package from scratch which I'd rather not do since it is fairly complex. Any help anyone can provide is appreciated. Thanks.If I were you I'll try saving a copy of that problematic package in another folder. Open the original problematic package, and then do a save.

Then using a file-comparison tool like Winmerge, text-compare the original and copy packages. If they remain the same, perhaps something is totally wrong with your BI Studio.

Another thing to check is if those "reverting" properties are saved in an external XML configuration file. It is possible that for some reason that dtsconfig file has been set to readonly, hence your package picking up the original values over and over again.

Goodluck.|||

Thank you. The external XML configuration file was not set as readonly. I had a dtsconfig file that was common to all packages which a co-worker suggested may be the cause of my problem, but I'm not so sure that is the case. He also suggested disabling the use of a configuration file for the package I was having a problem with which I did, but I still have the issue of connection strings for connection definitions reverting to their original setting for the path and file name. This isn't too big a problem because at execution time I pass the actual path and file name in a user variable, but at definition time I have to always reset the path/filename.

I haven't tried the comparison option yet, but if disabling the configuration file doesn't solve my run time issues, I'll try that next. Thanks again.

|||Jeff, when you copy a package, you need to generate a new GUID for it. On the background of the control-flow, select properties. Then underneath the Identification section, select the Generate New ID option in the ID field. This should solve your problem.|||

Phil, Thank you for your help. I reset the package id as you suggested and also re-enabled package configurations and created a package specific configuration file. That seems to have solved the problem of data flow component definitions reverting to default values. However, I am still seeing some of the issues I had before - the file specification for a connection definition keeps reverting to that for the package from which the new package was copied. Am I misinterpreting how the connection definitions from one copy of a package to another work? Are they global to the two packages or are they independent copies? I thought that by creating a configuration file specific to the new package that the connection definitions would be independent of the source package.

I also get a message each time I re-open the new package that the connection string for the database connection and database datasource are not identical and need to be synchronized. I select OK to do the synchronization, but it doesn't seem to take.

Thanks again for your help. Any further insight you can provide is appreciated.

|||

Jeff-B wrote:

Phil, Thank you for your help. I reset the package id as you suggested and also re-enabled package configurations and created a package specific configuration file. That seems to have solved the problem of data flow component definitions reverting to default values. However, I am still seeing some of the issues I had before - the file specification for a connection definition keeps reverting to that for the package from which the new package was copied. Am I misinterpreting how the connection definitions from one copy of a package to another work? Are they global to the two packages or are they independent copies? I thought that by creating a configuration file specific to the new package that the connection definitions would be independent of the source package.

I also get a message each time I re-open the new package that the connection string for the database connection and database datasource are not identical and need to be synchronized. I select OK to do the synchronization, but it doesn't seem to take.

Thanks again for your help. Any further insight you can provide is appreciated.

Between the two copied packages, are you using different package config files? If you are sharing the same config file, then you'll run into the scenario you are describing.|||I had been using the same config file, but I created a new, separate config file for the new package assuming that was what I needed to do to make package specific changes to the connection definitions. When I created the new config file, I selected all the elements shown to be available (variables, connection definitions, etc.) and then began to make changes to connection defintions, but the changes don't seem to take. As soon as I do a save, the file specifications for connections revert to the settings for the original source package.|||Resolved most of my remaining issues by not using a config file. I realize that is not a proper solution, but for now it works. I need some additional work with config files before using them in my project.