While there are many possible reasons why an MDX
query of an OLAP cube may not perform as well as expected - the cube dimension
hierarchies may not be optimised for best use, the MDX query itself may require
optimisation etc - an often overlooked area is the measure group partition
definitions and the partition Slice property.
In a typical SSAS project I would
normally design an "initial" partition inside SSDT (formerly BIDS):
I would deploy this solution and create a
scheduled job to create additional partitions when necessary, depending
on the partition strategy (eg every night/week/month etc).
The Create Partition xmla script might look something like this:
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ParentObject>
<DatabaseID>MyDb</DatabaseID>
<CubeID>MyDb</CubeID>
<MeasureGroupID>MyMeasureGroup</MeasureGroupID>
</ParentObject>
<ObjectDefinition>
<Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"
xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200"
xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"
xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300"
xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300"
xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400"
xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
<ID>20150102</ID>
<Name>20150102</Name>
<Source xsi:type="QueryBinding">
<DataSourceID>MyDs</DataSourceID>
<QueryDefinition>SELECT
* FROM MyTable WHERE MyDate = 20150102</QueryDefinition>
</Source>
<StorageMode>Molap</StorageMode>
<ProcessingMode>Regular</ProcessingMode>
<ProactiveCaching>
<SilenceInterval>-PT1S</SilenceInterval>
<Latency>-PT1S</Latency>
<SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
<ForceRebuildInterval>-PT1S</ForceRebuildInterval>
<Source xsi:type="ProactiveCachingInheritedBinding" />
</ProactiveCaching>
<EstimatedRows>5000000</EstimatedRows>
<AggregationDesignID>AggregationDesign</AggregationDesignID>
</Partition>
</ObjectDefinition>
</Create>
Once deployed and processed, this
solution may work well. Users find the cube responsive
and fast. Over time, however, users start to complain that the
same daily query/report that used to run almost instantaneously is now
painfully slow. No design changes have been deployed, no
hardware modifications have been made - nothing has been altered. So
how is it that the same query now takes significantly longer?
An often overlooked area is the slice setting
for partitions. As an example of what can happen, let's look at the
AdventureWorks cube from the SQL 2012 samples (available here http://msftdbprodsamples.codeplex.com/releases/view/55330).
A good starting point for debugging queries is
often to run a SQL Profiler trace on the SSAS database and see what's happening
when this query is executed. Let's take a simple query:
SELECT [Measures].[Internet
Sales Amount] ON 0,
[Geography].[City].Members ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar].[Date].&[20080607]
[Geography].[City].Members ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar].[Date].&[20080607]
This will return the "Internet Sales
Amount" measure by City on 7th June 2008. If we use the Object
Browser in Management Studio to examine the Internet Sales measure group we see
this:
The measure group contains four partitions, one
for each year. Therefore we would expect that our query above should only
hit the one single partition, Internet_Sales_2008. Let's confirm by
viewing the SQL Profiler trace results:
The trace shows that SSAS is actually scanning
three partitions, not one - 2005, 2006 and 2008. But we know the result
set lies only in 2008, so why this extra scanning? We may want to confirm
that the results are only going to be in the 2008 partition by examining the
partition query - the SQL query used as the source for the partition data.
In this case it's been checked and they are all set up with the correct
queries and WHERE clauses.
The reason for the extra partition scans is down
to the Slice property of the partition not being set. The slice property
is used by SSAS to determine which partition(s) contains the desired data.
If SSAS knows up front where the data lies it can avoid wasting time and resources
scanning partitions that will not return any data. Let's take a look at the
properties of one of the partitions in SSMS:
We can see here that the Slice property is
blank. When left blank, SSAS uses it's own default method of determining
which partitions may contain the data. While this may sometimes be
sufficient, the trace above shows it is hardly foolproof. Far better to
actually set this property to the value we know to be valid, enabling the SSAS
engine to quickly be directed to partition containing the results.
Valid values for a slice property are an MDX
member, set or tuple. In this example the slices should be set to the
relevant year, eg:
[Date].[Calendar Year].&[2008]
If we now set this for each of the partitions
and rerun the same query we see the following in the trace file:
The engine is going directly to the correct
partition and ignoring all others. The result is a much faster running
query. I have seen examples where there were 100+ partitions, all without
the slice set. The result was that queries were taking up to 30 seconds
to complete. On setting the slices appropriately, query time reduced to
around 1 second.
Note that we can set the slice either via
properties in SSDT at design time, in SSMS properties post deployment, or better
yet, in the original XMLA we used at the start of this post to automatically
create the partitions on a schedule:
…<StorageMode>Molap</StorageMode>
<ProcessingMode>Regular</ProcessingMode>
<Slice>[Date].[Calendar Year].&[2008]</Slice>
<ProactiveCaching>….
<ProcessingMode>Regular</ProcessingMode>
<Slice>[Date].[Calendar Year].&[2008]</Slice>
<ProactiveCaching>….
So the lesson is: Always set the Slice property
:)
