Power BI Desktop Tutorial: Building a Star Schema from an Excel Source

An updated version of this tutorial (including a video lecture) is available here: https://businessintelligist.com/?p=2718

This tutorial shows how to create a Star Schema using Power BI Desktop.

Data for the tutorial can be obtained by downloading Excel file from here

Very often our analysis starts with a flat data set that contains all of the pertinent columns in a single table that looks like the one above. As we can see, we can analyze this data using three different lenses or dimensions:

1. Product 
2. Geography 
3. Date
   

In this tutorial, we will learn how to massage the data using Power BI Desktop so that we can build a more user friendly model.

Step 1 – Get Data

Open Power BI Desktop

From the ribbon select Get Data

Select Excel and click Connect

Browse to the location of the downloaded source file, select the file and click Open

Navigator window opens, select SalesData and click Edit

Query Editor window opens with SalesData Query selected in the right panel

Highlight Zip column and change data type to Text

Make sure Date column is of Date data type

Now we are going to create 4 queries and prepare each of query to be a different dimension/lens.

Right click on SalesData Query in the left panel and duplicate it 3 times to create a total of 4 queries

Highlight SalesData query and from the ribbon select Choose Columns

Choose columns dialogue opens

Select ProductID, Product, Category, Segment and Manufacturer. Unselect remaining columns

Click Ok

In the right panel, under Query Settings, rename the query to Product

Highlight ProductID fields and from the ribbon select Remove Duplicates. This will create a unique list of Products

Highlight query SalesData (2) and from the ribbon select Choose Columns

From choose columns dialogue, select Zip, City, State and Region columns

Rename query to Geography

Highlight Zip field and from the ribbon select Remove Duplicates to create a query of unique Zips

Similarly, highlight query SalesData (3) and from the ribbon select Choose Columns

From choose columns dialogue, select Date column

Rename query to Date

Highlight Date column and from the ribbon select Remove Duplicates to create a query of unique dates

Highlight SalesData (4) and from the ribbon select Choose Columns

From choose columns dialogue, select ProductId, Zip, Date and Amount fields

Rename query to SalesFact.

There should be 4 queries at this point, Product, Geography, Date and SalesFact

From the ribbon select File -> Close & Apply -> Close & Apply

Notice a dialogue appears and shows the progress of the data being loaded to Power BI Desktop model

Step 2 – Create Relationship

Once data is loaded, from the ribbon select Home -> Manage Relationships

Manage Relationships dialogue opens. Notice Power BI Desktop auto detects relations

If no relations are detected, click on Autodetect and notice if any of the relationships are detected

There should be a relationship between

    SalesFact and Product

    SalesFact and Geography

    SalesFact and Date

Let’s suppose a relationship is not detected between SalesFact and Date

Click on New button and Create Relationship dialogue opens

From the first drop down select SalesFact and highlight Date field

From the second drop down select Date and highlight Date field

Click OK and Close

Step 3 – Create Measure

To create a Measure, in the right panel expand SalesFact under Fields panel

Right click on SalesFact and select New Measure

Notice a formula bar appears in the main panel

Enter Sales = SUM(‘SalesFact'[Amount]) in the formula bar. Notice this will create a new field under SalesFact table

Format Sales field as US dollar currency

Step 4 – Create Report

From Fields panel in the right panel, expand SalesFact select Sales field

From Fields panel in the right panel, expand Product and select Manufacturer

This will create a column chart representing the Sales by Manufacturer

Click on the ellipsis on the top right corner and sort by Sales