Querying Data Mining Models (Analysis Services - Data Mining)

A singleton query is useful when you want to create simple predictions in real time. For example, you might obtain information from a customer by using a Web site, and then use that data to return predictions, presented as recommendations specific to that customer. Or, you might analyze the contents of an e-mail and then use an existing classification model to assign a category to the e-mail and route the message accordingly.

Singleton queries do not require a separate table that contains input. Instead, you pass a single row of data into the model, and a single prediction is returned in real time. You can also extend the singleton query to make multiple predictions by typing a SELECT statement that contains a single case, adding the UNION operator, and then typing another SELECT statement to specify another case.

You can create singleton queries against an existing model in the following ways:

• Using the Data Mining Designer.

• Using a singleton query template.

• Creating a DMX statement programmatically or in another Analysis Services client.

When you create a singleton query, you must provide the new data to the model in the form of a PREDICTION JOIN. This means that even though you are not mapping to an actual table, you must make sure that the new data matches the existing columns in the mining model. If the new data columns and the new data match exactly, Analysis Services will map the columns for you. This is called a NATURAL PREDICTION JOIN. However, if the columns do not match, or if the new data does not contain the same kind and amount of data that is in the model, you must specify which columns in the model map to the new data, or specify the missing values.

For information about how to create a singleton query in Data Mining Designer, see How to: Create a Singleton Query in the Data Mining Designer and Using the Prediction Query Builder to Create DMX Prediction Queries.

For information about using DMX to create a singleton query, see Prediction Queries (DMX).

For an example of how to use the DMX Query templates in SQL Server Management Studio, see How to: Create a Singleton Prediction Query from a Template.

Back to Top

When you perform a prediction join, you map the model to a new data source, and Analysis Services then makes predictions for each row in the new data based on patterns in the model. A prediction join is useful when you have lots of information in a table or other external data source and want to make predictions by using the trained model.

You can create batch prediction queries against an existing model in the following ways:

• Using the Data Mining Designer.

• Using a template.

• Creating a DMX statement programmatically or in another Analysis Services client.

• If you create a batch prediction query by using the Data Mining Designer, the external data source must first be defined as a data source view.

If you use DMX to create a prediction join, you can specify the external data source by using the OPENQUERY, OPENROWSET, or SHAPE commands. The default data access method in the DMX templates is OPENQUERY. For information about these methods, see <source data query>.

Regardless of how you define the external data, the source of data that you specify must contain columns with data that is like the data in the model. However, the new information can be incomplete. For example, your customer list might have an Age column but no information about income. Even if income data was used when training the model, you can still map the new data to the model and create a prediction; however, in some cases, the lack of complete information might affect the quality of predictions.

To get the best results, you should join as many of the matching columns as possible between the new data and the model. However, the query will succeed even if there are no matches. If no columns are joined, the query will return the marginal prediction, which is equivalent to the statement SELECT <predictable-column> FROM <model> without a PREDICTION JOIN clause.

A prediction query is not like a query on a relational database. Each prediction function that you add to a query returns its own rowset. Therefore, when you make a prediction on a single case, the result might be a predicted value together with several columns of nested tables containing additional detail.

Whenever you combine multiple functions in one query, the return results are combined as a hierarchical rowset. However, if your provider cannot handle hierarchical rowsets, you can flatten the results by using the FLATTEN keyword in the prediction query.

For more information, including examples of flattened rowsets, see SELECT (DMX).

Time series models provide more flexibility in the way that you can use new data and create predictions. You can either use the model as it is to create predictions, or you can provide new data to the model to update predictions based on recent trends. If you add new data, you can specify the way the new data should be used, either to extend the model cases, or replace the model cases.

When you extend the model cases, you add the new fact data to the time series model and further predictions are based on the new, combined series. When you replace the model cases, you keep the trained model, but replace the underlying cases with a new set of case data.

Regardless of which approach you use, the starting point for predictions is always the end of the original series.

For example, suppose that you have an existing time series model that has been trained on the sales data from the previous year. After you have collected several months of new sales data, you decide to update your sales forecasts for the current year. You can create a prediction join that updates the model by adding new data and extends the model to make new predictions.

Alternatively, you could build your model based on existing data, and then create a prediction join that replaces the case data with new data. This is useful if, for example, one store is missing a lot of data and you want to use a model built on other store data to make predictions. For more information about how to create prediction joins on time series models, see Querying Time Series Models or PredictTimeSeries (DMX).

Back to Top

You can use a SELECT FROM <model>.CONTENT statement to return different kinds of content, depending on the type of model you are querying. For an association model, nodes that represent rules have a NODE_TYPE value of 8, whereas itemsets have a NODE_TYPE value of 7. Thus, the following query would return the top 10 itemsets, ranked by support (the default ordering).

SELECT TOP 10 NODE_DESCRIPTION, NODE_PROBABILITY, SUPPORT
FROM <model>.CONTENT WHERE NODE_TYPE = 7

The following example returns three columns: the ID of the node, the complete rule, and the product on the right-hand side of the itemset—that is, the product that is predicted to be associated with some other products as part of an itemset.

The FLATTENED keyword indicates that the nested rowset should be converted into a flat table. The attribute that represents the product on the right-hand side of the rule is contained in the NODE_DISTRIBUTION table; therefore, we retrieve only the row that contains an attribute name by adding a requirement that the length be greater than 2. A simple string function is used to remove the name of the model from the third column. Usually the model name is always prefixed to the values of nested columns. The WHERE clause specifies that the value of NODE_TYPE should be 8, to retrieve only rules.

SELECT FLATTENED NODE_UNIQUE_NAME , NODE_DESCRIPTION,
     (SELECT RIGHT(ATTRIBUTE_NAME, (LEN(ATTRIBUTE_NAME)-LEN('Association model name'))) 
FROM NODE_DISTRIBUTION
WHERE LEN(ATTRIBUTE_NAME)>2
) 
AS RightSideProduct
FROM [<Association model name>].CONTENT
WHERE NODE_TYPE = 8 
ORDER BY NODE_SUPPORT DESC

For more examples, see Querying an Association Model (Analysis Services - Data Mining).

One scenario where querying the model content is valuable is when you want to follow up on a prediction query by returning the rule that explains why that state was predicted. For example, you can add the prediction function PredictNodeId (DMX) to a query to obtain the ID of the node that contains the rule, by using the following syntax:

SELECT  Predict([Bike Buyer]), PredictNodeID([Bike Buyer]) 
FROM [<decision tree model name>]
PREDICTION JOIN 
<input rowset> 

For a decision tree model, the caption contains the description of the path to the outcome. Therefore, once you have the ID of the node that contains the outcome, you can retrieve the rule or path that explains the prediction by creating a content query, such as the following:

SELECT NODE_CAPTION
FROM [<decision tree model name>] 
WHERE NODE_UNIQUE_NAME= '<node id>'

For more examples, see Querying a Decision Trees Model (Analysis Services - Data Mining).

Back to Top