Problem
I am using Excel to access my SQL Server Analysis Services cubes and I want to give end users additional drill down functionality. Can you let me know what Excel drill down actions are available for SQL Server Analysis Services (SSAS)?
Solution
Of all the available client tools for SSAS, Excel pivot tables are likely (and by far) the most popular method for looking at SSAS data. Certainly, pivot tables are exceptional at providing users with the ability to quickly and accurately query and navigate a cube and its measures and dimensions. The ability to use Excel to place this data at a user’s fingertips and then allow them to slice and dice data is truly a great tool. However, there are times when a user needs more data, such as a detailed list of customers, or needs a set of data not in a pivot format for input into another system. Alternately the date may be needed for further analysis or maybe a link needs to open to another system for further input. From my experience, the drill down / action functionality available for OLAP pivot tables created in Excel is an often overlooked and underutilized set of functionality.
SSAS Drill Down Actions
Actually, SSAS provides nine different drill down actions including:
- Dataset-returns a data set
- Rowset-returns a row set
- Drillthrough-returns a predefined drill down statement
- Report-runs report server report
- Commandline-runs a command prompt command
- OLE DB Statement-runs an OLE DB statement
- URL-opens a web page
- HTML-runs an HTML script
- Proprietary- runs a command not listed above
To be able to see some these actions “in action”, we will use the AdventureWorks 2012 MultiDimensional database.
Once you have installed the 2012 SQL Server and SSAS databases and have processed the SSAS database, you will want to open up SQL Server Data Tools (SSDT) which replaced Business Intelligence Development Studio (BIDS). Once SSDT is opened and you have navigated to the Adventure Works project, double click on the cube and then navigate to the Actions tab as shown below. Although we will not have the ability to go over all the available actions, we will cover three of them.
Once on the actions tab, three action buttons are available. As noted below, the three action buttons are grouped together on the action tab. The first button, New Action handles all the actions except a Drillthrough action or a report action. The middle button handles the drillthrough actions while the last button handles a new reporting action.
SSAS URL Action
The first action we will explore is the URL method. This method allows for a URL to be passed to the default web browser when the user executes the action in Excel. This example, as illustrated below, will use the postal code field to open a browser session (default browser is used) to Bing Maps, mapped to that postal code.
The following items needed to be completed for the action. First an action name needs to be specified; this name is the one that will show up in Excel. Next, the target type and target object are selected. The target type defines the object type to be used such as attributes members, dimension members, cube, cells while the target object is filtered based on the selected type. For this example the attribute member type is selected and then the Geography > Postal Code attribute is selected. These items define which fields will “enable” the created action.
The condition area allows the action to be toggled on and off based on an input MDX expression</a>; in this case, the condition will not be utilized. The action content describes the action type to be executed; this list includes all the action methods except the drillthrough action and report action. For our example we are using URL.
The next step, the action expression, allows us to define the URL to be opened for this action. Notice in the below illustration, that we can also append values to the URL in order to pass those to the browsers. For this particular illustration we are appending the postal code to the URL. This URL will lookup the postal code when the browser is opened.
Finally under additional properties, we invoke the action in an interactive mode, use the caption property to add a “tool tip” for the action (you will see this tool tip when we look at the Excel results next). The application property identifies the name of the client application that can run the action, although in most cases this item is just a suggestion or recommendation. The caption will say “Map of : ZipCode-City Name”. If the caption is not specified then the action name will be displayed. Finally, adjust the Caption is MDX to True and then save the action.
Now that the action is setup and saved, we can see how it is implemented in Excel. To use the Action, I right click on the 03064 postal code, and then click on Additional Actions. Notice how the caption appears with the postal code and then the city name, this items is the tool tip mentioned earlier.
Once the caption is selected, the default browser (Internet Explorer on this test machine), opens to a Bing map of selected zip, as shown below.
The URL action is a great start for actions; next we will move through a drill through action.
SSAS Drillthrough Action
A drill through action provides the users with a dataset of a predefined values. The values to be returned can be dimension attributes or measures, although calculated measures cannot be selected (a work around does exist, please see the next steps at the end of this tip).
As illustrated below, first you must name the drill through action. Next, the measure group member’s list box can be used to limit the list of drill through columns.
The condition box works similarly to the URL action; for instance, we could create a condition so that the drillthrough was only available for reseller order quantities amounts greater than 500 as noted in the example below. The drillthrough columns area allows you to select which fields, either measures or attributes, will be returned when the drillthrough action is invoked. The list is filtered by measures or specific dimension names. For our example, we are selecting several of the reseller sales measures plus the promotion, promotion type, and promotion category which fall under the promotion dimension.
Under additional properties, two new properties are available for the drill through action. The first additional option defines whether this drillthrough action is the default drill through when a user double clicks or invokes the drill through in Excel; only one drillthrough action can be the default. It may be beneficial to select a smaller set for this default action. The second additional property, maximum rows, limits the numbers of row returned when this action is initiated. A user may try to drill into a million rows. The remaining properties are similar to the URL action.
Jumping back to the Excel spreadsheet, we can now see the drillthrough in action. By right clicking on the order quantity, selecting Additional Actions, we now have the Excel Drillthrough Action method available.
Clicking on the action creates a new worksheet with a table that contains each of the fields selected in the drillthrough columns. This data can now be used for further detail analysis.
Navigating back to the original pivot notice in the below screen print that when attempting to drill down from the 499 order quantity value, no drill through action is available because of the condition restriction. Furthermore, if the number of rows exceeded 2,000 rows, only the first 2000 rows would be returned.
SSAS Report Server Report Action
The last of the actions we are going to cover in this tip is a drill through to a Report Server (SSRS) report. Again, many of the properties are the same as the URL action. For instance and as shown below, we specify the action name, the target type and target object, and optionally a condition. Furthermore, the additional properties such invocation, application, and caption resemble the URL action implementation.
Two sections, the Report Server and Parameters sections are what differentiates the SSRS action from the other actions. With the Report Server area, as noted below, the SSRS server name and report path location (note the path is the ReportServer path) are defined. Further, you can define the render format. In the Parameter area, you define what parameters to pass from the pivot table to the SSRS report. For our example below, we are passing the Employee ID to the Adventure Works Employee Sales Summary report found on my local machine.
Now when we right click on the Employee ID, the SSRS Drill Down Action is available, as shown below.
This drill down action submits a http request to the default browser telling it to open the noted Report Server URL for the Employee Sales Summary Report and use the selected employee id for the Employee parameter. The report in our below example is rendered in HTML format, but we could select other options in the setup including PDF or Excel.
Next Steps
- SSAS drillthrough actions provide rich functionality for Excel users (and other user applications) that seems to be used only sparingly by SSAS designers. Nine different actions are available to use, but the three most common of these are: 1) URL action 2) drillthrough action or 3) SSRS action. With these three methods, users are able to drill to a URL, a SSRS reports, or drillthrough dataset. For the drillthrough report, the data is returned in a tabular form. Each action requires the action to be given a name and also optionally allows for the definition of a MDX condition to determine if the action is enabled or disabled. Additionally, each action allows for the customization of the caption used in the Action; this caption is sort of a tool tip. Finally, the drillthrough action allows for one drillthrough action to be set as the default action while also providing the ability to create a row limit for the number of rows to return.
