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

as JPEG or TIFF, in a report), maps, data bars, sparklines, and indicators.
These can be put together with tabular data, as we show below.
We next describe our case study. The management of the Northwind
company wants to put together in a dashboard a group of indicators to
monitor the performance of several sectors of the company. The dashboard
will contain the following elements:

￿ A graph showing the evolution of the total sales per month , together with
the total sales in the same month for the previous year.
￿ To the right of the former graph, we will place a gauge to monitor the
percentual variation of total sales with respect to the same month for the
previous year. The goal is to obtain a 5% increase, and the gauge allows
the manager to easily visualize if the goal has been achieved.
￿ Below the first graph, we will place another one conveying the shipping
costs . The graph reports, monthly, the total freight cost with respect to
the total sales. The goal is that the shipping costs must represent less than
the 5% of the sales amount.
￿ To the right of this graph, we place a gauge showing the percentage of
shipping costs with respect to the total sales from January to the end of
April 1998 (data in the Northwind data warehouse range from 1996 to
1998). This is the KPI introduced in Sect. 9.2 .
￿ In the lower part of the dashboard, we will place a table that can be used to
analyze the performance of the sales force of the company and take actions
if necessary. Thus, we list the three employees with the least number of
sales as of April 1998. For each one of them, we compute the total sales
and the percentage with respect to the expected yearly sales quota. We
assume that an employee is expected to increase her sales 5% each year.

Figure 9.15 shows the definition of the dashboard in Reporting Services
using SQL Server Data Tools. As can be seen in the left part of the figure,
the data source of the report is the Northwind data warehouse. There are
five datasets, one for each element of the dashboard. Each dataset has an
associated SQL query. The one in the dialog box corresponds to the top left
chart of the report, which shows the monthly sales compared with those of the
previous year. Each dataset has a set of fields, shown below the dataset name,
which correspond to the columns returned by the SQL query. Figure 9.16
shows the resulting dashboard. We explain below its different components.
The top left chart shows the monthly sales compared with those of the
previous year. The corresponding SQL query is given next:

WITH MonthlySales AS (
SELECT DATEPART(yy, T.Date) AS Year,

DATEPART(mm, T.Date) AS Month,
SUBSTRING(DATENAME(mm, T.Date), 1, 3)
AS MonthName, SUM(S.SalesAmount) AS MonthlySales

FROM Sales S, Time T

WHERE S.OrderDateKey = T.TimeKey