Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2016-10-05   |   Comments   |   Related Tips: > 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


next webcast button


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools