Enabling Drillthrough in Analysis Services 2005

T.K. Anand
Microsoft Corporation

July 2005

Applies to:
    SQL Server 2005 Analysis Services

Summary: Discover the new Analysis Services 2005 drillthrough architecture. See how to set up drillthrough in Analysis Services 2005 and get guidance on migrating drillthrough settings from Analysis Services 2000 databases. (10 printed pages)

Contents

Introduction
Drillthrough Architecture
DRILLTHROUGH Statement
Drillthrough Actions
Scenarios
Migration
Conclusion

Introduction

Drillthrough is the operation in which a user specifies a single cell and the Analysis server returns the most detail level data that contributed to that cell. Although drillthrough was supported in Analysis Services 2000, this feature has undergone a complete re-architecture in Analysis Services 2005. One significant consequence of the re-architecture is that drillthrough settings in Analysis Services 2000 databases are not preserved by Analysis Services 2005 upgrade or migration.

This whitepaper describes the new drillthrough architecture in detail. It uses some typical examples to illustrate how drillthrough can be effectively set up in Analysis Services 2005. It also provides guidance on migrating drillthrough settings from Analysis Services 2000 databases.

Drillthrough Architecture

Users or client applications invoke drillthrough by sending a DRILLTHROUGH statement which specifies a single cell and returns a rowset containing the detail level data. The DRILLTHROUGH statement is described later in this document.

Analysis Services 2000

In Analysis Services 2000, database administrators set up drillthrough on a cube by doing the following:

1. Enable drillthrough on the cube (AllowDrillthrough property in DSO).

2. Specify the tables and columns to be included in the drillthrough result (DrillThroughColumns, DrillThroughFrom, DrillThroughJoins properties in DSO). Any table in the cube's data source can be specified, including those that are not part of the cube schema (fact and dimension tables) (Figure 1).

3. Optionally specify a filter condition that restricts the rows in the drillthrough result. (DrillThroughFilter property in DSO).

4. Optionally specify custom drillthrough settings for each partition (essentially repeating steps 2 and 3 for the partition).

5. Grant drillthrough permission to appropriate roles.

   

   Figure 1. The Drillthrough Options dialog in Analysis Services 2000

When the server receives a DRILLTHROUGH statement, it constructs a SQL query per partition, based on the cell coordinates and the drillthrough settings. It then executes the SQL queries on the relational data source and returns the results back to the client. Note that the client will get back multiple rowsets, one for each partition.

Following are some noteworthy points about the Analysis Services 2000 drillthrough architecture:

1. It returns one rowset per partition, burdening client applications with the task of presenting the results to the user in an integrated fashion.
2. It does not honor cell security permissions completely. A user with drillthrough permission and read access to a cell can drillthrough on the cell even if cells below it are secured.
3. It does honor dimension security permissions. A user with drillthrough permission and read access to a cell cannot drillthrough on the cell if dimension members below it are secured. The user will get back a security error.

Analysis Services 2005

Analysis Services 2005 has a completely different drillthrough architecture from Analysis Services 2000. The most important aspect of it is that drillthrough returns detail level data from within the cube. The server does not execute any SQL queries on the relational data source; hence, all columns to be returned in the drillthrough result must be part of the cube schema, either as dimension attributes or as measures.

Drillthrough is conceptually similar to SQL queries on cubes, since both return rowsets containing data from within the cube; hence, drillthrough reuses the SQL query infrastructure internally. When the server receives a DRILLTHROUGH statement, it translates it into an equivalent SQL statement. The SQL statement is then executed and the results are returned to the client.

Following are some noteworthy points about the Analysis Services 2005 drillthrough architecture:

1. Drillthrough is implicitly enabled for every cube, since it does not return any information that the user cannot otherwise obtain using MDX or SQL queries; hence, database administrators do not have to explicitly turn on drillthrough, or worry about the security implications of granting drillthrough access to a user.
2. Since drillthrough is built on the SQL query infrastructure, the result is returned to the client as a single rowset.
3. Drillthrough returns a security error if the user has non-trivial cell security access to the cube.
4. Drillthrough on a cell succeeds even if dimension members below the specified cell are secured. The secured records are just excluded from the drillthrough result.

The biggest impact of the new drillthrough architecture on cube designers is the requirement that all drillthrough columns must be part of the cube schema. Cube designers must include additional attributes and measures in the cube. This can increase the schema complexity and the data size.

DRILLTHROUGH Statement

The DRILLTHROUGH statement specifies the cell coordinate on which to drillthrough and the list of columns to be returned in the result. The syntax is as follows:

DRILLTHROUGH 
    [MAXROWS n] 
    <mdx-select> 
    [RETURN <column> [AS <alias>], <column> [AS <alias>], ...]

<mdx-select> -> any MDX SELECT statement that specifies a single cell
<column> -> any attribute or measure in the cube
<alias> -> any string alias for the column

For example, the following statement drills through on the store sales to customers in the USA in 2005.

DRILLTHROUGH MAXROWS 10
SELECT 
    {[Measures].[Store Sales]} on 0, 
    {[Customer].[Geography].[USA]} on 1
FROM [Sales]
WHERE
    {[Time].[Year].[2005]}
RETURN
    [Measures].[Store Sales], 
    [Measures].[Store Cost], 
    [Measures].[Unit Sales], 
    [Customer].[Name], 
    [Product].[Name], 
    [Store].[Name], 
    [Time].[Day]

MAXROWS

The MAXROWS clause specifies an upper limit on the number of rows that the drillthrough can return. This is important because drillthrough can return a large number of rows, especially if the cell coordinate is at a high granularity. Client applications can use MAXROWS to prevent themselves from getting inundated by a huge drillthrough result requested by an unsuspecting user. The default value for MAXROWS is 10,000.

MDX SELECT

The MDX SELECT statement must specify a single cell in the cube. Moreover, the cell cannot have a calculated member (only regular members) on any of its coordinates.

For example, the following MDX SELECT statements are valid for drillthrough and they produce the same result.

1. DRILLTHROUGH SELECT    {[Measures].[Store Sales]} on 0,    {[Customer].[Geography].[USA]} on 1 FROM [Sales] WHERE    {[Time].[Year].[2005]}
2. DRILLTHROUGH SELECT    {[Measures].[Store Sales],    [Customer].[Geography].[USA], [Time].[Year].[2005]} on 0 FROM [Sales]

The following MDX SELECT statements are not valid for drillthrough and they produce an error.

1. DRILLTHROUGH SELECT    {[Measures].[Store Sales]} on 0,    [Customer].[Geography].[USA].children on 1 FROM [Sales] WHERE    {[Time].[Year].[2005]}
2. DRILLTHROUGH SELECT    {[Measures].[Store Sales]} on 0,    {[Customer].[Geography].[USA]} on 1 FROM [Sales] WHERE    {[Time].[Year].[2004], [Time].[Year].[2005]}

RETURN Clause

The RETURN clause specifies the list of columns to be returned in the drillthrough result. The columns can be either measures or dimension attributes, optionally aliased using the AS clause.

Measures

When a measure is specified in the RETURN clause, the drillthrough result column will contain the value of that measure for that detail level record. Interestingly, the RETURN clause can contain measures from a different measure group than the cell coordinate for which drillthrough is being executed (an example of this will be described later in this document). However all measures in the RETURN clause must belong to the same measure group, otherwise the drillthrough will fail. This is because drillthrough is internally executed on a specific measure group and can only return results from that one measure group. We will refer to this as the "target measure group" henceforth in this document.

Dimension Attributes

When a dimension attribute is specified in the RETURN clause, the drillthrough result column will contain the member name of that attribute for that detail level record. Attributes are actually complex entities containing multiple scalar values. So the RETURN clause supports functions that can extract these scalar values.

• Key(attr, N): Returns the Nth part of the composite key of the member.
• Name(attr): Returns the name of the member.
• MemberValue(attr): Returns the value of the member. This is the default.
• Caption(attr): Returns the caption of the member.
• UniqueName(attr): Returns the unique name of the member.
• CustomRollup(attr): Returns the custom rollup expression of the member.
• CustomRollupProperties(attr): Returns the custom rollup properties of the member.
• UnaryOperator(attr): Returns the unary operator of the member.

The following example illustrates a complex RETURN clause.

DRILLTHROUGH MAXROWS 10
SELECT 
    {[Measures].[Store Sales]} on 0, 
    {[Customer].[Geography].[USA]} on 1
FROM [Sales]
WHERE
    {[Time].[Year].[2005]}
RETURN
    [Measures].[Store Sales] AS [Sales], 
    [Measures].[Store Cost] AS [Cost], 
    [Measures].[Unit Sales] AS [Units Sold], 
    Caption[Customer].[Name]) AS [Customer Name], 
    Caption([Store].[Name]) AS [Store Name], 
    Caption([Product].[Name]) AS [Product Name], 
    MemberValue([Time].[Day]) AS [Date Of Sale]

The RETURN clause is optional in a DRILLTHROUGH statement. When it is unspecified, a default RETURN clause is used which contains:

1. A column for each granularity attribute of the target measure group.
2. A column for each measure of the target measure group.

The default return clause represents a typical fact table structure. The cube designer can override the default RETURN clause by creating a "default" drillthrough action (described in the next section).

Drillthrough Actions

An action is a pre-defined operation scoped to a specific portion of the cube. Client applications can query the server for the list of actions (using the MDSCHEMA_ACTIONS schema rowset) for a given scope and expose them to the user. The action is typically executed by the client application only when the user elects to do so.

The target of an action is the portion of the cube that the action applies to. The target can be a cube, dimension, hierarchy, level, dimension members, hierarchy members, level members, set, cells, etc. An action that targets cells can be further restricted to a subspace of the cube using an MDX expression.

The type of an action indicates what the client application should do with the action expression. The typical action types are URL, HTML, Statement, Rowset, Dataset, etc. The action expression for a URL action would be a URL. The action expression for a Rowset action would be a query that when executed on the Analysis server returns a rowset.

Analysis Services 2005 introduces a new action type called Drillthrough. The target of a drillthrough action can only be cells in the cube. The MDSCHEMA_ACTIONS schema rowset exposes these as rowset actions. The action expression is a DRILLTHROUGH statement that can be executed by the client application and the resulting rowset displayed to the user.

Clearly drillthrough fits in very cleanly into the actions framework. But the real advantage of drillthrough actions is that it provides the cube designer with the ability to pre-define the return columns of the DRILLTHROUGH statement (Figure 2). This is analogous to the Analysis Services 2000 experience where the database administrator specifies the tables and columns in the Drillthrough Options dialog in Analysis Manager.

Figure 2. The Actions tab showing Drillthrough actions on AdventureWorks (click image to zoom)

There is an interesting Boolean property called Default on a drillthrough action. A cube can have multiple drillthrough actions with Default=true. The Default property does not affect the behavior of the action itself. When a client sends a DRILLTHROUGH statement that does not contain the RETURN clause, the server looks for a default drillthrough action whose target subspace contains the cell coordinate for which drillthrough is being executed. If such an action is found, the server uses the return columns from that action. If there are multiple drillthrough actions that meet these criteria, the server picks one arbitrarily. Thus default drillthrough actions enable the cube designer to override the default RETURN clause.

Scenarios

In this section, we will use some typical scenarios to illustrate how drillthrough can be effectively set up in Analysis Services 2005. The general steps required to set up drillthrough are as follows:

1. Modify the cube schema to include the drillthrough return columns. Every return column should be included as an attribute or a measure.
2. Create a default drillthrough action containing all the return columns.

Degenerate Dimension

Consider the following schema from the AdventureWorks sample database (Figure 3).

Figure 3. AdventureWorks sample database schema (click image to zoom)

The FactResellerSales fact table has foreign keys (e.g., ProductKey, OrderDateKey, ResellerKey, etc.) pointing to various dimension tables. In addition, it has two columns, SalesOrderNumber and SalesOrderLineNumber, that refer to the specific sales order line that contributed to this transaction. These columns are typically not interesting from the perspective of dimensional analysis. However they are great candidates for return columns in a drillthrough result since they provide the finest detail about the transactions that contributed to a particular cell.

To include SalesOrderNumber and SalesOrderLineNumber as return columns:

1. Create a dimension with these columns as attributes.
2. Add the dimension to the cube.
3. In the Dimension Usage tab of the cube editor, add the dimension to the measure group as a degenerate dimension (also called fact dimension).
4. Include the attributes in the default drillthrough action.

Degenerate dimensions are typically large since their cardinality is the same as the fact table. Cube designers who are concerned about the size can set the storage mode of the dimension to ROLAP. Dimensional analysis that does not query the degenerate dimension will be unaffected, but drillthrough queries will be slower as a result.

Since the degenerate dimension is a primary key for the fact table, any other drillthrough return columns in the fact table can also be added to it.

Alternate Fact Table

There are certain scenarios where the drillthrough result for a cell must come from a different fact table than the target measure group. For example, consider the following schema (Figure 4).

The sales_by_day fact table contains sales transactions for each day. The sales_by_month fact table is a summary of sales_by_day containing the aggregate sales by month. For typical dimensional analysis, querying sales by month is sufficient. Hence the cube is built on the sales_by_month fact table. Moreover, the sales_by_day fact table is much larger, and managing a cube based on it is more expensive. On the other hand, it is very reasonable for a drillthrough request to return rows from the sales_by_day fact table, since it contains the finest detail about the transactions that contributed to a particular cell.

Figure 4. A schema illustrating how drillthrough results can come from a different table than the target measure group (click image to zoom)

To return rows from sales_by_day in the drillthrough result:

1. Create a new measure group in the cube based on sales_by_day. Include the measures that are required as drillthrough return columns. Set the granularity appropriately for all dimensions in the Dimension Usage tab of the cube designer.
2. Optionally set the storage mode to ROLAP to minimize processing and storage overhead.
3. Include the measures from this measure group in the default drillthrough action, along with any dimension attributes. Note that you cannot include measures from multiple measure groups, as discussed earlier.

Multiple Measure Groups

When a cube has multiple measure groups with different dimensionalities (e.g. Sales and Inventory), the drillthrough return columns will typically be different depending on the target measure group.

To enable drillthrough on a cube with multiple measure groups:

1. Add the drillthrough return columns to the cube schema as attributes or measures.
2. Create multiple drillthrough actions with Default=true, one for each measure group.
3. For each action, set the target as: MeasureGroupMeasures("<measure group name>"). This restricts the scope of the action to that measure group.
4. For each action, set the return columns as appropriate for the target measure group.

Migration

Due to the major architectural changes in drillthrough, drillthrough settings in Analysis Services 2000 databases are not preserved by Analysis Services 2005 upgrade or migration. The drillthrough settings must be manually migrated.

Following is the rough sequence of steps to be performed for migrating the drillthrough settings.

1. First of all, migrate the AS 2000 database to an AS 2005 server. The drillthrough settings will not be carried over.

2. Connect to the AS 2000 database using Analysis Manager.

3. Connect to the migrated AS 2005 database using the Business Intelligence Development Studio.

4. Examine the drillthrough settings in the Analysis Services 2000 database. If the return columns are already in the cube schema (as dimension attributes or measures), skip steps 5-6.

5. Edit the DataSourceView in the AS 2005 database. You will notice that migration has included only the tables and columns that are used by dimension attributes or measures. In order to include additional attributes or measures, we first need to add them to the DataSourceView. Right click on the DataSourceView designer surface and choose "Refresh..." This will bring up a dialog (Figure 5) showing the list of changes that will be made to the DataSourceView. Press OK to accept the changes.

   

   Figure 5. The Refresh Data Source View dialog shows the changes to be made. (click image to zoom)

6. Every drillthrough return column that is not already in the cube schema should be added as one of the following. Use the guidelines in the previous section on scenarios to decide on the right approach.

   • A new dimension
   • A new attribute in an existing dimension
   • A new measure group
   • A new measure in an existing measure group

7. Define a default drillthrough action containing all the attributes and measures corresponding to the drillthrough settings in the AS 2000 cube. If this is a cube with multiple measure groups (this is how virtual cubes are migrated), then you will have to define a drillthrough action for each measure group.

After completing these steps, the AS 2005 cube should be drillthrough enabled and any DRILLTHROUGH statements used against the AS 2000 cube should return the same results.

Conclusion

Drillthrough is an important analytical tool in many Analysis Services applications. Analysis Services 2005 offers a new drillthrough architecture including drillthrough actions that simplify the building of such applications.