Export (0) Print
Expand All

Create Drillthrough Queries using DMX

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).

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.

Caution note Caution

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

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

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

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

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 was not 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'

Note that in this example, an alias has been used to rename the structure column. If you do not assign an alias to the structure column, the column is returned with the name 'Expression'. This is the default behavior for all unnamed columns.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft