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

Relate tables in Power Pivot without using relations

RELATED function in Power Pivot is equivalent to  common Vlookup/Hlookup function in pivot table. But the most important factor to note that before using RELATED function in Power Pivot one must create a relationship between fields containing lookup values in both tables.

 

Here we are using 3 tables  from Database AdventureWorkks2008r2

Ø  Product

Ø  ProductCategory

Ø  ProductSubCategory

Now  create two calculated columns in the Product table, and then you hide the product category and subcategory tables from the model. First, create a calculated column in the Product table with these DAX expressions:

 

ProductCategory=Related(ProductCategory[Name])

ProductSubCategory=Related(ProductSubCategory[Name])

 

 

 

At this point there is no need to show ProductCategory and ProductSubCategory  tables in Pivottable  because their content is now  available in Product table. For this reason it is better to hide them from data model. To do so select table in Diagram view, right-click it and choose Hide from Client tools



A Simple report creation using Calulated Field in Data Model

Here we are using 3 tables  from Database AdventureWorkks2008r2

1. Product
2. ProductCategory
3. ProductSubCategory

• Open Power Pivot window, load three tables as discussed in my earlier blog.

(As Relationship already present in database, Power Pivot creates them during loading process)

• Now create a calculated field as we need to count no of products using below mentioned definition:

                      NumofProducts:=CountRows(Product)

 

 

 





• Now simply create a pivot table, put category name from ProductCategory table into rows and choose Calculated field 'NumofProducts' for values.

 

 

 

Advantages of using Data Model:

 

1. Using Data Model Approach, one did not need to use vlookup to create columns. Here Pivottable slice products using category name based on relationship.
2. CalculatedField  created is global for data model. It an be accessed by different Pivottables based on same data model.
3. Regarding speed and memory usage, data model is capable of holding hundreds of millions of rows and computing very complex Pivottables in a matter of seconds.