In this article we will examine how to design a Drillthrough Action using SQL Server Analysis Services 2012.
What is a Drillthrough Action?
A Drillthrough Action provides a way to return detailed transactions based on a aggregated value, and allows for you to restrict the total number of rows returned, which helps improve query performance. For example, let's say you run a summary report for total revenues by territory. With Drillthrough enabled, you could click on a revenue data value under the desired territory column to get details of how the aggregated value is derived. The details could include: Sales Person, State, City, Sales Amount, Tax, etc.
What we'll cover in this session:
- Define Drillthrough Action
- Action Target
- Drillthrough Columns (Dimensions and Return Columns)
- Addtional Properties
- Reviewing the Drillthrough Action
This article assumes you have the following:
- Installed SQL Server 2012 or 2008 R2, with Analysis Services.
- Attached AdventureWorksDW2012 Data File.
- Experience with SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS). Note: For this session we'll be using SSDT.
- Experience with deploying and attaching to an exisitng Analysis Services database using SSDT or Business Intelligence Development Studio (BIDS).
- Some experience developing with Multidimensional Expressions (MDX) language.
Note: A download of the demo is available at the end of this session.
Lets get started!
Step 1 - Define Drillthrough Action
1) Download and open the attached Analysis Services solution provided at the end of this turorial.
2) Under the Cubes folder, double click on the Analysis Services Tutorial to open the Cube Designer.
3) Click on the Actions tab.
4) Right click from within the Action Organizer pane and select New Drillthrough Action.
5) Change the Name field to "Reseller Sales - Southwest" (without quotes).
6) Under the Action Target section, select "Reseller Sales" for the Measure Group Members value.
Note: The Action Target defines the scope of the Action. In this case, we're defining any measure(s) under the Reseller Sales measure group are included in the scope. That means, you can right click any of these data values in a Data Viewer (e.g., SSAS Broswer or Excel) to render the details view.
7) In the Condition textbox enter the following MDX:
IIF([Sales Territory].[Sales Territory Region].CurrentMember IS
[Sales Territory].[Sales Territory Region].[Southwest], TRUE, FALSE)
A Condition is similar to a Where clause in SQL. In this example, we're defining the Drillthrough Action to only provide details of the scope if the Sales Territory Region is equal to Southwest.
8) Next, we'll define our Drillthrough Columns by including which measures and dimensions columns will be displayed in our details data viewer.
Drillthrough Columns are Attributes and Measures displayed on the client when they execute the Drillthrough Action.
Add the Following Measure and Dimensions:
- Measures - Reseller Sales-Order Quantity, Reseller Sales-Sales Amount, Reseller Sales-Tax Amount, Reseller Sales-Freight and Reseller Sales Count
- Reseller - Bank Name and Phone
- Reseller Geography - City, State-Province and Country-Region
- Sales Territory - Sales Territory Region
9) Set the following values for the Addtional Properties section:
- Default = False
- Maximum Rows = 10
- Invocation = Interactive
- Application = (leave blank)
- Description = "Reseller Sales for the Southwest Region"
- Caption MDX = False
Your Drillthrough Action designer should look similar to below.
Step 2 - Testing your Drillthrough Action
1) From within the Solution Explorer, right click the Anaysis Services Tutorial and seclt Deploy.
2) Once the deploy has completed successfully, click on the Cube Designer's Browser tab.
Testing with Excel
3) On the toolbar, click on the icon to analyze data using Excel.
Note: This will open Excel and connect to this instance of the cube.
Note: A version of Excel 10 or greater is required if you wish to use this feature.
4) Excel is started.
5) From the PivotTable Fields pane, expand Reseller Sales and add Reseller Sales-Sales Amount to the Values area.
6) Scroll down the PivotTable Fields and expand Sales Territory >> More Fields... and check Sales Territory Region to add it in the Rows area.
7) Under the Reseller Sales-Sales Amount column, right click the value for the Northwest row and select Additional Actions. You'll notice (No Action Defined). This is because of our previously set Condition of Southwest.
8) Now right click the Sales Amount value for Southwest row and select Additional Actions. You'll notice our Drillthrough Action "Reseller Sales - Southwest" is available; click on this Action item.
9) Excel will add the detail columns and data to a new worksheet. Examine the colums and data. You'll notice all the colums we defined in our Drillthrough Action are available and the Max Rows of 10.
Testing with SQL Server Management Studio
1) Open SSMS and connect to the Analysis Services server where your database is located.
2) Expand Analysis Services Tutorial database >> Cubes.
3) Right click on the Analysis Services Tutorial cube and select Browse.
4) From within the Measures Group pane, expand Measures >> Reseller Sales.
5) Drag Reseller Sales-Sales Amount to the Detail area of the browser window.
6) Expand Sales Territory and drag Sales Territory Region to the Row Fields area of the browser.
7) Right click on the Northwest Sales Amount value. You'll notice no option to execute the Drillthrough Action.
8) Right click on the Southwest Sales Amount value. You'll notice Reseller Sales - Southwest drillthrough action is now available. Click the action to view the details.
You have completed creating a Drillthrough Action using SQL Server Analysis Services 2012.
Downloads: Analysis Services Tutorial.zip (143.49 kb)