Microsoft Power BI is a potent and intuitive tool that you can use to build visualizations, reports, and dashboards. You can even create complex calculations, transform data according to your liking, and model it directly in Power BI; instead of doing all this computation-heavy activity in the source environment or purchasing and learning to use other tools.
However, one area that bothers many developers and organizations is that the size of the Power BI PBIX file can get dangerously large when you’re dealing with large amounts of data. PBIX tends to create havoc for many developers and organizations.
It may reach hundreds of MBs in size, leading to tons of problems. We discuss these problems related to PBIX in the next section. This article offers some tips that you can leverage to reduce the size of your PBIX file. Reducing or compressing your PBIX file size also leads to several other benefits of Power BI.
Dealing with Large PBIX File Sizes
When you are dealing with a large amount of data, the size of your PBIX file can get too large since all the data and calculations are stored directly in a single file. This problem in PBIX happens especially while accessing multiple heterogeneous data sources – and utilizing Microsoft Power BI’s Import Mode.
Of course, you could consider transitioning to the Direct Query mode so that no data resides in the PBIX file, but that is not possible for all use cases due to bandwidth and performance considerations. Most organizations and developers tend to use the Import mode and have everything in a single place instead of PBIX.
This PBIX problem can have multiple detrimental effects, including difficulties in sharing files across the organization, longer publishing times, and general performance detriments. If it’s a small file, you can simply upload or share it with others in an email within seconds. However, if your file size is hundreds of MBs, this becomes a much more difficult task, both for the person sharing the file and those consuming it. Since there’s a vast amount of data in your file, there’s also the likelihood that your report’s performance will suffer while handling the data.
It is essential to reduce the size of your Microsoft Power BI PBIX file in Data Analytics to work around all the aforementioned problems, even when you’re working in Import mode with PBIX. The next section will cover how to reduce PBIX file size. Let’s learn how to compress power bi file
How to Work Around Large File Sizes
As with most problems, there is no one-shoe-fits-all solution when it comes to PBIX. If you want to know how to reduce Power BI file size, you have come to the appropriate place. The following will explain how to reduce Power BI file size. However, depending upon your use case and your data, the following processes may drastically bring down the size of your Power BI PBIX file:
The biggest culprit in PBIX is often that you have imported a lot of columns that you are not actually using. For example, a table in PBIX may have 200+ columns, but you’re probably only using 20 or so in your visualizations and joins. It’s better to remove all the unneeded columns in PBIX file. Head over to Power Query and then select the “Keep Columns” option to keep only the columns you need. The most significant benefit of this approach is that removing unneeded columns in PBIX file doesn’t remove the associated data permanently. It gets compressed in an optimized manner. If you later realize that you accidentally removed a column in PBIX file; you can simply go to the “Keep Columns” step in Power Query and restore the withdrawn column within a few seconds. There is no disadvantage to cleaning up your data; everything can be rolled back quickly if needed.
Other Solutions To Compress Power BI File
- Apart from removing the columns in PBIX files you don’t need, it’s also essential to remove the rows you don’t need. If you’re only visualizing data from the past 3 months, it makes little sense to store historical data from the past five years. There is no need to store any “deprecated” data either. You can remove this data by navigating to Power Query filtering based on your date and/or other columns. Keep only the data that you need. Once again, you can roll back any removals through Power Query.
- It is also ideal to compress Power BI file for managing the grain of your data. If your visualizations show data daily, you don’t need to store date data as a date/time column; simply reduce the granularity. This will decrease the amount of data you are saving. This will reduce Power BI file size and save you data. While this may sound like a minor change, it can make a notable difference if you have millions of rows. See how you can change the data type below:
- Another good idea is to reduce reliance on Calculated Columns. Only use them when necessary since you cannot store them in a compressed format. It is preferable to use Measures where possible as they are calculated at runtime and do not use extra space.
- Disable Auto Date/Time loading in the Data Loading options in Power BI settings. This ensures that such tables are not automatically created on loads to compress Power BI file.
- While it may not make a massive difference, try removing unused and unneeded visuals and pages from your Power BI report.
Benefits of Reducing Power BI File Size
Reduced Power BI file size is beneficial in many ways. It leads to
- Improved Performance: Smaller file sizes result in faster loading times and smoother performance.
- Optimized Resource Consumption: Smaller files consume fewer system resources, preventing slowdowns or crashes.
- Enhanced Accessibility: Smaller files make it easier to share reports and improve accessibility for users with slower internet connections.
- Reduced Storage Costs: Minimizing file sizes can save on storage costs, particularly with cloud-based storage solutions.
Conclusion
As discussed before, Power BI is a powerful tool but only when you realize its full potential. Non-destructive compression of unneeded data is a significant capability that can lead to massive gains in performance while drastically reducing your file size by several orders of magnitude.
Depending upon your use case and your data, you can reduce the size of your PBIX file by up to 95%, which, in turn, offers several downstream, such as ease of file sharing, publishing, and performance gains.
This entire activity requires a solid understanding of the model and the underlying data. Xavor has proven experience in Power BI and its related domains, so if you are facing problems related to your data modeling and performance issues in general, reach out to us for consultation at [email protected].
FAQs
Power BI files can become large due to importing unnecessary columns and rows, storing historical data, and using calculated columns excessively.
To reduce file size, remove unnecessary columns and rows, manage data granularity, minimize calculated columns, and disable auto date/time loading in Power BI settings.
Reduced file size leads to improved performance, optimized resource consumption, enhanced accessibility, and reduced storage costs.
Yes, changes made in Power Query, such as removing columns or rows, can be easily rolled back if needed.
Depending on your data and usage, you can compress your Power BI file size by up to 95%, leading to significant downstream benefits in ease of sharing, publishing, and performance.