Create a Content Query on a Mining Model
You can query the mining model content programmatically by using AMO or XML/A, but it is easier to create queries by using DMX. You can also create queries against the data mining schema rowsets by establishing a connection to the Analysis Services instance and creating a query using the DMVs provided by Analysis Services.
The following procedures demonstrate how to create queries against a mining model by using DMX, and how to query the data mining schema rowsets.
For an example of how to create a similar query by using XML/A, see Create a Data Mining Query by Using XML/A.
Querying Data Mining Model Content by Using DMX
To create a DMX model content query
In SQL Server Management Studio, on the View menu, click Template Explorer.
In the Template Explorer pane, click the cube icon to change the list and display Analysis Services templates.
In the list of template categories, expand DMX, expand Model Content, and double-click Content Query.
In the Connect to Analysis Services dialog box, select the instance that contains the mining model you want to query, and click Connect.
The Content Query template opens in the appropriate code editor. The metadata pane lists the models that are available in the current database. To change the database, select a different database from the Available Databases list.
Enter the name of a mining model in the line, FROM [<mining model, name, MyModel>].CONTENT. If the mining model name contains spaces, you must enclose the name in brackets.
If you don't want to type the name, you can select a mining model in Object Explorer and drag it into the template.
In the line, SELECT<select list, expr list, *>, type the names of columns in the mining model content schema rowset.
To view a list of columns that you can return in mining model content queries, see Mining Model Content (Analysis Services - Data Mining).
Optionally, type a condition in the WHERE clause of the template to restrict the rows returned to specific nodes or values.
Click Execute.
Querying the Data Mining Schema Rowsets
To create a query against the data mining schema rowset
In SQL Server Management Studio, on the New Query toolbar, click Analysis Services DMX Query, or Analysis Services MDX query.
In the Connect to Analysis Services dialog box, select the instance that contains the objects you want to query, and click Connect.
The Content Query template opens in the appropriate code editor. The metadata pane lists the objects that are available in the current database. To change the database, select a different database from the Available Databases list.
In the query editor, type the following:
SELECT *
FROM $system.DMSCHEMA_MINING_MODEL_CONTENT
WHERE MODEL_NAME = '<model name>'
Click Execute.
The Results pane displays the contents of the model.
[!NOT]
To view a list of all the schema rowsets that you can query on the current instance, use this query: SELECT * FROM $system.DISCOVER_SCHEMA_ROWSETS. Or, for a list of schema rowsets specific to data mining, see Data Mining Schema Rowsets.