In this lesson, you will learn how to categorize data elements into measurable quantitative facts and descriptive qualitative context attributes to build effective analytical models.
When we design data models for analytics, we organize data into two fundamental structures: Facts and Dimensions. Think of this as separating the "what happened" from the "who, what, where, and when."
What are Facts?
Facts are the quantitative measurements or metrics resulting from a business process. They are usually numeric and can be aggregated (summed, averaged, counted). If you can perform math on a data field—like calculating a total or an average—it is likely a fact.
- Examples:
order_amount,quantity_sold,discount_value,call_duration.
What are Dimensions?
Dimensions provide the descriptive context that allows us to slice and dice those facts. They represent the "who, what, where, and when." Dimensions are typically qualitative (text-based) and are used to filter or group your data.
- Examples:
customer_name,product_category,store_location,date_of_sale.
Putting It Together
Imagine an online retail database. You might have a sales record that looks like this:
-- An example of a sales record
SELECT
-- Dimension attributes (Context)
customer_name,
product_category,
store_region,
-- Fact attributes (Measurements)
quantity_sold,
total_revenue
FROM sales_data;
In this scenario, quantity_sold and total_revenue are your Facts. You can sum these up to find out how much money you made. The customer_name and store_region are your Dimensions. These allow you to answer complex questions like, "What was the total revenue in the West region?" or "How many products did John Doe buy?"
If you tried to "sum" a customer name, it wouldn't make sense—that confirms it is a dimension. If you try to calculate an average of a sales price, it works perfectly—that confirms it is a fact. Mastering this distinction ensures that your analytical queries remain performant and your business logic remains clear.
Key Takeaway: Facts are the measurable numbers that quantify a business process, while dimensions are the descriptive labels that provide the context needed to categorize and filter those measurements.