close

A Step-by-Step Guide to Improve your Data Model’s Performance using the Best Practices in Power BI

Posted by Errin O'Connor on Mar, 22, 2021 10:03

A Step-by-Step Guide to Improve your Data Model’s Performance using the Best Practices in Power BI

Today here in this article we will be going over some rules to add to your Tabular Editor. It will scan your entire data model as per every rule defined and provide suggestions as per the objectives whenever a condition is met. This method will increase your efficiency in a task that usually takes long hours if done manually.

Power BI and DAX expressions:

  • Make use of the DIVIDE function for splitting up different information and evade using the IFERROR function in the Power BI. 
  • Qualify the Column references entirely and Measure references should be entire without qualifications.
  • Two measures should never have identical descriptions.

How to prevent an error?

  • To avoid any types of errors, Make sure that all the data columns have a source column without fail. Also, all the calculated columns should have an expression.
  • Make certain that the model is as contracted and lean as it can be.
  •  You should have longer and leaner tables for optimum working of Power BI.

How to ace formatting?

  • Add or remove data category for columns in your data model.
  • Do not recapitulate numeric columns and also make sure that the initial letter of items is in capital. 
  • Hide fact table columns.
  • Put out of sight the foreign keys and mark the main keys. 
  • Months (as a string) should be organized and sorted out.
  • Any object should neither begin nor finish with a space. 
  • Percentages has to be formatted with a number of separators and just one decimal.
  • Provide a design string for “Date” and “month” columns in your data model.
  • Association columns should be of numeral data type.
  • Whole numbers should be formatted with thousands separators and no decimals

Maintenance of data model:

  • Make sure that the tables have associations.
  • Objects should be without any description.
  • Eliminate data sources that have no references.
  • Get rid of unnecessary columns and measures to increase the speed of your data model: If you have the option to choose amongst a premeditated column or a measure, go for the measure because they are more flexible, don’t acquire too much processing time and don’t capture a lot of space in the model too.
  • If you need time portion in your model, then divide the timestamp into two columns namely a date column and a time column. This decreases the uniqueness of the high cardinal column and the engine is going to be able to squeeze the data accurately to a large extent.
  • Make sure that the model is as thin and incline as it can be.
  • Longer and leaner tables are much easier to work with.

Columns Naming Conventions to come into play:

  • Bring into play Camel Case for columns that are out of sight or hidden.
  • Divider name must match the table name for distinct partition tables.
  • The names of the objects are not supposed to hold any special characters.

SSAS Best Practices:

  • Stay away from bi-directional associations in opposition to the elevated cardinality columns: What exactly is high cardinality columns? Columns with an elevated amount of distinctive values. The more distinctive values a column has, the more impenetrable it is for the engine to squeeze it.
  •  Try to keep away from text columns that are of no use, except for when you enormously require them in your reports.
  • Keep away from the snowflake schema structural diagram.
  • Carry out the usage of hovering point data types.
  • Huge tables should be separated.
  • Reduce the power query transformations.
  • The data model should include a date table and the auto-date table should be removed.
  • For users who are giving out printed reports, colorless or light color backgrounds are the most suitable for printers.
  • Don’t place a lot of visuals in a particular report as it slows down the report’s presentation.
  • Decrease the usage of calculated tables.
  • Templates make sure that commercial branding is functional to all the pages.
  • Numerous scrolls on a particular page can direct to an unconstructive user experience.
  • Examine traditional visual presentation in reports to make sure that the report loads quickly.

What is a Tabular Editor and how does the best practice rule works in it? 

Tabular Editor is a tool that is a substitute for SSDT to generate Tabular models. It’s a lot quicker and has a number of superior features like scripting. The best part about is that you can connect it up to a Power BI model as well!

Here are the steps from which you can understand how the Best Practice Rules works in Tabular Editor:

1. Download and Install Tabular editor in your device.

2. Install the Best Practice Rules from Git Hub 

3. Inside the set up menu, type %localappdata% and then click on Enter to proceed further.

4. Find the way to the ‘Tabular Editor’ file.

5. Next, Copy the file of rules i.e. (.json) and then insert it into the Tabular Editor folder.

6. Subsequently, open the Tabular Editor and attach to your data model.

7. Choose ‘Tools’ from the File menu and select ‘Best Practice Analyzer’ right after that.

After being done with this procedure, there you have loaded all the rules to the local Tabular Editor and you can now run these rules in opposition to your model. 

How can you view the rule details?

To view extra information about a meticulous rule, take the helm to ‘Tools -> Manage BPA Rules’ in Tabular Editor. Tap on ‘Rules for the local user’ and there you have it, you can view all the details you want about as many rules as you want.

How can you fix the issues?

A few of the problems that are highlighted by the Best Practice Rules are to a certain extent easy and can get fixed in just a few seconds. Others are extra convoluted and may perhaps take some time. A number of rules that are comparatively simpler have an integral mechanism in them to fix the matter. All that you have to do is right snap on an object that is within the Best Practice Analyzer and subsequently choose ‘Generate fix script’. This is going to copy a C# script which can be pasted and implemented in the superior Scripting window in Tabular Editor. 

How to disable the rules?

If a specific rule is not relevant, there are quite a lot of ways to conceal these rules or put them out of sight. To begin with, you can unpick a rule inside the ‘Manage Best Practice Rules’ windowpane. This is going to put the rule out of action as a whole. If you wish to immobilize a rule for a specific item, then you can double click on an object inside the Best Practice Analyzer and then click on ‘Ignore items’. 

What is rule severity?

To set the ‘severity’ level for each rule, there is an option. This has zero force and is of no use when the most excellent Practice Analyzer rules are running within the Tabular Editor. Nevertheless, if you install a model via the Command Line alternative and state the Best Practice Analyzer rules to scamper, it is then that the severity level comes into participation. 

Conclusion:

This set of rules were drafted from the after-effects of analyzing a number of tabular models formed by experts from across the planet for big data modeling. The best practices in the Power BI can work like magic for your data model in enhancing the performance of your data model. However, You need to be pretty careful while working with these practices to ensure the smooth and safe working of your data model.

Errin O'Connor
About the Author

Errin O'Connor

With over 25 years of experience in Information Technology and Management Consulting, Errin O’Connor has led hundreds of large-scale enterprise implementations from Business Intelligence, Power BI, Office 365, SharePoint, Exchange, IT Security, Azure and Hybrid Cloud efforts for over 165 Fortune 500 companies.

Let's Get to Work Together!

Talk to our Microsoft Gold Certified Consultants

Contact EPC Group

Call for help:

(888) 381-9725

Email Us:

[email protected]

Head Office:

4900 Woodway Drive - Suite 830 Houston, Texas 77056