Star Schema Design AI Prompt

Design a star schema for this business process: {{business_process}}

Source data: {{source_tables}}
Key business questions to answer: {{business_questions}}

1. Fact table design:
   - Identify the grain: what does one row represent? (e.g. one order line, one daily session, one claim)
   - State the grain explicitly — this is the most important design decision
   - Numeric measures: what is being measured? (revenue, quantity, duration, count)
   - Additive vs semi-additive vs non-additive measures:
     - Additive: sum across all dimensions (revenue, quantity)
     - Semi-additive: can sum across some dimensions but not time (account balance)
     - Non-additive: cannot sum at all (ratios, percentages — store numerator and denominator instead)
   - Foreign keys: one surrogate key per dimension
   - Degenerate dimensions: order_number, invoice_number (store in fact, no separate dim)

2. Dimension tables:
   - For each dimension: list the descriptive attributes
   - Surrogate key (integer) as primary key — never use the source system natural key as PK
   - Include the source natural key as an attribute for traceability
   - Slowly changing dimension type per attribute: Type 1 (overwrite), Type 2 (version), Type 3 (keep prior)

3. Dimension hierarchies:
   - Identify rollup hierarchies within each dimension (product → category → department)
   - Flatten hierarchy into the dimension table (denormalized) for query performance

4. Date dimension:
   - Always include a date dimension — never join on raw date columns
   - Generate one row per day for a 10-year range minimum
   - Include: date_key, full_date, year, quarter, month, week, day_of_week, is_weekend, is_holiday, fiscal_period

Return: fact table DDL, dimension table DDLs, date dimension generation SQL, and ER diagram (text).