onsdag den 21. december 2011

Compare Cube space in an indirect way

A couple of weeks before, as we migrated our BI solution to different environments, an important task was to ensure that everything was in sync.
This makes sense as we run separate ETL flows on a daily basis in each environment, that in turn pulls out data from their own system of records, not to mention that the SSAS databases were transferred out of our control.
How can we check that both schema and the cube space for the SSAS databases is the same ?
I was not aware of any tool that does this job. On the other side, there are tools that accomplish the task for a relation database.
So, what should do the trick : to compare schema and data for the underlying databases and compare the SSAS XMLA schemas.

I used my Visual Studio Studio 2008 Team Database Edition GDR for the first task
When started, I saw duplicate dropdown items appear under Data menu. I proceeded any way but any attempt failed with an error message like:   
"The operation is not supported within this release."
A schema or data compare between two SQL 2008 R2 databases not supported in a Visual Studio 2008 SP1 release ?
I realized that the incompatibily issue sounds misleading and that the 'error' is related to the duplicate menues.
I was afraid of the idea to repair / uninstall / install the products in the 'right' order but the following steps solved my problem :

  • I closed all instances of Visual Studio Team System 2008 editions.
  • At the Windows Command Prompt, typed the following command:
  • %ProgramFiles%\Microsoft Visual Studio 9.0\DBPro\DBProRepair.exe RemoveDBPro2008 and pressed Enter
  • At the Windows Command Prompt, typed the following command: %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe   
A word of caution here: data compare is naturally based on a primary key constraint for the corresponding tables. If your fact tables do not have a primary key constraint, then you have to figure out another way in order to compare their data.

For the SSAS part, I used the BIDS helper. I strongly recommand this free add-on to BIDS and you can download it from: http://bidshelper.codeplex.com/
I scripted first out my SSAS databases in SSMS by right click -> script database as --> create to --> and saved the XMLA files
Then I used BIDS helper "smart diff" to compare the XMLA files and tracked the differences that may influence the final result from a user perspective..

Ingen kommentarer:

Send en kommentar