Query Performance and Partition Scans in SSAS

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.