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

FROM Sales S, Time T
WHERE S.OrderDateKey = T.TimeKey AND
DATEPART(yy, T.Date) = 1997
GROUP BY S.EmployeeKey )
SELECT TOP (3) E.FirstName + '' + E.LastName AS Name,
SUM(S.SalesAmount) AS SalesApril1998,
SUM(S.SalesAmount) * 3 AS ExpectedSales1998, Q.Quota,
SUM(S.SalesAmount) * 3 / Q.Quota AS Percentage
FROM Sales S, Time T, Employee E, Quota1998 Q
WHERE S.OrderDateKey = T.TimeKey AND
S.EmployeeKey = E.EmployeeKey AND
S.OrderDateKey = T.TimeKey AND
S.EmployeeKey = Q.EmployeeKey AND
DATEPART(yy, T.Date) = 1998 AND DATEPART(mm, T.Date) < =4
GROUP BY S.EmployeeKey, E.FirstName, E.LastName, Q.Quota
ORDER BY Percentage
In the temporary table Quota1998 , we compute the sales quota that employees
must achieve for 1998, as 5% increase of the sales amount for 1997. Then,
the main query computes in the column SalesApril1998 the sales of employees
from January to April 1998 and in the column ExpectedSales1998 the expected
sales for 1998, calculated as three times the previous value; finally, the query
computes the percentage of the expected sales and the quota.
9.4 Summary
In this chapter, we have discussed how a data warehouse can be exploited
to obtain valuable and hidden information. We started describing three
commonly used data mining techniques and their implementation in Analysis
Services. We also applied them to the Northwind case study. Then, we
studied KPIs, gave a classification of them, and provided guidelines for their
definition. We also illustrated how to define KPIs for the Northwind case
study in Analysis Services. We continued with the study of dashboards.
We characterized different types of dashboards and gave guidelines for their
definition. We concluded by illustrating how to create a dashboard for the
Northwind case study using Microsoft Reporting Services.
9.5 Bibliographic Notes
There is a wide literature in data mining, where the concepts that we
explained in this chapter could be studied in detail. For example, the topic
by Han, Kamber, and Pei [ 77 ] provides a good introduction to data mining
techniques. Another good reference is the topic by Tan et al. [ 196 ]. The classic
Apriori algorithms can be found in [ 3 , 190 ]. The latter covers association