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?
1. Introduction
Although operational data is a key asset for an organization, such data is generally decentralized, inconsistent and not exploited to its full potential. In the context of this initiative we propose the development of a Business Intelligence (BI) project, implemented by the construction of a data warehouse (DW) and its Online Analytical Processing (OLAP) cube, for a holding company in the financial sector. These solutions, queries through a friendly interface, aggregated information regarding current and provisional financial data balances, by bank, company, country and account. In result, enable financial administration staff of the company to access to a homogenized and comprehensive view of the organization, supporting forecasting and decision-making processes at the enterprise level.
The origin of the information resides in a transactional database system that supports the accountability management department. Currently, the information is provided in the form of static reports, which are very inflexible and need a significant amount of work to be produced. Additionally, the data exploration features offered by the transactional application are very limited and time consuming, making a rapid response to new requests impossible.
Thus, we developed a DW, which was built using Microsoft technology already existing in the company, namely the Microsoft SQL Server (Integration Services and Analysis Services). In the end, the DW generates an OLAP cube information that can be exploited from Microsoft Excel.
The rest of the paper is organized as follows: First, we perform a revision of literature in the field of data warehousing by looking for three perspectives: data capture, data storage, and data access and analysis. After that, we briefly analyze the main DW initiatives and models for financial analysis. Then, we present the approach, methodology and phases adopted for the implementation of the project. Furthermore, we present the main results of the technical implementation of the project. Finally, we discuss the impact of these results in the activities of the holding company, and we draw the conclusions of our work.