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.
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.
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.
|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|
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.
|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 measure should follow same rules as above. Should be meaningful to the business users and follow business terminologies.
|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|
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.
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!!