THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Drillthrough improvements: call for feedback!

We all like Analysis Services, but when it comes to drillthrough, there are a number of issue that we like to be fixed. I added them in a feedback item on Connect – so please vote it and give to drillthrough a chance to improve in future releases!

Here is the list of the required improvements I would like to see:

  • Enable editing of drillthrough columns order. They can be arranged in any order you want inside the cube XML file, but editor in BIDS forces you to put all the attributes of a dimension near each other, without allowing to change the order of columns of the same dimension. Moreover, you cannot move dimensions up and down.
  • Enable use of properties in drillthrough and don’t limit the drillthrough only to those attributes with AttributeHierarchyEnabled set to true. I don’t know if there is a structural problem, to me it seems more like a semantic one.
  • Enable use of drillthrough on calculated members. In my opinion, who write calculated members has the knowledge to write a tuple describing the subset of the cube that you want to drillthrough. A dynamic MDX expression evaluated for each drillthrough request (just like the dynamic MDX caption) would be the best.
  • Fix behavior of drillthrough with semi-additive measures (or offer a way to customize it - see drillthrough on calculated members). Drill-through on semi-additive measure returns rows that are not considered in the value displayed. For example, a LastNonEmpty of a year returns all the year rows instead of the last day/month available. Best option, in my opinion, is to customize drillthrough for each measure if required, just as suggested for calculated members.
  • Optimize drillthrough on ROLAP dimension. If a drillthrough uses attributes from a ROLAP dimension, there is a SELECT DISTINCT over the whole dimension table if it has a regular relationship with the measure group, followed by a complex SQL statement which ends in filtering all the fact table. If the ROLAP dimension has a fact relationship (imagine a degenerate dimension with data as Order Number), the SELECT DISTINCT is not executed, but there is still a complex SQL query. When we have a surrogate key used as a primary key in the fact table (which is the case in most of data marts I’ve seen on SQL Server), it would be way faster getting the set of ID’s obtained by the MOLAP measure group and filtering the fact table with them.
Published Wednesday, January 21, 2009 12:34 PM by Marco Russo (SQLBI)



Chris Webb said:

I was thinking about this problem too: wouldn't it be good if you could create a named query in the DSV, then use it in a drillthrough? You'd have the flexibility of writing your own SQL which returned anything you wanted; you'd also want to be able to parameterise it or have SSAS use it in a subselect or something. Perhaps you could use MDX expressions to supply the parameter values.

The other thing you could use parameterisable named queries for would be for partitions. You'd set up a parameterisable named query as the template query for your partition, and then when you were creating your monthly partitions on your measure group all you'd have to do would be to supply a new set of parameter values rather than have to manage a whole SQL SELECT statement bound to the parameter.

January 21, 2009 4:15 PM

Marco Russo (SQLBI) said:

Good suggestion. You might want to add it as comment in the Connect item.

January 21, 2009 6:04 PM

Michael Brönnimann said:

Wouldn't it be an option to extend the ASSP project (codeplex)?

The assembly based drillthrough first just retrieves the 'primary row keys', which then are passed internally into a stored proc or udf on a separate sql connection. The SQL based udf or stored proc could then re-join all wanted lookup tables and also controls the column order, column naming and the row sort order. The result set retrieved this way by the assembly function will then be returned as 'rowset' to the calling code (drillthrough action in MDX cube script).

February 11, 2010 6:52 PM

Ian said:

I am quite often asked to provide solutions to DrillThrough Functionality when using Excel PivotTables via SSAS.

Now with the new PowerPivot software people are even more keen to see this, it is already so much easier to "pull", "relate" and do some analysis on large volumes of data / information that with more powerfull drillthrough capabilities PowerPivot will be even more excellent.

May 16, 2010 1:13 PM

Maria Balsamo - SQL Server Development Team said:

Marco,  I work for the SQL Team and I love your blog posting.   It's so helpful of you to record your suggestion in Connect and get people to vote on your connect item via your blog posting.  Well done!  

May 19, 2010 6:04 PM

Marco Russo (SQLBI) said:

Maria, it's nice to see that Connect items are considered by MS too! :)

May 19, 2010 6:21 PM
New Comments to this post are disabled

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



Privacy Statement