Building an Effective Data Warehouse

The SSIS project consists in the creation of a data source package that establishes the data flow tasks that extract, transform and load the data into the database (ETL). These tasks are aggregated in four sequence containers, as shown in the Figure 3.

A time table was previously created with one record for each day between 2009 and 2016. Each record contains the date's week, month, quarter, semester and year, and also indicates if that date is the last day of the period (week, month, quarter, semester and year). The time table is managed by the DW administrator. By the end of each year, this table should be added with a set of records that corresponds to one more year of records, on for each day. For example, in the end of 2016, the time table should be added with 365 records, one for each day of 2017.

Figure 3 represents graphically the data flow tasks of the SSIS project.

Figure 3. Data flow tasks of SSIS project

1) Load data into Dimensions
Each sub-task includes the insertion in the dimension tables of the data coming from the treasury management software's database, using SQL syntax.

2) Load Auxiliary Tables
This phase consists in loading data into auxiliary tables that are not represented in the dimensional model, trough the following sub-tasks:

a) Load updated information related to the exchange rates;
b) The sub-task that follows, which is represented in detail in Figure 4, comprises charging to an auxiliary table called "Account Balances", the balances for each account on each day that occurred a movement. For this purpose, this process collects current movements (real), the opening balances (actual) and forecast movements. Furthermore, it converts the value-data in the correct format calling the tool "derived columns", identify the currency using a "lookup" tool associated to the currencies table, aggregate in sum the total balance (treasury) and from sheet movements (only actual movements) for each account and different value-data, thus resulting in the everyday balance;

Figure 4. Data flow balances

c) It follows the loading of another auxiliary table called "Time vs Accounts" that receives the result of the joining between the account table with the time table. This table constitutes the support base structure to the calculation of daily sales because it contains a record for each account/day. Without this structure, DW would contain only balances on days that had movements, thereby making it difficult to query for a balance in day without movement and, on the other hand, the average balance calculation.

3) Load Fact Table
The last phase is the creation of fact table called "FactAccountBalances". This process merges the table "Time vs Accounts" with the "Account Balances", thus resulting in a record with the balance of each account in each day of the time table (and not just on the days when there was movements). The fact table is supplemented with complementary information regarding companies and banks by using the "lookup" tool associated with the account table.

Since the available exchange currency rate in the system, needed to calculate the account balance in Euros, is two days overdue, the account balance conversion to Euro should be updated every day, considering the most updated information about the exchange currency rate. So, although DW is characterized by data volatility, this is, once data is integrated it will no longer be modified, it was decided to update facts if the values in Euros are different, by assuming that the new value is more correct.

Therefore, this process verifies if each fact already exists in the fact table by comparing the two key columns: due date and account. If the fact does not exist, then it is loaded into the fact table. Otherwise, the process checks if the account balance in Euros and the working account balances in Euros are different from the values in the existing fact in the fact table. If so, then the fact is updated with the new values, otherwise the fact is not loaded since it already exists and the values are equal.