Share via

Drillthrough Queries (Data Mining)

  • Article

A drillthrough query lets you retrieve details from the underlying cases or structure data, by sending a query to the mining model. Drillthrough is useful if you want to view the cases that were used to train the model, versus the cases that are used to test the model, or if you want to see additional details from the case data.

Analysis Services Data Mining provides two different options for drillthrough:

  • Drilling through to the model cases

    Drillthrough to model cases is used when you want to go from a specific pattern in the model-such as a cluster or branch of a decision tree-and view details about the individual cases.

  • Drilling through to the structure cases

    Drillthrough to structure cases is used when the structure contains information that might not be available in the model. For example, you would not use customer contact information in a clustering model, even if the data was included in the structure. However, after you create the model, you might want to retrieve contact information for customers who are grouped into a particular cluster.

This section provides examples of how you can create these queries.

Using Drillthrough in Data Mining Designer

Creating Drillthrough Queries using DMX

Considerations When Using Drillthrough

Using Drillthrough in Data Mining Designer

If a mining model has been configured to allow drillthrough, and if you have the appropriate permissions, when you browse the model, you can click on a node in the appropriate viewer and retrieve detailed information about the cases in that particular node.

Drill Through to Case Data from a Mining Model.

If the training cases were cached when you processed the mining structure, and you have the necessary permissions, you can return information from the model cases and from the mining structure, including columns that were not included in the mining model.

Creating Drillthrough Queries using DMX

You can drill through to case data by creating a DMX query, if you have permissions on the model or on the structure. For examples of the syntax for creating drillthrough queries in DMX, see the following topic:

Create Drillthrough Queries using DMX

Considerations When Using Drillthrough

  • If you use the Data Mining Wizard, the option to enable drillthrough to the model cases is on the final page of the wizard. Drillthrough is disabled by default. For more information, see Completing the Wizard (Data Mining Wizard).

  • You can add the ability to drill through on an existing mining model, but if you do, the model must be reprocessed before you can drill through to the data.

  • Drillthrough works by retrieving information about the training cases that was cached when you processed the mining structure. Therefore, if you cleared the cached data after processing the structure by changing the MiningStructureCacheMode property to ClearAfterProcessing, drillthrough will not work. To enable drillthrough to structure columns, you must change the MiningStructureCacheMode property to KeepTrainingCases and then reprocess the structure.

  • If the mining structure does not allow drillthrough but the mining model does, you can view information only from the model cases, and not from the mining structure.

Security Issues for Drillthrough

If you want to drill through to structure cases from the model, you must verify that both the mining structure and the mining model have the AllowDrillThrough property set to True. Moreover, you must be a member of a role that has drillthrough permissions on both the structure and the model. For information about how to create roles, see Role Designer (Analysis Services - Multidimensional Data). see.

Drillthrough permissions are set separately on the structure and model. The model permission lets you drill through from the model, even if you do not have permissions on the structure. Drillthrough permissions on the structure provide the additional ability to include structure columns in drillthrough queries from the model, by using the StructureColumn (DMX) function.

Note

If you enable drillthrough on both the mining structure and the mining model, any user who is a member of a role that has drillthrough permissions on the mining model can also view columns in the mining structure, even if those columns are not included in the mining model. Therefore, to protect sensitive data, you should set up the data source view to mask personal information, and allow drillthrough access on the mining structure only when necessary.

Limitations on Drillthrough

  • The following limitations apply to drillthrough operations on a model, depending on the algorithm that was used to create the model:
Algorithm name Issue
Microsoft Naïve Bayes algorithm Not supported. These algorithms do not assign cases to specific nodes in the content.
Microsoft Neural Network algorithm Not supported. These algorithms do not assign cases to specific nodes in the content.
Microsoft Logistic Regression algorithm Not supported. These algorithms do not assign cases to specific nodes in the content.
Microsoft Linear Regression algorithm Supported. However, because the model creates a single node, All, drilling through returns all the training cases for the model. If the training set is large, loading the results may take a very long time.
Microsoft Time Series algorithm Supported. However, you cannot drill through to structure or case data by using the Mining Model Viewer in Data Mining Designer. You must create a DMX query instead.

Also, you cannot drill through to specific nodes, or write a DMX query to retrieve cases in specific nodes of a time series model. You can retrieve case data from either the model or the structure by using other criteria, such as date or attribute values.

You can also return the dates from the cases in the model, by using the Lag (DMX) function.

If you wish to view details of the ARTXP and ARIMA nodes created by the Microsoft Time Series algorithm, you can use the Microsoft Generic Content Tree Viewer (Data Mining).

Related Tasks

Use the following links to work with drillthrough in specific scenarios.

Task Link
Procedure describing use of drillthrough in the Data Mining Designer Drill Through to Case Data from a Mining Model
To alter an existing mining model to allow drillthrough Enable Drillthrough for a Mining Model
Enabling drillthrough on a mining structure by using the DMX WITH DRILLTHROUGH clause CREATE MINING STRUCTURE (DMX)
For information about assigning permissions that apply to drillthrough on mining structures and mining models Grant permissions on data mining structures and models (Analysis Services)

See Also

Data Mining Model Viewers
Data Mining Queries