Building an Effective Data Warehouse
This case study provides insight into how a data warehouse was built for a firm in the financial sector using its existing Microsoft technology. It touches on the current form of "static reports" currently used within the company, which we have identified as problematic. This case study showcases a step-by-step method of how this DW is built. After reading, you should understand the theory and practical application of the DW approach. How would you apply a similar framework to a large department store chain's supply chain?
4. Approach
4.5. Multidimensional Model
In this stage we firstly describe the data source. The following points address the definition of the facts table, level of granularity, dimensions e the relational schema.
1) Data Source
All the data that is gathered to integrate the DW is coming from a transactional database, in SQL format, that supports the software application for treasury management and treasury operations. The only exception is the time table as explained later.
2) Fact table
As Figure 2 shows, the fact table contains the value date and account attributes (that form the primary key). It also contains attributes for the foreign keys from each of the dimension tables shown below, as well as quantitative attributes like account balances in euro and original currency of the transaction, and the working account balances in euro and in original currency.
3) Granularity
The maximum detail level of the fact table records corresponds to daily balance of each account, which results from the aggregation of all movements occurred in each day, for each account.
4) Dimension Tables
The defined dimension tables are: Companies, Banks, Accounts, Currencies, Countries and Time,
5) Relational Schema
The adopted schema, represented in the class diagram in Figure 2, is mapped in a star schema where each dimension table is directly connected to the fact table.
Figure 2. Class diagram of the DW
