SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to compare two SSAS databases with a custom MDX query

[2017-Jan-29] It came as a request from one of my customers where I had worked as a consultant to quickly compare two similar SSAS databases located in different environments. So I quickly ran an MDX query with some key metrics against each of the SSAS databases and presented the results to the customer. Further along the way, they asked me if I could make it as configurable tool with options to change SSAS servers, database names and possibly adjust some main filtering criteria.

I thought that it would be possible and decided to created an Excel workbook with two OLEDB data connections to SSAS databases with customer MDX queries and additional configurable parameters to adjust those data connections.

There is a very good article that explains how to create a custom MDX query based on a data connection in Excel (Excel – Using a Custom MDX Query in Excel Table) by Gilbert Quevauvilliers, which is not very intuitive. With that thing covered I also needed to get a full list of my cube metrics to build an MDX query (i.e. manually retyping them wasn't the case). SSAS dynamic management views could be very handy for this; with a lot information about SSAS DMVs available I'd recommend this article (SSAS: Using DMV Queries to get Cube Metadata by @BennyAustin) with some basic metadata extract examples already prepared.

In order to show you this work-case I've used the [Adventure Works Internet Sales Tabular Sample Database] and deployed it to my testing SQL Server SSAS 2016 environment. You can adjust your test case and can use your SSAS database either in multidimensional or tabular modes, because MDX data extract logic could be used in both cases.



Then I created this Excel file (SSAS Compare.xlsm) with all dynamic SSAS data connections and MDX queries to support my data compare results, make sure to enable Macro settings in your Excel.



Basically, it's all being done by the use of two SSAS OLEDB connections within this Excel file where I change Connection Strings to SSAS and Command Text for MDX query.




so then it was very easy to create a macro within this Excel workbook and let the configuration magic works:


 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
    ActiveWorkbook.Sheets("MDX Query").Select
strMDX_Query = Range("A1").Value

ActiveWorkbook.Sheets("Compare").Select

'' Period settings
strMDX_Period_1 = Range("B7").Value
strMDX_Period_2 = Range("E7").Value

'' Server settings
strSSAS_Server_1 = Range("B5").Value
strSSAS_Database_1 = Range("B6").Value
strSSAS_Server_2 = Range("E5").Value
strSSAS_Database_2 = Range("E6").Value

With ActiveWorkbook.Connections("SSAS_Environment_1").OLEDBConnection
.Connection = "OLEDB;Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" + strSSAS_Database_1 + ";Data Source=" + strSSAS_Server_1 + ";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error"
.CommandText = Replace(strMDX_Query, strMDX_Filter, strMDX_Period_1)
End With

With ActiveWorkbook.Connections("SSAS_Environment_2").OLEDBConnection
.Connection = "OLEDB;Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" + strSSAS_Database_2 + ";Data Source=" + strSSAS_Server_2 + ";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error"
.CommandText = Replace(strMDX_Query, strMDX_Filter, strMDX_Period_2)
End With

ActiveWorkbook.Connections("SSAS_Environment_1").Refresh
ActiveWorkbook.Connections("SSAS_Environment_2").Refresh

MsgBox "Compare has been finished successfully.", vbInformation, "SSAS Measure Compare"

As a result I was able to dynamically configure SSAS servers and databases; also, I had my MDX query that I used to test for both SSAS data connection and I also added a filtering criteria to enhance this scenario. Feel free to change this file (SSAS Compare.xlsm) any way you want to in order to accommodate your own compare test case.

And have a happy data adventure!

Data Adventures

My personal journey in an intricate world of data and continuous effort to make it more structured and well understood can be found in this blog.

I live and work in Canada - see my profile on LinkedIn.

Comments

Leave a comment on the original post [datanrg.blogspot.com, opens in a new window]

Loading comments...