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?
2. Literature Review
2.2. Data Storage
The design of DWs is based on a multidimensional paradigm for data representation that provides at least two major advantages: on the functional side, it can guarantee fast response times even to complex queries, while on the logical side the dimensions naturally match the criteria followed by knowledge workers to perform their analyses. There are two types of data tables in a multidimensional representation: dimension tables and fact tables. In general, dimensions are associated with the entities around which the process of an organization revolves. Dimension tables then correspond to primary entities contained in the DW, and in most cases, they are directly derived from master tables stored in OLTP systems. On the other hand, fact tables usually refer to a transaction and contain two types of data: links to dimension tables; and numerical values of the attributes.
A DW includes typically several fact tables, interconnected with dimension tables, linked, in their turn, with other dimensions. A fact table connects with n dimension tables and may be represented by an n-dimensional data cube where each axis corresponds to a dimension. Multidimensional cubes are a natural extension of the popular two-dimensional spreadsheets, which can be interpreted as two-dimensional cubes.
In order to standardize data analysis and enable simplified usage patterns DWs are commonly organized as problem-driven, small units, called "data marts", each data mart is dedicated to the study of a specific problem. A data mart can be considered as a functional or departmental DW of a smaller size and more specific type than the overall company DW. As a consequence, a data mart contains a subset of the data stored in the company DW, which are usually integrated with other data that the company department responsible for the data mart own and deems of interest.
In order to understand and locate data in the DW users need information about the data warehousing system and its content. This information is known as metadata. The metadata indicates for each attribute of a DW the original source of the data, their meaning and the transformations to which they have been subjected. The metadata should also include business definitions, data quality alerts, organizational changes, business rules and assumptions.