You design a Business Intelligence (BI) solution by using SQL Server 2008. A data warehouse named CustomerDW contains a Fact table named FactCustomer. The FactCustomer table contains two columns named CustomerKey and CustomerSales. You create a data mining model named CustomerModel by using SQL Server 2008 Analysis Services (SSAS).
A report that is developed by using SQL Server 2008 Reporting Services (SSRS) lists the top 50 customers based on the sales amount. The report extracts data from a SQL Server relational database.
You add a column named UpSell to the report.
You need to ensure that the UpSell column displays the probability values of the expensive products that customers are likely to purchase.
Which Data Mining Extensions (DMX) query should you use?
A.
SELECT PredictProbability(t.[UpSell]) as [UpSell],
[CustomerKey], m.[CustomerSales]
From [CustomerModel] m
PREDICTION JOIN OPENQUERY([CustomerDW],
‘SELECT
[CustomerKey], [CustomerSales] From FactCustomer
ORDER BY [CustomerSales]
‘) AS t
ON m.[CustomerKey] = t.[CustomerKey]
B.
SELECT PredictProbability(m.[UpSell]) as [UpSell],
[CustomerKey], t.[CustomerSales]
From [CustomerModel] m
PREDICTION JOIN
OPENQUERY([CustomerDW],
‘SELECT TOP 50
[CustomerKey], [CustomerSales]
FROM FactCustomer
ORDER BY [CustomerSales]
‘) AS t
C.
SELECT PredictProbability(m.[UpSell]) as [UpSell],
[CustomerKey], t.[CustomerSales]
From [CustomerModel] m
PREDICTION JOIN OPENQUERY([CustomerDW],
‘SELECT TOP 50
[CustomerKey],[CustomerSales]
From FactCustomer
ORDER BY [CustomerSales]
‘) AS t
ON m.[CustomerKey] = t.[CustomerKey]
D.
SELECT Probability(m.[UpSell]) as [UpSell],
[CustomerKey], t.[CustomerSales]
From [CustomerModel] m
PREDICTION JOIN OPENQUERY([CustomerDW],
‘SELECT
[CustomerKey], [CustomerSales]
From FactCustomer
ORDER BY [CustomerSales]
‘) AS t
ON m.[CustomerKey] = t.[CustomerKey]
Explanation:
Tip: "Data Mining Extensions" (that asks about MDX command) = "TOP 50 … ON" (with TOP 50 and ON clause)http://msdn.microsoft.com/en-us/library/ms132031.aspx
SELECT FROM <model> PREDICTION JOIN (DMX)
SQL Server 2008 R2 Other Versions
Uses a mining model to predict the states of columns in an external data source. The PREDICTION JOIN statement matches each case from the source query to the model.SELECT [FLATTENED] [TOP <n>] <select expression list>
FROM <model> | <sub select> [NATURAL] PREDICTION JOIN
<source data query> [ON <join mapping list>]
[WHERE <condition expression>]
[ORDER BY <expression> [DESC|ASC]]DMX Prediction Queries
The Data Mining Extensions language is modeled after the SQL query language. You probably recognized the SQL-like syntax of SELECTFROMJOINON from the code generated in
the preceding example. Note the use of the DMX Predict function and the PREDICTION JOIN keyword.(Smart Business Intelligence Solutions with Microsoft SQL Server 2008, Copyright 2009 by Kevin Goff and Lynn Langit)
Answer C contains: TOP 50 ( top 50 customers based on the sales amount), ON m.[CustomerKey] = t.[CustomerKey]