THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

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 setting. 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.

Published Saturday, January 17, 2009 1:21 PM by Marco Russo (SQLBI)
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Akshai said:

Hi Marco,

The explanation is that the KeyDuplicate error was intended to catch situations where there were referential integrity issues that can be exposed because of the SELECT DISTINCT style of query.

However, it cannot work consistently when the query is not using SELECT DISTINCT -- which can happen for a drillthrough degenerate ROLAP query (because we want to scan the fact table and build the dimension members using the same query). It can also happen for MOLAP if you turn the property ProcessingGroup to ByTable, and also when an attribute has a Binary binding (SQL couldn't successfully execute SELECT DISTINCT on a Blob).

In AS 2005, IIRC the default value for KeyDuplicate errors was to warn for them but not to fail -- we changed this in AS 2008 to help customers find poorly defined attribute relationships, but it cannot work in all cases. The best option would be to show another AMO warning in BIDS if the dimension is ROLAP or the ProcessingGroup is set to ByTable, but that solution didn't make it in.

Hope this helps in understanding the situation.

Thanks,

Akshai

January 17, 2009 3:05 PM
 

Marco Russo (SQLBI) said:

Akshai, your explanation is a valuable resource!

Is this documented somewhere? If not, can I refer to this in a book about SSAS I'm co-authoring?

Marco

January 17, 2009 5:44 PM
 

Akshai said:

Hi Marco,

I believe some of this is in the old Perf Guide (it talks about ByTable but not ROLAP/BLOBs), but certainly feel free to explain it in your book.

Thanks,

Akshai

January 17, 2009 8:27 PM
 

Marco Russo (SQLBI) said:

Thank you very much!

January 18, 2009 3:14 AM
 

Steve P said:

OK, that's damn useful.

I've been struggling with this all weekend. My drillthrough worked fine when MOLAP but died when ROLAP. Just changed those settings and its now working when ROLAP. Is it me or is the new drillthrough support a backwards step, what's with the useless column names and random column ordering?

Many thanks for the posting, it has spared my sanity.

June 29, 2009 8:04 AM
 

Marco Russo (SQLBI) said:

Steve,

you can change column order by editing the XML cube file with a text editor (Visual Studio is good too). However, after you change the column order, you have to be careful - any drillthrough change made with GUI editor will lose your custom order.

Marco

June 29, 2009 8:31 AM
 

Steve P said:

I'm still hating the new drillthrough. You can't format dates properly, they come trhough with 00:00:00 on the end and the whole ROLAP degenerate dimension just seems to break down and die when your data sets get really large.

I've posted some details here, hopefully an answer from MS will be forthcoming

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/905bc23e-5cbf-4dcd-bb6a-0ab4ebd5e4d1

regards

Steve

July 14, 2009 9:40 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement