DRILLTHROUGH Statement (MDX)
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Retrieves the underlying table rows that were used to create a specified cell in a cube.
Drillthrough is an operation in which an end user selects a single cell from a cube and retrieves a result set from the source data for that cell in order to get more detailed information. By default, a drillthrough result set is derived from the table rows that were evaluated to calculate the value of the selected cube cell. For end users to drill through, their client applications must support this capability. In Microsoft SQL Server Analysis Services, the results are retrieved directly from MOLAP storage, unless ROLAP partitions or dimensions are queried.
Drillthrough security is based on the general security options defined on the cube. If a user cannot get some data by using MDX, drillthrough will also restrict the user in the exactly the same manner.
An MDX statement specifies the subject cell. The value specified by the MAXROWS argument indicates the maximum number of rows that should be returned by the resulting rowset.
By default, the maximum number of rows that are returned is 10,000 rows. This means that if you leave MAXROWS unspecified, you will get 10,000 rows or less. If this value is too low for your scenario, you can set MAXROWS to a higher number, such as MAXROWS 20000. If it is too low overall, you can increase the default by changing the OLAP\Query\DefaultDrillthroughMaxRows server property. For more information about changing this property, see Configure Server Properties in Analysis Services.
Unless otherwise specified, the columns returned include all granularity attributes for all dimensions related to the measure group of the specified measure, other than many-to-many dimensions. Cube dimensions are preceded by $ to distinguish between dimensions and measure groups. The RETURN clause is used to specify the columns returned by the drillthrough query. The following functions can be applied to a single attribute or measure by the RETURN clause.
The following example specifies cell for the month of July, 2007 for the reseller sales amount measure (the default measure) for the country of Australia. The RETURN clause specifies that the date of each sale, the product model name, the employee name, the sales amount, the tax amount and the product cost values that underlie this cell be returned.
DRILLTHROUGH SELECT ([Date].[Calendar].[Month].[July 2007]) ON 0 FROM [Adventure Works] WHERE [Geography].[Country].[Australia] RETURN [$Date].[Date] ,KEY([$Product].[Model Name]) ,NAME([$Employee].[Employee]) ,[Reseller Sales].[Reseller Sales Amount] ,[Reseller Sales].[Reseller Tax Amount] ,[Reseller Sales].[Reseller Standard Product Cost]