How to query a SQL Server Analysis Services Data Mining Model with DMX


By:   |   Updated: 2016-10-05   |   Comments   |   Related: > Analysis Services Development

Problem

SQL Server Analysis Services (SSAS) Data Mining models and structures are created using the SQL Server Data Tools (SSDT). A Data Mining model is composed of a structure and a data mining algorithm associated with it. Once the mining model is configured, it is executed against the test data or the target data for prediction and analysis, which results in a set of data structures that can be visualized in SSDT. But often Developers are faced with the requirement to query this structure such that it can be used by client systems in a tabular format. For this, one needs to query the data mining model from SQL Server Management Studio (SSMS) to determine and analyze the output schema of the data mining model.

Solution

Consider you have the sample SSAS AdventureWorks database already deployed on a SSAS Multidimensional instance on your machine.  Follow the below steps to visualize a sample data mining model in SSDT and query the data with SSMS.

1: Open the AdventureWorks OLAP database in SSDT either from the sample project or directly from the server where you would have installed the database. The project should look similar to the one shown below.

New Project in the SQL Server Data Tools

2: Open the Customers Mining model, and you should be able to see the visual representation of the cluster as shown below.

Cluster Visualization in the Mining Model Viewer

3: From the Viewer dropdown, select Microsoft Generic Content Tree Viewer and you should be able to view the actual data set that is used to generate the visualization we saw in the above step.

Microsoft Generic Content Tree Viewer

4: If you carefully analyze the data, you will be able to find that there are 10 clusters and each cluster has a detailed data set associated with it. If this data can be queried from client tools like SSMS or even end client application, the same can be fed to more sophisticated reporting tools to create intuitive visualizations of choice. SSAS facilitates this requirement by the means of Data Mining Extensions (DMX) queries.

5: Open SSMS, and log on to the SSAS instance and database in which this mining model is deployed. As seen in the below screenshot, select the Customer Clusters mining model in the object explorer pane. Type the DMX content query as shown below. The syntax is similar to a SELECT T-SQL query, but used with the .CONTENT keyword with the mining model name. This would result in the entire content dataset of the mining model that we saw in Step 3.

DMX Query for the Content Mining Model

In this way one can query any data mining model using DMX queries and use the resulting dataset in client applications for different intuitive visualizations.

Next Steps


Last Updated: 2016-10-05


get scripts

next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips





Comments For This Article





download


Recommended Reading

Tabular vs Multidimensional models for SQL Server Analysis Services

Comparing Data Warehouse Design Methodologies for Microsoft SQL Server

Create Tabular Model Sample from SQL Server Database - Part 1

Reduce the Size of an Analysis Services Tabular Model Part 1

How to use the TREATAS function in DAX





get free sql tips
agree to terms


Learn more about SQL Server tools