Welcome to the fourth chapter of The Complete 2023 Beginner’s Guide to Power BI. If you’re just starting out with Power BI, we recommend reading the first chapter “ The Complete 2023 Beginner’s Guide to Power BI”, which provides a basic overview of Power BI, including how to connect to data sources, create simple reports, and visualize data. For those looking to master the basics of the DAX language used in Power BI, we recommend checking out our second chapter, “ The Next Power BI Guide with DAX formulas and date tables”.
In this chapter, our focus will be on providing data optimization tips that will greatly enhance the speed and efficiency of your Power BI experience. By implementing these tips, you'll be able to load your dashboards quickly and efficiently, enabling you to gain valuable insights from your data. Let's dive in and explore these tips to supercharge your Power BI performance!
Have you ever wondered how much data you can load into Power BI? With a Power BI Pro license, the size of your data doesn't matter when you're working with Power BI Desktop. However, challenges arise when you need to upload your file to Power BI Service. Unfortunately, Power BI Service has a strict limit, allowing you to upload Power BI files under 1 GB in size. If you're interested in learning more about this limitation, be sure to check out this blog post on how much data you can load into Power BI.
In addition to the upload issue, large Power BI files can cause other problems.
- They take a longer time to load
- Data refreshes can be a time-consuming task. It's not uncommon for data refreshes to take hours or even an entire day to complete.
If you're facing these challenges, don't worry. We're here to help. In this blog post, we'll provide you with clear and concise steps to significantly reduce the size of your Power BI file, ensuring faster loading times and more efficient data refreshes.
Before we dive into the demonstration, let me show you this special Power BI tool called VertiPaq Analyzer in DAX Studio. It lets you peek behind the scenes of your dataset and get some valuable insights. You'll find out how much space tables and columns are taking up, what data types they have and so on.
But first things first, we need to download VertiPaq Analyzer and DAX Studio.
Once you've got these tools installed, we'll be all set to start analyzing the dataset together.
To begin with, I have a dashboard that includes a table called 'crime.' This table has been directly imported from the chicago_crime dataset available publicly on BigQuery public dataset. Currently the Power BI file size is 465,968 KB
Once you have your dashboard set and saved.
Next, Open DAX Studio.
- In the prompt, select "Power BI/SSDT Model" and click on "Connect." This will establish a connection to your Power BI or SSDT (SQL Server Data Tools) model.
- Once connected, navigate to the menu bar and select "Advanced."
- From the drop-down menu, choose "View Metrics."
- By following these steps, you will now be able to see the size, data type, and cardinality of both columns and tables. This valuable information will provide insights into the composition and characteristics of your dataset.
Now by following these steps, you'll be able to optimize your Power BI file.
- Turn Off Auto Time Intelligence
Power BI offers a convenient feature called "Auto Time Intelligence" that automatically detects date or time-related fields in your data model and applies default time intelligence calculations, such as year-to-date, quarter-to-date, or month-over-month comparisons. While this feature can be helpful in certain scenarios, it can also impact performance, especially when dealing with large datasets or complex calculations.
By turning off the Auto Time Intelligence feature, you gain more control over the calculations performed on your date or time-related fields, allowing you to optimize performance and tailor the calculations to suit your specific reporting needs. Here's how you can do it:
After turning off the auto time intelligence feature, my Power BI file size reduced from 465,968 KB to 461,157 KB.
- Delete unwanted tables and columns
While turning off auto time intelligence feature did reduce the size of the Power BI file, However it did not reduce the file size significantly. When it comes to optimizing your Power BI data model, one effective strategy is to identify and eliminate unnecessary tables and columns, particularly those that store long strings of data. This process not only reduces the file size but also improves query performance and the overall responsiveness of your reports.
When using VertiPaq Analyzer to analyze my Power BI file. It showed me that the "case_number" column in my dataset was taking up a lot of space and had the highest cardinality. Cardinality, in simple terms, refers to the uniqueness or distinctiveness of values within a column. A higher cardinality implies a greater diversity of values.
Since the "case_number" column was important for my analysis, I decided to remove the "unique_key" column because I didn't need it for my analysis. After saving the file, I see that deleting this column made my Power BI file smaller.
By simply removing unwanted tables and columns, my Power BI file size reduced from 461,157 KB to 458.466 KB
- Importing the Largest Table with DirectQuery Mode
Lastly, an effective way to further optimize your Power BI file size is by importing the largest table using the DirectQuery mode instead of the Import mode. Power BI offers two types of import modes: Import mode and DirectQuery mode.
Import Mode: In Import mode, the data is copied from the source to the Power BI table. This mode is not real-time, meaning that any updates or additions to the source data require manual refreshing or scheduled refreshes. While Import mode allows you to manipulate rows and columns in the Power Query Editor and utilize DAX expressions, it can result in larger file sizes and longer refresh times, especially for large datasets.
DirectQuery Mode: On the other hand, DirectQuery mode establishes a direct connection between the Power BI file and the data source. This mode provides real-time data access, ensuring that any changes in the source data are immediately reflected in your Power BI reports. However, it is important to note that DirectQuery mode limits your ability to manipulate rows and columns within the Power Query Editor. Nevertheless, DAX expressions can still be used effectively.
After importing the table using DirectQuery mode, my file size reduced from 461,157 KB to 107 KB. That is a significant improvement!
In my personal experience, I encountered slow data loading and long refresh times when I initially imported my largest dataset using Import mode. As my Power BI file exceeded the 1 GB limit, it became a significant challenge. However, after switching to DirectQuery mode, I witnessed a remarkable improvement in performance. Not only did my file size decrease by approximately 60%, but the refresh and upload processes became significantly faster.
By opting for the DirectQuery mode and establishing a direct connection with the data source, you can reduce the size of your Power BI file and experience faster refreshes and uploads. While some limitations apply, such as the inability to manipulate rows and columns in the Power Query Editor, the benefits of real-time data access and improved performance often outweigh these considerations.
Remember, choosing the appropriate import mode depends on your specific requirements. By carefully evaluating the trade-offs between Import mode and DirectQuery mode, you can determine the most suitable approach for your Power BI project and achieve optimal performance.
Conclusion
I hope you have found these tips on reducing Power BI file size helpful in optimizing the performance of your reports and dashboards. By implementing these strategies, such as streamlining your data model, turning off Auto Time Intelligence, and importing the largest table in DirectQuery mode, you can significantly improve the loading speed, data refresh time, and overall efficiency of your Power BI projects.
However, if you have discovered any other effective methods to reduce file size or enhance Power BI performance, we would love to hear from you. Please share your insights and additional tips in the comments section below, as your contributions can greatly benefit the Power BI community.
Lastly, if you aspire to create stunning Power BI dashboards that captivate and inform your audience, be sure to explore our blog - 6 design tips to create stunning Power BI dashboards. We regularly share valuable insights, tutorials, and best practices to help you unleash the full potential of Power BI and take your visualizations to the next level.
Thank you for reading, and happy Power BI optimization!
Further Links
This post is part of the Complete Guide to Power BI Data Analytics series from datadice and explains to you every month the newest features of Power BI.
Follow us on LinkedIn for insights into our daily work and important updates on BigQuery, Data Studio, and marketing analytics.
Subscribe to our YouTube channel for discussions on DWH, BigQuery, Looker Studio, and Google Tag Manager.
If you are interested in learning BigQuery from scratch, get access to our free BigQuery Course
Elevate your skills with Google Data Studio and BigQuery by enrolling in our Udemy course.
Need help setting up a modern, cost-efficient data warehouse or analytical dashboard? Email us at hello@datadice.io to schedule a call.
