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

Define logical
schema

Define ETL
processes

Fig. 10.10 Steps for logical design

10.5.1 Logical Schemas

After the conceptual design phase has been completed, it is necessary to
apply mapping rules to the resulting conceptual schema in order to generate
a logical schema. These mapping rules depend on the conceptual model used.
In Sect. 5.3 , we described some general mapping rules that translate the
MultiDim conceptual model into the relational model. In this section, we
apply these rules to the conceptual multidimensional schemas developed in
the previous phase. As explained in Sect. 5.2 , the logical representation of
a data warehouse is often based on the relational data model using specific
structures such as star and snowflake schemas. We also studied in Chap. 7
that many data warehouse applications include precomputed summary tables
containing aggregated data that are stored as materialized views. However,
we do not consider such tables to be part of the core logical schema.
We comment next on some design decisions taken when transforming the
schema shown in Fig. 10.7 into relational tables. To facilitate the reading, we
repeat the logical schema of the Northwind data warehouse in Fig. 10.11 .
First, considering users' analysis needs, query performance, and data reuse,
we must decide whether a star or a snowflake representation should be
chosen. In Sect. 5.2 , we have already stated the advantages and disadvantages
of star (denormalized) or snowflake (normalized) schemas for representing
dimensions with hierarchies. The following decisions were taken in our case
study.
Given that the Calendar hierarchy is only used in the Time dimension, for
performance reasons we denormalize these hierarchies and include them in a
single table instead of mapping every level to a separate table, thus choosing
a star representation for the Time dimension.
The hierarchies Territories , Geography (for customers), and Geography (for
suppliers) in Fig. 10.7 share the levels City , State , Region , Country ,and
Continent . In order to favor the reuse of existing data, we decided to use
the snowflake representation for this hierarchy, that is, we represent each one
of the levels above in a separate table, except for Region , which is embedded
in the table State . We explain this choice next. The hierarchy City → State

→ Continent is a ragged one (Sect. 4.2 ). To map this

hierarchy to the relational model, we can embed all the data of the parent
level in the child one (a denormalized mapping) or we can create a table
for each level and an optional foreign key referencing the potential parent

Region → Country →