DWH Design

A Data warehouse (DW) is a database used for reporting and analysis. The data stored in the warehouse is uploaded from the operational systems. A data warehouse maintains its functions in three layers: staging, integration, and access. Staging is used to store raw data for use by developers. The integration layer is used to integrate data and to have a level of abstraction from users. The access layer is for getting data out for users. Data warehouses can be subdivided into data marts. Data marts store subsets of data from a warehouse. This definition of the data warehouse focuses on data storage. The main source of the data is cleaned, transformed, catalogued and made available for use by managers and other business professionals for data mining, online analytical processing, market research and decision support An expanded definition for data warehousing includes business intelligence tools, tools to extract, transform and load data into the repository, and tools to manage and retrieve metadata. Below is a Diagram depicting the Datawarehouse Semantic Layers

The Principles of Dimensional Modelling address the unique requirements of DWH Systems.A Star Schema design is optimized for queries that access large volumes of Data rather than individual transactions.A Dimensional Model divides the information associated with a business process into two major categories called Facts and Dimensions.Facts are the measurements by which a process is evaluated whilst Dimensions give Facts their contexts.Each Group of Dimensions is placed in a Dimension Table and the Facts placed in a Fact Table the result is a Star Schema so called because it resembles a star when diagrammed with the Fact Table in the center as depeicted in Diagram below