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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2016-10-05

Comments For This Article

















get free sql tips
agree to terms