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

it is desirable to involve at this stage a domain expert who can help in defining
business terminology for these elements and in indicating, for example,
whether measures are additive, semiadditive, or nonadditive.
10.3.4 Source-Driven Requirements for the
Northwind Case Study
We illustrate next the source-driven approach for the Northwind case study.
We assume that the entity-relationship schema of the operational database,
shown in Fig. 10.4 , is available and data of appropriate quality can be
obtained. We skip the step of identifying the source systems, except for the
geographic data, which were obtained from external sources (typically web-
based), complementing the ones in the database in the Customers , Employees ,
and Suppliers tables.
Apply Derivation Process
We chose a manual derivation process to provide a more general solution,
although automatic or semiautomatic methods could have also been applied.
We start by identifying candidate facts. In the schema of Fig. 10.4 ,wecan
distinguish the many-to-many relationship type OrderDetails , with attributes
that represent numeric data. This is a clear candidate to be a fact in a
multidimensional schema. Candidate measures for this fact are the attributes
UnitPrice , Quantity ,and Discount .Anorderin Orders is associated with
many products through the relationship type OrderDetails . Since users have
expressed that they are interested in individual sales rather than in the whole
content of an order, a fact should be associated with an order line. Thus, the
products in OrderDetails may be subsumed in the Orders table so that each
record in the latter now becomes a fact. We call this fact Sales . A sales fact
is associated with a unique employee (in entity type Employees ), shipper (in
entity type Shippers ), and customer (in entity type Customers ). In addition,
it is associated with three dates: the order date, the required date, and the
shipped date. These are potential dimensions, analyzed below.
Since each sales fact is associated with an order line, we may also envision
adimension Order , with a one-to-one relationship with the fact Sales .Thus,
Order is a candidate to be a fact or degenerate dimension (see Chap. 3 ), which
can be used, for example, to determine the average sales amount of an order
or the average number of items in an order.
The other many-to-many relationship type in the schema is EmployeeTer-
ritories . Since it does not have associated attributes, initially we can consider
it as candidate to be a nonstrict hierarchy rather than a fact.