DRILLTHROUGH - Analysis Service

DRILLTHROUGH [MAXROWS Unsigned_Integer]
select mdx statement
Return set of attributes and measures



Its used to get detailed information from a cell of aggregated result
If storage is MOLAP then detailed information will be retrived from the cube. If its ROLAP then information will be retrieved from table of source database

MAXROWS is maximum number of rows that will be returned from source table
IF not specified then all rows will be returned

Cube dimension will are preceded by $ to separate them from measures

following information can be retrieved using drill through

Name(attribute_name)
Returns the name of the specified attribute member.

UniqueName(attribute_name)
Returns the unique name of the specified attribute member.

Key(attribute_name[, N])
Returns the key of the specified attribute member, where N specifies column in the composite key (if any). The default value for N is 1.

Caption(attribute_name)
Returns the caption of the specified attribute member.

MemberValue(attribute_name)
Returns the member value of the specified attribute member.

Translation(attribute_name[, N])
Returns the translated value of the specified attribute member, where N is the language.

CustomRollup(attribute_name)
Returns the custom rollup expression of the specified attribute member.

CustomRollupProperties(attribute_name)
Returns the custom rollup properties of the specified attribute member.

UnaryOperator(attribute_name)
Returns the unary operator of the specified attribute member.

Lets take an example


We want row detail for fourth quarter of year 2004

DRILLTHROUGH maxrows 1000
SELECT ([Date].[Calendar].[Calendar Quarter].&[2002]&[4]
)
ON 0
FROM [Adventure Works]
RETURN
[$Date].[Date] AS CALENDAR_DATE
,KEY([$Product].[Model Name]) AS PRODUCTKEY
,NAME([$Employee].[Employee]) AS EMPLOYEENAME
,UNIQUENAME([$Employee].[Employee]) AS EMPLOYEE_UNIQUE_NAME
,CAPTION([$Employee].[Employee]) AS EMPLOYEE_CAPTION
,MEMBERVALUE([$Employee].[Employee]) AS EMPLOYEE_MEMBERVALUE
,[$Geography].[Country] AS COUNTRY
,[Reseller Sales].[Reseller Sales Amount] AS RESELLER_SALES_AMOUNT
,[Reseller Sales].[Reseller Tax Amount] AS RESELLER_TAX_AMOUNT
,[Reseller Sales].[Reseller Standard Product Cost] AS
RESELLER_STANDARD_PRODUCT_COST