Building an Effective Data Warehouse

Table 2 and Table 3 have a similar structure and intend to present the results obtained for each use case. In this structure, the first section lines show the results from the modality I: "Use a PivotTable in an Excel spreadsheet, previously parameterized and whose source of information is the OLAP cube, where the "refresh" operation can be used to update the information". The second section lines show the results from the modality II: "Method used before the implementation of the OLAP cube, which involves extracting the daily balances from the cash management program, export the data to Excel and update a spreadsheet Excel previously prepared to present the required information.

For each modality we measure the arithmetic average and standard deviation of three measurements, with a millisecond precision level. A comma was used to represent a decimal point.

In the third section lines the difference in time spent in favor of modality I is displayed, which is obtained by subtracting the mean time spent in modality I, the average time spent in modality II, for each of the following three scenarios: a) per day; b) per month, considering 22 days per months (working days); c) per year, considering 264 days (22 days multiplied by 12 months). For each scenario we considered that the use case is performed once per day.

In the fourth section we perform a hypothesis testing regarding the difference of the two execution times means (Modality II - Modality I). For that, we adopt a t-student test, considering that the total number of samples is less than 30, and we register the t-value (2-tailored) for a confidence level of 95% and 99%.

Finally, it should be noted that we used a personal computer with Intel Core i3 2.3GHz processor with 4 GB RAM to perform those performance tests. The computer is equipped with Windows 7 32-bit and were installed the SQL Server database and all the programs mentioned in the system architecture section.

5.1. Use Case I - Estimation of Account Balances

Table 2 presents the measurements of time spent in the following use case: obtain estimated account balances for next month and next year, based on the known balances by the end of the day.

Table 2. Estimation of account balances in next month and year

A comparative graphical analysis between the measurements of the time spent in the estimation of account balances in next month and next year through OLAP cubes is given in Figure 5.

Figure 5. Estimation of account balances in next month and year

5.2. Use Case II - Getting the average balance from all accounts for all months

Table 3 presents the measurements of time spent in the following use case: receive the average balance for each account and each month, based on the known balances by the end of the day.

Table 3. Getting the average balance from all accounts for all months since last 3 years

A comparative graphical analysis between the measurements of the time spent getting the average balance from all accounts for all months through OLAP cubes is given in Figure 6.

Figure 6. Query the average balance since last 3 years