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

In a similar way, we define tables for the other hierarchies and levels.
Finally, the fact table is created containing all measures included in the
conceptual schema and referencing all participating dimensions.
10.5.2 ETL Processes
During the conceptual design phase, we identified the mappings required
between the sources and the data warehouse. We also specify some transfor-
mations that could be necessary in order to match user requirements with
the data available in the source systems. However, before implementing the
ETL processes, several additional tasks must be specified in more detail.
In the logical design phase, all transformations of the source data should
be considered. Some of them can be straightforward, for example, the
separation of addresses into their components (e.g., street, city, postal code)
or the extraction of date components (e.g., month and year). Note that
the transformation may depend on the logical model. For example, in the
relational model, each component of a department address will be represented
as a separate attribute.
Other transformations may require further decisions, for instance, whether
to recalculate measure values to express them in euros or dollars or to use
the original currency and include the exchange rate. It should be clear that
in real situations, complex data transformations may be required. Further,
since the same data can be included in different source systems, the issue
of inconsistencies may arise, and an appropriate strategy for resolving them
must be devised. Also, developers should design the necessary data structures
for all elements for which users want to keep changes, as explained in Sect. 5.7 .
A preliminary sequence of execution for the ETL processes should also
be determined. This ensures that all data will be transformed and included,
with their consistency being checked. We do not explain here the ETL design
for the Northwind data warehouse since it was studied in detail in Chap. 8 .
10.6 Physical Design
As with the logical-design phase, we should consider two aspects in the
physical-design phase: one related to the implementation of the data ware-
house schema and another that considers the ETL processes. This is
illustrated in Fig. 10.12 . Since in Chap. 8 we have presented an in-depth
analysis of an ETL case study, we do not extend further in this subject,
and we focus on the implementation of the data warehouse schema.
During the physical design phase, the logical schema is converted into a
tool-dependent physical database structure. Physical design decisions should