cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
wittr9876 Frequent Visitor
Frequent Visitor

Performance/Resource-Usage "best practice" question on how PowerBi/GetData load data

Greetings.

 

My question is ultimately concerned with resource usage & data-load performance.

 

Here's an example scenario. Assume that I have a set of data table containing very much data. Using Get Data & the query editor, I load these tables, then proceed to perform various data-shaping steps on them - renaming columns, filtering the data based on values in certain columns, creating conditional custom columns that analyze & compare values from multiple of the base columns to determine what value to display, etc., so that if you look in the Advanced Editor you see many steps being performed. How does PowerBI actually process this? Does it load ALL of the data from the large table(s), then perform all of these steps on that data (when ultimately I'll never be viewing all of the data at a given time, only a small, filtered subset)? Or is it somehow optimized to only load a certain amount/subset/page of the data & process that, loading additional data as needed to display it?

 

Where I am going with this question, is I am wondering whether, performance-wise, it is better to (1) create a SQL query to perform all of this data-shaping in the Azure SQL data engine, & then only load the final result into PowerBI, OR (2) whether PowerBI's data engines are optimized/efficient enough that it's better to just let PowerBI do all of the work? And (3) maybe there's a specific size of data at which option (1) or (2) is the best choice? Is my concern for data quantity/memory usage & data load performance unnecessary, i.e., are Azure SQL & PowerBI so optimized that these concerns are non-issues?

 

Thanks,

Randy

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Performance/Resource-Usage "best practice" question on how PowerBi/GetData load data

@wittr9876,


When using import mode to connect to Azure SQL database in Power BI Desktop, all data of large tables will be loaded into Power BI Desktop, large amount of transform steps in query editor are processed based on all the data of large tables, which will eat memory of your machine and cause performance issues at the time you apply changes to data model.

In your scenario, it is recommended to import required columns and rows with a SQL query and then make transformations in Power BI Desktop, for more tips about Power BI performance, please review this article: https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Moderator v-yuezhe-msft
Moderator

Re: Performance/Resource-Usage "best practice" question on how PowerBi/GetData load data

@wittr9876,


When using import mode to connect to Azure SQL database in Power BI Desktop, all data of large tables will be loaded into Power BI Desktop, large amount of transform steps in query editor are processed based on all the data of large tables, which will eat memory of your machine and cause performance issues at the time you apply changes to data model.

In your scenario, it is recommended to import required columns and rows with a SQL query and then make transformations in Power BI Desktop, for more tips about Power BI performance, please review this article: https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors