Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors