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
sem1
Frequent Visitor

Balancing Memory and Database Usage on Load

Hello,

 

I'm working in an organization where we do not have a reporting database or data warehouse (this is planned for the next year) and we still provide reporting on an extract basis from our operational systems. This is obviously not a best practice as it can impact operational performance from time to time.

 

In setting up a data model we noticed there was strain on the operational system and I'm imagining this is because of Power Query using T-SQL/Native Queries to perform as many transformations as possible on the source database.

 

We would like to design our data models so that they perform simple tasks on the original database but switch over to in-memory processing for the things we think are taking the majority of the load.

 

Question 1: How can I force a step in Power Query to stop using native querying? (Therefore putting the rest of the load in memory, relieving the resource usage from the operational server)

 

Question 2: Am I correct in stating the following? When developing the model, this in-memory load will be on my PC. When refreshing via the data gateway, the in-memory load will be on the server in which the gateway is installed. (This server is different from the operational database so it will be ok to use its resources).

2 ACCEPTED SOLUTIONS
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @sem1 ,

For your question 1:

I'm afraid that there is not option for us to stop the native query in power query. Actually, we only could view the native query in the applied steps in query editor.

For your question 2:

Yes, when developing the model, this in-memory load will be on your PC. And when refreshing via the data gateway, the in-memory load will be the server which install the gateway to read the source, but the in-memory also load in cloud in could when deal with the data source in Azure service bus.

You could have a good look at the document to understand it better.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
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


@v-piga-msft wrote:

Hi @sem1 ,

For your question 1:

I'm afraid that there is not option for us to stop the native query in power query. Actually, we only could view the native query in the applied steps in query editor.

 


Can't you use a transformation not supported by native queries and that will force the remainder of the transformations to occur via power query in-memory?

 

Edit; Tested this and yes I can. How I'm doing it is left joining tables from the data source to a table I input with one non-matching key then deleting the column after expanding it. (This leaves the data source the same as it was before the join). Because cross-data source merges can't be done in Native Query, Power BI then loads the data into memory and processes it for all further steps.

View solution in original post

2 REPLIES 2
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @sem1 ,

For your question 1:

I'm afraid that there is not option for us to stop the native query in power query. Actually, we only could view the native query in the applied steps in query editor.

For your question 2:

Yes, when developing the model, this in-memory load will be on your PC. And when refreshing via the data gateway, the in-memory load will be the server which install the gateway to read the source, but the in-memory also load in cloud in could when deal with the data source in Azure service bus.

You could have a good look at the document to understand it better.

Best  Regards,

Cherry

 

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


@v-piga-msft wrote:

Hi @sem1 ,

For your question 1:

I'm afraid that there is not option for us to stop the native query in power query. Actually, we only could view the native query in the applied steps in query editor.

 


Can't you use a transformation not supported by native queries and that will force the remainder of the transformations to occur via power query in-memory?

 

Edit; Tested this and yes I can. How I'm doing it is left joining tables from the data source to a table I input with one non-matching key then deleting the column after expanding it. (This leaves the data source the same as it was before the join). Because cross-data source merges can't be done in Native Query, Power BI then loads the data into memory and processes it for all further steps.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors