Drill-Through | Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)

Drill down is the process of navigating from a summary level to more detailed levels across a cube dimension. Drill-through is a completely different animal. Drill-through retrieves fact data corresponding to a cell or some specified range of cells. Often the lowest level of detail in a cube is still comprised of aggregated values, but users occasionally have a need to see the associated row-level data from the fact table. In Analysis Services 2005, even if you use the MOLAP storage mode (discussed later in this chapter), you can still use drill-through. You can modify a server configuration advanced property, OLAP\Query\DefaultDrillthroughMaxRows, control the default size of the returned dataset.

By default, drill-through returns the granularity attribute of each dimension and all measures. If you want your drill-through action to behave like drill-through in Analysis Services 2000, you can create a ROLAP dimension from the fact table that contains the measures to return. And just how do you define drill-through? You could create an application that performs drill-through programmatically using the SQL query supported by Analysis Services 2005. A new option in Analysis Services 2005 is to create a drill-through action. The following sections describe the available action types, including how to create a drill-through action.

For some insight on how drill-through actually works in Analysis Services 2005, it is informative to contrast it to the implementation of drill-through in Analysis Services 2000. Analysis Services 2000 fetched all requested measures directly from the relational data source which is potentially a slow process. Analysis Services 2005 fetches the requested measures from the MOLAP database directly and therefore runs much faster. Indeed, the system is self-contained and requires no connection to SQL Server. Drill through can be defined as an Action (to be seen in next section) and can drill through on cells that have drill through action defined. You will learn to define Drill through and understand it's behavior in the next section.

Actions

Actions are predefined metadata components stored on the server that send commands to client applications to perform certain operations based on a selection by the user in the cube browser. For example, the user could select dimension members, levels, a set, specific cube cells, and so on. An action command usually includes a command string, such as a URL, and the suggested command behavior, such as opening a Web browser for the URL. MDX expressions are often built into commands to include the context of the user selection in the action. If a user initiates an action by selecting a product, for example, an MDX expression could be used to generate a URL for a catalog page describing the selected product.

Action Types

Analysis Services 2005 supports seven action types. These action types empower client applications with more analytical capabilities than traditional OLAP analysis drill up, drill down, and pivot activities. For example, if a sales manager is analyzing sales for cities in Washington State, the ability to click on a city member to view an MSN city map would be helpful. Similarly, if your implementation includes Reporting Services, you could link a report that analyzes sales reasons by product category to the product category members by adding an action to the cube. When the sales manager clicks a product category, the action passes the selected product category as a parameter to the report which then displays in a Web browser. If a sales number for a specific region appears to be surprisingly high or low, the sales manager could use a Drill-through action to retrieve all detailed transactions contributing to the value. The seven action types supported in Analysis Services 2005 are listed in the following table along with the information on what can be done by a client when such an action type is returned by Analysis Services 2005.

Action Type	Description
CommandLine	Returns a command that can be run under command prompt.
HTML	Returns an HTML script that can be rendered using HTML browser.
URL	Returns a URL that can be launched using a browser. Report Action (to be see later) uses this Action type.
Statement	Returns a statement that can be run as an OLE DB command.
Rowset	Returns a rowset to a client application.
Proprietary	Performs an operation by using an interface other than those listed in this table. The client application retrieving this action type should know how to use this proprietary action type.
Dataset	Returns a dataset to a client application.

Action Target Types

Each action is tied to a target type. Target types refer to a specific object or objects, inside the cube. If a user clicks an object that has been defined as a target for an action, the action will be enabled in the client application for that specific object. For example, if you define a URL action to be associated with attribute members of the geography.city attribute, that action will be available when the user selects any member of the city attribute. When you define an action, you must specify the type of objects that will be targets of the action. Analysis Services 2005 supports the following Action Target Types.

Target Type	Description
Attribute Members	Only valid selection is a single attribute hierarchy. The target of the action will be all members of attribute wherever they appear (that is, it will apply to multilevel hierarchies as well).
Cells	All Cells is the only selection available. If you choose Well Cells as a target type, type an expression in Condition to restrict the cells with which the action is associated.
Cube	CURRENTCUBE is the only selection available. The action is associated with the current cube.
Dimension members	You need to select a single dimension. The action will be associated with all members of the dimension.
Hierarchy	You need to select a single hierarchy. The action will be associated with the hierarchy object only. Attribute hierarchies appear in the list only if their AttributeHierarchyEnabled and AttributeHierarchyVisible properties are set to True.
Hierarchy members	You need to select a single hierarchy. The action will be associated with all members of the selected hierarchy. Attribute hierarchies appear in the list only if their AttributeHierarchyEnabled and AttributeHierarchyVisible properties are set to True.
Level	You need to select a single level. The action will be associated with the level object only.
Level members	You need to select a single level. The action will be associated with all members of the selected level.

URL Action

In this section you will learn to create a few actions. The URL Action is probably one of the actions which we expect customers to use widely. Follow the steps below to create a URL action.

Using BIDS, open the Adventure Works DW sample project (located at C:\Program Files\ Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project\ Enterprise), and double-click the Adventure Works cube in Solution Explorer to open the cube designer. Click the Action tab to open the actions editor, as shown in Figure 9-34.

Figure 9-34
Click the New Action button in the Actions toolbar. Type a name for the new action: My City Map. Open the Target type list box (by clicking on the down arrow) to see the available action target types, and then choose Attribute Members, as shown in Figure 9-35.

Figure 9-35
In the Target object drill down box, pick Geography.City as the attribute target (shown in Figure 9-36).

Figure 9-36
Leave the Action Condition text box blank. If you want to enable the action only under certain conditions, you can enter an MDX expression that returns a Boolean value. Because you always want the My City Map action to be enabled, you don't need an Action Condition expression here.

In the Action Content section of the editor, keep the default action type, URL. In the Action Expression text box, type the following MDX expression:

     // URL for linking to MSN Maps     "http://maps.msn.com/home.aspx?plce1=" +     // Retreive the name of the current city     [Geography].[City].CurrentMember.Name + "," +     // Append state-province name     [Geography].[State-Province].CurrentMember.Name + "," +     // Append country name     [Geography].[Country].CurrentMember.Name +     // Append region parameter "&regn1=" +     // Determine correct region parameter value     Case         When [Geography].[Country].CurrentMember Is              [Geography].[Country].&[Australia]         Then "3"         When [Geography].[Country].CurrentMember Is              [Geography].[Country].&[Canada]              Or              [Geography].[Country].CurrentMember Is              [Geography].[Country].&[United States]              Then "0"              Else "1"     End

This MDX expression returns a string URL used by the client application to open MSN Map for the user-selected City. The user's selection is passed into the MDX expression as:

     [Geography].[City].CurrentMember

If the user selects a different city and launches the action, the MDX expression is re-evaluated and returns a different URL.

Scroll down to the section Additional Properties, and expand the section to review the available properties. There are three options for the property Invocation shown in the table below along with their meaning. Since you want the action to be triggered by the user, leave the default Invocation value Interactive. You can also leave the application and description fields blank, because they are informational properties.

The following table describes the possible values for the Invocation property:

Method

Description

Interactive

The action is triggered by user interaction.

Batch

The action runs as a batch operation.

On Open

The action runs when a user opens the cube.
In the Caption text box, type the following MDX expression:
```
     [Geography].[City].CurrentMember.Member_Caption + " City Map ..." 
```
The specified caption is displayed to end users to indicate an action is available. The user clicks the caption to initiate the action. The "Caption Is MDX" property controls how the server evaluates the contents of the caption. If you leave this property value as false, the server treats the caption as a static string.
Change the Caption Is MDX value to True. The server evaluates the MDX expression in the Caption text box to construct the caption, which in this case will result in different city names included in the caption as different cities are selected in the browser.

Method	Description
Interactive	The action is triggered by user interaction.
Batch	The action runs as a batch operation.
On Open	The action runs when a user opens the cube.

Now that you've created a My City Map action, deploy the project to save the action to the server. Just as with KPI definition, an action definition is metadata stored on the server with the cube. Adding or changing the action won't impact the cube data and doesn't require a reprocess. When the project deploys, you can verify the newly created action right away.

Browse URL Action in the Cube Browser

Many standard OLAP client applications such as pivot tables in Office Web Components and the BIDS Cube Browser support actions out of the box. In this section, you learn to invoke the action My City Map from Cube Browser.

In the cube designer, click the Browser tab to open the cube browser for the Adventure Works cube.
In the metadata pane on the left, open the Geography dimension. Then, open the Geography hierarchy and drag the City level from metadata window to Rows in the data window on the right side, as shown in Figure 9-37.

Figure 9-37
Right-click on any city in the data window. Notice actions listed on the pop-up menu. Corresponding city camp action captioned as <CityName> City Map… is one of the actions listed. Figure 9-38 show the action for the city Newcastle.

Figure 9-38
Click Newcastle City Map… BIDS will invoke a Web browser and construct a URL from the predefined MDX expression. The result of the action is shown in Figure 9-39.

Figure 9-39

Report Actions

Report actions are similar to URL actions, except a Report action has additional properties to build the report server access to a URL for you. These properties are described in the following table:

Property	Description
ReportServer	The name of Report server
Path	The path exposed by report server
ReportParameters	Extra parameters for report
ReportFormatParameters	Extra parameters for report format

When a Report action is invoked, Analysis Services generates a URL string similar to the string here:

     http://ReportServer/Path&ReportParameter1Name= ReportParameter1Value&     ReportParameter2Name= ReportParameter2Value.......&     ReportFormatParameter1Name=ReportFormatParameter1Value&     ReportFormatParameter2Name=ReportFormatParameter2Value ...

To review a Report action, follow these steps:

With the Adventure Works DW sample project still open in BIDS, click the Actions tab of the Cube Designer and then click Sales Reason Comparisons Report in the Action Organizer. Let's take a look at the properties of this report action (see Figure 9-40). The optional parameter values are MDX expressions that provide the action with the context of the user selection.

Figure 9-40
As with a URL action, you can invoke a Report action from the Cube Browser. Click the Clear Results button in the toolbar to start a new query. Sales Reasons Comparisons action's target is members of the Product.Category hierarchy. Drag, and drop Product.Category hierarchy from metadata pane to OWC's rows or columns. Right-click on the member Bikes to see the action's caption on the pop-up menu as shown in Figure 9-41.

Figure 9-41
Click Sales Reason Comparions for Bikes. A browser window will open. If Reporting Services 2005 is installed, you will see a message, The item ‘/AdventureWorks Sample Reports/Sales Reason Comparisons’ cannot be found; in other words, rsItemNotFound. This is because the report is fictitious. Were it a valid report, the report would display the sales reasons for the Bike category.

Drill-Through Action

OLAP is all about aggregating data and serving aggregating data to end users quickly. Users want to analyze data hierarchically by drilling up and drilling down which requires aggregated data from millions of daily transaction rows. Sometimes it is very useful for users to be able to retrieve the transaction rows that have been aggregated into a particular cell. Access to such details often helps business users understand any abnormal business activities (such as extremely large or small numbers) and investigate root causes. Drill-through provides this access to details by enabling users to fetch the fact table rows that contribute to an aggregation value of a cube cell.

Drill-through in Analysis Services 2000 is a special MDX command which is also supported in Analysis Services 2005. However in Analysis Services 2005 you can also create a special action type called Drillthrough. A Drillthrough action's target is always one or more cube cells associated with a specific measure group. In other words, cells with measures in the target measure group will display the available drill-through actions on the pop-up menu. Drillthrough actions return the related fact table rows in a tabular rowset. As the action developer, you specify which columns the action returns. The columns in the return rowset when a drill through action is executed are not limited to the actual fact table columns. Any dimension attributes linked to the selected measure group target can be included. Many-to-many dimensions and referenced dimensions are also supported, so attributes from these special dimensions are available for drill-through return columns as well.

In Analysis Services 2000, you explicitly had to set the Enable drillthrough flag in the Drillthrough options dialog for each cube. This flag to allow drill through is deprecated in Analysis Services 2005. Cube designers in Analysis Services 2005 can allow or deny drill through by defining a security role on each cube. Only an Analysis Services 2005 administrator can perform drill through against any cube without explicit permissions. If a user does not have drill through rights on a specific cube, then the drill-through will not execute and an error message displays

In addition, the cubes and partitions properties DrillthroughFilter, DrillthroughFrom, and DrillthroughJoin in Analysis Services 2000 are no longer valid in Analysis Services 2005. The same functionality can now be achieved using Data Source View.

Follow the steps below to understand an existing drillthrough action in the Adventure Works DW sample database and enhance it.

With the Adventure Works DW sample project still open in BIDS, click the Actions tab.
Click Finance Details in the Action Organizer, as shown in Figure 9-42.

Figure 9-42

You can see the action target is the Financial Reporting measure group. The Drillthrough columns to be returned by the action are Amount, Fiscal Year, Date, and Calendar Year from the Date dimension, Accounts from the Account dimension, and so on.
Suppose the business user also wants Account Type and Account Number to be included as additional Drillthrough columns. Click the Accounts dimension attribute in the column labeled Return Columns to open the drop-down box. All available attributes for the Account dimension are listed here. Choose Account Type and Account Number as shown in Figure 9-43. Click OK.

Figure 9-43
In the Additional Properties section, the Maximum Rows setting is very useful for the designer to limit the maximum number of rows that can be returned for a drill-through action. This is important because a cell, especially a top-level cell, could be aggregated from millions of fact table rows. Setting the maximum rows value is always a good practice to protect your server from accidental or malicious operations, which will consume huge server resources. If the property is not set, the default max drill-through row count from server property Olap\Query\DefaultDrillthroughMaxRows is used. The default value of the setting is 10000. In the Maximum Rows text box, type in 5000 as shown in Figure 9-44. Deploy the project to save the action to the server.

Figure 9-44

You can now view the drill-through results in the cube browser.
Click the Browser tab to open the Cube Browser. Since the action for Finance details is on the measure group Financial Reporting, you need to drag and drop Measures.Amount in the Financial Reporting folder to the OWC's Detail Fields ara. Right-click on the cell and you will see Drillthrough… as a menu item on the pop-up menu, as shown in Figure 9-45, indicating you can invoke the Drillthrough action for the cell. Before you actually invoke the action, you should limit your drill-through to a much narrower data region to prevent the action from returning all fact tables rows if no maximum row count limit is specified.

Figure 9-45
Drag and drop the Account dimensions on rows, drag and drop the Date.Fiscal hierarchy (in the Fiscal folder) on columns. Then set the slicers at the top of the cube browser for customers in the city Redmond (in USA and Washington) and the Research and Development department, as shown in Figure 9-46.

Figure 9-46
Suppose you are interested in Other Income and Expense from May 2004. Expand Net Income on rows, and on columns expand FY 2004, H2 FY 2004, and Q4 CY2004. Right-click on the cell intersection of May 2004 and Other Income and Expense, and then choose Drillthrough, as shown in Figure 9-46. A new window opens to display the fact table rows that aggregate to the cell value, as shown in Figure 9-47. Note that the newly added account type and account number is returned.

image from book
Figure 9-47

Under the hood, client applications, including the Cube Browser, use a schema rowset to get the proper drill-through query for a specific action. Then the client application sends the drill-through query to the server, which returns a rowset with the detailed fact table rows. Following is the query sent by the Cube Browser to get the previous drill-through results. You can find the maximum rows setting and the return column settings in various sections of the query. The SELECT clause of the query is the specific cell that was selected when activating drill-through.

     DRILLTHROUGH MAXROWS 10000 Select     ([Date].[Fiscal].[Month].&[2004]&[5],[Measures].[Amount],[Account].[Accounts].&[88]     ) on 0     From [Adventure Works]     RETURN [Financial Reporting].[Amount],         [$Date].[Fiscal Year],         [$Date].[Date],         [$Date].[Calendar Year],         [$Account].[Accounts],         [$Account].[Account Type],         [$Account].[Account Number],         [$Organization].[Organizations],         [$Scenario].[Scenario],         [$Department].[Departments],         [$Destination Currency].[Destination Currency]