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

discovered analyzing the content of the source database in the requirements
phase.
Finally, for human resource management (Columns 4a-4c in Table 10.1 ),
we need to analyze sales by employee supervisors. Thus, in dimension
Employee , we defined a recursive hierarchy denoted Supervision .
Check Data Availability and Specify Mappings
The next step in the method is to check the availability of data in the
source systems for all elements included in the data warehouse schema. In our
example, the logical schema of the data source is depicted in Fig. 10.4 ,thus
facilitating the task of specifying mappings. In the absence of a conceptual
representation of the source systems, their logical structures can be used
instead. Table 10.3 shows an example of a table that specifies the way in which
source tables and attributes of the operational databases are related to the
levels and attributes of the data warehouse. The rightmost column indicates
whether a transformation is required. For example, data representing the
ProductName , QuantityPerUnit ,and UnitPrice of products in the operational
database can be used without any transformation in the data warehouse for
the corresponding attributes of the Product level. Note that Table 10.3 is
just a simplification of the information that should be collected. Additional
documentation should be delivered that includes more detailed specification
of the required mappings and transformations.
Develop Final Conceptual Schema and Mappings
Revision and additional consultation with users are required in order to
adapt the multidimensional schema to the content of the data sources. When
this has been done, the final schema and the corresponding mappings are
developed. In our example, some of the issues found during the revision
process were:
￿ We need to create and populate the dimension Time . The time interval of
this dimension must cover the dates contained in the table Orders of the
Northwind operational database.
￿ The dimensions Customer and Suppliers share the geographic hierarchy
starting with City . However, this information is incomplete in the opera-
tional database. Therefore, the data for the hierarchy State , Country ,and
Continent must be obtained from an external source.
Metadata for the source systems, the data warehouse, and the ETL
processes are also developed in this step. Besides the specification of trans-
formations, the metadata include abstract descriptions of various features
mentioned earlier in this section. For example, for each source system, its