cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sem1 Frequent Visitor
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

Accepted Solutions
Community Support Team
Community Support Team

Re: Balancing Memory and Database Usage on Load

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

Re: Balancing Memory and Database Usage on Load


@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.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Balancing Memory and Database Usage on Load

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

Re: Balancing Memory and Database Usage on Load


@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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 5 members 4,431 guests
Please welcome our newest community members: