Fact and dimension tables are the foundation of data modeling in Power BI using a star schema. The fact table stores measurable data, while dimension tables provide descriptive context. This structure improves query performance, simplifies analysis and makes reports clearer.
- Fact tables: Store quantitative data like sales, revenue or orders.
- Dimension tables: Provide details such as dates, products or customers.
- Star schema: Connects one central fact table to multiple dimension tables.
Star Schema Model
A Star Schema is a type of database schema where a central fact table is connected to multiple dimension tables, forming a star-like structure. This design is widely used in data warehousing and business intelligence because it:
- Simplifies queries by clearly defining relationships between facts and dimensions.
- Reduces data redundancy through organised dimension tables.
- Improves performance for large datasets and complex analytics.
Now lets see some components of Star Schema Model:
1. Fact Table
The fact table is at the center of the Star Schema and stores the core transactional data you want to analyze such as sales records, orders or financial transactions. Each row in the fact table is unique and contains keys that link it to related dimension tables.
Example: SalesFact Table
Step 1. Load Data
- Open Power BI Desktop.
- Click Get Data, choose Text/CSV, and select SalesFact.csv
- Click Load.
- Your fact table is now available in Power BI.
Step 2. Check the data in SalesFact
- Go to Data View (table icon).
- Select the SalesFact table.
- Identify: Numeric columns like SalesAmount and Quantity (used for calculations) and Key columns like ProductKey, CustomerKey, and DateKey (used to connect tables)
Step 3: Load the Product table
- Click Get Data again.
- Select Text/CSV and load Product.csv
- Go to Data View and open the Product table.
Step 4: Create the relationship
- Switch to Model View (diagram icon). You will see SalesFact and Product as separate boxes.
- Drag ProductKey from the Product table and drop it onto ProductKey in the SalesFact table. A relationship line appears.
- This creates a one-to-many relationship from Product to SalesFact.
Step 5: Create measures
- In Report View, right-click the SalesFact table and choose New measure.
- Create the following measures:
Total Sales = SUM('SalesFact'[SalesAmount])
Total Quantity = SUM('SalesFact'[Quantity])
- These measures calculate totals dynamically.
Step 6. Build Reports
- Stay in Report View.
- Drag Product Name from Product table and Total Sales from measures
- Choose a table or chart visualization to view the results.
2. Dimension Tables
Dimension tables are connected to the fact table and store descriptive information that adds context to the fact data. They help categorize, filter and aggregate data efficiently. Each table provides additional details, such as location names, product descriptions or calendar dates.
Example: Product Dimension Table
Step 1. Load the Dimension Table
- Click Home -> Get Data and choose the source (Excel / CSV / SQL).
- Select your dimension file, for example Product.csv, and click Load.
Step 2. Check the data in the Dimension Table
- Go to Data View (table icon on the left).
- Select the Product table.
Step 3. Create relationship with the Fact Table
- Switch to Model View (diagram icon). You will see both tables Product and SalesFact.
- Drag ProductID from Product and drop it onto ProductID in SalesFact.
- A line will appear connecting the tables. Make sure, Relationship type is One-to-Many and Direction is Dimension -> Fact
- This allows product details to filter sales data.
Step 4. Use the Dimension Table in Reports
- Go to Report View.
- Choose a chart (bar, column, table, etc.).
- Drag a dimension field (e.g., ProductName or Category) -> X-axis / labels.
- Drag a fact table measure (e.g., Total Quantity) -> Y-axis / values.
Keys and Data Aggregation
1. Primary Key
- A primary key is a unique identifier for each row in a table.
- In a fact table, it could be the Transaction ID, while in a dimension table, it could be a Product ID, Customer ID, or Location ID.
- The primary key ensures that each record can be uniquely identified and referenced in relationships.
2. Foreign Key
- A foreign key is a field in the fact table that links to the primary key of a dimension table.
- This connection establishes the relationship between fact and dimension tables, allowing data from multiple tables to be combined for meaningful analysis.
3. Aggregation
- Aggregation is the process of summarizing data to extract insights.
- Dimension tables enable this by providing descriptive attributes such as summarizing sales by product, revenue by region or orders by date, making it easier to analyze trends.
Advantages of Using a Star Schema
The Star Schema makes data more efficient and easier to query in several ways:
- Reducing Redundancy: Repeated data like location names or product details, is stored only once in dimension tables and referenced in the fact table.
- Improving Query Performance: Power BI can quickly join fact and dimension tables, enabling faster queries, especially with large datasets.
- Easier Data Management: Updates are simplified. For example, changing a location name in the dimension table automatically updates all related records in the fact table.