One of the objects supported by a SQL Server Analysis Services cube is the action. An action is an event that a user can initiate when accessing cube data. The event can take a number of forms. For example, a user might be able to view a Reporting Services report, open a Web page, or drill through to detailed information related to the cube data
Analysis Services supports three types of actions:
- Report. Returns a Reporting Services report that is associated with the cube data on which the action is based.
- Drillthrough. Returns a result set that provides detailed information related to the cube data on which the action is based.
- Standard. One of five action subtypes that are based on the specified cube data:
- Dataset. Returns a mutlidimensional dataset.
- Proprietary. Returns a string that can be interpreted by a client application.
- Rowset. Returns a tabular rowset.
- Statement. Returns a command string that can be run by a client application.
- URL. Returns a URL that can be opened by a client application, usually a browser.
Although the basic steps necessary to implement the various types of actions are similar, the configuration of each one is unique. In this article, I explain how to add a drillthrough action to your cube. The cube I use for my examples is based on the one I describe in the article “Five Basic Steps for Implementing an Analysis Services Database”. For that solution, I created the following database components:
- A data source that points to the AdventureWorksDW2008 database on a local instance of SQL Server 2008.
- A data source view that includes the tables shown in Figure 1.
- Database dimensions based on each dimension table in the data source view.
- A cube based on the database dimensions as well as on the two fact tables in the data source view.
Be sure to refer to the article mentioned above for more details about the solution. The article explains how to create the cube necessary to support the action I describe in this article. You can also refer to SQL Server Books Online for more information about how to implement an Analysis Services database and its cubes. Once you’ve created the database, you’re ready to add actions to your solution.
Creating a Named Calculation
The drillthrough action that I describe allows users to view a list of customers associated with aggregated Internet sales. To support this action, we’ll first create a named calculation that concatenates the customers’ first and last names.
You create named calculations in the data source views that you reference in your dimensions and measure groups. In this case, we’ll update the DimCustomer table of the Sales data source view.
Open the data source view in Business Intelligence Development Studio (BIDS). In the Tables pane of the data source view design surface, right-click the DimCustomer node, and then click New Named Calculation.
Name the column FullName and provide a description. In the Expression text box, add the following expression:
FirstName + ‘ ‘ + LastName
The Create Name Calculation dialog box should now look similar to the one shown in Figure 2.
Once you’ve added the named calculation, you can reference it in your dimensions and cubes as you would any other column in a data source view. For our drillthrough action example, be sure to update the Customer dimension to include the FullName column, and process the cube as necessary so the named calculation will be available to your action.
Creating a Drillthrough Action
To add a drillthrough action to a cube, open the cube in BIDS. On the Actions tab of the cube designer, click the New Drillthrough Action button. A new form is displayed in the design pane, as shown in Figure 3.
The first thing to do is to give the action a name. I used Customer drillthrough, but you can use whatever you think is appropriate. Next, select Fact Internet Sales from the Measure group members drop-down list. Our action will be based on the aggregated totals for Internet sales.
The next property, Condition, is a Multidimensional Expressions (MDX) statement that returns a Boolean value. Although this property is optional, we will create an expression to ensure that the action can be triggered only when a cell contains a value. In other words, if there are no sales for a particular category of cube data, the user should not be able to drill down to an empty dataset.
To ensure that the drillthrough action applies only to those aggregated totals that have sales, add the following expression to the Condition dialog box:
[Measures].[Sales Amount] > 0
As you can see, the Sales Amount measure must contain a value greater than 0 for the action to be available to the user.
The next property that must be configured for the action is Drillthrough Columns. In this case, you simply select the dimension and its columns that you want returned by the drillthrough action. For our example action, we’ll use the FullName attribute (based on the named calculation) in the Customer dimension. Later, you can add additional attributes or measures to see how they work.
Configuring Additional Properties
If you refer back to Figure 3, you’ll notice that there’s a set of down arrows for additional properties. Click those arrows to access the properties.
The first additional property, Default, is included for backward compatibility. It has to do with how drillthrough actions are evaluated when a DRILLTHROUGH statement is executed by a client application. You can leave its value as False.
The next additional property is Maximum rows, which determines the maximum number of rows that will be included in the drillthrough dataset. In this case, I’ve set the property to 50, but you can set it to whatever number you think appropriate.
The Invocation property determines when the action should run. The default value, Interactive, specifies that the action will run when the user chooses to run the action. However, you can set the property to Batch to indicate that the action should run as part of a batch operation, or you can set the property to On Open so the action runs when the cube is first opened. For our example, we’ll stick with Interactive.
The next property, Application, is the name of an application that can run the drillthrough action. You can use this property to identify which client application most commonly uses this action or to display icons next to the action in a pop-up menu. However, the property does nothing but provide a recommendation to the client about which application to use. It does not control access to the action. In this case, we’ll leave this property blank.
For the Description property, simply type in a description for your action. I used Describes the action, but you can type in any description you want, or not include a description at all.
The Caption property determines what text is displayed when referencing the action. The caption can be a simple string or it can be an MDX expression. For our example, we’ll use the following MDX expression:
"Drillthrough to "
+ [Territory].[Sales Territory Region].CURRENTMEMBER.MEMBER_CAPTION
+ " customers"
The expression concatenates two strings with the current sales territory region. For example, if the region is Northeast, the caption will read Drillthrough to Northeast customers.
The final property is Caption is MDX. If the Caption property is an MDX expression, then the Caption is MDX property should be set to True. Otherwise, it should be set to False. We, of course, must go with True. Your drillthrough action form should now look similar to the one shown in Figure 4.
After you’ve configured all the properties for your drillthrough action, you should save and process your cube. You can then use the Browser tab of the cube designer to view the action you just created.
Viewing the Drillthrough Action
To test the drillthrough action, you’ll want to set up the browser with cube data. Take the following steps on the Browser tab of the cube designer:
- Drag the Sales Amount measure from the Fact Internet Sales measure group to the Detail Fields Here section of the browsing surface.
- Drag the Sales Territory Region attribute of the Territory dimension to the Drop Column Fields Here section of the browsing surface.
- Drag the Calendar Year attribute from the Order Date dimension to the Drop Row Fields Here section of the browsing surface.
- Drag the Sales Territory Country attribute from the Territory dimension to the Drop Filter Fields Here section of the browsing surface. Clear the All checkbox on the filter, and then check the United States check box.
Your browsing surface should now look similar to the one shown in Figure 5.
Right-click one of the cells that contains aggregated sales amounts to bring up the context menu. The menu should include an option for being able to drill through to the customers. For example, when I right-click the cell for 2003 sales in the Central region, my context menu includes the option Drillthrough to Central customers, as shown in Figure 6.
If I were to right-click a cell that shows no aggregated totals, such as 2001 sales for the Northeast region, the drillthrough option would not be available in the context menu. That’s because we included the MDX expression in the Condition property to make the action available only if there are sales.
When you click the drillthrough option, a dialog box appears and displays the list of customer names. These are the customers who made purchases in the specified region and year. Figure 7 shows the customers in the Central region who made purchases in 2003.
As you can see, we’ve included only the names, but we could have included other information, such as birth dates or annual salaries. Better still, we could have included the Sales Amount measure so we see the amount for each sale. This would have made each row unique, rather than simply having duplicate names like we have now.
Indeed, there’s a great deal of flexibility in how you design your drillthrough actions. By setting up the data source views to support the actions and then implementing the actions with the details you want to include, you can provide users with a variety of information that allows them to dig deeper into their cube data. And drillthrough actions are only one type of many. You can launch reports, open Web pages, run applications, and a number of other tasks. Be sure to check out SQL Server Books Online for more information about each one of them.