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

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.

View solution in original post

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

View solution in original post

2 REPLIES 2
Highlighted
Resident Rockstar
Resident Rockstar

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.

View solution in original post

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

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021