Best practice guide for Power BI users – Part 2

This is the second post of a series covering best practice guide for Power BI users. If you have not read the previous post, you can check using below link.

https://asankap.wordpress.com/2020/04/18/best-practice-guide-for-power-bi-users-part-1/

In this post, I’m mainly focusing on naming conventions and trying to point out some mistakes people do when naming tables, fields and measures within Power BI.   Additionally, I will discuss what are things you need to focus on when you do model within Power BI.

Naming Convention

Tables

All the tables must become business entities within a model. In other words, business user must be able to understand the model by his own using his own knowledge about business.

Do Don’t
Keep table name singular Use names as it is in source table. Example: DimCustomer, FactSales, vw_Sales
Use business user friendly names Example: Customer, Calendar, Online Sales Use abbreviations unnecessary. Example: Cal_Date, FT_Daily_Trans, SaleAmt
Use domain specific names which help to business users Example: Direct Sales, Inventory Use camel case, pascal case notation as in database world. Example: CustomerSales, dailySales
Use spaces to break words and make it plain English. Example: Customer sales, Daily Transactions Use Prefix, suffix in table names Example: vw_Sales, vwCustomer

Fields (Columns)

All the field names must be meaningful to the business users. Business user must be able to search the model by using business terms irrespective of he/she familiar with the model.

Do Don’t
Use business user friendly names Example: Customer Name, Fiscal Year, Daily Sales Use abbreviations unnecessary. Example: Cust_Name, CalYear, DY_Sales
Use stranded abbreviation when possible. Example: YTD Sales, YoY Growth % Use camel case, pascal case notation as in database world. Example: CustomerName, customerCode
Use spaces to break words and make it plain English. Example: Customer Name, Daily Sales Use Prefix, suffix in field names Example: Cust_Name, Cust_Address
Use descriptive names for columns rather than keeping simple names. Example: Net Sales Amount instead of Sales

Calculated Measures

Calculated measure should follow same rules as above. Should be meaningful to the business users and follow business terminologies.

Do Don’t
Use business user friendly names Example: Total Sales, Growth % Use abbreviations unnecessary.
Use stranded abbreviation when possible. Example: YTD Sales, YoY Growth %, LYSP Sales Use camel case, pascal case notation as in database world. Example: ytdSales, GrowthYOY%
Use spaces to break words and make it plain English. Example: Total Sales, Average Sales Use Prefix, suffix in masure names Example: avgSales
Use descriptive names to for measures rather than simple measure names. Example: Mobile user churn count instead of Churn

Data Modelling

Relationship Types

Bi- directional relationships and Many to Many relationships should not be used unless it is a must in your design. Nevertheless, it is recommended to revisit the design of the model if you come across a scenario to use any of these.

Data Types

Ideally the data type of a field must be set inside Power Query Editor. However, if not, it should be done in here. Make sure you do set appropriate data type and formatting when creating a measure as well. Additionally, make sure you mark Calendar table as the Date Table in model.

Things to check when setting data types:

· Data Type (Ex: Text, Decimal Number, Date)

· Format (Ex: Currency, Percentage)

· No of decimal points

· Percentage type

· Thousand separators

· Default Summarization Type (Ex: SUM, Average)

. Data Category ( Ex: City, Postal code)

Organizing the model

Model must be organized so that business user can find required information easily. It recommended to perform following steps in-order to structure the model

1. Hide tables which are not required by business users.

2. Hide all unwanted columns (Surrogate keys, Primary Keys), measures, flags, fields use to calculated measures only.

3. Always try to create hierarchies rather than having individual attributes in dimension tables. When creating a hierarchy, hide attributes of that hierarchy from the model. For example, when you create Fiscal Hierarchy (Fiscal Year-Fiscal Month- Date), hide individual attributes from the mode (Fiscal Year, Fiscal Month).

4. Use folders to organize when your table have lots of measures and columns. It is recommended to have all the calculated measures of a table inside one folder.

That is all for this post. In my next post, I will be discussing about best practices to follow in report development. Till then, thank you for reading and stay safe!!

One thought on “Best practice guide for Power BI users – Part 2

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s