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

Drillthrough for #BISM Tabular and attribute keys in #SSAS #Denali

In an Analysis Services cube you can define drillthrough actions on a cube. This feature has been working since SQL 2005 and now it will be available in both BISM Multidimensional and BISM Tabular in the next version of SQL Server “Denali”.

In the current CTP3 you can already use the drillthrough on a Tabular model: by default, all the columns of the table containing the measure definition you are asking for drillthrough are returned. Even if the BIDS user interface doesn’t have any feature to do that, you can send XMLA statements to your Tabular model in order to create the desired Drillthrough Action. In this way, Excel will show to the end user alternative drillthrough actions and you can also override the default drillthrough action! This is particularly important in case you want to include columns from related tables of your data model.

The XMLA syntax you have to use is the same used by BISM Multidimensional. There is another good news for Multidimensional projects in Denali: you can ask for attribute keys instead of description of attribute name in a drillthrough action! We can finally obtain the underlying (almost always hidden) key of an attribute value. There is no support in BIDS user interface, but you can modify the XMLA code in your model.

WARNING: hand-editing of XMLA script for tabular models is not supported. The right (and supported) way to handle this is:

  1. Alter a DB already on the server (not the workspace database, a deployed database).
  2. Use the Import from Server (Tabular) project template to bring that altered model back into the designer.
  3. The metadata structure of the model should be preserved at that point and you should be able to keep authoring. This is a hacky solution, but the UI supports it.
     

This is an excerpt of the code for a standard drillthrough action in Adventure Works that extract the Promotion name attribute:

<Action xsi:type="DrillThroughAction">

    <ID>Drillthrough Action</ID>

    <Name>Reseller Details</Name>

    <Caption>Drillthrough...</Caption>

    <TargetType>Cells</TargetType>

    <Target>MeasureGroupMeasures("Reseller Sales")</Target>

    <Type>DrillThrough</Type>

    <Default>true</Default>

    <Columns>

        <Column xsi:type="MeasureBinding">

            <MeasureID>Sales Amount 1</MeasureID>

        </Column>

        <Column xsi:type="CubeAttributeBinding">

            <CubeID>Adventure Works</CubeID>

            <CubeDimensionID>Dim Promotion</CubeDimensionID>

            <AttributeID>Promotion Name</AttributeID>

            <Type>All</Type>

        </Column>

 

If you change the <Type> node, you can obtain the key instead of the name value:

        <Column xsi:type="CubeAttributeBinding">

            <CubeID>Adventure Works</CubeID>

            <CubeDimensionID>Dim Reseller</CubeDimensionID>

            <AttributeID>Reseller Name</AttributeID>

            <Type>Key</Type>

        </Column>

This syntax is accepted by previous version of Analysis Services, but it is simply ignored.
Returning an attribute key is useless in Tabular, because each column has just one value and there is no concept of “related key”.

A big thank you to Akshai Mirchandani and Cathy Dumas for sharing the technical details about this behavior.

Published Thursday, August 18, 2011 12:18 AM 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

 

Greg Galloway said:

If you feel strongly there needs to be a UI for defining which columns appear in drillthrough results for a Tabular model, then vote for this BIDS Helper feature suggestion:

http://bidshelper.codeplex.com/workitem/31273

August 18, 2011 10:47 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