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

[wittr9876]

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

[v-yuezhe-msft · Accepted solution]

@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