Prediction Join

The syntax for a prediction join is as follows:

SELECT [FLATTENED] [TOP ] (select expressions)
FROM (data mining model name)
PREDICTION JOIN
(DMX query)

Analagous to a relational natural join, if you wish to let the system map the columns that are referred to in the DMX query with that in the data mining model, you make use of a NATURAL PREDICTION JOIN.

The [Flattened] keyword allows the user to flatten (i.e. conversion from a nested table to a relational form) the results returned by some of the data mining models (e.g association rules), where the results returned are intrinsically nested.

The [TOP ] keywords tell the system to return only the Top n results.

Each time you build a data mining model using Business Intelligence Development Studio, and you deploy it, it is kept and maintained by Analysis Services, similar to how relational tables are maintained by SQL Server. Each of these mining model has a model_name (similar to a table name), and you refer to this name in the FROM clause.

In the DMX query, you provide the cases you want the mining model to predict. If the nature of the input is nested, then you have to provide a case in a nested form. E.g.

SELECT
PREDICT([Association].[v Assoc Seq Line Items],INCLUDE_STATISTICS,4)
From
[MyAssocModel]
NATURAL PREDICTION JOIN
(SELECT (SELECT 'O2 Mini' AS [ItemName]) AS t

Note, the multiple select clause used in the DMX query. This denotes a nested query, commonly usely together with the association rule data mining algorithm. The PREDICT function is commonly used to return the predicted value or a set of values w.r.t to the column specified. DMX provides approx 26 DMX functions to be used in a DMX query.