Click here to monitor SSC
  • Av rating:
  • Total votes: 75
  • Total comments: 3
Robert Sheldon

Adding Actions to a Cube in SQL Server Analysis Services 2008

29 March 2010

 Actions are powerful way of extending the value of SSAS cubes for the end user. They can click on a cube or portion of a cube to start an application with the selected item as a parameter, or to retrieve information about the selected item. Actions haven't been well-documented until now;  Robert Sheldon once more makes everything clear.

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:

  1. A data source that points to the AdventureWorksDW2008 database on a local instance of SQL Server 2008.
  2. A data source view that includes the tables shown in Figure 1.
  3. Database dimensions based on each dimension table in the data source view.
  4. A cube based on the database dimensions as well as on the two fact tables in the data source view.

Figure 1: Default diagram from the Sales 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.

Figure 2: Creating a named calculation on the DimCustomer table

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.

Figure 3: Adding a drillthrough action to an Analysis Services cube

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.

Figure 4: Configuring a drillthrough action in an Analysis Services cube

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:

  1. Drag the Sales Amount measure from the Fact Internet Sales measure group to the Detail Fields Here section of the browsing surface.
  2. Drag the Sales Territory Region attribute of the Territory dimension to the Drop Column Fields Here section of the browsing surface.
  3. Drag the Calendar Year attribute from the Order Date dimension to the Drop Row Fields Here section of the browsing surface.
  4. 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.

Figure 5: Browsing sales data based on the sales territory and calendar year

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.

Figure 6: Verifying that the drillthrough option has been added to the context menu

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.

Figure 7: Viewing the customer names for aggregated sales totals

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.

Robert Sheldon

Author profile:

After being dropped 35 feet from a helicopter and spending the next year recovering, Robert Sheldon left the Colorado Rockies and emergency rescue work to pursue safer and less painful interests—thus his entry into the world of technology. He is now a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. He has also written news stories, feature articles, restaurant reviews, legal summaries, and the novel 'Dancing the River Lightly'. You can find more information at http://www.rhsheldon.com.

Search for other articles by Robert Sheldon

Rate this article:   Avg rating: from a total of 75 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: pUsyJgQRjqOnJNAi
Posted by: ztlbtqclcup (not signed in)
Posted on: Tuesday, April 06, 2010 at 8:51 PM
Message: SvQwyB <a href="http://ccvbgithlpxu.com/">ccvbgithlpxu</a>, [url=http://vosyeltjxdeb.com/]vosyeltjxdeb[/url], [link=http://zuoabdrffggx.com/]zuoabdrffggx[/link], http://gpkoqrngrdnj.com/

Subject: G8 Blog
Posted by: Venkat Besthavemula (view profile)
Posted on: Friday, March 18, 2011 at 4:27 AM
Message: One more terrific article from Rob. It's explained so clearly all the aspects of drillthrough. Appreciate your wonderful effort.

@Rob, I have a question for you. Is there a way to set Maximum Rows number dynamically. For example I need to set max rows value based on client input in a DB table. Can I do it by using any MDX script. If so can you give me a hint. Your help will be highly appreciated.

Thanks,
Venkat

Subject: Need Help please!
Posted by: thinkingeye (view profile)
Posted on: Monday, June 24, 2013 at 7:15 AM
Message: Thanks Robert,
I already have a drill thorugh action set up in my cube, recently i added a new field to the (return columns). in the browser i can see the drill through action, but for some reason in excel its showing a complete different set of fileds. After adding the field i saved and processed the cube, please need help

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.