Data Modeling Fundamentals

# Answer: OLTP vs OLAP

# OLTP: Optimized for transactional operations (INSERT, UPDATE, DELETE)
# - Normalized structure to reduce redundancy
# - Fast write operations
# - Real-time consistency
# - Few rows per transaction
# Example: Banking system for deposits/withdrawals

# OLAP: Optimized for analytical queries (SELECT, aggregations)
# - Denormalized structure for query speed
# - Fast read operations
# - Historical data retention
# - Aggregated data for analysis
# Example: Sales reporting and business intelligence

# Comparison Table:
# Aspect         | OLTP              | OLAP
# Optimization   | Writes            | Reads
# Structure      | Normalized        | Denormalized
# Data Volume    | Current only      | Historical
# Query Type     | Simple, short      | Complex, long
# Users          | Many concurrent   | Few, power users

# Normalized Structure (Poor for Analytics)
# Many tables with complex joins required
SELECT SUM(amount)
FROM sales s
JOIN transactions t ON s.t_id = t.id
JOIN customers c ON t.c_id = c.id
JOIN products p ON s.p_id = p.id
WHERE c.country = 'USA' AND YEAR(t.date) = 2024;

# Star Schema / Dimensional (Better for Analytics)
# Few joins, simple structure, fast aggregations
SELECT SUM(sales_amount)
FROM fact_sales f
JOIN dim_customer dc ON f.customer_key = dc.customer_key
JOIN dim_product dp ON f.product_key = dp.product_key
WHERE dc.country = 'USA' AND YEAR(f.date) = 2024;

# Why dimensional is better:
# - Fewer joins = faster queries (5 joins vs 3 joins)
# - Fewer tables = simpler to understand
# - Denormalization = pre-calculated aggregations possible
# - Clear separation of concerns (facts vs dimensions)
# - Users can navigate intuitively
# - Better performance on large datasets

# E-Commerce Star Schema Design
# Grain: One row per order line item (not per order)

# FACT TABLE: fact_order_lines
fact_structure = {
    'Primary Key': ['order_line_key'],
    'Foreign Keys': [
        'order_key', 'customer_key', 'product_key', 
        'warehouse_key', 'date_key', 'promotion_key'
    ],
    'Measures': [
        'unit_price', 'quantity_ordered', 'discount_amount', 
        'tax_amount', 'total_amount', 'shipping_cost'
    ],
    'Degenerate Dimensions': [
        'order_number', 'line_number', 'sku'
    ]
}

# DIMENSION TABLES
dimensions = {
    'dim_date': ['date_key', 'date', 'month', 'quarter', 'year', 'day_of_week', 'is_holiday'],
    'dim_customer': ['customer_key', 'customer_id', 'name', 'segment', 'lifetime_value', 'country'],
    'dim_product': ['product_key', 'product_id', 'name', 'category', 'brand', 'supplier_key', 'price'],
    'dim_warehouse': ['warehouse_key', 'warehouse_id', 'location', 'region', 'capacity', 'manager'],
    'dim_promotion': ['promotion_key', 'promotion_id', 'type', 'discount_pct', 'start_date', 'end_date'],
    'dim_supplier': ['supplier_key', 'supplier_id', 'name', 'country', 'rating']
}

# Analysis Capabilities:
# ✓ Net sales by customer segment, product category, region
# ✓ Promotion effectiveness (impact on sales)
# ✓ Warehouse performance and efficiency
# ✓ Time-based trends and seasonality
# ✓ Supplier performance metrics
# ✓ Customer lifetime value analysis

# Answer: Fact and Dimension Tables

# FACT TABLES:
# - Store measurable business events
# - Many rows (millions or billions in large systems)
# - Foreign keys to dimensions
# - Numeric measures (amounts, counts, rates)
# - Examples: sales, inventory, transactions

# DIMENSION TABLES:
# - Provide context and descriptive attributes
# - Few rows (thousands or less)
# - Support filtering and grouping
# - Text and categorical values
# - Examples: customer, product, date, location

# Visual Star Structure:
#           dim_customer
#                |
#   dim_product--+--fact_sales--dim_date
#                |
#           dim_store

# Key Relationship:
# One dimension row → Many fact rows (one-to-many)
# Example: One customer appears in many sales records

# PROBLEM: Mixing Transaction and Daily Data
# If you mix both grains in one fact table:

# Mixed Fact Table (BAD):
# |transaction_id|amount|is_daily_aggregate|
# |    NULL      | 100  |       0          |  <- transaction-level
# |    NULL      | 250  |       1          |  <- daily aggregate
# |    NULL      | 50   |       0          |  <- transaction-level

# Query Result is WRONG:
SELECT SUM(amount) FROM fact_sales
WHERE date = '2024-01-01'
# Result: 400 (includes duplicates and aggregates!)

# SOLUTION: Separate Tables by Grain

# Grain 1: Transaction Level
fact_sales_detail = {
    'sales_key', 'date_key', 'customer_key', 
    'amount', 'units_sold'  # One row per transaction
}

# Grain 2: Daily Level
fact_sales_daily = {
    'daily_key', 'date_key', 'customer_key',
    'total_amount', 'total_units'  # One row per day per customer
}

# Benefits:
# ✓ No double-counting or confusion
# ✓ Clear semantics (users know which table to use)
# ✓ Correct aggregations guaranteed
# ✓ Better query performance (daily data is smaller)

# College Course Enrollment Star Schema

# FACT TABLE: fact_enrollments
# Grain: One row per student enrollment per course per semester

fact_enrollments = {
    'Primary Key': 'enrollment_key',
    'Foreign Keys': [
        'student_key',
        'course_key',
        'semester_key',
        'instructor_key',
        'department_key'
    ],
    'Measures': [
        'credit_hours',
        'tuition_amount',
        'final_grade_points',
        'attendance_percentage'
    ]
}

# DIMENSION TABLES

dim_student = {
    'Primary Key': 'student_key',
    'Attributes': [
        'student_id', 'student_name', 'major',
        'class_level', 'gpa', 'enrollment_status'
    ]
}

dim_course = {
    'Primary Key': 'course_key',
    'Attributes': [
        'course_id', 'course_name', 'credit_hours',
        'department', 'difficulty_level', 'capacity'
    ]
}

dim_semester = {
    'Primary Key': 'semester_key',
    'Attributes': [
        'semester_id', 'year', 'term',
        'start_date', 'end_date', 'is_current'
    ]
}

dim_instructor = {
    'Primary Key': 'instructor_key',
    'Attributes': [
        'instructor_id', 'name', 'department',
        'tenure_status', 'specialty'
    ]
}

dim_department = {
    'Primary Key': 'department_key',
    'Attributes': [
        'department_id', 'department_name',
        'college', 'budget_code'
    ]
}

# ANALYSIS ENABLED:
# ✓ Student performance by course and semester
# ✓ Course popularity and enrollment trends
# ✓ Instructor workload and effectiveness
# ✓ Department enrollment patterns
# ✓ Prerequisite validation and progression analysis

# Answer: Cardinality defines the multiplicity of a relationship

# FOUR MAIN TYPES:

# 1. One-to-Many (*:1) - MOST COMMON IN STAR SCHEMAS
# - One customer can have many orders
# - One order belongs to one customer
# Example: dim_customer → fact_sales

# 2. Many-to-One (1:*) - Same as 1:many, different perspective
# - Same concept, opposite direction view

# 3. One-to-One (1:1) - RARE IN ANALYTICS
# - One employee has one passport
# - One passport belongs to one employee
# Use only when fields change at different rates

# 4. Many-to-Many (*:*) - REQUIRES BRIDGE TABLE
# - Many students can take many courses
# - Many courses can have many students
# Solution: Create bridge/junction table

# IMPORTANCE:
# ✓ Incorrect cardinality causes formula errors
# ✓ Double-counting in aggregations
# ✓ Incorrect filtered values in related tables
# ✓ Model performance issues

# PROBLEM: Direct Many-to-Many Creates Duplicate Rows

# Student Table:
# |student_key|name   |
# |    1      |Alice  |
# |    2      |Bob    |

# Course Table:
# |course_key|name        |
# |   101    |Math        |
# |   102    |English     |

# Direct join without bridge: WRONG
# You get artificial cross-product (4 rows from 2+2)
# Alice appears once per course
# Bob appears once per course
# This creates duplicate records and ambiguity

# SOLUTION: Create Bridge Table (Enrollment)

enrollment_bridge = {
    'enrollment_key': [1, 2, 3, 4],
    'student_key': [1, 1, 2, 2],
    'course_key': [101, 102, 101, 102],
    'grade': ['A', 'B', 'B', 'A']
}

# Relationships become clear:
# dim_student (1) ---> enrollment_bridge (many)
# dim_course (1) ---> enrollment_bridge (many)
# Each enrollment row is unique and unambiguous

# Benefits:
# ✓ One row per actual relationship
# ✓ No artificial duplication
# ✓ Can store relationship attributes (grade)
# ✓ Proper aggregations and calculations

# Multi-Dimensional Star Schema with Proper Relationships

# SCHEMA STRUCTURE:
#           dim_customer
#                |
#   dim_product--+--fact_sales--dim_date
#                |
#           dim_store

# FACT TABLE: fact_sales
fact_sales = {
    'Primary Key': 'sales_key',
    'Foreign Keys': [
        'customer_key',    # Links to dim_customer
        'product_key',     # Links to dim_product
        'date_key',        # Links to dim_date
        'store_key'        # Links to dim_store
    ],
    'Measures': [
        'amount',
        'quantity',
        'discount'
    ]
}

# RELATIONSHIPS (All One-to-Many from dimensions to fact)
relationships = [
    {
        'Name': 'Customer to Sales',
        'From': 'dim_customer[customer_key]',
        'To': 'fact_sales[customer_key]',
        'Cardinality': 'One-to-Many (*:1)',
        'Filter Direction': 'Single (dimension → fact)',
        'Is Active': True
    },
    {
        'Name': 'Product to Sales',
        'From': 'dim_product[product_key]',
        'To': 'fact_sales[product_key]',
        'Cardinality': 'One-to-Many (*:1)',
        'Filter Direction': 'Single',
        'Is Active': True
    },
    {
        'Name': 'Date to Sales',
        'From': 'dim_date[date_key]',
        'To': 'fact_sales[date_key]',
        'Cardinality': 'One-to-Many (*:1)',
        'Filter Direction': 'Single',
        'Is Active': True
    },
    {
        'Name': 'Store to Sales',
        'From': 'dim_store[store_key]',
        'To': 'fact_sales[store_key]',
        'Cardinality': 'One-to-Many (*:1)',
        'Filter Direction': 'Single',
        'Is Active': True
    }
]

# ANALYSIS CAPABILITY:
# Users can simultaneously filter by:
# ✓ Customer (select Alice)
# ✓ Product (select Widget)
# ✓ Date (select 2024)
# ✓ Store (select NYC)
# Result: Sales of Widget to Alice in NYC during 2024

# BEST PRACTICES:
# ✓ All relationships are one-to-many (optimal)
# ✓ Single-direction filters (better performance)
# ✓ One active relationship per dimension
# ✓ All foreign keys point FROM fact TO dimensions

# Answer: Role-Playing Dimension Definition
# A single dimension table referenced multiple times with different meanings

# CLASSIC EXAMPLE: Date Dimension
# One dim_date table can represent:
# - Order Date (when customer placed order)
# - Ship Date (when order left warehouse)
# - Delivery Date (when customer received order)
# - Due Date (when customer must pay)

# Memory Efficient Approach:
# Instead of 4 separate date tables, use 1 table with multiple relationships

# fact_orders structure:
# |order_key|order_date_key|ship_date_key|delivery_date_key|customer_key|amount|
# |   1     |   20240101   |  20240103   |   20240108      |    101     | 500  |

# Power BI Relationships:
# 1. dim_date[date_key] → fact_orders[order_date_key] (ACTIVE)
# 2. dim_date[date_key] → fact_orders[ship_date_key] (INACTIVE)
# 3. dim_date[date_key] → fact_orders[delivery_date_key] (INACTIVE)

# Using USERELATIONSHIP for Analysis:
# -- Analyze by Ship Date (not default)
# Analyze by Ship Date
# CALCULATE(SUM(fact_orders[amount]),
#   USERELATIONSHIP(fact_orders[ship_date_key], dim_date[date_key]))

# OTHER EXAMPLES:
# - Employee dimension: Manager, Supervisor, Interviewer
# - Location dimension: Billing Address, Shipping Address, Headquarters
# - Date for: Period Date, Due Date, Completed Date

# BENEFITS:
# ✓ One copy of date data (memory efficient)
# ✓ Consistent definitions across roles
# ✓ Same attributes for all dates (month, quarter, year)
# ✓ Easier maintenance

# Answer: Type 2 SCD - Creates New Rows for Changes

# EXAMPLE: Product Price Changes Over Time

# dim_product with SCD Type 2:
# |product_key|product_id|name  |price|version|effective_date|end_date   |is_current|
# |    1      |  P001    |Widget| 10  |   1   | 2024-01-01   |2024-06-30 |  False   |
# |    2      |  P001    |Widget| 12  |   2   | 2024-07-01   |9999-12-31 |  True    |

# WHEN TO USE:
# ✓ Need to track history of dimension attributes
# ✓ Accuracy of historical analysis is important
# ✓ Want to know "what was the product price when sold?"
# ✓ Managing slowly changing attributes (not frequently)

# WHEN NOT TO USE:
# ✗ Dimension changes very frequently (daily, hourly)
# ✗ Don't need historical tracking
# ✗ Storage space is constrained
# ✗ Changes are cosmetic (formatting, capitalization)

# IMPLEMENTATION:
# 1. Add version column to dimension
# 2. Add effective_date and end_date columns
# 3. Add is_current flag for easier filtering
# 4. When attribute changes, insert new row with new version
# 5. Close old row with end_date

# Query with SCD Type 2:
# SELECT SUM(amount), product_name
# FROM fact_sales f
# JOIN dim_product d ON f.product_key = d.product_key
#   AND f.order_date BETWEEN d.effective_date AND d.end_date
# WHERE d.category = 'Electronics'
# GROUP BY product_name

# BENEFITS:
# ✓ Maintains complete history
# ✓ Accurate historical reporting
# ✓ Can answer "what changed and when?"
# ✓ Supports audit requirements

# COMPREHENSIVE RETAIL DATA MODEL

# ═════════════════════════════════════════════════════════════
# CONFORMED DIMENSIONS (Shared across multiple facts)
# ═════════════════════════════════════════════════════════════

conformed_dimensions = {
    'dim_customer': [
        'customer_key', 'customer_id', 'name', 'segment',
        'city', 'country', 'lifetime_value'
    ],
    'dim_product': [
        'product_key', 'product_id', 'name', 'category',
        'brand', 'supplier_key', 'price', 'version',
        'effective_date', 'end_date'  # SCD Type 2
    ],
    'dim_store': [
        'store_key', 'store_id', 'store_name', 'region',
        'manager', 'opening_date'
    ],
    'dim_date': [
        'date_key', 'date', 'month', 'quarter', 'year',
        'is_holiday', 'day_of_week'
    ],
    'dim_supplier': [
        'supplier_key', 'supplier_id', 'supplier_name',
        'country', 'rating'
    ]
}

# ═════════════════════════════════════════════════════════════
# FACT TABLES (Different processes, different grains)
# ═════════════════════════════════════════════════════════════

fact_tables = {
    'fact_sales': {
        'grain': 'One row per transaction line item',
        'keys': [
            'customer_key', 'product_key', 'store_key',
            'order_date_key', 'flag_key'  # junk dimension
        ],
        'measures': [
            'quantity', 'unit_price', 'discount', 'total_amount'
        ]
    },
    'fact_returns': {
        'grain': 'One row per returned item',
        'keys': [
            'customer_key', 'product_key', 'store_key',
            'return_date_key'
        ],
        'measures': [
            'quantity_returned', 'refund_amount', 'reason_code'
        ]
    },
    'fact_inventory': {
        'grain': 'One row per product per store per day',
        'keys': ['product_key', 'store_key', 'date_key'],
        'measures': [
            'quantity_on_hand', 'quantity_reserved', 'reorder_point'
        ]
    },
    'fact_supplier_orders': {
        'grain': 'One row per supplier order line',
        'keys': ['supplier_key', 'product_key', 'date_key'],
        'measures': [
            'quantity_ordered', 'cost', 'lead_time_days'
        ]
    }
}

# ═════════════════════════════════════════════════════════════
# SPECIALIZED DIMENSIONS
# ═════════════════════════════════════════════════════════════

specialized_dimensions = {
    'dim_return_reason': [
        'return_reason_key', 'reason', 'category', 'is_warranty'
    ],
    'dim_order_flags': {  # Junk Dimension
        'flag_key': 'composite key',
        'attributes': ['is_rush_order', 'is_promo', 'payment_method']
    }
}

# ═════════════════════════════════════════════════════════════
# ROLE-PLAYING DIMENSIONS
# ═════════════════════════════════════════════════════════════

# dim_date used in multiple roles:
role_playing_dates = {
    'fact_sales': {
        'order_date_key': 'ACTIVE relationship',
        'ship_date_key': 'INACTIVE relationship',
        'delivery_date_key': 'INACTIVE relationship'
    }
}

# ═════════════════════════════════════════════════════════════
# ANALYSIS CAPABILITIES
# ═════════════════════════════════════════════════════════════

analysis_capabilities = [
    'Net Sales (Sales - Returns) by product, store, date',
    'Inventory Turnover (COGS / Avg Inventory)',
    'Supplier Performance (on-time delivery, quality)',
    'Customer Segmentation (lifetime value, frequency)',
    'Category Performance with trends',
    'Regional comparison and benchmarking',
    'Return rate analysis by reason',
    'Promotion effectiveness analysis'
]

# ═════════════════════════════════════════════════════════════
# PERFORMANCE OPTIMIZATIONS
# ═════════════════════════════════════════════════════════════

optimizations = [
    'Create aggregate tables for common queries',
    'Use role-playing dimensions to avoid duplication',
    'Implement SCD Type 2 for product tracking',
    'Use junk dimension for order flags',
    'Index fact tables on foreign keys',
    'Maintain single-direction relationships',
    'Consider materialized views for complex queries'
]