.png)
SELECT FROM <structure>.CASES
Returns the cases that were used to create the mining structure.
If drillthrough is not enabled on the structure, the statement will fail. Also, the statement will fail if the user does not have drillthrough permissions on the mining structure.
In Analysis Services, drillthrough on new mining structures is enabled by default. To verify whether drillthrough is enabled for a particular structure, check whether the value of the CacheMode property is set to KeepTrainingCases.
If the value of CacheMode is changed to ClearAfterProcessing, the structure cases are cleared from the cache and you cannot use drillthrough.
 Note |
|---|
You cannot enable or disable drillthrough on the mining structure by using Data Mining Extensions (DMX). |
If drillthrough is enabled on both the model and the structure, any member of a role that has drillthrough permissions on the mining structure and the model can return structure columns that were not included in the model, by using the following syntax:
SELECT StructureColumn('<column name>') FROM <model>.CASES
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 or mining model only when necessary.
The following examples are based on the mining structure, Targeted Mailing, which is based on the Adventure Works DW2008R2 database, and the associated mining models. For more information, see Basic Data Mining Tutorial.
Example 1: Drill through to Structure Cases
The following example returns a list of the 500 oldest customers in the mining structure, Targeted Mailing. The query returns all the columns in the mining model, but restricts the rows to those who purchased a bike, and orders them by age. You can also edit the expression list to return only the columns that you need.
SELECT TOP 500 * FROM [Targeted Mailing].Cases WHERE [Bike Buyer] = 1 ORDER BY Age DESC;
Example 2: Drillthrough to Test or Training Cases Only
The following example returns a list of the structure cases for Targeted Mailing that are reserved for testing. If the mining structure does not contain a holdout test set, by default all cases are treated as training cases, and this query would return 0 cases.
SELECT [Customer Key], Gender, Age FROM [Targeted Mailing].Cases WHERE IsTestCase();
To return the training cases, substitute the function IsTrainingCase().
