7

Does anyone know of an SSAS specific tool that compares SSAS databases? Not their data, just schema and maybe partition definitions. When I say tool - something that knows about SSAS and has that business knowledge built into it.

Jeff Maass
  • 3,632
  • 3
  • 26
  • 30
  • What is the schema representation of SSAS databases? Is it a relational database? – Ira Baxter Oct 15 '10 at 16:56
  • The DDL language for SSAS is XMLA ( http://technet.microsoft.com/en-us/library/ms187152.aspx ). SSAS is not a relational database ( http://www.microsoft.com/sqlserver/2008/en/us/analysis-services.aspx ) – Jeff Maass Oct 15 '10 at 17:36
  • tried to make question more specific - wanting a tool that knows about SSAS, its quirks, etc. – Jeff Maass Nov 10 '10 at 13:28

4 Answers4

8

I'm a Product Manager at Red Gate. We have just released a free preview of SSAS Compare. This allows you to review, script and deploy changes between SSAS instances.

http://www.red-gate.com/products/bi-tools/ssas-compare/

Adam Lear
  • 38,111
  • 12
  • 81
  • 101
Nick Sutherland
  • 96
  • 1
  • 1
  • I haven't used this tool, but I've been using RedGate tools since the original backup tool and have always found their tools to be top notch. I'm sure this new tool is that awesome. – Jeff Maass Aug 11 '12 at 22:21
3

You can use BIDS helper (the free add-on to BIDS): http://bidshelper.codeplex.com/

This has "smart diff" option which allows different files (such as SSIS packages and SSAS XMLA schemas) to be compared. Smart diff provides more meaningful comparisons when comparing XML files.

So first you need to script out your DBs in SSMS by rclick > script database as > create to

then in BIDS use smart diff to compare the XMLA scripts to see the differences.

Stagg
  • 2,660
  • 5
  • 34
  • 32
  • I will have to give this a try, and see what the diff for an SSAS XMLA looks like, but this sounds like a super answer. Producing meaningful differences between .dtsx files would be wunderbar as well. – Jeff Maass Nov 10 '10 at 13:29
1

Depending on what you want to compare you could also script a simple comparison tool using amo (http://technet.microsoft.com/en-us/library/microsoft.analysisservices.aspx). Given you just want to compare a few attributes this could be a lightweight approach.

sarnu
  • 363
  • 1
  • 2
  • 8
  • Thank you for taking the time to answer! This is something I had considered, but this is an ancillary project so I was hoping for more of a tool that had already picked out the important differences. I can say this for the AMO approach - I'm pretty sure that AMO is used to generate the XML text files others suggested scripting, then comparing. So, you're right in thinking that this might be lightweight - with fewer bits to compare. Though, I'd be afraid, I'd ignore the wrong bits. Hence, the search for a tool. – Jeff Maass Nov 10 '10 at 13:26
0

It's crude but you could extract the definitions of each database, either with Visual Studio (BI Studio/Reverse Engineer option when creating a new project) or with another XMLA capable tool, and then run a visual diff on the XML files. The XML cube definitions are reasonably straight-forward to read if you're familiar with SSAS.

Have you tried this?

Sir Wobin
  • 1,080
  • 1
  • 8
  • 12
  • I have, and found it to not be useful. Which was the primary reason for the word in the question, "tool" Thank you so much for taking the time to answer! – Jeff Maass Nov 10 '10 at 13:21