Dimensional Modeling: Facts, Dimensions, and Grains

Dimensional modeling is the most widely used approach for organizing analytics data. Developed by Ralph Kimball, it structures data into two types of tables: facts (what happened) and dimensions (the context around what happened). The technique optimizes for query speed and business readability, not for storage efficiency or transactional integrity. If your goal is to answer business questions quickly and consistently, dimensional modeling is where you start. Facts and Dimensions: The Two Building Blocks Fact tables store measurable events. Each row represents something that happened: a sale, a click, a shipment, a login. Fact tables are narrow (a few foreign keys and numeric measures) and deep (millions or billions of rows). A typical sales fact table might look like: CREATE TABLE fact_sales ( sale_id BIGINT , date_key INT , customer_key INT , product_key INT , store_key INT , quantity INT , unit_price DECIMAL ( 10 , 2 ), total_amount DECIMAL ( 12 , 2 ) ) Dimension tables provide cont