Using Drillthrough on Mining Models and Mining Structures (Analysis Services - Data Mining)
Drillthrough means the ability to query both a mining model and a mining structure to learn details about the cases included in the model or in the structure. SQL Server 2008 provides two different options for drilling through into case data. You can drill through to the cases that were used to build the data, or you can drill through to the cases in the mining structure.
Drilling through to case data 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 review the attributes of the case data.
Drilling through to structure cases is useful when the structure contains information that might not be available in the model. Typically, if you have a mining structure that supports many different kinds of models, the data from the structure is used more selectively 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.
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.
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. For information, see How to: Drill Through to Case Data from a Mining Model.
For information about how to enable drillthrough on an existing mining model, see How to: Enable Drillthrough for a Mining Model.
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).
If you create the mining structure by using DMX, use the WITH DRILLTHROUGH clause. For more information, see CREATE MINING STRUCTURE (DMX).
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, 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). For information about assigning permissions that apply to specific mining structures and mining models, see Granting Access to Mining Structures and Mining Models.
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 wither the model or the structure by using other criteria, such as date or attribute values. 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 Designer). |
Creating Drillthrough Queries by Using DMX
For all models that support drillthrough, and provided drillthrough is enabled, you can drill through to case and structure data by creating a DMX query in SQL Server Management Studio or any other client that supports DMX. 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 example, the following DMX query returns the cases for a specific product line in 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 is 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'.
For more information about using DMX queries to return case data, see SELECT FROM <model>.CASES (DMX) and SELECT FROM <structure>.CASES.
See Also