Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All


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

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 Note

You cannot enable or disable drillthrough on the mining structure by using Data Mining Extensions (DMX).

SELECT [TOP n] <expression list> FROM <structure>.CASES
[WHERE <condition expression>][ORDER BY <expression> [DESC|ASC]]


Optional. An integer that specifies how many rows to return.

expression list

A comma-separated list of expressions.

An expression can include column identifiers, user-defined functions, and VBA functions.


The name of the structure.

condition expression

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 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 DW Multidimensional 2012  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.

FROM [Targeted Mailing].Cases
WHERE [Bike Buyer] = 1

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

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

Community Additions

© 2015 Microsoft