Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Create Star Schema in Power BI

Cody Schneider••9 min read

Building a Power BI report on a single, messy table is like trying to build a new house using a pile of unsorted junk - it might work, but it's going to be slow, unstable, and a nightmare to navigate. The solution is a technique called the star schema. This article walks you through what a star schema is, why it's essential for Power BI, and how you can build one step-by-step.

GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Watch Graphed demo video

What Exactly is a Star Schema?

A star schema is a way of organizing your data model that separates your information into two distinct types of tables: Fact Tables and Dimension Tables. The fact table sits in the center, and the dimension tables radiate outwards, forming a shape that looks like a star - hence the name.

Imagine you run an online store. Your data model might look like this:

  • Your central fact table contains all your individual sales transactions. This table holds the numbers - things like Order Quantity, Sale Price, and Total Revenue. It's typically very long, with a new row for every single item sold.
  • Surrounding it are your dimension tables, which provide context. You'd have a 'Products' table describing each product, a 'Customers' table with a list of all your customers, and a dedicated 'Dates' table.

The dimension tables connect to the fact table using unique keys (like ProductID or CustomerID), allowing you to slice and dice your factual data by its dimensions. For example, you could filter total sales (from the fact table) by product category (from the dimension table).

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Fact Tables vs. Dimension Tables

Understanding the difference between these two table types is the most important part of data modeling in Power BI.

Fact Tables Contain the Numbers (& Keys)

Fact tables store observable events or transactions. They are the "what happened" in your data. In business contexts, this usually means sales, expenses, web traffic sessions, or support tickets logged.

  • What they contain: They are filled with numeric, quantitative data (the "facts") and the key columns needed to link to the dimension tables.
  • How they look: They are typically narrow (few columns) but very deep (millions or even billions of rows).
  • Example Columns in a Sales Fact Table: OrderID, DateKey, ProductKey, CustomerKey, QuantitySold, SaleAmount.

Dimension Tables Contain the Context (& Keys)

Dimension tables store the descriptive details or attributes about the people, places, and things involved in those facts. They answer the "who, what, when, where, and why" behind your data.

  • What they contain: They are filled with textual, descriptive data (the "dimensions" or attributes). They also contain a single, unique key column that identifies each row.
  • How they look: They are typically wide (many columns) but relatively shallow (fewer rows compared to fact tables).
  • Example Columns in a Product Dimension Table: ProductKey, ProductName, ProductCategory, ProductSubcategory, ProductColor, UnitCost.

Why a Star Schema is a Game-Changer for Power BI

Moving away from a single "flat file" to a star schema might seem like extra work, but the payoff in Power BI is enormous. Power BI's internal engine (called the VertiPaq engine) is specifically optimized to work with this structure.

Here are the key benefits:

  • Insanely Fast Performance: With a star schema, the relationships between tables are simple and direct. This means Power BI can slice, filter, and aggregate your data with incredible speed, even with massive datasets. Reports that once chugged along with a single flat table will now feel snappy and responsive.
  • Simple and Intuitive: The model is easy for anyone to understand. When you browse the fields pane, you'll see separate tables for "Products," "Customers," and "Sales" - just like how you think about your business. It makes report building far more logical.
  • Drastically Simpler DAX Formulas: Writing Data Analysis Expressions (DAX) is the key to unlocking powerful insights in Power BI. A clean star schema makes your DAX formulas shorter, easier to write, and much less likely to produce incorrect results. Complicated time-intelligence calculations become straightforward.
  • Scalable and Maintainable: Need to add new details about your products? Just add a new column to your 'DimProduct' table. No need to touch the millions of rows in your 'FactSales' table. The model is flexible and easy to update without breaking existing reports.

Step-by-Step Guide to Building a Star Schema in Power BI

Let's walk through a common scenario: transforming a single, flat sales report from an Excel spreadsheet into a proper star schema.

Step 1: Load Your Data into Power Query

First, get your data into Power BI. From the Home ribbon, click Get Data > Excel Workbook (or whichever source you are using). Select your flat file and when the Navigator dialog appears, choose your data tab and click Transform Data. This will open the Power Query Editor, which is where we will do all the work.

Your starting table might look something like this, with product info, customer info, and sales figures all mixed together:

GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Watch Graphed demo video

Step 2: Create Your Dimension Tables

Our goal is to break that all-in-one table into separate, clean dimensions. We’ll do this by duplicating our original query.

Start by renaming your original query to something like Fact_Sales. This will eventually become your central fact table.

Now, let's create the dimension tables:

Create a Product Dimension (Dim_Product)

  1. Right-click on the Fact_Sales query in the left-hand Queries pane and select Duplicate.
  2. Rename this new query to Dim_Product.
  3. With Dim_Product selected, go to the Home ribbon and click Choose Columns. Select only the columns related to the product: Product Key, Product Name, and Category.
  4. Now, we need to make sure we have one row per product. Right-click on the header of the Product Key column and select Remove Duplicates. This is a critical step!

You now have a clean dimension table that lists each unique product just once.

Create a Customer Dimension (Dim_Customer)

Repeat the same process for your customers:

  1. Right-click the Fact_Sales query and Duplicate it again.
  2. Rename it to Dim_Customer.
  3. Use Choose Columns to keep only customer-related attributes: Customer Key, Customer Name, City, and Country.
  4. Right-click on the Customer Key header and Remove Duplicates.

Step 3: Create a Dedicated Date Dimension

Using Power BI's automatic date hierarchies is convenient but limiting. A custom date table is essential for powerful time-intelligence analysis. You can create one from scratch easily.

  1. Create another duplicate of your Fact_Sales table and call it Dim_Date.
  2. This time, choose only the OrderDate column.
  3. Remove duplicates from this column so you have a unique list of all dates on which a sale occurred.
  4. With the date column selected, go to the Add Column tab. You'll find tools to add columns for Year, Month, Name of Month, Quarter of Year, Week of Year, and Day of Week. Add any you might need for your reports.

For more advanced calendars, you can use pre-made M code scripts, but this basic method works perfectly for most use cases.

Step 4: Clean Up the Fact Table

Now that our dimension tables hold all the descriptive text, we can trim down the fact table. Go back to your Fact_Sales query.

  1. Click Choose Columns.
  2. Keep only the foreign key columns needed to connect to your dimensions (Product Key, Customer Key, OrderDate), and your numeric fact columns (Quantity, Sales).

Your fact table should now look lean and mean - just keys and numbers.

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Step 5: Close & Apply and Create Relationships

You're done with the Power Query work! Click Close & Apply on the Home ribbon. Power BI will load your four clean tables into the data model.

Finally, let's connect everything:

  1. Click on the Model view icon on the left-hand side of the Power BI window. You'll see your four tables.
  2. Drag the key column from each dimension table to the corresponding key column in the fact table. For example, click and drag Product Key from Dim_Product and drop it on top of Product Key in Fact_Sales.
  3. Do this for Dim_Customer (Customer Key → Customer Key) and Dim_Date (OrderDate → OrderDate).

You will see lines appear between the tables, representing the relationships. Power BI is usually smart enough to detect a one-to-many relationship, which is exactly what we want. This means one product in the dimension table can be related to many sales in the fact table.

Your final data model should look like a perfect star:

Common Mistakes to Avoid

  • Using Bi-Directional Relationships: Power BI might occasionally create a relationship with filtering in both directions. Stick to single cross-filter direction (from the dimension table to the fact table) for 99% of cases. It prevents ambiguity and boosts performance.
  • Not Creating a Date Table: Failing to create a dedicated date dimension will prevent you from using many of DAX's powerful time-intelligence functions like TOTALYTD() or SAMEPERIODLASTYEAR().
  • Keeping Descriptive Text in the Fact Table: Don't leave columns like 'Product Name' or 'City' in your fact table. This bloats your model's size and slows it down. The whole point is to move this info into the dimension tables.
  • Snowflaking Unnecessarily: A "snowflake" schema is when a dimension table links to another dimension table (e.g., Dim_Product links to a Dim_Category table). While it sometimes has its uses, it adds complexity. For Power BI, it's almost always better to flatten these structures and include the category directly in the product table.

Final Thoughts

Arranging your data into a star schema is the single most important skill you can learn to build fast, efficient, and scalable Power BI reports. By separating your quantitative facts from your descriptive dimensions, you create a model that is both intuitive for humans and highly optimized for Power BI’s engine.

Of course, the process of data modeling in tools like Power BI is a professional skill set in itself. For marketing and sales teams who need answers from their data without becoming data engineers, we wanted an even simpler solution. With a tool like Graphed, we handle the data modeling for you. You just connect your sources like Shopify, Google Analytics, and Facebook Ads, then ask for the dashboard you need in plain English. That lets you skip straight to the insights.

Related Articles