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.
2: Open the Customers Mining model, and you should be able to see the visual representation of the cluster as shown below.
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.
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.
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
- Try to apply filters on the DMX Query and refine the dataset to limit the results as per requirements. You can read more about DMX from here.
- Check out the following resources:
