SELECT FROM <model>.CASES (DMX)
APPLIES TO: SQL Server Analysis Services Azure Analysis Services Power BI Premium
Supports drillthrough, and returns the cases that were used to train the model. You can also return structure columns that are not included in the model, if drillthrough has been enabled on the mining structure and on the mining model, and if you have the appropriate permissions.
If drillthrough is not enabled on the mining model, this statement will fail.
In Data Mining Extensions (DMX) you can only enable drillthrough when you create the model. You can add drillthrough to an existing model by using SQL Server Data Tools (SSDT), but the model must be reprocessed before you can view or query the cases.
SELECT [FLATTENED] [TOP <n>] <expression list> FROM <model>.CASES [WHERE <condition expression>][ORDER BY <expression> [DESC|ASC]]
Optional. An integer that specifies how many rows to return.
A comma-separated list of expressions. An expression can include column identifiers, user-defined functions, UDFs, and VBA functions, and others.
To include a structure column that is not included in the mining model, use the function
StructureColumn('<structure column name>').
A model identifier.
A condition to restrict the values that are returned from the column list.
Optional. An expression that returns a scalar value.
If drillthrough is enabled on both the mining model and the mining structure, users who are member of a role that has drillthrough permission on the model and the structure can access columns of the mining structure that are not included in the mining model. Therefore, to protect sensitive data or personal information, you should construct your data source view to mask personal information, and grant AllowDrillthrough permission on a mining structure only when it is necessary.
The Lag (DMX) function can be used with time series models to return or filter on the time lag between each case and the initial time.
Using the IsInNode (DMX) function in the WHERE clause returns only cases that are associated with the node that is specified by the NODE_UNIQUE_NAME column of the schema rowset.
The following examples are based on the mining structure Targeted Mailing, which is based on the AdventureWorksDW2012database and its associated mining models. For more information, see Basic Data Mining Tutorial.
Example 1: Drillthrough to Model Cases and Structure Columns
The following example returns the columns for all the cases that were used to test the Targeted Mailing model. If the mining structure on which the model is built does not have a holdout test data set, this query would return 0 cases. You can use the expression list to return only the columns that you need.
SELECT * FROM [TM Decision Tree].Cases WHERE IsTestCase();
Example 2: Drillthrough to Training Cases in a Specific Node
The following example returns just those cases that were used to train Cluster 2. The node for Cluster 2 has the value '002' for the NODE_UNIQUE_NAME column. The example also returns one structure column, [Customer Key], that was not a part of the mining model, and provides the alias
CustomerID for the column. Note that the name of the structure column is passed as a string value and therefore must be enclosed in quotation marks, not brackets.
SELECT StructureColumn('Customer Key') AS CustomerID, * FROM [TM_Clustering].Cases WHERE IsTrainingCase() AND IsInNode('002')
To return a structure column, drillthrough permissions must be enabled on both the mining model and the mining structure.
Not all mining model types support drillthrough. For information about the models that support drillthrough, see Drillthrough Queries (Data Mining).