Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server 2019 and earlier Analysis Services 
Azure Analysis Services
Fabric/Power BI Premium
Important
Data mining was deprecated in SQL Server 2017 Analysis Services and now discontinued in SQL Server 2022 Analysis Services. Documentation is not updated for deprecated and discontinued features. To learn more, see Analysis Services backward compatibility.
To create a prediction query in SQL Server Data Mining, first select the mining model the query is based on. You can select any mining model that exists in the current project.
After you select a model, add a prediction function to the query to get a prediction. You can also add prediction functions that return related statistics, such as the probability of the predicted value, or information that was used in generating the prediction.
Prediction functions can return the following types of values:
The name of the predictable attribute, and the value that is predicted.
Statistics about the distribution and variance of the predicted values.
The probability of a specified outcome, or of all possible outcomes.
The top or bottom scores or values.
Values associated with a specified node, object, or attribute.
The type of prediction functions that are available depend on the type of model you're working with. For example, prediction functions applied to decision tree models can return rules and node descriptions. Prediction functions for time series models can return the lag and other information specific to time series.
For a list of the prediction functions that are supported for almost all model types, see General Prediction Functions (DMX).
For examples of how to query a specific type of mining model, see the algorithm reference topic in Data Mining Algorithms (Analysis Services - Data Mining).
Choose a mining model to use for prediction
In SQL Server Management Studio, right-click the model, and select Build Prediction Query.
--OR--
In SQL Server Data Tools, select the Mining Model Prediction tab, and then select Select Model in the Mining Model table.
In the Select Mining Model dialog box, select a mining model, and then select OK.
You can choose any model within the current SQL Server Analysis Services database. To create a query using a model in a different database, you must either open a new query window in the context of that database, or open the solution file that contains that model.
Add prediction functions to a query
In Prediction Query Builder, configure the input data used for prediction by providing values in the Singleton Query Input dialog box or by mapping the model to an external data source.
For more information, see Choose and Map Input Data for a Prediction Query.
Important
You don't need to provide inputs to generate predictions. When there's no input, the algorithm typically returns the most likely predicted value across all possible inputs.
Select the Source column and choose a value from the list:
Value Description <model name> Select this option to include values from the mining model in the output. You can only add predictable columns.
When you add a column from the model, the result returned is the non-distinct list of values in that column.
The columns that you add with this option are included in the SELECT portion of the resulting DMX statement.Prediction Function Select this option to browse a list of prediction functions.
The values or functions you select are added to the SELECT portion of the resulting DMX statement.
The list of prediction functions isn't filtered or constrained by the type of model you select. Therefore, if you have any doubt about whether the function is supported for the current model type, you can add the function to the list and see if there's an error.
List items that are preceded by $, such as $AdjustedProbability, represent columns from the nested table that's output when you use the function PredictHistogram. You can use these shortcuts to return a single column and not a nested table.Custom Expression Select this option to type a custom expression and then assign an alias to the output.
The custom expression is added to the SELECT portion of the resulting DMX prediction query.
This option is useful if you want to add text for output with each row, to call VB functions, or to call custom stored procedures.
For information about using VBA and Excel functions from DMX, see VBA functions in MDX and DAX.After adding each function or expression, switch to DMX view to see how the function is added within the DMX statement.
Important
The Prediction Query Builder doesn't validate the DMX until you select Results. Sometimes the expression that the query builder produces isn't valid DMX. Typical causes are referencing a column that isn't related to the predictable column, or trying to predict a column in a nested table, which requires a sub-SELECT statement. At this point, you can switch to DMX view and continue editing the statement.
Example: Create a query on a clustering model
If you don't have a clustering model available for building this sample query, create the model TM_Clustering by using the Basic Data Mining Tutorial.
From SQL Server Management Studio, right-click the model TM_Clustering and select Build Prediction Query.
From the Mining Model menu, select Singleton Query.
In the Singleton Query Input dialog box, set the following values as inputs:
Gender = M
Commute Distance = 5-10 miles
In the query grid, select the TM_Clustering mining model for Source, and add the column Bike Buyer.
For Source, select Prediction Function, and add the function, Cluster.
For Source, select Prediction Function, add the function, PredictSupport, and drag the model column Bike Buyer into the Criteria/Argument box. Type Support in the Alias column.
Copy the expression representing the prediction function and column reference from the Criteria/Argument box.
For Source, select Custom Expression, type an alias, and then reference the Excel CEILING function by using the following syntax:
Excel as <return type>Paste the column reference as the argument to the function.
For example, the following expression returns the CEILING of the support value:
EXCEL!CEILING(PredictSupport([TM_Clustering].[Bike Buyer]),2)Type CEILING in the Alias column.
Select Switch to query text view to review the DMX statement that was generated, and then select Switch to query result view to see the columns the prediction query output.
The following table shows the expected results:
Bike Buyer $Cluster SUPPORT CEILING 0 Cluster 8 954 953.948638926372
To add other clauses elsewhere in the statement, such as a WHERE clause, you can't use the grid. You must switch to DMX view first.