Speeding up SSAS Cube Browsing in SQL Server Management Studio

We have a client with whom we have been developing an OLAP cube over the past 3 years. Over this time the cube schema has grown to include many measures, and critically, a small number of complex time-related calculated measures which need to be calculated down at the grain of the data and then summed back up.

Over time, and as the cube has been loaded with more and more data, and on a daily basis we found that the response time when browsing the cube in SQL Server Management Studio (SSMS) for the first time after processing had been getting worse and worse – to the point where starting a cube browsing session became a good cue to go and make a cup of tea! Subsequent attempts to browse the cube led to an almost instant response, but something was clearly happening and we suspected it was cache related. Thankfully this was not an issue for the client, who uses Reporting Service and Excel to access data in cube, but it was frustrating for our own debugging and checking purposes.

We set up a profiler trace to see exactly what MDX was being executed when the cube was browsed through SSMS. Very quickly we found an MDX statement being executed by the act of browsing in SSMS that looked like it may be the culprit:

SELECT
FILTER(
[Measures].MEMBERS
,
[Measures].CURRENTMEMBER.MEMBER_CAPTION =
        Measures].CURRENTMEMBER.MEMBER_CAPTION
) ON COLUMNS
FROM
[Our Cube]

Executing this on a cold cache as an MDX query in SSMS led to a long-running query of almost precisely the same time as that taken to be able to browse the cube. And as expected, subsequent executions resulted in an almost instant response. This little snippet of XMLA code to clear the cache came in handy here, and replicated the cold-cache conditions that would be experienced directly after cube processing.

Dissecting the MDX query, it returns the value for every single measure in the cube at the root of all dimensions. We’re not quite sure why it takes this approach, what it does with the results, nor why it performs that rather odd FILTER statement with an expression that should always evaluate to true. However we could see that this was causing some of the more complex calculated measures to be calculated across the entirety of our date dimension right down at the day level which was not something they were ever designed for!

One option would have been to add scoped MDX code to simply return a NULL if these measures were requested at the root of all dimensions but this would require regular maintenance as new measures were added (and felt a bit like a bodge!). Thankfully an alternative option stood out – this seemed a perfect situation to employ a cache-warming strategy, i.e. performing this query automatically as part of the SSIS job that loads and processes the cube, and executed immediately after cube processing. This would pre-calculate the result as part of the load process and then have the results sitting nicely in the cache ready to give an almost instant response to browsing the cube in SSMS (provided that the results had not been subsequently pushed out of the cache by intervening queries!).

We decided that the fastest way to add a step to execute this MDX was to use an Execute SQL Task in SSIS to call SQL stored procedure in the warehouse database which generates a dynamic SQL statement (a fancy term for string concatenation!). This SQL then uses OPENQUERY against a linked SSAS server to execute an inner MDX statement. This is a somewhat roundabout approach, and I’m sure there are more direct ways of accomplishing this from SSIS, but it was an existing pattern that we understood and were already using.

The meat of the resultant stored procedure looked something like this:

DECLARE @LinkedServer varchar(50);
DECLARE @MdxQuery varchar(500);
DECLARE @SqlQUery varchar(500);
SET @LinkedServer = ‘OURLINKEDSERVER’;
SET @MdxQuery = ‘
SELECT
[Measures].Members ON 0
FROM
[Our Cube]’
SET @SqlQuery = ‘
SELECT 
*
FROM
OPENQUERY(‘ + @LinkedServer + ‘, ”’+ @MdxQuery + ”’)’
— Execute query against the cube
EXEC(@SqlQuery) END

As you can see, we simplified the MDX a little to just return a value for each member of [Measures] without the additional FILTER generated by SSMS. Within SSIS we then added an additional Execute SQL Task on successful completion of cube processing to execute this stored procedure. The aim being that when the cube was first browsed, it would have already cached the results of that long-running query, and the response would be close to instant.

And we are pleased to report that this seems to have done the trick. As mentioned above, any querying that occurs between this cache-warming procedure being run and browsing the cube for the first time in SSMS may push the results out of the cache, but in practice we have not seen this happen yet.

Thanks to Chris Webb for planting the seed in our head to think about cache-warming. We can thoroughly recommend his article on building an “intelligent” cache-warmer in SSIS.