In my current project i'm developing a cube. In this Self Service BI project i'm cooperating with Self Service BI teams, IT and end users, in order to build successful dashboards, trends and statusreports. Because of the different progress of the teams, the client wants a partial deployment of the cube. Therefore, I had a challenge because it isn't possible to deploy a cube partially easily, unfortunately. So far i haven't found anything that could do this. No 3rd party software seems to be available. So i had to come up with a pragmatic solution.
Situation
Suppose you have the following situation. There are a couple of stars in a development, acceptance and production environment (this is my current situation). The stars in the diagram below represents starschemas. The blue stars were equal between the different environments at a certain moment (t0). So the first blue star in the development area is equal to first blue star in the acceptance area and is equal to the first star in the production environment. So at one point in time the blue stars were equal. But, as time went on, some dimensions were added to the stars in the development area, new stars were created and some measures changed (t1). These are represented by the red lines. The red lines indicates a change or is new in relation to the initial situation. The diagram below is snapshot at some point in time. Some dimensions were transferred to the acceptance area, a new star has been developed in the dvelopment area and production is still equal to initial situation.
So how are you going to manage this? Well with caution, i can tell you. As said earlier the problem is the cube. The cube file is an XML file and an idea could be transferring pieces of the XML file (snippets) but in my opinion a 'big' risk and you need a thorough understanding of the structure of the XML file. I decided to solve this with manual actions and some features of BIDS helper. Below i'm going to show you how i did this.
I'm not using all the fancy stuff of a SSAS cube. I've created stars with facts and dimensions, measures (with measure groups, some calculations in the cube and a some security roles. In the solution below i'll focus on the facts, dimensions and the measures.
Solution
Below are the steps written that was needed for a successful implementation of a piece of a cube in new environment.
1. Compare the database with database diff tool (SQL compare, Apex).
In case you didn't keep track of changes in a source control software you can use a diff tool to compare databases and move the different parts to the new environment. Copy the change script into a separate folder and create a deployment script. This will be used when the code of the acceptance environment is transferred to production.
2. Load the tables in the acceptance environment.
3. Start SSAS and refresh the datasource view.
4. Copy the dimensionfiles form the SSAS project
After refreshing the datasourceview copy the dimensionfiles in the development environment and add (add exisiting item) them to the SSAS project in the acceptance environment. The dimensions are just files in he SSAS project.
The case that dimensions are stored in separate files is a big advantage because manual creating dmension in the SSAS project will very likely lead to errors. One error and a report will not work.
5. Manually change the relations in the dimension usage tab.
Make the appropriate changes. Experience learn that this an erroneous process because when a lot of dimensions and measuregroups are present it's difficult to overview the whole project. In the beginning you have to work iterative with the BIDShelper 'Printer friendly Dimension Usage' export. This explained in the following paragraph.
6. Use BIDSHelper and especially the option 'Printer Friendly Dimension Usage'.
This option prints a definition of the cube. You can find this in the context menu when you click on a cube.
It will show the following window.
And very useful is the export to excel. This enhances the comparison between two SSAS projects in the Development and Acceptanceenvironment.
7. There is also a 'Smart Diff' option available. This option compares two .cube files and analyzes the differences. You need to install Visual Studio Team System 2008 Team explorer (384 MB!) to get this working (in my case).
Below a screendump of a comparison between two cubes.
Look for changes in the measures:
8. Compare the dimensions in SSAS too
We ran in one problem that a report didn't work in the acceptance environment but did work in the development environment. Checked the cube again, try to debug the report and we were getting strange errors. Then i remembered that i changed the hierarchies in the existing dimensions (and we didn't moved them to the acceptance environment). So compare all dimensions of a star too with Smartdiff.
9. Process!
If the comparison of the cubes between development and production is successful the cube is ready for processing. Hopefully the process will succeed now.
10. Move from acceptance to production
When all scripts are gathered and are stored in a proper place, collect them and execute it on a production environment. Load the tables again and deploy the acceptance project on production and process the cube. Don't forget to set the right datasource connectionstrings. If you have a large cube deploy the cube to a test cube (in production) first. This way you can test whether the process of the cube is successful and this will not interfere with the productioncube. An option could be renaming the cube in order to minimize the downtime (didn't test this).
Conclusion
In this blogpost i've explained a partial deployment of SSAS cube from the development environment into the acceptance environment and production environment. This a manual process and there is no standard functionality available to deploy a cube partially. Most of the steps can be done by copying databasecode, dimension files and refreshing the datasourceview. The main problem is updating the cube, itself. That is a labour-intensive process.
I do realize that the method is not 100% accurate but for my current project it is sufficient for now.
If you have remarks about this approach let me know.
Greetz,
Hennie
Geen opmerkingen:
Een reactie posten