Best practice guide for Power BI users – Part 1

I have seen so many people have different ways of using Power BI as an analytic and reporting tool. They all have their own way of designing dashboards/reports, naming objects, create calculations, etc. While I’m not going to discuss about what’s wrong/correct in those approaches, I feel that I can use my experience to give a comprehensive guideline to beginner users about best practices about using Power BI. This blogpost is a  part of a series and within the series I will try to cover what are the best practices in implementing a Power BI report.

Installing Power BI Desktop

First thing first, you need to install Power BI desktop to start developing a report. How hard that can be?  Yes, it’s easy. However, Power BI desktop can be installed using two different ways.

1) Install Power BI App using Microsoft Store

This is the easiest way to install Power BI.  Go to the Microsoft App Store and search for Power BI and you will be able to find the Power BI App.

image

2) Download Power BI desktop .EXE file and Install

Microsoft allow us to download Power BI desktop as an EXE file. What you need to do is go to below link and there you can select 32 bits, or 64 bits version based on your preference.

   https://www.microsoft.com/en-us/download/details.aspx?id=58494

image

Why there are two ways? What is the best way? Let’s compare two approaches and see which one is best.

App Store

Download and Install

Can set to update the app automatically.

Must download and install manually when there is an update.

Always have the latest version and cannot select previous versions when installing.

As far as you have previous downloaded installation files with you, can always go back to a previous version of Power BI if something goes wrong.

Small and therefore can be installed quickly

Take more time to download compare to the App

Admin privilege is not required to install.

You need admin privilege to install an EXE.

With that comparison, I recommend you install using Microsoft App Store and set the automatic update option ON so that you will not miss all the cool monthly updates.

For more detail about installation, go to this link  : https://docs.microsoft.com/en-us/power-bi/desktop-get-the-desktop

Connecting to Data Sources

Power BI support 3 main ways of connecting to data sources.

  1. Direct/ Live Query
  2. Import
  3. Composite Model

Selecting appropriate connection type at the beginning is very critical. Refer following table when deciding connection type to your data source.

Connection Type

When to use

When not to use

Direct Query/Live Query

· The business user wants to see real time data as in the source system.

· Source system is too big to import to Power BI. Maximum model size is 1 GB in Power BI Pro.

· Import mode is an  option.

· Directly connecting to backend systems of applications which are already slow.

· Each page of reports has lots of visuals in it.

· Report loading time is very critical to the business user.

Import Mode

· Dataset is within 1GB limit (considering future data load) and real time/ near real time is not required

· End user expect reports to be loaded very quickly.

· Real time/near real time reporting is required.

· Source data volume is bigger than 1 GB.

· Data refresh take more than accepted time duration.

Composite Model

· Want to import dataset for better performance but have large tables in model which cannot be imported due to model size limitation.

Loading Data

When importing data, it is recommended to use Power Query Editor for pre data processing and data cleansing operations. Operations such as Removing unwanted fields, adding calculated columns, setting data types and formats, replacing null/empty with default values must be done inside Power Query Editor. Power BI converts all steps done inside Power Query Editor into a M Query and executes at data loading time. Therefore, although data loading consumes little time, it provides cleaned and well organized dataset for you.

Must perform operations in Power Query Editor:

· Remove all unwanted columns from source table (Flags, GUIDs, Time Stamps, etc.). If you are not sure about whether it’s required to business user or not, try to clarify from user and if that is not possible remove when loading.

· Rename field names into readable format.

· Rename table names into business-friendly readable format.

· Set appropriate data types for fields.

· Create calculated columns here as much as possible rather than using DAX to calculate columns inside model.

· Replace Null, Empty values with meaningful default values. (Example: N/A, Not Applicable)

. Create “Groups” to organize queries in order to  increase clarity of the Query panel.

. Disable loading of all intermediate tables and queries.

That’s it for the day. In next post I will discuss about best practice in naming convention in Power BI. Thanks for reading and cheers!

2 thoughts on “Best practice guide for Power BI users – Part 1

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