Organizations may move into the future with the help of cutting-edge business intelligence solutions thanks to Power BI. Now that data is available from a variety of sources.
Here are some recommended practices to follow while using Power BI to analyze data, build new reports, or optimize existing ones in order to greatly enhance your analysis and extract more value from your data:
Relations and role-modeling
- Make sure the relationships that the auto-detection identified are accurate.
- Remove unused relationships and only use bi-directional relationships as needed to enhance performance.
- Check to make sure inactive relationships are configured correctly for model verification.
- There can only be one active relationship between two tables that have several relationships (direct or indirect), making the others inactive. The DAX function USERELATIONSHIP might be used for selecting a particular relationship from among those that are offered.
- Consider deleting any unnecessary columns to free up space because performance is affected by several factors, including available space.
Microsoft Power BI is renowned for its proficiency in the implementation and optimization of Power BI.
Please read: PERSONALIZATION IN FINANCIAL SERVICES
Performance
Dashboard and report visualizations should be kept to a minimum
Report performance is slowed when there are too many visuals on one report. Limit grids to one per page and the number of widget graphics per report page to eight. 10 tiles maximum per dashboard. Assuming that each type of image is worth a different number of points, generally restrict pages to 30 points:
- Cards: 1
- Gauges: 2
- Charts: 3
- Maps: 3
- Grids: 5
Eliminate any pointless interactions between the images.
Every visual on a report page can automatically communicate with one another. Report performance is enhanced by reducing the number of back-end queries fired by removing pointless interactions.
Personal Gateway should be replaced with an on-premises data gateway.
On-premises data gateway (also known as Enterprise Gateway), which is more effective when working with huge datasets, imports nothing while Personal Gateway imports data into Power BI.
Use different gateways for planned data refresh and live Power BI service connections.
The performance of the live connection will deteriorate during the scheduled refresh if you use the same gateway for both.
Perform a test run before using a custom graphic.
When working with enormous datasets or intricate aggregations, custom visualizations can perform poorly. The Power BI team typically does not test uncertified custom graphics. If a custom visual performs poorly, you might want to think about switching to another visual.
Keeping data models’ complicated metrics and aggregations to a minimum
Instead of calculated columns, create calculated measures. Push calculated columns and measures as close as you can to the source. They are likely to perform more quickly the closer they are to the source.
When possible, choose the Star schema rather than the Snowflake schema.
Changes are challenging to implement because of the complicated query structure of the Snowflake model. Star schema tends to eliminate data redundancy, utilizes fewer joins, and is simple to read.
Sparingly use slicers
Slicers are an excellent tool for letting people explore data, but they have a performance penalty. Two queries are generated by each slicer: one retrieves the data, the other the selection information.
Performance is greatly slowed down by adding too many slicers. Use the Filter pane to determine which slicers are utilized the least in order to delete any extraneous ones.
Make sure that reports and data sources are located in the same area.
You can reduce the effects of network delay if the tenant and the data source are located in the same region. Data transport and query execution are sped up by sharing a region.
Instead of importing whole data sets, import only the necessary columns and tables.
Make sure the model is as sleek and narrow as you can. Because Power BI uses columnar indexes, tables that are longer and leaner perform better. Partition huge tables so that they may be processed in parallel when you need to import them.
Design
Make that the refresh rate of the data source matches the caching frequency.
The Power BI cache update frequency is initially set to once per hour. The interval between cache updates should be similar to the interval between data source refreshes.
You should adjust the cache frequency if, for instance, your data set only updates once every day. For end users, this enhances the efficiency and accuracy of reports.
Utilize a white or light background.
White or light backgrounds print well, making it simple to present your reports both online and offline.
Cut numbers down
Display numbers with no more than four numerals at a time. Limit measures to two numbers to the right of the decimal point to maintain consistency across decimal points. Scale up to hundreds or millions as necessary.
To give more details about measurements and visuals, use tooltips.
The use of report tooltips is an excellent method to cram more information into a small report. Limit the number of images you include in report tooltips to prevent information overload.
Use names that the users will understand.
You can give report object aliases using Power BI. By selecting names that are appropriate for business, you can label columns and metrics without confusing end users. Consider hiding any unnecessary columns to prevent user confusion.
Security
Switch on Row-Level Security (RLS)
Depending on the traits (role) of the user running a query, row-level security limits user access to specific rows in a database. Power BI only imports data that the user is permitted to view with RLS.
Utilize approved custom visuals
Custom graphics on AppSource that have undergone stringent quality testing are known as Power BI-certified visuals. Microsoft confirms the existence of reliable, high-performance code in authorized custom visualizations. Only certified bespoke graphics can be viewed in the Export to PowerPoint mode and via email subscribers.
Sort report information according to its effects on business.
To categorize data as having a high, medium, or low business impact, use Power BI sensitivity labels. Users must request a policy exception in order to disclose High Business Impact (HBI) data externally.
For data with Low Business Impact (LBI) and Medium Business Impact (MBI), exceptions are not necessary. Users’ understanding of security and appropriate report-sharing practices are increased by employing Power BI data sensitivity labels.
To sum up
The recommended practices listed above are something we can incorporate into our arsenal for Power BI reporting and analysis.
Wishing you the best of luck with your productive Power BI improvements with these best practices at your side!
