cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jitendrabi07 Regular Visitor
Regular Visitor

how to handle large data size

Hi,

I have a data size of 50 million records (only required columns considered) approx in sql server. what should be the best way (import or direct query) to build the report on this data size without any performance issue or slowness? Is adding a parameter good idea when connecting thorugh direct query?

Thanks,,,

 

@stretcharm 

@AnujMishra_09 

@v-xicai 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
stretcharm Senior Member
Senior Member

Re: how to handle large data size

I tend to avoid direct query if performance is important as unless you are filtering down to small data sets on well indexed sources it can be slow.

 

Take a look at aggregations as here you can get the best of both worlds by importing aggregations and direct query for individual records

 

https://docs.microsoft.com/en-us/power-bi/desktop-aggregations
https://www.sqlbi.com/tv/aggregations-in-power-bi/

https://powerbi.microsoft.com/en-us/blog/aggregations-for-petabyte-scale-bi-available-in-the-power-b...

https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table

View solution in original post

3 REPLIES 3
stretcharm Senior Member
Senior Member

Re: how to handle large data size

I tend to avoid direct query if performance is important as unless you are filtering down to small data sets on well indexed sources it can be slow.

 

Take a look at aggregations as here you can get the best of both worlds by importing aggregations and direct query for individual records

 

https://docs.microsoft.com/en-us/power-bi/desktop-aggregations
https://www.sqlbi.com/tv/aggregations-in-power-bi/

https://powerbi.microsoft.com/en-us/blog/aggregations-for-petabyte-scale-bi-available-in-the-power-b...

https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table

View solution in original post

Highlighted
kcantor Super Contributor
Super Contributor

Re: how to handle large data size

@jitendrabi07 

It really depends on what type of data you have. PowerQuery is really good and compressing data. In addition if you research the query folding data, you will be able to determine the best place for your query to fold to maintain performance. Here are a couple of good resourses for you.

https://powerpivotpro.com/2015/12/compression-with-power-pivot/

https://blog.crossjoin.co.uk/2015/12/08/working-with-compression-in-power-query-and-power-bi-desktop...

https://powerpivotpro.com/2010/02/surprising-example-of-powerpivot-compression/

https://www.mssqltips.com/sqlservertip/3635/query-folding-in-power-query-to-improve-performance/

https://blog.pragmaticworks.com/power-bi-checking-query-folding-with-view-native-query



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

Proud to be a Datanaut!




Super User
Super User

Re: how to handle large data size

Hi there. I have a model with about 80 000 000 rows in the fact table and would never even consider the DirectQuery mode if I can use Import. The Import mode is THE BEST OUT THERE you can have. You cannot beat it using the DirectQuery or Dual mode because PQ compresses data with a factor of between 10 and 100x and the data is all stored in memory. YOU JUST CANNOT BEAT IT...

It's as easy as that.

Best
Darek

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 63 members 1,228 guests
Please welcome our newest community members: