Data Warehousing

Data Warehousing

1. What is a Data Warehouse?

   • A collection of integrated-subject oriented databases designed to support DSS functions, where each unit of data is non-versatile and relevant.
   • Characteristics of DW
     • Subject oriented, Integrated, Time-variant (time series), Non-volatile, Summarised, Metadata, Web-based, Relational/Multi-dimensional, Client/Server, Real-time
   • Types of DW:
     • 1) Enterprise DW - Large-scale DW
     • 2) Operational Data Stores - Interim staging area for DW
     • 3) Data Marts - Smaller and focuses on a particular subject

2. DW Architecture

   • Three-tier architecture: DW that contains the data & software, Back-end, Front-end
   • Two-tier architecture: First 2 tiers in three-tier architecture is combined into one
   • Extraction,Transformation and Load (ETL) Process

3. Representation of Data in DW

   • 2) Star Schema
     • Most commonly used and the simplest style of dimensional modeling
   • 1) Snowflake Schema
     • An extension of star schema where the diagram resembles a snowflake in shape
   • Concept of hierarchy for location
     • A hierarchy is a structure made up to two/more levels of related dimensions
     • Hierarchies enable users to navigate to different levels of granularity within the measures of DW
   • Multidimensional Presentation
     • Dimensions (How are you going to analyse your data?), Measures (What are you going to measure? Numeric Figure), Time (Time variant)

4. Slowly Changing Dimensions

   • Dimension members are normally static, Dimensions change over time
   • 3 types of SCDs
     • Type 1 - Does not track changes; Type 2 - Tracks changes, 4 supplementary attributes must be added to the dimension to track history of that dimension; Type 3 - Tracks current and original state of a dimension member, Implemented using 2 additional attributes

5. OLAP Operations

   • Slice, Dice, Drill down/up, Roll up, Pivoting, Drill through
   • Variations of OLAP
     • 3) Hybrid (HOLAP) - Combines ROLAP & MOLAP benefiting greater scalability of ROLAP
     • 2) Relational (ROLAP)
     • 1) Multidimensional (MOLAP)

6. Steps in designing DW

   • Choosing Data Mart, Choosing Table Granularity, Identifying and Conforming the dimensions, Choosing Facts, Storing pre-calculations in the fact table