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

SELECT CustomerKey, MAX(TotalAmount) AS MaxAmount
FROM CustOrderTotal
GROUP BY CustomerKey )
SELECT C.CustomerKey, C.CustomerID, C.CompanyName,
C.Address, C.CityKey, D.AnnualProfit, D.AnnualRevenue,
D.BusinessType, D.DateFirstOrder, D.DateLastOrder,
D.OwnershipType, D.ParkingSurface, D.StoreSurface,
D.TotalEmployees, D.PermanentEmployees, D.YearEstablished,
CASE
WHEN M.MaxAmount > = 3500 THEN 1
ELSE 0
END AS HighValueCust
FROM Customer C, CustomerDemographics D, CustMaxAmount M
WHERE C.CustomerKey = D.CustomerKey AND
C.CustomerKey = M.CustomerKey
In addition, as we explained in Sect. 9.1 , we have the table NewCustomers
containing new prospective customers whose behavior we want to predict.
To create an association model for the Northwind case study, we need
to define views containing orders and items. For this, we create two views:
The first one, denoted AssocOrders , contains the order number and customer
data. The second one, denoted AssocLineItems , contains the details of each
order, which means the products that each order contains. We show next the
definition of these views:
CREATE VIEW AssocOrders AS
SELECT DISTINCT S.OrderNo, S.CustomerKey, O.CountryName,
D.BusinessType
FROM SalesS,TimeT,CustomerC,CustomerDemographicsD,
City Y, State A, Country O
WHERE S.OrderDateKey = T.TimeKey AND Year(T.Date) = 1997 AND
S.CustomerKey = C.CustomerKey AND
C.CustomerKey = D.CustomerKey AND
C.CityKey = Y.CityKey AND Y.StateKey = A.StateKey AND
A.CountryKey = O.CountryKey
CREATE VIEW AssocLineItems AS
SELECT OrderNo, OrderLineNo, P.ProductName
FROM Sales S, Time T, Product P
WHERE S.OrderDateKey = T.TimeKey AND Year(T.Date) = 1997 AND
S.ProductKey = P.ProductKey
We are now ready to present our data mining case study for the Northwind
company. We will show how to:
￿ Build a decision tree model to predict, given the new customer charac-
teristics, if she is likely to place an order with an amount higher than
$3,500.
￿ Build a clustering model to produce a profile of the current customers and
predict a new customer's profile.
￿ Create an association rule model to predict which items will be ordered
together.