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.