MDX Drillthrough

DRILLTHROUGH Statement

The DRILLTHROUGH statement is used in Multidimensional Expressions (MDX) to retrieve the source rowset or rowsets from the source data for a cube cell or specified tuple. This statement enables a client application to retrieve the rowsets that were used to create a specified cell in a cube. An MDX statement is used to specify the subject cell. All of the rowsets that make up the source data of that cell are returned. The total number of rowsets that are returned can also be affected by the MAXROWS and FIRSTROWSET modifiers. Not all cubes support drill–through. Only cubes that have a drill-through table that is specified at cube creation support drill-through.

Here is the syntax for the DRILLTHROUGH statement:

<drillthrough>    :=DRILLTHROUGH
[<maxrows>][<firstrowset>] <MDX select>
   <maxrows>     := MAXROWS <positive number>     
   <firstrowset> := FIRSTROWSET <positive number>

The following modifiers can be used with the DRILLTHROUGH statement:

MAXROWS

indicates the maximum number of rows that should be returned by the resulting rowset.

FIRSTROWSET

specifies the first rowset to return.

Specifying the Maximum Number of Drill-Through Rows

You can limit the number of drill-through rows that users request in a query by selecting the OLAP server definition setting Maximum number of flattened rows from SAS Management Console. This setting controls the maximum number of flattened rows that are allowed for flattened (two-dimensional) data sets.

The following steps enable you to specify the maximum number of flattened rows:

In the tree view for the Server Manager plug-in of SAS Management Console, select the node for your OLAP server. This is the physical OLAP server and is located by drilling down from the top of the Server Manager tree view.
After selecting the physical OLAP server, right-click and select Properties.
At the SAS OLAP Server Properties dialog box, select the Options tab, and then the Advanced Options button.
At the Advanced Options dialog box, select the Server tab and enter the needed value for the Maximum number of flattened rows field. The default setting is 300,000 rows.

Ensuring That Tables Are Accessible at Query Time

Data that is external to a cube must be available to the SAS OLAP Server under the following conditions:

If the cube does not include an NWAY, then the SAS OLAP Server must have access to the input data source table (also called the detail data) and any specified dimension tables.
If the cube is associated with a drill-through table, then the SAS OLAP Server must have access to the drill-through table.
If the cube uses pre-summarized aggregation tables, then the SAS OLAP Server must have access to those tables.

To ensure that the necessary tables are accessible at query time, the applicable library names need to be allocated when the OLAP server that is associated with the OLAP schema that contains the cubes is invoked.

Note: If any of the tables contain user-defined formats, then the SAS OLAP Server also needs information about how to find those formats. User-defined formats cannot be used with drill-through tables.

Note: For more information, see the SAS Intelligence Platform: Data Administration Guide and the SAS Intelligence Platform: System Administration Guide.

Working with User-Defined Formats

If you have existing SAS data sets, you might also have a catalog of user-defined formats and informats. You have two options for making these formats available to applications such as SAS Data Integration Studio:

The preferred solution is to name the format catalog formats.sas7bcat and to place the catalog in the following directory: path-to-configuration-directory\Lev1\SASMain\SASEnvironment\SASFormats
An alternative method of making user-defined formats “visible” is to follow this procedure:
1. Add a line to the configuration file path-to-configuration-directory\Lev1\SASMain\sasv9.cfg that points to a configuration file for handling user-defined format catalogs. For example, you might add the following line:
  -config path-to-configuration-directory\Lev1\SASMain\userfmt.cfg
2. In the file userfmt.cfg, enter a SET statement and a FMTSEARCH statement.
  -set fmtlib1 "path-to-configuration-directory\Lev1\Data\orformat" -fmtsearch (work fmtlib1.orionfmt library)
  This makes the format catalog fmtlib1.orionfmt available. For more information, see the SAS Intelligence Platform: Data Administration Guide.