More than pretty dashboards, all the user needs is quick reports.
Your users are sure to be delighted with aesthetically beautiful and well-crafted Business Intelligence reports, but that’s not enough. The speed at which data can be accessed will make them much happier. For that to happen, it all depends on what’s behind the data visualizations.
Lightness and speed should be the priority when building a report. So that the user does not come across a negative experience, it is important to organize the tables and relationships so that they do not become complex and redundant. Therefore, performance optimization is essential and ensures that data is executed more efficiently, consequently bringing greater agility in accessing reports and increasing customer satisfaction.
For those who are just starting out in the business intelligence world, it doesn’t hurt to stress that Power BI is a Microsoft data analysis tool designed to collect all the data and information of a business, organize it, process it and make calculations to create reports, generate indicators and assist in making strategic decisions. This data is presented through visual elements in super intuitive dashboards.
Several factors can influence the performance of Power BI. For example, when working with large data models, slow underlying data sources, or complex reports, we sometimes get stuck struggling with performance issues. This can be very bad for your project.
This article aims to point out good performance optimization practices in Power BI and help you who don’t know where to start optimizing your reports, in addition to highlighting which points are important at this time.
You can optimize your solution across different architectural layers. Layers include:
- The data sources;
- The data model;
- Visualizations, including dashboards, Power BI reports, and Power BI paginated reports;
- The environment, including capabilities, data gateways, and the network.
- Import only necessary columns, tables and data:
You can be practical and organized. Instead of loading all the information (which doesn’t even need to be there), make your data model as clean as possible, with only the columns and tables that will actually be used for analysis. The suggestion is to start small and understand the needs of the project. So you can expand with greater control over what is actually being created and executed.
It is also very likely that at the time of development you will not need the data in its entirety. Most of the time, just a sample of data will be enough for the speed you need and still guarantee a faster update from the dataset.
Tip: the base only really needs to be complete in the report publiched on the online service. - Make use of filters:
There are two filters that can help you in Power BI, they are entity and time. A specific set of dataset is loaded in the filtering by entity, from the selection of values in each column. In filtering by time, the loading of a specific set of a dataset is done by choosing certain periods of time. - Choose the appropriate dataset type for your solution:
The data model supports the entire visualization experience. Data models are hosted either externally or internally, and in Power BI they are called datasets. The three dataset modes are: Import, DirectQuery, and Composite. It is important to understand your options and choose the appropriate type of dataset for your solution. - Use variables:
A good practice is to use variables to write less complex and more efficient calculations and also to review DAX expressions, since some calculations can be very complex and end up requiring more processing. By using variables you save processing and query time, managing to optimize the performance of your data model. - Cardinality reduction:
Power Bi has a very good advantage over cardinalities, it is possible to use multiple tables and define the relationship between them. Cardinality refers to how the relationship between tables is mapped. It is recommended to have few bi-directional relationships as they can have a negative impact on performance. - Network latency:
Network latency can affect report performance by increasing the time it takes for requests to reach the Power BI service and for responses to be delivered. Tenants in Power BI are assigned to a specific region.
When users in a tenant access the Power BI service, their requests are always routed to that region. When requests reach the Power BI service, the service may send additional requests, for example, to the underlying data source or data gateway, which are also subject to network latency.
Tools like the Azure Speed Test provide an indication of network latency between the client and the Azure region. In general, to minimize the impact of network latency, strive to keep data sources, gateways, and the Power BI cluster as close together as possible. They should preferably reside in the same region.
If network latency is an issue, try to locate the gateways and data sources closest to your Power BI cluster by placing them in cloud-hosted virtual machines.
Conclusion
You can improve the performance of your reports with performance optimization in Power BI from the development of the data models. This ensures avoidance of negative impacts and fewer worries in the future.
If you are interested in optimizing your BI projects and want to have expert help on the subject. Know that we have a team of highly qualified professionals in the area, ready to assist you in whatever you need. Talk to us and schedule an online meeting.
