Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Enabling Drillthrough in Analysis Services


By:   |   Last Updated: 2011-10-03   |   Comments   |   Related Tips: > Analysis Services Development

Problem

Data analysis from a cube starts with aggregated and summarized data, followed by drill-down and drill-through of the data for a deeper and intelligent analysis. Drill down is inherently supported when you browse the data of the cube, but drill through has to be defined as per the requirements of analysis. In this tip we will see how to enable drill through on a cube.

Solution

In SSAS, actions are one of the best ways to customize browsing and drilling of data for the end-user.

There are three types of actions in SSAS:

  • Report
  • Drillthrough
  • Standard

The action of interest that we cover in this tip is Drillthrough.

For this example, we will be using the sample Adventureworks SSAS project that ships with SQL Server 2008.

Step 1
Open the Adventureworks cube.

Step 2
Open the Actions tab and from the toolbar select "New Drillthrough Action" as shown below.

SSAS actions tab

Step 3
When you create a new action, you will get a screen like below to define the action.

Let's say we want to see products related to the Reseller Sales amount. 

  • Name: I named the action "Show Related Products".
  • Measure group members: we need to select the target field that should offer the drillthrough action and in our case we selected Reseller Sales amount.
  • Condition: to keep it simple for now, we will not use the condition property.
  • Return Columns: select the columns to show when the user selects this action.
ssas drill through action

Step 4
Deploy this change and browse the cube in the browser tab.

I selected the Reseller Sales Amount measure and sliced it by the Geography hierarchy from the Geography dimension as shown below.

Right click on any value in the Reseller Sales Amount and you should find "Show Related Products" in the menu as shown below.

ssas cube browsing

Step 5 
Clikc on the Show Related Products menu option and you will get a pop-up window with the fields we defined in the action showing all the records that make up the value on which we selected the drillthrough action.

ssas cube drill through detail data

Next Steps
  • Check out other actions available in SSAS.
  • Configure additional properties available in the Drillthrough action to fine tune results of your action.
  • Review these other SSAS tips


Last Updated: 2011-10-03


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools