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.

Error Processing a Cube: Key cannot be null. Parameter name: key

I recently opened a new project in SSAS 2008 R2. After finishing developing the cube, I deployed and processed it on the server. I got a really weird error message that meant, unfortunately enough, nothing to me:

"Key cannot be null. Parameter name: key"

So what's a girl to do? Well actually, I remember that every so often when I was developing a cube, I would have a problem with the data connection. The default I had defined for me when I opened a new connection was .Net Provider\SqlClient Data Provider.

And somehow the default definition would always create an error in the process of the cube...
So I decided to try that first. Changing the data connection to Native OLE DB\SQL Server Native Client 10.0.

Deploying and processing it worked. I love me a good short fix! :)

Update July 14^th, 2011:
Another member in my team found this solution applicable to his problem. He had a connection string that was defined as: Native OLE DB\Microsoft OLE DB Provider for SQL Server. What he got was a process that gave him all the labels for all his members as "???" (question marks). That's even though right clicking the table in the DSV and chosing Explore data showed the Hebrew correctly. Locale Identifier was already set to 1037. I wondered if it was a matter of collation... no, it was redefining the data connection to Native OLE DB\SQL Server Native Client 10.0. I'm hoping for better error messages. One day...

SQL Server 2008 R2 SP1 download released

Yes, you read it right, Service Pack 1 to SQL Server 2008 R2 was just released. This service pack (as ever) is a bundle of all the cumulative updates thus far for SQL Server 2008 R2. It includes, amongst others, the following things:

And while you're looking into the SQL Server 2008 R2 SP1 download, you can also check out the Denali CTP3 download as well (they've been busy as hell in that team lately ;))