Designing a Dimensional Model for Olist: A Kimball Four-Step Walkthrough

A hands-on learning project applying Kimball’s dimensional design methodology to a real public e-commerce dataset.

A. Introduction

This article walks through the complete Kimball Four-Step Dimensional Design Process using the Brazilian E-Commerce Public Dataset provided by Olist on Kaggle.

Olist is one of Brazil’s largest online marketplace platforms. It connects small businesses across the country to customers through a single contract and a unified logistics network. Sellers list their products on the Olist platform and ship directly to customers using Olist’s logistics partners. After a customer purchases a product, the seller is notified to fulfil the order. The customer receives a satisfaction survey by email after each order experience.

As the platform grew, so did the volume and complexity of its transactional data. The business identified four key analytical areas where in-depth data analysis had become critical:

• Customer Satisfaction and Review Analysis
• Customer Sentiment Clustering
• Sales Prediction
• Delivery Performance Optimization

To support these goals, Olist requires a dedicated data warehouse that allows the business to store, manage, and analyse large volumes of diverse data efficiently.

B. Data Source Overview

The dataset is publicly available on Kaggle, generously provided by Olist. It covers real anonymised e-commerce transactions from the Brazilian market and consists of multiple relational tables capturing the full order lifecycle from purchase through payment, delivery, and customer review.

The dataset includes the following key tables:

• Orders: one record per order with status and timestamps
• Order Items: one record per item within an order, with seller, product, price, and freight value
• Order Payments: one record per payment method used per order
• Order Reviews: customer satisfaction scores and optional written comments
• Products: product attributes including category, weight, and dimensions
• Sellers: seller location information
• Customers: customer identifiers and location
• Geolocation: Brazilian zip code prefixes mapped to city, state, latitude, and longitude

A few important characteristics of the dataset directly shaped design decisions throughout this project:

• An order can contain items from multiple sellers
• Payment for a single order can be split across multiple payment methods
• Reviews are not limited to delivered orders, customers also leave reviews on cancelled and unavailable orders
• customer_id in the dataset is order-scoped, not person-scoped. Every order gets a new customer_id even if the same person placed multiple orders. The true person identifier is customer_unique_idand it is anonymized

C. Requirements Gathering Process

Before any design work began, a requirements gathering session was simulated with seven stakeholders across the business. The purpose was to understand business needs, identify analytical gaps, and surface data quality issues before touching the schema.

Each session was structured as a question and answer exchange where the stakeholder speaks from their own operational knowledge, just the business users describing what they need and what they know.

The seven sessions covered the following roles:

1. Business Operations Lead covered the end-to-end business process from seller registration through product listing, order placement, payment, fulfillment, and customer review. This session established that delivery is tracked at order level not seller level, that payment can be split across multiple methods, and that reviews are tied to the order and not to any specific product or seller.
2. Head of Analytics and BI defined the four analytical areas the business urgently needed to support: sales performance, payment behavior, customer satisfaction, and delivery performance. This session identified the primary consumers of the warehouse across sales operations, finance, CX, logistics, and the marketing and data science teams, and confirmed that all of them need self-service access through a BI tool.
3. Head of Data Engineering surfaced the most critical pain points in the current system. Analysts were querying directly from the production database, causing platform slowdowns. Several business questions could not be answered reliably due to incorrect joins, missing pipelines, and no shared place to store model outputs.
4. Customer Experience Manager defined the grain and attributes required for satisfaction analysis. This session confirmed that reviews must be segmented by order outcome because a review on a cancelled order reflects a fundamentally different problem from a review on a delivered but damaged order. It also established that category-level attribution is an accepted approximation since a review covers the whole order experience, not a specific product.
5. Head of Logistics and Operations defined the five delivery milestones the team tracks per order and the KPIs required from the warehouse. This session confirmed that delivery is tracked at order level with one timeline per order regardless of how many sellers fulfilled it, making customer destination state the most reliable geographic breakdown for delivery performance analysis.
6. Finance and Demand Planning Lead established that the finance team tracks total order volume and total transaction value as platform health metrics. This session also surfaced that vouchers are a direct marketing cost to Olist and that review scores per category per month are needed as a forecasting feature.
7. Marketing and Data Science Team defined the customer clustering features and how cluster assignments need to be stored in the warehouse. This session established the requirement to track customer movement between segments across model runs, which directly shaped the decision to store cluster assignments in a separate table rather than on the customer dimension.

The full requirements gathering session is available in the project documentation.

What the Sessions Revealed

The data engineering team confirmed that analysts were querying directly from a normalised operational database. Two major problems followed. Heavy analytical queries slowed down the live platform, causing timeouts during peak hours. And because all data lived in normalised transactional tables, producing even basic reports required joining multiple tables, which was error-prone and slow. Producing the average review score per product category per month, for example, required joining five separate tables and took minutes to run.

Beyond the infrastructure problems, several business questions simply could not be answered reliably at all:

• Correct payment totals by payment method per month. Analysts regularly produced numbers that did not match finance expectations with no way to enforce the correct approach. Raised by the Head of Data Engineering and confirmed by the Head of Analytics and BI.
• Critical review scores after late deliveries. Combining review and delivery data was complex and analysts regularly got it wrong. Raised by the Head of Data Engineering.
• Customer segment transaction value. There was no customer feature table, no clustering pipeline, and no place to store model outputs. Raised by the Head of Analytics and BI, the Head of Data Engineering, and the Marketing and Data Science Team.
• Historical seller and product performance. When attributes were updated in the source system, old values were overwritten and all historical records changed retroactively, corrupting trend analysis. Raised by the Head of Data Engineering.
• Satisfaction by order status. Current reporting averaged all reviews regardless of order outcome, hiding serious problems in cancelled and unavailable orders. Raised by the Head of Data Engineering and deepened by the Customer Experience Manager.

Each of these gaps maps directly to a decision in the dimensional design. Incorrect payment totals are prevented by storing payment in its own fact table separate from items. Satisfaction mixed across order statuses is fixed by preserving order status on every review row. Cross-process analysis between delivery and reviews is enabled by shared conformed dimensions. Customer segment storage is handled by the cluster dimension and cluster assignment table. The one gap not fully resolved in this design is historical attribute tracking for sellers and products, which requires slowly changing dimension handling defined during physical schema design after the four steps are complete.

These gaps directly shaped every design decision that followed.

D. Designing the Data Warehouse Model

The data modelling approach follows Kimball’s Four-Step Dimensional Design Process. The four steps are applied in strict order because each step constrains the next. Declaring the grain in Step 2 determines what dimensions and facts are valid in Steps 3 and 4. Jumping ahead creates design errors that are expensive to fix later.

Step 1: Select the Business Process

A business process is a low-level activity performed by the organisation, expressed as an action verb. Each process generates measurable events at a specific point in time and produces data the organisation needs to analyse. The companion dimensions describe the descriptive context surrounding each event.

Four core business processes (BP) were identified and selected for the Olist Data Warehouse.

1. BP1: Customers Placing Orders and Purchasing Items. The foundational process of the platform. Every analytical initiative traces back to understanding what was sold, to whom, by which seller, at what price, and when.
2. BP2: Collecting Payments. Payment is recorded at order level while item purchases are recorded at item level. Keeping payment as its own process prevents incorrect totals and unlocks payment method mix, voucher cost, installment behaviour, and discount analysis.
3. BP3: Fulfilling and Delivering Orders. Order fulfilment moves through five sequential milestones. Storing all five timestamps together on one record per order enables stage-level delay decomposition and on-time delivery analysis directly from the BI tool.
4. BP4: Collecting Reviews from Customers. A review is a separate customer action that happens after the order experience. Reviews exist for all order statuses, not just delivered orders, and must be stored with the order outcome preserved to enable correct satisfaction analysis.

Step 2: Declare the Grain

The grain is a precise statement of what one single row in the measurement record represents. Every row must represent exactly one grain instance, no more and no less. This is the most critical decision in the design process because every dimension and every measure added later must be consistent with it.

Step 3: Identify the Dimensions

A dimension provides the descriptive context surrounding each business process event, answering who, what, where, and when. Each identified dimension becomes a foreign key on the measurement record pointing to its own dimension table.

Five dimensions were identified for the Olist Data Warehouse.

1. dim_date

Captures when a business process event occurred. Used across all four business processes.

2. dim_customer

Captures who the customer is. Used across all four business processes.

3. dim_seller

Captures who the seller is. Used in BP1 to analyse transaction value by seller and in BP3 as a secondary geographic breakdown for delivery route analysis.

4. dim_product

Captures what item was purchased. Used in BP1 to analyse transaction value and order volume by product category.

5. dim_cluster

Captures the segment definitions produced by the data science team’s clustering model. Used in BP4 to allow the marketing team to analyse review behaviour and build campaigns by segment directly from the BI tool.

Dimensions by Business Process:

Step 4: Identify the Facts

Facts are the numeric measurements produced by each business process event. They must be consistent with the declared grain. Every fact on a measurement record must be true for every row at that grain.

1. fct_order_items (Transaction Fact Table)

• Business Process: BP1 Customers Placing Orders and Purchasing Items
• Grain: One row per order item purchased by a customer
• Dimensions:

• Degenerate Attributes: order_nk, order_item_sequence
• Measure: price, freight_value, order_status

2. fct_order_payments (Transaction Fact Table)

• Business Process: BP2 Collecting Payments
• Grain: One row per payment method used per order
• Dimensions:

• Degenerate Attributes: order_nk, payment_type, payment_sequential
• Measure: payment_value, payment_installments, expected_order_value, discount_amount

3. fct_order_delivery (Accumulating Snapshot Fact Table)

• Business Process: BP3 Fulfilling and Delivering Orders
• Grain: One row per order shipment, updated at each milestone
• Dimensions:

• Degenerate Attributes: order_nk, multi_seller_order
• Measure: order_status, days_purchase_to_approval, days_approval_to_carrier, days_carrier_to_delivery, days_estimated_to_actual, delivery_status, late_severity

4. fct_order_reviews: Transaction Fact Table

• Business Process: BP4 Collecting Reviews from Customers
• Grain: One row per review submission per order submitted by a customer
• Dimensions:

• Degenerate Attributes: review_nk, order_nk
• Measure: order_status, review_score, sentiment_score, review_length_chars, review_severity, comment_status, days_since_purchase

ERD

A complete Entity Relationship Diagram (ERD) encompassing all fact and dimension tables, with relationships clearly visualized between them.

Bus Matrix

X (x5) = Role-playing dimension. Dim Date is referenced five times in Fulfilling and Delivering Orders under different aliases: purchase_date_id, approval_date_id, carrier_handoff_date_id, estimated_delivery_date_id, and actual_delivery_date_id. All five point to the same single dim_date table.