SQL Server Analysis Services Documentation

Mining Model Prediction

This document explains how to use mining models to generate predictions in SQL Server Analysis Services.

Introduction to Prediction

After a data mining model has been created and trained, its primary purpose is to make predictions on new data. Analysis Services provides a rich set of prediction functions and tools to integrate predictions into your applications and reports. This section covers the core concepts and methods for obtaining predictions from trained models.

Prediction Queries

Prediction queries are executed against a trained mining model to generate predictions. These queries can be formulated using DMX (Data Mining Extensions) or the AMO (Analysis Management Objects) API. The basic structure of a prediction query involves specifying the model to query, the data to provide as input, and the type of prediction desired.

DMX Prediction Query Example

Here's a simple DMX query to predict whether a customer will churn, based on a hypothetical [Customer Churn Model]:

SELECT
    [Customer Churn Model].[IsCustomerChurned] AS predicted_churn,
    [Customer Churn Model].[IsCustomerChurned].PROBABILITY AS churn_probability
FROM
    [Customer Churn Model]
PREDICTION JOIN
    OPENQUERY(MyDataSourceView, 'SELECT CustomerID, Age, Income FROM Customers WHERE Status = ''Active''')
ON
    [Customer Churn Model].CustomerID = MyDataSourceView.CustomerID

Prediction Functions

DMX provides various functions to retrieve different types of predictions and associated information:

• PREDICT(): Returns the predicted value for a specified column.
• PREDICT_ATTRIBUTES(): Returns the predicted values for all attributes.
• PREDICT_FILTER(): Returns only those attributes that are predicted to be different from their original values.
• PREDICT_SEQUENCE(): For sequence models, predicts the next item in a sequence.
• PROBABILITY(): Returns the probability of a predicted value.
• EXPLAIN_ROW(): Provides an explanation of why a specific prediction was made.

Prediction in Applications

Predictions generated by Analysis Services can be consumed in various ways:

• Reporting Services (SSRS): Embed predictions directly into reports.
• Power BI: Connect to Analysis Services models and visualize predictions.
• Custom Applications: Use AMO or ADOMD.NET to execute prediction queries and integrate results into business applications.

Scenarios for Prediction

• Customer Segmentation: Predict which customer segment a new customer belongs to.
• Sales Forecasting: Predict future sales based on historical data.
• Risk Assessment: Predict the likelihood of loan default or insurance claims.
• Personalization: Predict product recommendations for users.

Best Practices

• Ensure your input data is clean and representative of the data on which the model was trained.
• Understand the prediction functions available and choose the ones that best suit your needs.
• Consider the trade-offs between prediction accuracy and computational cost.
• Monitor model performance over time and retrain as necessary.

For more advanced prediction scenarios, refer to the specific documentation for each mining algorithm and DMX functions.