Extending and Customizing Cubes - SQL Server Analysis Services 2012 Cube Development - page 141

onable amount of time. Secondly, returning too many rows using the
DRILLTHROUGH command can impose a severe burden on the SSAS
instance. This is especially true when using 32-bit hosts or hosts with
a limited amount of memory.
• Invocation : Its supported values are interactive, on open, or batch.
Generally you should use the interactive option—you should not
present thousands of rows to the user unless this amount of data is
necessary.
• Application : It specifies the name of the application that executes
the action.
• Description : It supplies additional metadata about the action.
• Caption : This is the string that users will click on to execute the ac-
tion. Be sure to supply the value that clearly conveys the purpose of
the action.
• Caption is MDX : It advises SSAS whether the caption is defined
as an MDX expression. Generally this is FALSE for drillthrough ac-
tions.
5. Save the action by clicking on the SSDT Save button.
6. To test the action, create a pivot table in Excel, expose the Reseller
Sales Amount measure, and display resellers on rows. Right-click on a
cell showing Reseller Sales Amount for a single reseller, and then nav-
igate to Additional Actions | Drillthrough… . This opens a new tab within
the Excel worksheet and displays all attributes and measures included in the
Reseller Details action. Clearly the action's caption within the sample
database doesn't include much detail about the action—you should not follow
this poor practice in the actions you create.
How it works...
Drillthrough actions are implemented using the DRILLTHROUGH MDX command. As
usual, you can use SQL Server Profiler to see the statement executed behind the
scenes. For example, the following MDX command is executed when invoking the
Reseller Details action for the Community Department Stores reseller:
DRILLTHROUGH Select ([Measures].[Reseller
Sales Amount],[Reseller].[Reseller].&[571]) on