Edit

Drillthrough on Mining Structures

  • Article

Applies to: SQL Server 2019 and earlier Analysis Services Azure Analysis Services Fabric/Power BI Premium

Important

Data mining was deprecated in SQL Server 2017 Analysis Services and now discontinued in SQL Server 2022 Analysis Services. Documentation is not updated for deprecated and discontinued features. To learn more, see Analysis Services backward compatibility.

Drillthrough means the ability to query either a mining model or a mining structure and get detailed data that is not exposed in the model.

SQL Server 2017 provides two different options for drilling through into case data. You can drill through to the data that were used to build the mining model, or you can drill through to the source data in the mining structure.

Drillthrough to Model Cases vs. Drillthrough to Structure

Drilling through to model cases is useful for finding additional details about rules, patterns or clusters in a model.

In contrast, drillthrough to structure data is intended to provide access to information that was not made available in the model. For example, if you have the appropriate permissions, you might want to find out which rows of data were used for training the model and which were used for testing.

You can also view attributes of the data that were not used in analysis, provided they have been included in the structure definition. For example, often mining structures support many different kinds of models, and some structure columns might have been excluded from a model because the data type was incompatible or the data was not useful for analysis. For example, you would not use customer contact information in a clustering model, even if the data was included in the structure, but by enabling drillthrough you gain access to this information without running separate queries against the data source.

Enabling Drillthrough to Structure Data

To use drillthrough on the mining structure, the following conditions must be met:

  • Drillthrough on the model must also be enabled. By default, drillthrough of both kinds is disabled. To enable drillthrough in the Data Mining Wizard, select the option to enable drillthrough to model cases on the final page of the wizard. You can also add the ability to drillthrough on a model later by changing the AllowDrillthrough property.

  • If you create the mining structure by using DMX, use the WITH DRILLTHROUGH clause. For more information, see CREATE MINING STRUCTURE (DMX).

  • Drillthrough works by retrieving information about the training cases that was cached when you processed the mining structure. Therefore, if you clear 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.

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

Security Issues for Drillthrough

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.

For information about how to create roles and assign permissions in Analysis Services, see Role Designer (Analysis Services - Multidimensional Data).

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.

See the following topics for more information about how to use drillthrough with mining models.

Task Link
Use drillthrough to structure from the mining model viewers Use Drillthrough from the Model Viewers
See examples of drillthrough queries for specific model types. Data Mining Queries
Get information about permissions that apply to specific mining structures and mining models. Grant permissions on data mining structures and models (Analysis Services)

See Also

Drillthrough on Mining Models