Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ZikoPowerBI
Helper II
Helper II

Power BI Challenges with huge data volume

Hi Team , 

 

I have a general query about performance and capacity of Power BI.

 

I have a scenario where I have to fetch 200 Million records per date . The data source that I am using is Memsql which is on premises database . Premium capacity is P2 . Can someone please help what is the appropriate capacity configuration that we should have with respect to data source as well as Power BI Premium capacity.

 

Tried Direct query , but it is too slow. With Import mode it took almost 7 hours to fetch the whole data. Anyway , we will be implementing IR , but the risk here is that the first load atleast should happen successfully on service wih full volume.

 

Followed all the below steps to fetch the data:

  • Remove unused tables or columns, where possible. 
  • Avoid distinct counts on fields with high cardinality – that is, millions of distinct values.  
  • Take steps to avoid fields with unnecessary precision and high cardinality.
  • Use integers instead of strings, where possible.
  • Be wary of DAX functions, which need to test every row in a table – for example, RANKX – in the worst case, these functions can exponentially increase run-time and memory requirements given linear increases in table size.
  • When connecting to data sources via DirectQuery, consider indexing columns that are commonly filtered or sliced again. Indexing greatly improves report responsiveness.  

 

1 ACCEPTED SOLUTION
AnalyticPulse
Impactful Individual
Impactful Individual

@ZikoPowerBI 
with that king of large data you are expected to face performance issue, to improve performance you can consider below;
check the table relationships, indexing, and data types.

If your data is time-series data like  daily records, consider partitioning your data in your data source. Partitioning can really improve query performance, especially when retrieving data for specific time periods.

try to load your data incrementally by applying incremental refresh.

if your report is slow then try to use the performance analyzer option to know which calculation or dax is taking too much time and try to optimise it.

My blog:
https://analyticpulse.blogspot.com/2024/03/superstore-sales-2022-vs-2023-year-on.html
https://analyticpulse.blogspot.com/
https://analyticpulse.blogspot.com/2024/04/case-study-powerbi-dashboard-developer.html
https://analyticpulse.blogspot.com/2024/05/what-if-sun-disappeared.html

See my Pins :
https://pin.it/5aoqgZUft
https://in.pinterest.com/AnalyticPulse/

 

View solution in original post

2 REPLIES 2
ZikoPowerBI
Helper II
Helper II

Thanks for the insights!

 

I am majorly focusing on what is the appropriate capacity configuration that we should have with respect to data above 200 - 300 millions per date in Power BI Premium capacity with all our data in import mode. If someone can help us with their historical experience in PBI

AnalyticPulse
Impactful Individual
Impactful Individual

@ZikoPowerBI 
with that king of large data you are expected to face performance issue, to improve performance you can consider below;
check the table relationships, indexing, and data types.

If your data is time-series data like  daily records, consider partitioning your data in your data source. Partitioning can really improve query performance, especially when retrieving data for specific time periods.

try to load your data incrementally by applying incremental refresh.

if your report is slow then try to use the performance analyzer option to know which calculation or dax is taking too much time and try to optimise it.

My blog:
https://analyticpulse.blogspot.com/2024/03/superstore-sales-2022-vs-2023-year-on.html
https://analyticpulse.blogspot.com/
https://analyticpulse.blogspot.com/2024/04/case-study-powerbi-dashboard-developer.html
https://analyticpulse.blogspot.com/2024/05/what-if-sun-disappeared.html

See my Pins :
https://pin.it/5aoqgZUft
https://in.pinterest.com/AnalyticPulse/

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.