未加星标

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

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

By:Siddharth Mehta || 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 inSSDT and query the data withSSMS.

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.


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

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


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

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.


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

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.


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

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 Update:

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLSQL ServerSAS
分页:12
转载请注明
本文标题:How to query a SQL Server Analysis Services Data Mining Model with DMX
本站链接:http://www.codesec.net/view/480326.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(44)