Default Cube Action in Excel
When using Excel 2007 to connect to a SQL 2005\2008 SSAS cube, double-clicking a cell, or right clicking the cell and selecting Show Details, within a Pivot table brings up details about the specific sliced cell within the Pivot table. However, the default nature of the cube is to bring up the raw Fact table data for that slice, which displays all fields within the Fact table for the records that make up the cell. The fields within a Fact table are often surrogate keys that point to the Dimensions that join to the Fact table as well as all Measurements stored within the Fact table. This default nature is not the most helpful information to business analysts since the keys have no business meanings to the users.
The question then is how to change this default behavior to bring back information to supply meaningful details to a business analyst when using the cube. The answer is to create a Drillthrough Action with the SSAS cube and set the Default property to True. The Drillthrough Action will return the information that the business defines as meaningful when wanting to see the details within the cube.
The following lists out the steps on how to create the Drillthrough Action within the SSAS project for the cube and then demonstrates how to use the Drillthrough Action in an Excel Pivot Table.
Setup the Drillthrough Action
In the SSAS cube, navigate to the Action table and select the icon for the New Drillthrough Action:
After selecting the New Drillthrough Action, setting up the what the Action should do is the next step. For this example, the AdventureWorks cube is being used to build the action and the example will return the following fields for the sliced cell as its default:
- Reseller Sales Amount, Reseller Order Quantity
- City, State, Country
The following screen prints lays out these fields and sets up the Default property to True:
Using the SSAS Action
After creating the action and processing the cube, an analyst can now connect to the cube via Excel 2007 and create a Pivot table as its starting report. For the example, the report is going to select Sales Territory by Calendar Year for Reseller Sales Amount.
With the Pivot setup, the following methods will display the the SSAS action, note these actions can only be performed on Non Calculated Members within the cube:
- Double click the cell within the Pivot Table
- Right click on the cell to drill into and select Show Details
- Right click on the cell and select Additional Actions–>AW Default Action (this is the name of our Drillthrough Action in this example).
Right Click–>Show Details Menu
Right Click–>Additional Actions Menu:
Regardless of the method chosen, Excel will create a new Tab within the Excel workbook and display the data as defined within the Drillthrough Action:
As demonstrated, a Drillthrough Action within a SSAS cube is another tool for a business analyst to dive into their data and answer questions that is pertinent for the question at hand. Setting up a Drillthrough Action with the Default behavior set to True allows for the users to not see data that is meaningless to the users, since the users will not be able to translate surrogate keys to actual dimension text, but instead provides useful information to the analyst to help make business decisions. The tool of providing of this useful information is the point of developing the cube in the first place.