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

DRILLTHROUGH
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

DRILLTHROUGH MAXROWS 500
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

16 Responses to Debug MDX queries using Drillthrough in SSMS

  • Pingback: Tweets that mention Debug MDX queries using Drillthrough in SSMS | Purple Frog Systems -- Topsy.com
  • 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…
    regards,
    pedro

  • Hi,

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

    Cheers!
    Jon

    • 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/

      Thanks
      Alex

      • 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.
        Jon

        • Take a look at the ASSP Drillthrough, it offers much more flexibility than built-in features
          http://asstoredprocedures.codeplex.com/wikipage?title=Drillthrough&referringTitle=Home

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

    ASSP.GetCustomDrillthroughMDX(”
    [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 ASSIGNMENT :
    SCOPE([Measures].[Margine Percentuale DrillThrough]);
    THIS= IIF([Measures].[Importo Venduto]=0,NULL,(1 – ([Measures].[Costo Vend Ultimo]/[Measures].[Importo Venduto])));
    END SCOPE;

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

    Thanks so much for any kind reply.

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

  • 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 ?

    DRILLTHROUGH MAXROWS 10000
    SELECT
    FROM [CubeName]
    RETURN
    NAME([$STANDARD ATTRIBUTES].[Comp]) AS COMPANY
    ,NAME([$GEO].[AREAS]) AS AREAS
    ,NAME([$PROD].[PRODUCTS]) AS PRODUCT
    ,NAME([$PROD].[MARKET]) AS MARKET
    ,NAME([$GEO].[SUBTERRITORIES]) AS SUBTERRITORIES
    ,NAME([$PERIOD].[Week]) AS WEEK
    ,NAME([$PERIOD].[Month]) AS MONTH
    ,[SALES].[UNITS] AS [UNITS]
    ,[SALES].[VALUES] AS [VALUES]

    • 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.
      Alex

  • Hi Alex,

    Many Thanks for your Clear explanation

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

    Thanks,
    Anub

    • 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/

      Alex

      • 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

        DRILLTHROUGH
        SELECT NON EMPTY { [Measures].[Device Count] } ON COLUMNS
        FROM ( SELECT ( { [REPORT DATE].[REPORT DATE DETAIL].&[20180319] } ) ON COLUMNS FROM [SA_Cube])
        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,
        Anub

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

          Regards
          Alex

Leave a Reply

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

HTML tags are not allowed.

347,963 Spambots Blocked by Simple Comments

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Data Platform MVP

Frog Blog Out
twitter
rssicon