Data Analytics: Exploiting the Data Warehouse - Data Warehouse Systems: Design and Implementation - page 348

Node Caption Support Children

00000000400 Year Established < 1955 153 2

00000000401 Year Established > = 1955 and < 1960 50 2

00000000402 Year Established > = 1960 and < 1970 110 4

00000000403 Year Established > = 1970 and < 1975 71 2

00000000404 Year Established > = 1975 and < 1990 239 7

0000000040402 Business Type = ' Delicatessen ' 43 2

··· ··· ··· ···

We can then issue a prediction query that uses the decision tree model that
has been produced. For this, we use the table called NewCustomers in Fig. 9.1 .
Each new customer can be input to the model to check if she is likely to place
a $3,500 value order. The query uses the DMX function PredictProbability to
display the probability of a customer with the attribute values indicated in
the subquery (e.g., a store surface of 773 square meters and established in
1956), to be classified as a high-valued one ( HighValueCust = 1 ):

SELECT [High Value Cust], PredictProbability([High Value Cust], 1) AS [Probability],
FROM [NWDW Decision Tree] NATURAL PREDICTION JOIN
( SELECT ' Restaurant ' AS [Business Type], 5 AS [Permanent Employees],
1956 AS [Year Established], 1 AS [Annual Profit],
773 AS [Store Surface], 460 AS [Parking Surface] ) AS T

This query results in the following table, which tells that there is a probability
of 75% that the customer places an order above $3,500:

High Value Cust Probability
1

0.7551

The next query does the same, but scans the whole NewCustomers table using
the OPENQUERY statement. Note that the PREDICTION JOIN operation is
performed between the attributes in the table and the ones in the model. The
final WHERE clause of the query filters the results to return only high-valued
customers:

SELECT CompanyName, BusinessType, PermanentEmployees AS [PE],
YearEstablished AS [YE], AnnualProfit AS [AP],
StoreSurface AS [SS], ParkingSurface AS [PS],
PredictProbability([High Value Cust], 1) AS [Prob=1],
PredictProbability([High Value Cust], 0) AS [Prob=0]
FROM [NWDW Decision Tree] PREDICTION JOIN
OPENQUERY(Sales,
' SELECT CompanyName, BusinessType, PermanentEmployees,
YearEstablished, AnnualProfit, StoreSurface, ParkingSurface
FROM NewCustomers ' )ASTON
[NWDW Decision Tree].[Business Type] = T.[BusinessType] AND