Create drillthrough queries by using DMX

Applies to: SQL Server 2019 and earlier Analysis Services Azure Analysis Services Fabric/Power BI Premium

Important

Data mining was deprecated in SQL Server 2017 Analysis Services and now discontinued in SQL Server 2022 Analysis Services. Documentation is not updated for deprecated and discontinued features. To learn more, see Analysis Services backward compatibility.

For all models that support drillthrough, you can retrieve case data and structure data by creating a DMX query in SQL Server Management Studio or any other client that supports DMX.

Important

To view the data, you must enable drillthrough and have the necessary permissions.

Specifying drillthrough options

The general syntax for retrieving model cases and structure cases is as follows:

SELECT <model column list>, StructureColumn('<structure column name>') FROM <modelname>.CASES  

For more information about using DMX queries to return case data, see SELECT FROM <model>.CASES (DMX) and SELECT FROM <structure>.CASES.

Examples

The following DMX query returns the case data for a specific product series from a time series model. The query also returns the column Amount, which wasn't used in the model but is available in the mining structure.

SELECT [DateSeries], [Model Region], Quantity, StructureColumn('Amount') AS [M200 Pacific Amount]  
FROM Forecasting.CASES  
WHERE [Model Region] = 'M200 Pacific'  

In this example, the query uses an alias to rename the structure column. If you don't assign an alias to the structure column, the column is returned with the name 'Expression'. This name is the default for all unnamed columns.

See also

Drillthrough Queries (Data Mining)
Drillthrough on Mining Structures