Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI Star Schema


Share

One Big Table

In the First Report chapter, all the Poke Mart data was in one table.

Each row held everything about one order: the date, the store, the region, the product, the category, the price and the totals.

A table like this is called a flat table.

Here are its first rows:

123OrderIDDATEDateABCStoreABCRegionABCProductABCCategory123Quantity123UnitPrice123Sales123Cost
10011/1/2024Lilycove CityHoennHyper PotionMedicine112001200680
10021/1/2024Cerulean CityKantoPoke BallBalls1920038002090
10031/2/2024Lilycove CityHoennGreat BallBalls1360078004290
10041/2/2024Cerulean CityKantoAntidoteStatus Healers9100900405
10051/3/2024Violet CityJohtoPotionMedicine1930057002850
..............................

The table has 2,740 rows and 10 columns.

It works, but it has three problems:

  • Repetition: the same names are typed again and again. The pair "Goldenrod City, Johto" is on 650 rows. "Poke Ball", "Balls" and the price 200 are on 512 rows.
  • Size: 2,740 rows times 10 columns is 27,400 values, and most of the text values are copies.
  • Updates: if the shop moves Revive to a new category, you must change 153 rows. Miss one, and your report shows Revive in two categories.

Fact Tables and Dimension Tables

A star schema splits the data into two kinds of tables:

Fact tableDimension table
HoldsEvents, such as ordersThe things that take part in the events, such as products, stores and dates
One row perEvent (one order)Thing (one product)
ColumnsKeys that point to the dimension tables, and numbers to add upA unique key, and columns to filter and group by
Number of rowsMany, and more every dayFew
Poke MartSalesProducts, Stores, Date

Dimension tables are for filtering and grouping.

The fact table is for adding up.

A key is a column that identifies each row, such as ProductID.

The fact table stores only the key, for example P01. The dimension table stores each key once, together with the details: P01 is a Poke Ball, in the Balls category, with a unit price of 200.

If you know Excel, this is like VLOOKUP: the sales list holds a product ID, and you look up the name and the price in a product list.


The Poke Mart Star

This is the star schema you build in this chapter:

Salesfact table
  • OrderID
  • Date
  • StoreID
  • ProductID
  • Quantity
2,740 rows
Productsdimension
  • ProductID
  • Product
  • Category
  • UnitPrice
  • UnitCost
10 rows
Storesdimension
  • StoreID
  • Store
  • Region
6 rows
Datedimension, added later
  • Date
  • Year
  • Quarter
  • Month Number
  • Month
731 rows

Bold columns are keys. Each line runs from a key in a dimension table (the 1 side, where each value appears once) to the same key in Sales (the * side, where it appears many times).

  • Sales is the fact table in the middle: one row per order, 2,740 rows.
  • Products has one row per product: 10 rows.
  • Stores has one row per store: 6 rows.
  • Date will have one row per day. You create it in the Date Table chapter.

The dimension tables sit around the fact table like the points of a star.

That is where the name comes from.

Real models often have more points, such as customers, employees and suppliers.



Flat vs Star

The star holds the same information as the flat table.

Count the values in each design:

TableRowsColumnsValues
Flat: Sales2,7401027,400
Star: Sales2,740513,700
Star: Products10550
Star: Stores6318
Star: total13,768

The star needs about half as many values.

Every name is stored once: "Goldenrod City" is on one row of Stores, instead of on 650 rows of Sales.

Updates get easier too.

To move Revive to a new category, you change one cell in Products, and all of its 153 orders follow.

Where did the Sales and Cost columns go?

They can be calculated: Quantity from Sales, times UnitPrice or UnitCost from Products.

You add them with DAX in the RELATED chapter.

Note: Power BI stores the data it loads in a compressed form, so a star schema does not make your file exactly half the size.

The main reasons to use a star are that the model is easier to use, easier to keep correct, and fits the way Power BI filters and adds up data.


Load the Star Schema

Download the practice file.

It has three sheets: Sales, Products and Stores.

Download poke_mart_model.xlsx

  1. Open Power BI Desktop and start a blank report.
  2. On the Home tab, select Get data > Excel workbook. If a pop-up named Introducing the new Get Data experience! appears, select No thanks.
  3. Find poke_mart_model.xlsx and select Open.
  4. In the Navigator window, select the check boxes next to Products, Sales and Stores.
  5. Select Load.

The Navigator window in Power BI Desktop with the three sheets Products, Sales and Stores selected, a preview of the Sales sheet, and the Load button

Power BI loads the three sheets as three tables.

Fix the Stores Headers

The Stores sheet holds only text, so Power Query cannot tell its header row from the data rows.

Stores loads with the columns Column1, Column2 and Column3, and the headers StoreID, Store and Region end up as its first data row.

Fix it in the Power Query Editor:

  1. On the Home tab, select Transform data. The Power Query Editor opens.
  2. In the Queries pane on the left, select Stores.
  3. On the Home tab of the editor, select Use First Row as Headers.
  4. Select Close & Apply.

Stores now has the columns StoreID, Store and Region, and 6 rows.

Select Table view on the left side of the window to see the rows of the three tables.

Sales (2,740 rows)

123OrderIDDATEDateABCStoreIDABCProductID123Quantity
1001Monday, January 1, 2024S6P061
1002Monday, January 1, 2024S2P0119
1003Tuesday, January 2, 2024S6P0213
1004Tuesday, January 2, 2024S2P089
1005Wednesday, January 3, 2024S4P0419
1006Wednesday, January 3, 2024S5P091
...............

Order 1001 is the same order as in the flat table: store S6 is Lilycove City, and product P06 is a Hyper Potion.

Products (10 rows)

ABCProductIDABCProductABCCategory123UnitPrice123UnitCost
P01Poke BallBalls200110
P02Great BallBalls600330
P03Ultra BallBalls1200700
P04PotionMedicine300150
P05Super PotionMedicine700380
P06Hyper PotionMedicine1200680
P07ReviveMedicine1500900
P08AntidoteStatus Healers10045
P09Paralyze HealStatus Healers20095
P10AwakeningStatus Healers250120

Stores (6 rows)

ABCStoreIDABCStoreABCRegion
S1Viridian CityKanto
S2Cerulean CityKanto
S3Goldenrod CityJohto
S4Violet CityJohto
S5Slateport CityHoenn
S6Lilycove CityHoenn

See the Model

Select Model view on the left side of the window.

Model view shows each table as a box with its columns.

The lines between the boxes are relationships.

Power BI created them by itself, because the tables share the columns ProductID and StoreID.

The Sales to Products line came with the first load.

The Sales to Stores line came after you fixed the Stores headers and selected Close & Apply.

Model view in Power BI Desktop showing the Sales table in the middle, connected by relationship lines to the Products table and the Stores table

Your model already looks like the star in the diagram, without the Date table.

The next chapter explains the lines.


Stop Summarizing OrderID

In the First Report chapter, OrderID had a sigma sign (Σ) in the Data pane, because Power BI planned to add it up.

OrderID in this Sales table has a sigma sign too.

Here its Summarization is set to Count, so a visual would count the orders instead of listing them. Fix it now:

  1. Select Report view on the left side of the window.
  2. In the Data pane, expand Sales and select OrderID.
  3. On the Column tools tab, set Summarization to Don't summarize.

The sigma sign is gone.

When you add OrderID to a visual, Power BI now lists the order numbers instead of counting them.

Leave Quantity as it is.

Adding up quantities makes sense.


Save the Report

The report has never been saved, so File > Save is dimmed.

Select File > Save as > Browse this device, name the file poke_mart_model, and select Save.

You keep working in this file for the rest of the Modeling chapter, and in the DAX chapter.



Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.