0845 643 64 63

Debug MDX queries using Drillthrough in SSMS

One of the great features of using Excel to browse an SSAS OLAP cube is the drillthrough ability. If you double click on any cell of an OLAP pivot table, Excel will create a new worksheet containing the top 1000 fact records that went to make up the figure in the selected cell.

N.B. The limit of 1000 rows can be altered, as per one of my previous blog posts here.

This feature is pretty well known, but not many folk realise how easy it is to reproduce this in SQL Server Management Studio (SSMS). All you need to do is prefix your query with DRILLTHROUGH.

i.e. Assuming an MDX query of

SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE [Date].[January 1, 2004]

Which returns the following results…

A query of

SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE [Date].[January 1, 2004]

Returns the records contributing to the total figure. Great for diagnosing problems with an MDX query.

By default, only the first 10,000 rows are returned, but you can override this using MAXROWS

SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE [Date].[January 1, 2004]

The columns that are returned are those defined in the Actions tab of the Cube Designer in BIDS (The Business Intelligence Development Studio).

If no action is defined, then the fact measures will be returned along with the keys that link to each relevant dimension, which tend not to be that helpful.

You can override the returned columns by using the RETURN clause

DRILLTHROUGH SELECT [Measures].[Internet Sales Amount] ON 0 FROM [Adventure Works] WHERE [Date].[January 1, 2004] RETURN [$Internet Sales Order Details].[Internet Sales Order] ,[$Sales Territory].[Sales Territory Region] ,NAME([$Product].[Product]) ,KEY([$Product].[Product]) ,UNIQUENAME([$Product].[Product]) ,[Internet Sales].[Internet Sales Amount] ,[Internet Sales].[Internet Order Quantity]

Note that there are some restrictions on what you can drill through

  • You can’t drill through an expression/calculation, only a raw measure
  • The MDX query needs to return a single cell (otherwise the cube does not know which one to drill through)
  • The data returned will be at the lowest granularity of the cube’s fact table

To explain the last point further, the cube does not return the raw data from the underlying data warehouse, but a summary of the facts grouped by unique combination of the relevant dimensions. i.e. if a warehouse table containing individual sales (by date, product, customer & store) is brought into a cube as a fact table that only has relationships with the date and product dimensions, then the cube drill through will return unique combinations of date and product, summarising sales for each combination. Extra granularity which the warehouse may contain (customer and store) will not be available.

Note that if you specify the RETURN columns, the rows are still returned at the lowest level of the fact table granularity, even if not all of the dimensions are brought out as columns. This may result in returning multiple identical records. Don’t worry, these will be distinct facts, just differentiated by a dimension/attribute that isn’t being returned.

You can find out more on TechNet here

Frog-Blog Out

17 comments on “Debug MDX queries using Drillthrough in SSMS

  1. Pingback: Tweets that mention Debug MDX queries using Drillthrough in SSMS | Purple Frog Systems -- Topsy.com

  2. PedroCGD on said:

    Hi Alex,
    Very interesting post. I usually use this funtionality for troubleshoot purposes… but could be interesting if we can change the render of this drilldown to details…

  3. John on said:


    Is it possible to sort the results of the Return clause, for example sort by Sales Orders?


    • Alex on said:

      Hi Jon

      Interesting question. It’s not directly possible in MDX, however you can get around that by using a linked server to SSAS. Then you can wrap the MDX in an OpenQuery(), and sort the results in the SQL statement.

      Not ideal, but it would work.

      There’s info on linked servers for SSAS in another blog post at https://www.purplefrogsystems.com/blog/2010/09/olap-cube-documentation-in-ssrs-part-1/


      • John on said:

        Hi Alex,

        A linked server would not work unfortunately.
        My aim with the Drillthrough statement is to expose it as an SSAS Action command..

        Thanks for your suggestion.

        • Andrej Kuklin on said:

          Take a look at the ASSP Drillthrough, it offers much more flexibility than built-in features

  4. MAURO MARCHI on said:

    Hi Alex.
    I installed ASSP Dll..
    I found them very useful but i still have to write an ACTION with calculated measure.
    Is there a workaround to do that ???
    I used simply ASSP named GetCustomDrillthroughMDX

    My calculate measure i want to return in ACTION drillthrough is — [Margine Percentuale DrillThrough]

    I created in my cube this ACTION with this expression.

    [Vendite].[Margine Percentuale DrillThrough] AS [Margine Percentuale]
    ,[Vendite].[Importo Venduto] as [Importo Venduto]
    ,[Vendite].[Costo Vend Ultimo] as [Costo Venduto]
    ,[Vendite].[Quantita] as [Quantita]
    ,NAME([$Marca_Modello].[Codice Interno]) as [Codice Interno]
    ,NAME([$Codice_Filiale].[generated attribute 24]) as [Codice Filiale]
    ,NAME([$Tipo Documento].[Tipo Documento]) as [Tipo Documento]
    ,NAME([$DettaglioVendite].[Numero Riferimento]) as [Numero Riferimento]
    ,NAME([$DettaglioVendite].[Data Riferimento]) as [Data Riferimento]
    ,NAME([$DettaglioVendite].[Numero Riga]) as [Numero Riga]” )

    I read to create a real measure in measure group and then assign to it with SCOPE Statement , the right calculation i need.
    I did that but my expression returns always the default value i create my measure in measure group and NOT the calculation i wrote in SCOPE Statement…
    If i drag and drop the measure in Excel , it works fine so it does mean that i did right SCOPE’S Expression.
    It seems ASSP dll does not work with SCOPE assignments ????

    SCOPE([Measures].[Margine Percentuale DrillThrough]);
    THIS= IIF([Measures].[Importo Venduto]=0,NULL,(1 – ([Measures].[Costo Vend Ultimo]/[Measures].[Importo Venduto])));

    I hope my comment is clearly written and useful for others people…

    Thanks so much for any kind reply.

    • Alex on said:

      The drillthrough will always ignore the scoped assignment, so you need to make sure that the original measure (in your DSV) calculates a valid value for each row.
      So, change MerginePercentualeDrillThrough in your DSV to be
      CASE WHEN ImportoVenduto=0 THEN NULL ELSE 1-(CostoVendUltimo/ImportoVenduto) END

      Then, when viewing the cube in a pivot/MDX, the scope calculation will be used. But when you drill through, the raw calculation in the DSV will be displayed.

  5. Hello,

    It is a great post. The issue I have been facing is the Null/Empty cells are always ignored when I use Drillthrough operator the query I use as follows.

    Is there any way to keep the Null/Empty cells with Drillthrough statement ?

    FROM [CubeName]
    ,NAME([$PERIOD].[Week]) AS WEEK
    ,NAME([$PERIOD].[Month]) AS MONTH

    • Hi Seth
      Null/Empty cells in a cube are the absence of data, therefore there’s nothing to drill through to – there is simply no underlying data there so no, you’re not able to drillthrough to null values.

  6. Naresh on said:

    Hi Alex,

    Many Thanks for your Clear explanation

  7. Anub on said:

    Hi Alex,

    Greeting for the day.

    I have created 4 actions in my ssas cube. Is it possible to drill through on a particular Action using MDX.


    • Alex on said:

      Hi Anub

      Yes absolutely, I wrote a blog post about it at https://www.purplefrogsystems.com/blog/2011/02/debug-mdx-queries-using-drillthrough-in-ssms/


      • Anub on said:

        Hi Alex,

        Thank you for your quick reply.

        But i am still not clear on the answer.

        Let me rephrase the question.

        I have a SSAS cube with one measure i.e. [Device Count]

        i have created 3 different Drill-through actions
        1. Action A — Dim1, Dim 2, [Device Count]
        2. Action B — Dim3, Dim4, [Device Count] (I have marked this action as default in its properties)
        3. Action C — Dim5, Dim6, Dim7

        Now i wanted to create a MDX drill-through action on “Action C”

        i have written the below MDX query

        SELECT NON EMPTY { [Measures].[Device Count] } ON COLUMNS
        WHERE ( [REPORT DATE].[REPORT DATE DETAIL].&[20180319] )

        Please let me know where should i mention the Action name(“Action C”) in the MDX query.

        Thanks in Advance,

        • Alex on said:

          Hi Anub

          As far as I’m aware you can’t specify a particular drillthrough action. However you can define which fields you want returned within the DRILLTHROUGH query by using the RETURN statement along with a list of attributes.


  8. suxiaoyanss on said:

    Hello! I encountered a problem when using MDX for Drillthrough operations.
    The CalculatedMember cannot be drilled.
    It throws an exception: mondrian.rolap.RolapHierarchy$RolapCalculatedMeasure cannot be cast to mondrian.rolap.RolapStoredMeasure.
    I used saiku analysic. I’ve found some solutions, but they don’t suit me. I would be very grateful if you could tell me how to solve it.

Leave a Reply

Your email address will not be published. Required fields are marked *


371,433 Spambots Blocked by Simple Comments

HTML tags are not allowed.

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.


Alex Whittles
Jeet Kainth
Jon Fletcher
Nick Edwards
Joe Billingham
Lewis Prince
Reiss McSporran
Microsoft Gold Partner

Data Platform MVP

Power BI Sentinel
Frog Blog Out