Documenting SSAS Configuration Changes Over Time

I hate documenting just as much as the next developer, which is one of the reasons why I spent some time last week (while on vacation…and by ‘vacation’ I mean ‘nearly breaking my neck snowboarding’ in Telluride) coming up with a method for dynamically capturing SSAS server-level configuration properties.

 (definitely not me)

Background

The other reason for this solution became obvious while performance tuning a fairly large Analysis Services scale-out query server solution. Part of the process involved tweaking the SSAS configuration file (i.e. msmdsrv.ini) and running a load-test to measure increases (or decreases) in performance. We did this over and over until we found the optimal configuration – which in this case was the one providing the most throughput (measured in queries / minute).

Note: for expert info on load-testing Analysis Services, check out both Bob Duffy’s (b | t) session from SQLBitsX (link). And Gerhard Brueckl (b | t) also has a session which unfortunately I haven’t had a chance to see, but I’m sure its also top notch (details)

Making identical changes to the SSAS configuration file across all query servers isn’t that big of a deal assuming all query servers are identical from a hardware and drive configuration perspective – just use a master msmdsrv.ini file to make changes, then copy the master ini file out to the SSAS configuration directory on each query server (hint: this can be scripted via PowerShell). The tricky part was tracking the changes from load-test to load-test.

Consider the scenario where you have 20 load-tests…

• LoadTest01 – msmdsrv.ini_01
• LoadTest02 – msmdsrv.ini_02
• LoadTest03 – msmdsrv.ini_03
• …
• LoadTest20 – msmdsrv.ini_20

…and for each load test you have a copy of the configuration file (e.g. msmdsrv.ini_09) and you have a result (e.g. 450 queries / minute). If you’re methodical, you’re only making 1 or 2 changes per load-test (fewer the better as it isolates the change and makes it easy to attribute the gain/loss to that change).

Now – keeping in mind that each change won’t always going to produce an improvement from the previous test – after 10 tests you might see results like the following…

 

At this point it’s pretty clear that test #7 is the best…but what changed and in what order?

How Did We Get Here

The quick and dirty method is to use just use Windiff.exe to compare msmdsrv.ini_07 with one of the config files from a separate test…and I can assure you that gets old really fast …and it gets VERY ugly when you try to compare more than 2 files at a time…

Instead, I’d like to propose a more dynamic method for collecting and comparing SSAS configurations across various tests. Additionally, this method can also be used to historically track changes to SSAS configurations over time – a best practice as part of an Analysis Services baseline/proactive monitoring solution.

At the crux of this solution is a nasty xQuery that shreds the msmdsrv.ini file and flattens it into a tabular format. Since the code is over 1k lines, i opted not to embed it in this post (you’re welcome! mobile readers). Instead, you can find a link at the bottom of this post containing a zip file with the sql code).

On my machine, the query returns the following result set…

…which can easily be logged to a table. Then after several snapshots, you can compare differences via basic SQL query such as the one below which compares the most recent SSAS configuration snapshot against the one prior…

DECLARE @SSAS_Instance NVARCHAR(128) = 'ANTON-PC\SSAS_MD';

;WITH
    CTE_timestamps AS (
        SELECT     Rank = ROW_NUMBER() OVER(ORDER BY CollectionDate DESC)
                ,CollectionDate
        FROM    #tmp
        WHERE    SSAS_Instance = @SSAS_Instance
        GROUP BY CollectionDate
    )
    ,CTE_LoadTest_20 AS (
        SELECT    *
        FROM    #tmp
        WHERE    SSAS_Instance = @SSAS_Instance AND
                CollectionDate = (SELECT CollectionDate FROM CTE_timestamps WHERE Rank = 1)
    )
    ,CTE_LoadTest_19 AS (
        SELECT    *
        FROM    #tmp
        WHERE    SSAS_Instance = @SSAS_Instance AND
                CollectionDate = (SELECT CollectionDate FROM CTE_timestamps WHERE Rank = 2)
    )
    ,CTE_LoadTest_18 AS (
        SELECT    *
        FROM    #tmp
        WHERE    SSAS_Instance = @SSAS_Instance AND
                CollectionDate = (SELECT CollectionDate FROM CTE_timestamps WHERE Rank = 3)
    )
SELECT     lt20.SSAS_Instance
        ,lt20.SSAS_Property
        ,lt20_Value = lt20.SSAS_Value
        ,lt19_Value = lt19.SSAS_Value
        ,lt18_Value = lt18.SSAS_Value
        ,ChangedFlag = 
            IIF(
                lt20.SSAS_Value != lt19.SSAS_Value OR 
                lt20.SSAS_Value != lt18.SSAS_Value 
                ,1
                ,0
            )
FROM    CTE_LoadTest_20 lt20
        LEFT OUTER JOIN CTE_LoadTest_19 lt19 ON lt20.SSAS_Property = lt19.SSAS_Property
        LEFT OUTER JOIN CTE_LoadTest_18 lt18 ON lt20.SSAS_Property = lt18.SSAS_Property
GO

 Obviously you’ll want to wrap this script in a stored procedure and automate the snapshot collection using some form of scripting (e.g. PowerShell) which likely depends on how you plan to use it. For example, if using it in conjunction with load-testing, you would likely just update the load-test harness to execute the stored procedure prior to beginning the load test. Or, if on the other hand, it is simply being used to track changes to server configurations over time as part of a proactive documentation and/or monitoring process then you might create the stored procedure in a way that only preserves results if at least one of the SSAS property values has changed – then you can setup an Agent job to execute the stored procedure on a recurring basis (e.g. nightly, weekly, etc)

Couple of Points

• This script works for both SSAS multidimensional and tabular instances
• the windows user account (under which the query runs) will need read-permissions on the target msmdsrv.ini file
• if the Analysis Services instance is on a separate server than the SQL instance (from which the query runs) then you will need to access the file via the UNC-path. For this you can either create a network share for the SSAS-config directory or access it using an account with elevated permissions via the c$ unc-path…e.g. \\<ssas-server-fqdn>\c$\..\OLAP\Config\msmdsrv.ini
  ** be careful with either approach in a PROD environment **
• This query was built using the msmdsrv.ini file for 2012. I have not compared it to a 2014-config file but if there are differences, I’m sure you’ll be able to figure out how to update the query (if not, let me know and I’ll make another version). For those inclined to go the extra mile, a dynamic script (e.g. C#) that parses the msmdsrv.ini file and builds the sql query would be really awesome since it would work for any msmdsrv.ini file (past, present, or future) – assuming the config remains xml. Fwiw, I do expect to see changes in future config files as Tabular continues to mature.

Here’s the script – test before using in PROD…
sqlScript-shred-msmdsrv-ini-file.zip