Identifying Core Business Processes for Data Modeling

In this lesson, you will learn how to isolate specific operational activities within an organization to define the primary focus areas for your dimensional models.

Before you write a single line of SQL or design a schema, you must identify the "heartbeat" of the business. A business process is a set of activities that results in a measurable outcome. If you try to model "everything" at once, your data warehouse will become an unusable mess. Instead, focus on the discrete events that provide value.

How to Isolate a Process

To find a core business process, look for activities that generate data as they happen. Ask yourself: "What is the primary action being recorded?"

Common examples include:

• E-commerce: "Customer places an order."
• Logistics: "Package reaches a sorting facility."
• Healthcare: "Patient completes a lab test."

Each of these is a candidate for a Fact Table. If you mix these processes together—for example, trying to model "Order Processing" and "Inventory Replenishment" in one table—you will struggle with grain consistency and slow query performance.

A Concrete Illustration

Imagine you are building a model for a bookstore. You might identify two distinct processes:

1. Sales: A customer buys a book.
2. Restocking: The warehouse receives a shipment from a publisher.

By isolating these, you create two distinct dimensional models:

-- Focus: Sales Process
CREATE TABLE fact_sales (
    date_key INT,
    product_key INT,
    store_key INT,
    quantity_sold INT,
    total_amount DECIMAL(10,2)
);

-- Focus: Restock Process
CREATE TABLE fact_restock (
    date_key INT,
    product_key INT,
    supplier_key INT,
    units_received INT
);

By keeping these processes separate, you ensure your data remains clean and easy to navigate. If you forced these into a single "Book Movements" table, you would deal with complex, sparse rows and confusing metrics. Always identify the specific operational activity first, build the model around that single event, and then link them via shared dimensions.

Key Takeaway: A well-structured data model is built by isolating individual business processes into separate, focused fact tables rather than attempting to capture the entire organization in one massive schema.