Real Sales Dimensional Model (Kimball)

1. Business Process

Sales — capturing each product sold to a customer at a point in time.

2. Declared Grain

One row per product per order per day

• Each row represents a single line item on a sales order
• Grain is fixed and never violated

3. Star Schema Overview

4. Fact Table

fact_sales

• order_id (degenerate dimension)
• date_key
• customer_key
• product_key
• store_key
• promotion_key

• quantity_sold
• gross_sales_amount
• discount_amount
• net_sales_amount
• tax_amount
• cost_amount

5. Dimension Tables

dim_date

• date_key (surrogate key)
• full_date
• day_of_week
• week_number
• month
• quarter
• year
• is_holiday

dim_customer

• customer_key (surrogate key)
• customer_id (natural key)
• first_name
• last_name
• gender
• birth_date
• customer_segment
• region
• effective_date
• expiration_date
• current_flag

dim_product

• product_key
• product_id
• product_name
• brand
• category
• subcategory
• package_size

dim_store

• store_key
• store_id
• store_name
• store_type
• city
• state
• country
• open_date

dim_promotion

• promotion_key
• promotion_name
• promotion_type
• start_date
• end_date
• discount_percent

6. Degenerate Dimension

• order_id stored directly in fact_sales
• No separate dimension table
• Used for drill-through and reconciliation

7. Conformed Dimensions

• dim_date shared with inventory, finance, shipments
• dim_product shared across sales and inventory
• dim_customer shared across marketing and CRM analytics

8. Example Query Use Case

• Total net sales by product category and month
• Top customers by revenue
• Promotion effectiveness analysis

9. Why This Model Works

• Simple joins
• High-performance aggregations
• Business-readable structure
• Scales cleanly on MPP systems

10. One-Line Summary

This sales dimensional model captures transactional sales at line-item grain using a star schema optimized for analytics.