Issues executing a DRILLTHROUGH with a ROLAP dimension

I recently encountered an issue using DRILLTHROUGH with ROLAP dimension and I wasn’t able to find any good information making searches about it, so I think it’s a good idea writing a few notes. Probably I will write a more complete on the DRILLTHROUGH issue later, but there is a specific issue (a bug feature by design, probably…) that can be hard to resolve.

Let’s start talking about DRILLTHROUGH. If you have some degenerate dimension (attributes in the fact table that are not measures) that you want to be part of a drillthrough action, you can add a dimension with these attributes, making it a fact dimension (or a regular one – a discussion of what is best in a future post). Now, when you process the database, this dimension is copied into the MOLAP storage, just because (by default) a dimension has the StorageMode property set to MOLAP. It works, but it requires some heavy SELECT DISTINCT on your fact table. Not a good idea with large volume of data.

At this point, changing the StorageMode setting to ROLAP might seem a good idea. In SSAS 2005/8 you can define (with the Enterprise version) that a dimension can be ROLAP even if the measure groups are MOLAP. You should pay the cost of SELECTs to the fact table only when a drillthrough action is required.

After changing the StorageMode setting, you have to reprocess the dimension and the cube. Making a drillthrough query, you might encounter an error. With Excel 2007, this error sounds like this message:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘dbo_FactInternetSales’, Column: ‘SalesOrderNumber’, Value: ‘SO43697’. The attribute is ‘Sales Order Number’.

The specific name of table, column value and attribute may very depending on the cube you are using. I’m getting this error with Adventure Works DW 2008 sample (with a modified setting, as you will read later).

If you execute an equivalent drillthrough query in SQL Server Management Studio, like this:

DRILLTHROUGH
Select  ([Measures].[Internet Order Quantity],[Date].[Calendar].[Calendar Quarter].&[2001]&[3])  on 0
From [Adventure Works]

you end up in the same error, even if the SSMS console only says in the messages panel:

Executing the query …

Server: The operation has been cancelled.

However, you can see the same error displayed by Excel by tracing the analysis services activity using SQL Server Profiler.

Well, what is this issue? The exactly same query, with the same data, works fine if the dimension storage is MOLAP. In reality, if you try the original Adventure Works DW 2008 sample, it works fine. The setting I had in a dimension of another project that produced the issue is the ErrorConfiguration/KeyDuplicate property of the dimension. If it is set to “Ignore”, like the original sample, the drillthrough works well in both ROLAP and MOLAP dimension storage settings. But if you set the ErrorConfiguration/KeyDuplicate property of the dimension to “ReportAndStop”, the drillthrough works well when it is set to MOLAP, but it fails with the error I described before when it is set to ROLAP.

I posted this behavior on the Connect site with FeedbackID 400702. But after several hours spent on this issue, I hope to save time to other developers who might encounter the same issue again.

The obvious workaround is to set the ErrorConfiguration/KeyDuplicate dimension property to Ignore. However, I hope this will be explained in a better way by MS.