The Great Dimension Quest

📜 Prologue: Your Great Mission

Hail, brave data explorer! You have arrived in the Kingdom of Retailia, where lords and ladies of commerce gather in the grand halls. The Royal Council has summoned you, for they need your wisdom and skill to forge a powerful data model that can analyze sales throughout the realm, manage the kingdom’s vast inventory, and track the magical promotions across many distant lands (stores).

Your quest has several chapters, each testing your mastery of the arcane arts of data modeling. Each step will ask you to gather specific mystical columns into dimension or fact “chambers.” Master them all to complete your epic dimensional model and earn the title of Data Archmage!

Here is the ancient lore you must study to succeed on your journey:

• 👤 Customers from villages, towns and cities across the kingdom.
• 📦 Products sorted into magical categories of merchandise.
• 🎁 Promotions that bestow savings and enchantments across the land.
• 🏠 Locations (Region → City → Store) where merchants establish their commerce hubs.
• 📊 Sales Orders (fact table) containing the sacred scrolls of transaction details.
• 📋 Inventory (another fact) to track the mystical stock levels in each store.

Your path through this perilous quest:

1. Forge powerful dimension tables with surrogate keys to bind your data.
2. Imbue Slowly Changing Dimensions (SCD) to capture the ancient history of Customers.
3. Construct a bridge table to channel the energy of multiple promotion spells.
4. Master the hierarchical dimension for tracking the Locations across the realm.
5. Summon a second fact table (Inventory) to balance with the first.
6. Assign the proper data types to each column for the final blessing and enchantment.