PBI Memory Consumption and Refresh Problem.

[shah007]

The problem is :-

We are presenting PBI dashboards on many screens for our visitors and auditors, so we cannot stop it. Now the problem is it takes a lot of time to refresh and sometines the data crashes. The data is in sql form, it is a live data so it is continuously updated. Due to which our pbix file size has become greater than 400mb and is still increasing. Is there any by which we can reduce its memory consumtion to 100mb or even lesser, but we cant compromise with any data or visualizations i.e. is we can't delete any sthing. Can we reduce the data size, optimize it and reduce its refresh time.

[anmolmalviya05 · Accepted solution]

Hi @shah007 ,

When you use DirectQuery mode, Power BI doesn't store the actual data in the PBIX file. Instead, it queries the data directly from the SQL database each time you interact with a visual. Here’s how it helps:

1. Reduced File Size: Since the data isn’t being imported into the PBIX file, the file size can be significantly reduced. The PBIX file will primarily contain the metadata, visualizations, and query definitions, which are typically much smaller in size.

2. Real-Time Data: DirectQuery ensures that you are always working with the most current data since each interaction fetches the latest data from your SQL database.

Article link: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Anonymous · Accepted solution]

Hi @shah007 

You can reduce refresh time by using incremental refresh. With incremental refresh, the service dynamically partitions and separates data that requires frequent refreshes from data that can be refreshed less frequently, which can significantly reduce the amount of data that must be refreshed.

For more details, you can read related document link: 

Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn

Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft...

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[anmolmalviya05]

Hi @shah007 , Hope you are doing good!

Consider using DirectQuery instead of Import mode for some or all tables. This way, Power BI will query the data directly from the SQL database at runtime rather than storing it in the PBIX file. 

Else you can also look for Direct Lake mode in Microsoft Fabric Onelake, this will give you benefit of both import mode and direct query mode.

You can learn more about it in this article: https://learn.microsoft.com/en-us/fabric/get-started/direct-lake-overview

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[shah007]

Hi Anmol

Can you explain how will this solve my problem like the file size has crossed 400mb. How will it be reduced to 100 or lesser mb by doing this?

[anmolmalviya05 · Accepted solution]

Hi @shah007 ,

When you use DirectQuery mode, Power BI doesn't store the actual data in the PBIX file. Instead, it queries the data directly from the SQL database each time you interact with a visual. Here’s how it helps:

1. Reduced File Size: Since the data isn’t being imported into the PBIX file, the file size can be significantly reduced. The PBIX file will primarily contain the metadata, visualizations, and query definitions, which are typically much smaller in size.

2. Real-Time Data: DirectQuery ensures that you are always working with the most current data since each interaction fetches the latest data from your SQL database.

Article link: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[anmolmalviya05]

Hi @shah007 ,

When you use DirectQuery mode, Power BI doesn't store the actual data in the PBIX file. Instead, it queries the data directly from the SQL database each time you interact with a visual. Here’s how it helps:

1. Reduced File Size: Since the data isn’t being imported into the PBIX file, the file size can be significantly reduced. The PBIX file will primarily contain the metadata, visualizations, and query definitions, which are typically much smaller in size.

2. Real-Time Data: DirectQuery ensures that you are always working with the most current data since each interaction fetches the latest data from your SQL database.

Article link: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!