BI Consultant at Key2 Consulting
In the business intelligence world, Power BI has become a favorite among many. During my time designing and building various Power BI reports, I’ve both overcome obstacles and made several interesting discoveries. Here’s what I’ve found to be the 5 best practices for Power BI.
1. Pull data from views, not tables
Importing data from tables in a SQL Server or Oracle database creates strong dependencies between the physical data model and the reporting engine. Whenever table structures change, it’s best to pull relational data from views.
2. Filter before import
If you’re importing data into Power BI instead of a live connection, it’s best to limit the amount of processes that happen inside the tool. Power BI has a limit for the amount of data that’s allowed to be imported, so any steps to avoid reaching that limit will be a plus.
3. Narrow tables are faster than short and wide tables
I discovered this the hard way. If the performance is slowing down as you’re adding data, it’s mostly due to wide tables. Power BI reacts much faster using narrow and long tables versus short and wide ones.
4. Remove unused fields
As you’re adding more and more data, you will notice the pbix file size increasing. One of the best and quickest ways to reduce the pbix file is to remove any unused fields.
How: Click Edit Query > then select the table you want to remove the fields from > Click Choose Columns.
5. Label all of your steps
One of the great features in Power BI is that nothing is ever final. As you’re going through and modifying the imported data, Power BI creates a history (steps) that allows you to seamlessly go back and remove any changes that might break the datasets. Furthermore, labeling each of these steps allows you to easily remember what each does.