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

Returns a one column table that contains the distinct (unique) values in a column, for a column argument. Or multiple columns with distinct (unique) combination of values, for a table expression argument.

DISTINCT ( <ColumnNameOrTableExpr> )