Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
MSBI 49
#BI 39
BRH 39
SSRS 35
#MSBI 29
SQL Server 28
#SSRS 22
SSAS 22
#SQL Server 21
Reporting 19

Archive · View All
April 2010 8
May 2010 7
January 2013 6
November 2012 5
March 2012 5
July 2012 4
January 2012 4
October 2011 4
June 2010 4
February 2013 3

Some Random Thoughts

Set the Slice on your SSAS Cube Partitions now!

Jan 31 2012 10:36PM by Jason Thomas   

I am pretty sure that most of you would have heard or read about this at least once in your SSAS career, but how many of you actually do set slices for your partitions? Well, I am also guilty of the charge and seldom did set slices till a recent issue.

1 Setting slice in SSAS cube partitions

Situation : Duh, that’s strange!
It’s a sunny day in Charlotte and I was just playing around with SQL Server Profiler for learning some performance tuning stuff. I executed this simple MDX query on the sample Adventure Works DB for some purpose which is beyond the scope of this post -

select {[Measures].[Internet Sales Amount]} on 0,
{[Ship Date].[Calendar].[Month].&[2008]&[4]} on 1
from [Adventure Works]

Now while I was going through the Profiler trace, I noticed something strange – three partitions (2005, 2006 and 2008) were being read while I expected just one partition (for 2008) to be read as my query just had April 2008 on the rows.

2 Multiple partitions being read in Profiler trace
Now, for people who are not familiar with Adventure Works cube, there are four partitions for the Internet Sales measure group, one for each year from 2005 to 2008 as shown below -

4 Partitions in Internet Sales Measure Group

I decided to take a detour from my actual purpose to investigate this issue.

Debugging : Need to dig up that grave!
I was aware that SSAS partitions generate automatic data slicers for each partition and these information are present in the info.*.xml file of the partition data file (you can find them usually at C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data folder followed by the cube, measure group and partition name if you haven’t changed the default settings. Note that I am using SQL Server 2008 R2). I decided to have a look at these three partition data files.

4 Partition autimatic data slices

As seen above, there was clearly a difference between the partitions of 2005 / 2006 and 2008 (which are having the names Internet_Sales_2001.0.prt / Internet_Sales_2002.0.prt and Internet_Sales_2004.0.prt. These names are the partition IDs and can be seen in the ID property of the partitions)

5 Partition ID property

It seemed like the auto-slices were not getting created for the 2005 and 2006 partition while it was being done for the 2008 partition. This was further confirmed on opening and inspecting the info.*.xml file with notepad.

Knee-jerk Solution : Or is it?
I went into research mode on Google and soon came up with a  property in the msmdsrv.ini file which limits the creation of auto-slicers based on the number of rows in the partition – the IndexBuildThreshold property.

6 Modifying the msmdsrv.ini file

By default, the value is 4096 rows and both the partitions for 2005 and 2006 had less than 4000 rows. This explained why the auto-slicers were not getting created and I modified it to 1000. Now on reprocessing the measure group, I was able to see that the auto-slicers were generated in the partition data files and only the correct partition was being read in the profiler trace.

Actual Solution : The real deal!
I decided to gloat over my findings to my friend and colleague, Javier Guillen (blog | twitter) who was sitting in the next room. When I described the problem statement to him, the very first question he asked me was whether I had set the partition slices, and I was like – sheesh, I missed on that cardinal rule, as usual. I was under the impression that as long as your partition is for a single data member (as in this case – a single year), it should work. But later I found out that partitions for which autoslices are not generated will be scanned anyway, unless the partition slices are explicitly defined. I went back and set the slices for each partition correctly to their respective years and also reverted the IndexBuildThreshold property to the original value of 4096.

7 Setting the partition slices

Once this was done, I processed my measure group and found out that only the correct partitions were being read from the trace files.

Reading Materials
I took some time to read and learn more about partition slices, thought you would be interested in it too if you made it this far.

1) Get most out of partition slices - Mosha Pasumansky
2) SSAS Partition Slicing - Eric Jacobsen
3) Partition Slice Impact on Query Performance - Jesse Orosz

Tags: SSAS, MDX, #BI, #MSBI, #SQLServer, BI#BI, BI,


Jason Thomas
19 · 9% · 2997
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

1  Comments  

  • Ora, Moncler cappotto è stato introdotto in Cina da Adrienne Ma, figlia di Joyce Ma.. L'anno scorso, quando il negozio pugno a Hong Kong ha aperto, tutti i piumini sono stati venduti in diverse moncler outlet settimane in questa città calda, dove la gente solo bisogno di indossare T-shirt a svernare. I suoi colori ricchi e design alla moda attirano un sacco di fan.

    Ci sono diversi siti on-line web che offrono l'assortimento Moncler. Ci sono molti aspetti positivi per la ricerca a questi siti internet. La prima, del corso di formazione, è il risparmio. Moncler può essere un produttore speciale che è popolare per la combinazione di moda e di eseguire bene nel commercio di abbigliamento. La lana di pecora grave o cashmere può essere progettato con fa certo la qualità sostanziale moncler outlet online buona e operano fondamentale di mantenere il wunana1120 calore del corpo. A parte, i diversi disegni e modelli splendidi permettono di essere non solo un cappotto scopo, ma in aggiunta un'icona della moda ..

    D'un tratto qualcuno spinse la porta, era Annie. Anne ha detto con un sorriso, ho paura che solo, di trascorrere del tempo con te, mangiare le mele. Si sedette sul mio letto, accuratamente tagliata dalla mela. Si adatta in particolare clima Moncler neve o nebbia. Anche con il corvo che può essere moncler jackets outlet impostato per fare il lavoro e l'incrinatura stile che si desidera conto strada. Assolutamente nessuno comune una giacca Moncler, dovrebbe naturalmente nel guardaroba di ogni donna indipendentemente da cosa è inverno ..

    commented on Nov 20 2012 2:40AM
    xicheng
    2376 · 0% · 5

Your Comment


Sign Up or Login to post a comment.

"Set the Slice on your SSAS Cube Partitions now!" rated 5 out of 5 by 2 readers
Set the Slice on your SSAS Cube Partitions now! , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]