Source Of Summary:

DM- Sec (DW)_FCDS.pdf

1. 🚀 Data Warehouse 📚

1.1 Overview

• A data warehouse, also called an enterprise data warehouse (EDW):
  • An enterprise data platform used for the analysis and reporting of structured and semi-structured data from multiple data sources.
  • A data warehouse can store both current and historical data in one place and is designed to give a long-range view of data over time. ✅
• A data warehouse is a central repository of information: that can be analyzed to make more informed decisions. 📈

2. 🗄️ Data Warehouse Schema

2.1 Star Schema 🌟

• In data warehousing, a star schema: a dimensional model for organizing data into a structure that helps to improve analytical query performance. 🔍
  • Two types of tables: fact and dimension. 📊
  • Fact table: sits at the center of the model, surrounded by one or more dimension tables. 🎯

2.2 Snowflake Schema ❄️

• Structure: one fact table in the middle, multiple dimension tables connected to it and to one another. 🔗
• Normalization: dimensions are in normalized form across multiple related tables. 🎯

2.3 Fact Constellation Schema (Galaxy Schema) 🌌

• Definition: two or more fact tables sharing one or more dimensions. Also called Galaxy schema. ✨
• No one “best” schema: choice depends on resources, data types, and analytical goals. 💡

3. 📝 Case Study Questions

3.1 Q1 ✏️

• Scenario: A data warehouse consists of four dimensions (time, item, branch, and location) and two measures (unit_sold and dollars_sold).
• Task: Draw the star schema diagram for this data warehouse. 🎯

3.2 Q2 ✏️

• Scenario: A data warehouse consists of seven dimensions (Time, Location, Item, Month, State, Brand, and Customer). 🧩
  • Location dimension:
    • Attributes:
      • location_id
      • city
      • state_id
    • Links via state_id to the State table, which provides state_id and name. 🔗
  • Time dimension:
    • Attributes:
      • time_id
      • timestamp
      • month_id
    • Links via month_id to the Month table, which provides month_id and name. 🕒
  • Item dimension:
    • Attributes:
      • item_id
      • name
      • brand_id
    • Links via brand_id to the Brand table, which provides brand_id and name. 🏷️
• Task: Draw the snowflake schema diagram for this data warehouse. 🎯