Create a Star Schema Model in Power BI

In order to create a  star schema model from  source data system, one may have to perform some data Denormalization.

Although transactional database system  tend to be highly normalized; reporting systems are denormalized into star schema.

One of the best models to use when analyzing large set of data is  Star Schema.

We’ll create following steps to create Star Schema model:

(We are using Access database Adventureworks)

 

Ø Create table relations:

 

Using Table Import  Wizard in Power Pivot Data Model, connect to database, select tables and fields to import data.(Date, Customer and Internet Sales)

                Tables and selected field lists in imported data:

Source Table                         Friendly Name     Fields

DimDate                                 Date                        DateKey, FullDateAlternateKey, EnglishMonthName,               

                                                                                MonthnumberofYear, CalendarQuarter, CalendarYear

 

DimCustomer                        Customer               CustomerKey, BirthDate, MaritalStatus, Gender, YearlyIncome,

                                                                               TotalChildren,HouseOwner, NumberofCars

FactInternetSales                 Internet Sales       ProductKey,OrderDateKey,ShipDateKey,CustomerKey,     

                                                                               SalesTerritoryKey,SalesOrderNumber,SalesOrderLineNumber,

                                                                               OrderQuantity,UnitPrice,TotalProductCost, SalesAmount

 

Switch the Power Pivot window to diagram view, we’ll see Customer and Internet Sales have a relationship defined.

Now create relationship between DateKey from Data table and OrderDateKey in the Internet Sales table. Similarly create relationship between DateKey  and  ShipDateKey.

 

Ø Denormalize  Data:

On the Home tab of  Power Pivot Model Designer window, click existing connections button. Open connection of

Adventureworks.acdb and select query option for importing data and change query friendly name  to Product.

After verifying that you are getting data, click OK button and Finish button on next screen.

Ø Create a Linked  Table:

To create a linked table, enter data shown below to an empty spread sheet. Convert data to table and rename table name SalesTerritory

 

SalesTerritoryKey	Region	Country	Group
1	Noerthwest	Unied States	North America
2	Northeast	Unied States	North America
3	Central	Unied States	North America
4	Southwest	Unied States	North America
5	Southeast	Unied States	North America
6	Canada	Canada	North America
7	France	France	Europe
8	Germany	Germany	Europe
9	Australia	Australia	Pacific
10	United Kingdom	United Kingdom	Europe
11	NA	NA	NA

 

On the Power Pivot  tab, click Add to Data Model button

Ø Now Create Hierarchy

Creating hierarchies is one way to increase the usability of your model and help users instinctively gain more value in their data analysis. In the next section you will see some other things you can do to the model to increase its usability.

Ø Now Create a Business Model with a Pivot Table