icCube

Documentation

Drillthrough

(MDX)

Allows for retrieving underlying table rows used to calculated cube cell(s).

Description

The DRILLTHROUGH statement is used to retrieve the rows of the data source used to compute one or more cube cells. In addition to supporting multiple cells, note that drilling through calculated members is also supported.


DRILLTHROUGH [MAXROWS integer-value]
SELECT-STATEMENT
[RETURN return-expression [ AS column-name ], ... ]


The optional MAXROWS limits the number of returned rows. See the icCube.xml file for the defaulted value (configuration property: icCube.drillthroughDefaultMaxRows) when not specified.

The optional RETURN statement allows for specifying which information/column is going to be retrieved. When not specified the following columns are returned:

  • multi-cell and/or calc-member mode : RowId(), Cell(), CalcMemberName(), CalcMemberTuple(), Key and Name for each dimension linked to the measure(s) being used during the cell(s) calculation.

  • single-cell mode : RowId(), Key and Name for each dimension linked to the measure being used during the cell calculation as well as all the measures members of the measure group of this measure.

The optional AS column-name allows for renaming the column. The column-name expression must be a valid MDX string.

The following RETURN expressions are available:

RowId() : the row id in the fact table.

Cell() : (multi-cell mode) the tuple of the cell the rows are attached to.

CalcMemberName() : (calc-member mode) the name of the calculated member being computed.

CalcMemberUniqueName() : (calc-member mode) the unique name of the calculated member being computed.

CalcMemberTuple() : (calc-member mode) the tuple of the calculated member being computed.

CalcMemberInstr() : (calc-member mode) some information identifying the current MDX expression being computed in the context of a calculated member calculation.

CalcMember() : (calc-member mode) a shortcut for CalcMemberName() and CalcMemberTuple().

Key( [mdx-dimension-name], n ) : the key of the member of the specified dimension (the optional 0-based n value allows for retrieving composite keys).

Name( [mdx-dimension-name] ) : the name of the member of the specified dimension.

UniqueName( [mdx-dimension-name] ) : the unique name of the member of the specified dimension.

[mdx-dimension-name] : the key and the name of the member of the dimension.

[Measures].[measure-name] : (single-cell mode) the specified measure value.

XMLA Properties

Find here the value of the XMLA properties when not specified otherwise.