Data Modelling
Data Modelling consists of below aspects
a) Relationships between Tables
b) Formatting Numeric Values
c) Modifying Summarization
d) Changing Sort Order
e) Build Hierarchies
f) Hide Tables/Fields from Report View
g) Creating a Date Table
Relationships between Tables
1) Data Modelling: Often, you'll connect to more than one data source to create your
reports, and you'll need all of that data to work together. Modeling is how you get it
there. you can use multiple tables from multiple sources, and define
the relationship between them
2) Table Types: Lookup Tables/Dimensions are tables used for objects like Customer
Table,Product table,Date table,Region Table etc. Lookup Tables always have one and only
one row for each Item. Transaction data Tables are called Fact Tables or Data Tables. These
tables have multiple rows and are added regularly for each transaction. They typically have
Column Keys to match with the Lookup Tables. This helps in logical linking of Data between
the Fact and Dimension tables.
a. Build relationships
i. Relationships between multiple tables/sources are diagrammatically
displayed in the relationship View. Here we can add/modify relationships
between the different tables/Elements, For more detailed view of the
relationships we can use Menu option “Manage Relationships” in the Home
menu.
Ex: Order date is related to date in Date dimension. We can create multiple
relationships between the 2 tables but only one of them will be
primary.(default) We need to use USERELATIONSHIP function to activate
inactive relationships. As in below formula to calculate sales amount based
on Shipping date instead of Order
date.=CALCULATE(SUM(InternetSales[SalesAmount]),
USERELATIONSHIP(InternetSales[ShippingDate], DateTime[Date]))
ii. Make sure that when you try to create a relationship the key columns should
not have a Sum symbol to the left of column name.This happens for numeric
columns. If it exists then select the respective column in Modelling tab and
change “Default summarization Count” to Do not summarize
iii. Using RELATED function for Lookup
b. Add Custom Columns
i. Translate a flag (0/1) into meaningful text using if function.
ii. Relationship is possible only when there is a Unique key. When there is no
unique key we may need to create a Custom column by combining two or
more columns.
3) Formatting Numeric/Date Values : Formatting Fields is done as
a part of Data Modelling and any Visual that uses the field
inherits the same format.
Modifying Summarization: All Numeric fields by default display a ∑
besides them. It means any visual that includes this field shall sum up
the values. But some of the fields are not expected to show sum. Ex
Year field is expected to show the year and not Sum( Years). For such
fields we should modify default summarization to “ Do not
summarize”
4. Sorting : Sort by Column
i. By default when a chart/Slicer is created with a Month Name, Month names
are sorted in Ascending order Obviously the month Names should be sorted
by Calendar month no. So to implement this . Select the Month Name column
in the fields list and in the Modelling, Menu choose Sort Column by and
select the column Month . Now we can see correct order in the visual.
ii. Once this is setup in data model it is applied to any visual/Slicer that has this
column.
Similarly Custom Sort list can be created to display columns like Products by Category
i) In the Home Tab click on enter data and save as below
ii) Save Table as CatSort
iii) Create a custom Column in Orders table ( Table that has category Column)
SortOderForCat = RELATED(Catsort[SortOrder])
iv) Select Category column in Fields List
a. Select Sort by Column In the Modelling Tab
b. Select SortorderForCat
iii. Hiding columns from Report designer:
Generally the Month number column used above for sorting is not directly
used in reports. So it can be hidden by selecting the option Hide in Report
View. This is available on right click of a field in Data Tab.
b. Custom Measures: using YTD,MTD…
c. Custom Table: Add a Date table that has row for every date so that DAX functions
(Time Intelligence functions) work correctly
Building Hierarchy: Power BI allows you to create your own hierarchies. When a visual has
a hierarchy, it enables the ability to drill down for additional relevant details. For example, you might
have a visualization that shows the Location(Country) hierarchy with revenue and you want to drill
down on a specific Region that is under-performing. By drilling down, you’re able to see the States
that make up the Region . Further Drilldown of State shows the Cities that are part of the state. This
helps pin-point exactly what cities are causing the decrease in revenue.
To build a hierarchy, we’ll need to know the levels that comprise the hierarchy. In our example, the
levels are Country => Region => state => City
Method 1: click on Region and drop on the Country field. It creates a Country Hierarchy.
Similarly drag fields State, City over the new field “Country Hierarchy”
Method 2 : Right Click on Country select create New Hierarchy. Right click on other Fields => right
click and select Add to Country Heirarchy
Hide Table/Fields : Some of Fields added in Data model may not be directly used in any
Visual . They may be required for building Relationships (ex ProductID,CustomerID) or may be used
to create Custom Columns. It is a good practice to hide them from Report Designer. That means they
are not visible in Fields list shown in Reports View
Similarly some of the Tables are not directly used . Because they may have been appended to
another table or merged with another. To hide such fields/Tables Right click on them and select
“Hide in Report View”
Date Table:
A Date table includes all dates in the required date range. Additionally we have
columns to show Year, Month no, Month name, Day, Date Key as columns in this table. Also you can
have Day of the Week, Week Num, Quarter … in this table so that these columns are used in Visuals.
Use CalenderAuto function to create a Date table automatically using min and max dates from the
Model
1. In excel: You can enter a date in first column. Second column = year(A1),Month =
Month(A1),Day = day(A1)….. and drag down to add continuous dates.
2. M query.:
a. Click new data source/Get data => Blank query.
b. Click on advanced editor.( to get into power query)
c. Write below code
= let
Source = #date(2015,1,1),
Dates = List.Dates(Source,3655,#duration(1,0,0,0))
in
Dates
d. Click on To table to convert the list into a Table.
e. Rename the column as Date and add other columns by selecting Add column =>
Date => Year….Month etc.
f. Rename the query appropriately.
g. Close and Apply to save the table
3.DAX we can use a dax function to create a date table . Goto Modelling=> New Table
DateDax = ADDCOLUMNS(CALENDARAUTO(3),"Year",year([Date]),"Month",month([Date]))
Or
DateDax = ADDCOLUMNS(CALENDARAUTO(12),"DateKey",year([Date]) & right(0 &
Month([Date]),2) & right(0 & Day([Date]),2),"Year",year([Date]),"Month",month([Date]))
Here we assume fiscal year ends with march(3). We have added columns for Date, Year and Month.
Similarly other columns can be added for Week Day of Week, Quarter etc.
Also select the table and Mark as Date Table.
year = YEAR(Dates[Date])
Month = MONTH(Dates[Date])
Month Name = FORMAT(Dates[Date],"mmmm")
Day = FORMAT(Dates[Date],"dd")
Total Month Sales = TOTALMTD([Total Sales],Dates[Date])
We can also setup the months to display based on Fiscal Year (April to March) by creating a
concatenated column for Fiscal year and Month num.
DAX Formula: FYMonthNum =
VAR FYStartMonth = 4
//Update the fiscal year starting month above *Use number between 1 to 12
RETURN
IF (
MONTH ( Dates[Date] ) >= FYStartMonth,
MONTH ( Dates[Date] )
– ( FYStartMonth – 1 ),
12
+(
MONTH ( Dates[Date] )
– ( FYStartMonth – 1 )
)
)
