Course: Advanced Excel Training
Topic: Understanding Fact Tables and Dimension Tables
Course Link: Advanced Excel Course
Demo Link: Register for a Course Demo
Website: JustAcademy
Fact tables and dimension tables are two fundamental components of data modeling. They help organize information into a structure that supports accurate reporting, data analysis, and business decision-making. In Excel, these concepts are especially useful when working with the Data Model, Power Query, Power Pivot, and PivotTables.
A fact table stores measurable business events, such as sales transactions, product quantities, payments, or inventory movements. A dimension table stores descriptive information about those events, such as customer names, product categories, dates, locations, and employee details.
Instead of placing every detail into one large worksheet, analysts can separate transactional data from descriptive information and connect the tables through relationships. This structure is commonly used in a star schema.
A retail company wants to analyze its sales by product category, customer city, and month. The Sales table contains quantities and sales amounts, while Products, Customers, and Calendar tables provide the descriptions used to group and filter the results.
After studying this topic, learners should be able to:
A fact table is a table that stores business events, transactions, observations, or measurements. It usually contains numerical values that can be analyzed and keys that connect the events to dimension tables.
For example, a sales fact table may record which product was sold, which customer purchased it, when the transaction occurred, how many units were sold, and the revenue generated.
| Column | Purpose | Example |
|---|---|---|
| SalesID | Identifies a transaction or sales line when needed. | S1001 |
| DateKey | Connects a transaction to a date dimension. | 20260115 |
| ProductKey | Connects the transaction to a product. | P101 |
| CustomerKey | Connects the transaction to a customer. | C201 |
| Quantity | Records the number of units sold. | 5 |
| SalesAmount | Records the revenue for the transaction line. | 2500 |
| DiscountAmount | Records the discount applied. | 100 |
A dimension table stores descriptive details about the entities involved in business events. These details help users filter, group, label, and compare the measures stored in fact tables.
For example, a Products dimension might contain product names, categories, brands, colors, and sizes. A Customers dimension might contain customer names, cities, regions, and customer segments.
| Column | Purpose | Example |
|---|---|---|
| ProductKey | Uniquely identifies a product record. | P101 |
| ProductName | Describes the product. | Wireless Mouse |
| Category | Groups products by type. | Accessories |
| Brand | Identifies the product brand. | TechPro |
| Color | Describes a product attribute. | Black |
| Feature | Fact Table | Dimension Table |
|---|---|---|
| Main purpose | Stores business events and measurements. | Stores descriptive business information. |
| Typical contents | Keys and measurable values. | Unique key and descriptive attributes. |
| Examples | Sales, inventory movements, payments. | Products, customers, calendar, stores. |
| Number of rows | Often many rows. | Often fewer rows than related facts. |
| Numerical values | Frequently contains measures to aggregate. | May contain numbers, but they are usually descriptive attributes. |
| Keys | Typically contains foreign keys to dimensions. | Typically contains a unique key used for relationships. |
| Reporting role | Provides values to summarize. | Provides categories to filter and group values. |
| Example question | How much revenue was generated? | Which product category generated the revenue? |
Remember: Fact tables answer questions about what happened and how much. Dimension tables help explain who, what, where, and when.
Measures are values used to quantify business activity. They commonly appear in fact tables and can be aggregated in reports.
Examples include:
Measures can be summarized using operations such as SUM, COUNT, MIN, MAX, and AVERAGE, depending on the meaning of the data.
Attributes are descriptive fields that help users understand and categorize business information. They commonly appear in dimension tables.
Examples include:
Attributes are commonly used in PivotTable Rows, Columns, Filters, and slicers to organize the results of calculations.
Keys allow fact tables and dimension tables to connect correctly.
A primary key uniquely identifies a row in a dimension table. For example, ProductKey identifies a particular product record in the Products table.
A suitable relationship key should be unique on the dimension side and should not contain duplicate identifiers.
A foreign key in the fact table refers to the corresponding key in a dimension table. The same foreign-key value may appear many times because multiple transactions can involve the same product or customer.
| Products Dimension | Sales Fact |
|---|---|
| ProductKey: P101 | ProductKey: P101 |
| ProductName: Wireless Mouse | Quantity: 2 |
| Category: Accessories | SalesAmount: 1600 |
| Brand: TechPro | TransactionDate: 2026-01-15 |
The ProductKey in the Products table identifies one product. The ProductKey in the Sales table connects each sales line to that product.
Granularity, also called grain, describes exactly what one row in a fact table represents. Defining the grain is one of the most important steps in designing a reliable data model.
For example, a sales fact table might contain one row per sales order line, while a monthly sales summary might contain one row per product per month. These tables contain different levels of detail and should not be treated as if they have the same grain.
| Fact Table Type | Meaning of One Row |
|---|---|
| Transaction fact | One sales order line. |
| Daily snapshot | One product's stock balance on one date. |
| Monthly summary | One product's sales total for one month. |
| Attendance fact | One employee's attendance event or daily attendance record, depending on the design. |
A star schema is a data modeling design in which a central fact table connects to surrounding dimension tables. It is widely used for analytical reporting because the structure separates measurements from descriptive details.
Calendar Dimension
|
|
Products Dimension -- Sales Fact -- Customers Dimension
|
|
Store DimensionIn this model, the Sales fact table stores sales measurements. The surrounding dimensions describe the products sold, customers who purchased them, dates of sale, and stores where transactions occurred.
Consider a retail business that wants to track revenue by product, customer, and month. The model can be built using a Sales fact table and three dimension tables: Products, Customers, and Calendar.
| ProductKey | ProductName | Category | UnitPrice |
|---|---|---|---|
| P101 | Wireless Mouse | Accessories | 800 |
| P102 | Keyboard | Accessories | 1500 |
| P103 | Laptop | Electronics | 50000 |
| CustomerKey | CustomerName | City | Segment |
|---|---|---|---|
| C201 | Asha | Mumbai | Retail |
| C202 | Rahul | Pune | Business |
| C203 | Neha | Thane | Retail |
| SalesID | ProductKey | CustomerKey | SalesDate | Quantity | SalesAmount |
|---|---|---|---|---|---|
| S1001 | P101 | C201 | 2026-01-15 | 2 | 1600 |
| S1002 | P102 | C202 | 2026-01-18 | 1 | 1500 |
| S1003 | P103 | C201 | 2026-02-05 | 1 | 50000 |
| S1004 | P101 | C203 | 2026-02-10 | 3 | 2400 |
For this example, each Sales row represents one sales order line. SalesAmount is the total amount for that line, not the unit price.
Before loading data into the Data Model, organize each dataset into a clean table.
| Suggested Name | Table Type | Purpose |
|---|---|---|
| FactSales | Fact | Stores sales transaction details. |
| DimProduct | Dimension | Stores product descriptions. |
| DimCustomer | Dimension | Stores customer descriptions. |
| DimCalendar | Dimension | Stores calendar and reporting periods. |
These names are a convention, not a mandatory Excel requirement. Clear naming makes complex workbooks easier to understand.
Relationships connect the fact table to its dimensions. In a typical star schema, the dimension key is unique, while the corresponding foreign key can repeat in the fact table.
DimProduct[ProductKey] 1 ---- * FactSales[ProductKey]
DimCustomer[CustomerKey] 1 ---- * FactSales[CustomerKey]
DimCalendar[DateKey] 1 ---- * FactSales[DateKey]The number 1 represents the unique side of the relationship, and the asterisk represents the many side. The exact column names depend on the workbook's design.
Important: A relationship can fail if the dimension key contains duplicates or if the related columns have incompatible data types. Also check for unmatched keys in the fact table.
Once the relationships are established, a PivotTable can use fields from different tables in the same report.
Dimension fields provide the labels and groupings, while the fact-table measures provide the values being summarized.
Fact tables can be designed in different ways depending on the business process and the questions the model must answer.
Stores individual business events at a detailed level. For example, a sales table may store one row per product line on a sales order.
Example use: Analyze individual transactions, product sales, or daily revenue.
Stores measurements at regular intervals, such as daily inventory levels or monthly account balances.
Example use: Compare stock levels at the end of each month.
Tracks a process as it progresses through defined stages. A row may be updated as the process moves from start to completion.
Example use: Track an order from creation through dispatch and delivery.
Stores events or relationships without traditional numerical measures. For example, it may record which students attended which classes. The number of records can still be counted for analysis.
Dimension tables often contain attributes that can be organized into reporting hierarchies. These hierarchies allow users to explore data at different levels of detail.
| Dimension | Example Hierarchy |
|---|---|
| Calendar | Year > Quarter > Month > Date |
| Geography | Country > State > City |
| Product | Category > Subcategory > Product |
| Organization | Department > Team > Employee |
For example, a report can begin with total sales by year and then move down to quarter or month. This makes the report useful for both summary-level and detailed analysis.
| Mistake | Why It Is a Problem | Recommended Solution |
|---|---|---|
| Duplicate dimension keys | Prevents a reliable one-to-many relationship. | Remove or resolve duplicate records and use a unique key. |
| Mixing different grains | Can produce misleading totals. | Define the meaning of one row before loading the table. |
| Repeating all descriptions in the fact table | Creates unnecessary duplication and makes updates harder. | Store descriptive attributes in appropriate dimensions. |
| Using names as relationship keys | Names can repeat or change over time. | Use stable identifiers such as CustomerKey or ProductKey. |
| Inconsistent data types | Can prevent relationships or create mismatches. | Standardize the related key columns. |
| Unmatched foreign keys | Some fact records may not relate to a dimension record. | Find missing identifiers and correct the data or model. |
| Incorrect aggregation | Totals may not represent the intended business metric. | Confirm whether each measure should be summed, counted, averaged, or calculated differently. |
| Combining unrelated facts carelessly | Can duplicate values when tables have different grains or relationship paths. | Model each business process carefully and validate report calculations. |
Task: Create a simple Excel Data Model for a retail business.
Project Objective: Build an interactive sales dashboard that uses a fact table and multiple dimension tables.
Answer: A fact table stores measurable business events or observations, along with keys that connect those records to dimension tables.
Answer: A dimension table stores descriptive information used to filter, group, and explain business measurements.
Answer: Fact tables provide the measurements to summarize, while dimension tables provide the descriptive context for grouping and filtering those measurements.
Answer: A measure is a value used to quantify business activity, such as revenue, quantity, cost, or hours worked.
Answer: A dimension attribute is a descriptive field, such as product category, customer city, or calendar year.
Answer: A primary key uniquely identifies a record in a table, such as ProductKey in a Products dimension.
Answer: A foreign key connects a record to a related record in another table. In a sales model, ProductKey in FactSales refers to ProductKey in DimProduct.
Answer: Granularity defines the level of detail represented by one row in a fact table, such as one sales order line or one monthly inventory snapshot.
Answer: A star schema is a data modeling structure in which a fact table is connected to multiple dimension tables.
Answer: Unique dimension keys ensure that each fact record can relate to the correct dimension record through a reliable relationship.
Answer: Yes. Fact tables can contain descriptive identifiers or transaction references, but they should be designed primarily around the business events, keys, and measurements needed for analysis.
Answer: Yes. A dimension can contain numerical attributes such as package size or product weight. The role of the column depends on its meaning, not just whether it contains numbers.
Answer: It defines what each row represents and helps prevent incorrect aggregation, duplicate counting, and incompatible combinations of data.
Answer: It provides consistent date attributes, such as year, quarter, and month, for time-based filtering and reporting.
Answer: They are loaded into the Excel Data Model and connected through relationships. PivotTables can then combine dimension attributes with fact measures for reporting.
Fact tables and dimension tables form the foundation of structured analytical reporting. Fact tables record measurable business activity, while dimension tables describe the products, customers, dates, locations, and other entities associated with that activity.
By defining a consistent grain, using reliable keys, preparing clean data, and creating appropriate relationships, Excel users can build Data Models that support accurate PivotTables, interactive dashboards, and meaningful business insights.
