DATABASE

SQL Server 2005 : Advanced OLAP - Actions

11/4/2012 8:02:44 PM
You might have noticed a pattern emerging in our study of calculations and KPIs: Analysis Services allows you to store a variety of things in your cube that you could, if called upon to do so, create within your own application or queries. Think about it: Your calculations simply represent a collection of commands that make up an MDX script, and each of your KPIs is, in effect, a collection of MDX expressions and literal values (description, status graphic name, trend graphic name) that you could store and evaluate on the client side.

But by that logic, views, functions, and stored procedures in relational databases could be handled by your applications as well. Of course, we use those server-side facilities because DBAs can design, secure, and provide them to a range of applications and users. Similarly, Analysis Services offers calculations and KPIs to put their management and security in the hands of DBAs and to make them available to anyone consuming your Analysis Services databases.

We will now quickly cover actions, which are yet another server-side, MDX-based object type.

Actions Simply Defined

You can think of actions as context-sensitive external commands. In other words, actions allow you to store an MDX-based template that, when evaluated, evaluates to a command string of one form or another. Actions are associated with objects in your cube, whether they be structural objects—such as measures, attributes, hierarchies, levels, or their members—or actual cells in the cube. The types of commands range from drillthrough queries that extract a cell’s underlying data from the relational database, to URLs that embed a cell or member’s value, to SQL or command-line strings that can also embed a cell or member’s value.

Actions are defined on the Actions tab of the cube designer, and they appear in the cube browser on shortcut menus.

Designing Actions

The cube designer’s Actions tab, like its Calculations and KPIs tabs, consists of an Organizer pane (Actions Organizer, in this case), a Calculation Tools pane, and a form that you must complete in order to fully describe the action you are creating or editing. Unlike the Calculations and KPIs tabs, however, the Actions tab has only a form view; there is no script view or browser view. By now you should be familiar with the meaning and mechanics of editing the fields in form view, so we can proceed with creating a couple of actions without a lot of background discussion.

Start by creating a drillthrough action by selecting the Cube/New Drillthrough Action option on the main menu or clicking the New Drillthrough Action button (fourth from the right) on the Actions tab’s toolbar. Enter Total Sales Drillthrough for the Name field (no brackets necessary), select Main from the Measure Group Members drop-down list, leave the Condition field blank and, in a new row in the Drillthrough Columns grid, select MEASURES in the Dimensions column and Total Sales in the Return Columns column. (You can do this by clicking the drop-down arrow, selecting the Total Sales item in the popup window, and then clicking its OK button.) The form should appear as shown in Figure 1.

Figure 1. The cube designer’s Actions tab, with a drillthrough action fully configured

This creates an action that allows you to right-click any cell in the cube browser and see in a popup window the Total Sales data for each row in the fact table that underlies it.

Next, add a (regular) action by selecting the Cube/New Action option on the main menu or clicking the New Action button (fourth from the left) on the Actions tab’s toolbar. Enter MSN Search in the Name field, select Level Members from the Target Type drop-down list (if it is not already selected), and select Geography.Country - State-Province - City - Postal Code.City in the Target Object drop-down list. (Click the drop-down arrow, drill down to and select the appropriate node in the dimension browser popup window, and click OK.) Leave the condition expression blank, select URL from the Type drop-down list (if it is not already selected) in the Action Content section, and enter the following in the Action Expression field:

"http://search.msn.com/results.aspx?q=" + [Geography].[City].CURRENTMEMBER.NAME

					  

The form should appear as shown in Figure 2.

Figure 2. The cube designer’s Actions tab, this time showing a fully configured URL action

This action generates a URL that navigates to the MSN Search Web site and performs a search on the selected city’s name. By right-clicking any city in the row or column headers in the cube designer’s Browser tab, you can select the action from the shortcut menu and bring up the corresponding URL in Microsoft Internet Explorer.

Testing Actions

You can test your actions by saving and deploying your changes and then entering the Browser tab of the cube designer. Drag the Total Sales measure to the detail fields area, and drag the City level of the Country - State-Province - City - Postal Code hierarchy of the Geography dimension (not the City attribute of the Geography dimension) to the row fields area. Right-click any cell in the data area and choose Total Sales Drillthrough from the shortcut menu to view the drillthrough query results for that cell in a popup window (as shown in Figure 3).

Figure 3. The Data Sample Viewer window, invoked from a drillthrough action on a cell in the cube browser


Right-click any city in the row header area and choose MSN Search (as shown in Figure 4) from the shortcut menu to view MSN search results for that city’s name in Internet Explorer.

Figure 4. URL actions appear as shortcut menu options on their target objects (level members, in this case) in the cube browser.

Experiment with the other action types and consider using actions extensively in your cubes. OLAP is all about drill-down analysis, and what better drill-down functionality is there than providing your users with detailed information about cube data from other applications, databases, and the Web?

Other  
  •  SQL Server 2005 : Advanced OLAP - Key Performance Indicators (part 2) - KPI Queries in Management Studio
  •  SQL Server 2005 : Advanced OLAP - Key Performance Indicators (part 1) - A Concrete KPI, Testing KPIs in Browser View
  •  Oracle Database 11g : Database Fundamentals - Work with Object and System Privileges, Introduce Yourself to the Grid
  •  Oracle Database 11g : Database Fundamentals - Become Familiar with Other Important Items in Oracle Database 11g
  •  Microsoft ASP.NET 4 : Repeated-Value Data Binding (part 2) - Creating a Record Editor
  •  Microsoft ASP.NET 4 : Repeated-Value Data Binding (part 1)
  •  Microsoft ASP.NET 4 : Single-Value Data Binding
  •  SQL Server 2005 : Dynamic T-SQL - Dynamic SQL Security Considerations
  •  SQL Server 2005 : Dynamic T-SQL - Supporting Optional Parameters (part 4) - sp_executesql: A Better EXECUTE
  •  SQL Server 2005 : Dynamic T-SQL - Supporting Optional Parameters (part 3) - SQL Injection
  •  
    Video
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone