cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.
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.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 63 members 1,241 guests
Please welcome our newest community members: