SSAS Compare by Red-Gate

This is my fifth post in a series of entry-level posts, as suggested by Tim Ford (b|l|t) in this challenge.
I have not set any scope for the topics of my, at least, twelve (12) posts in this blog category of mine, but I can assure, it’ll be focused on SQL Server stuff. This time, I want to share a really nice tool, which is totally free!

Som years back Red-Gate put out a free tool to compare SQL Server Analysis Services (SSAS) Databases. The tool is no longer updated, but still has some very useful features.

The Tool

The official download is available on this page at the Red-Gate site.

Quick Tour

With SSAS Compare, you can select SSAS databases to compare differences between cubes, dimensions, measures and other objects. You can select to compare XMLA, Project File or a live Database.

Running the application will automatically display the pop up New Comparison – if not, click the button New Comparison – Top Left Corner.

SSAS Compare New Comparison

Example – Compare Database to Database:

SSAS Compare Database to DatabaseExample – Compare Project File to Database

SSAS Compare File to Database

Example – Compare Project File to Project File

SSAS Compare File to File

Running the comparison will result in an update in the initial application – the pop up will close, and below you can see an example of a comparison of a Project File with a Live Database.

SSAS Compare File to Database OutputI have highlighted a Dimension, which the tool is displaying in bold to indicate a difference. As we can see, clearly the Attributes Comparison Weeks Order, Comparison Months Order, Weeks Order and Months Order are missing in the Live cube.

By clicking Deploy and checking the Include Processing, you can then merge the changes from source to destination – that being Project File, XMLA or Live Database.

The Downside

Obviously the biggest downside is that Red-Gate no longer updates the tool. This also means that some of the short comings will never be available, like you cannot use the application to generate a new Live Database from an existing one. The initial deploy needs to be done outside the tool. Likewise you cannot grab a Live Database and convert it to a new Project File or XMLA. For the latter, see my blog post on how to do that using PowerShell.

Being aware of the limited functionality the tool is still very useful in many scenarios – it’s part of my tool-pack for SSAS.

The complete documentation can be found here.

Leave a Reply