A Method for Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 387

2. For customers buying below their potential, increase their orders (in
number of orders and individual order amount):
(a) Monthly sales by customer compared to the corresponding sales (for
the same customer ) of the previous year .
(b) Total number of orders by customer , time period (e.g., year ), and
product .
(c) Average unit price per customer .
3. Increase sales of products selling below the company expectations:
(a) Monthly sales for each product category for the current year .
(b) Average discount percentage per product and month .
(c) Average quantity ordered per product .
4. Take action on employees performing below their expected quota:
(a) Best-selling employee per product per year with respect to sales amount.
(b) Average monthly sales by employee and year .
(c) Total sales by an employee and her subordinates during a certain time
period.
Table 10.1 shows, for each query, which are the candidate dimensions,
measures, and hierarchies. If priorities are considered, they will be associated
with each query; it is also usual that each query is associated with the users
that proposed it. In the first column from the left of the table, dimension and
measure names are distinguished by their fonts. Thus, for instance, Employee
is a dimension while Quantity is a measure. The table displays summarized
information in the sense that a check mark is placed if a query mentions at
least one level of one hierarchy in the second column from the left. Note also
that Table 10.1 includes more hierarchy levels than the ones referenced in
the goals and subgoals above. We assume that these have been discovered
by means of the analysis of other queries not shown here. We will also see
later that the complete design includes more dimensions and measures not
displayed here for the sake of clarity. For example, we do not show here
the information related to the shipping of products. Also, regarding the
Time dimension, note that we did not identify the three roles it plays in
the Northwind data warehouse, that is, as an order date, a shipped date, or
a due date. The queries we have addressed only allow to discover the order
date role; therefore, we have just called this dimension Time .
Table 10.1 does not only show the dimensions but also candidate hier-
archies inferred from the queries above and company documentation. For
example, in dimension Employee , we can see that there are two candidate
hierarchies: Supervision and Territories . The former can be inferred, among
other sources of information, from Requirement 4c, which suggests that
users are interested in analyzing together employees and their supervisors
as a sales force. The Territories hierarchy is derived from the documentation
of the company processes, which state that employees are assigned to a
given number of cities and a city may have many employees assigned