Query Performance and Partition Scans in SSAS

July 17, 2011

This blog is SUPER SUPER excited, because for the first time, after four years, we have a guest! I met Mr. Kobi Deutsch in the SQL Explore Convention held about two weeks ago. During a session we both took there, he mentioned something I wasn’t aware and I thought maybe I’d bring it to you as well. So, I’m leaving the stage for Mr. Deutsch:


Sometimes, when we check the MDX requests in the Profiler, we see that there is a partition that was scanned and loaded into memory although it is not suppose to be touched at all.
How can we know why this happened?


We have to remember that for every partition in the cube there is a slice that defines its boundaries. These boundaries must be composed of one member or a range of members only. When we run a process, the boundaries of the partitions are set as the lowest member and the highest one.The values of these boundaries are saved in the internal values of that specific partition. If we want to see what these values are, we have to look in the folder for the problematic partition.  
For exapmle in adventure works, the “FactFinance” partition



When we look inside of the INFO.XML file we get something like this



The values in the:


M_MININDEX and  M_MAXINDEX


Are the parameters that we are looking for.
We can find out the original member value by using the function DATAID (MEMBER) in the MDX WINDOW in the Management Studio.
For example:


with member CurrencyId as dataid([Dim Currency].[Dim Currency].currentmember)
select  [Dim Currency].[Dim Currency].members  on 1
,CurrencyId on 0
from [Adventure Works DW]


The DATAID value and the member value are not same and when we give some range of members to the partitions it’s not always the same in the DATAID members.
Also sometimes we have the “unknown” member that his DATAID value is always 1.  
It’s enough that we have even one record with an “unknown” member to break the boundaries of the partition and create a larger scan than what we expected.


For more details, please check a post by Mosha Pasumansky on Optimizing Partition Slices and an article by Chris Webb on Query Performance Tuning in SSAS.


On a related note, I would like to say the session both me and Mr. Deutsch attended was “Take Analysis Services to the Extreme” given by Mr. Itay Braun. I highly recommend you check Mr. Braun’s slides and blog for further information on better understanding and tuning your cube.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

2 comments

  1. Lior.Cohen.AmikamJuly 18, 2011 讘 16:58

    Hey ella
    The thing about DATAID is this : its the internal ID olap gives to every member in a dimension based on the ORDER at which it was read from the datasource at query time.
    yep.
    SO. if you are building a TIME dimension, do yourself a favour and built the DSV over a VIEW that selects from the time table using ORDER BY. ( you can use TOP 10000000 to enable ORDER BY in a view)
    Heres a link to a white paper that discusses this
    http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-analysis-services.aspx
    check section 3.5.2 Partition Slicing

    Reply
  2. Ella MaschiachJuly 19, 2011 讘 13:46

    Thank you for the input Lior. Glad you stopped by 馃檪

    Reply