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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mark_carlisle
Advocate IV
Advocate IV

Dataflows: Error: On-Prem execution not supported for entity

I'm encouting an error when using dataflows;

 

Error: On-Prem execution not supported for entity 'REDACTED/Rant&RaveGlobal' since it refers to another entity... RootActivityId = REDACTED.Param1 = REDACTED/REDACTED/Rant&RaveGlobal Request ID: 71958418-b401-ea01-b1be-98a685669190.


I know why I'm getting the error but I just want to know if I can work around it otherwise dataflows seems extremely limited.

 

I have a two dataflows currently. Lineup and Surveys.

 

  • Lineup contains two entities Lineup and GlobalSalesforceIds.
    • Lineup lists employees and what team they were on when, the unique key in this entity is EmployeeNumber-Month, e.g. 12345678-43374.
    • GlobalSalesforceIds contains the EmployeeName, GlobalSalesforceID and the EmployeeNumber the unique key in this entity is GlobalSalesforceID, some employees can have many Global Salesforce ID's so I cannot put them in the Lineup entity.
    • All the data in this dataflow is obtained from cloud sources, SharePoint and Salesforce, so no need for an on-prem gateway.
  • Surveys contains entities form three different data sources.
    • The EmployeeNumber is present in two so I can create a column that contains the EmployeeNumber-Month key so I can link back to Lineup when building reports.
    • The third entity, Rant&RaveGlobal, uses the GlobalSalesforceID of the employee and does not contain the EmployeeNumber.
    • All the data in this dataflow is obtained from on-prem sources and therefore uses the gateway.

What I therefore want to do is add the GlobalSalesforceIds entity from Lineup as a linked entity in Surveys and then merge the two so I can pull the EmployeeNumber and create a column for the EmployeeNumber-Month key.

 

I can achieve this but it means removing the gateway while linking the entity and then re-selecting it. So obviously the issue is that the linked entity cannot use the gateway but is there a way around it? Otherwise dataflows seem very limiting in that you cannot refresh data from cloud and on-prem data sources.

1 ACCEPTED SOLUTION
mark_carlisle
Advocate IV
Advocate IV

As is customary with these situations, I spend time writing a question with an example and then I discover the answer.

 

The answer in this case was to untick the Enable load option on the linked entity.

 

I've tested a on demand refresh and scheduled and both worked.

View solution in original post

10 REPLIES 10
otravers
Community Champion
Community Champion

Yes it's a hassle that you can't merge queries from on premises sources. You could try creating a separate dataflow for staging tables.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

So if I go with the solution of turning off Enable Load, I'm basically left with stale data coming from the Parameter Entity that I'm trying to merge from, correct?

And the real solution right now is to stage the Parameter Entity in a different Staging Dataflow and then merge that into my Dataflow?

otravers
Community Champion
Community Champion

It shouldn't be stale if Enable Load is off, it just won't be materialized as data in the underlying data lake (at least that's how I think it works) and it won't be visible from outside of the dataflow.

 

Having separate staging dataflows is more elegant and it might save you in some instances from loading the same table twice in case that table needs to be materialized/visible outside of the dataset.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals
BruceT
Regular Visitor

I get the same error message.

 

In query 1,  I am calling a function to return a value from a table populated by query 2.

when I remove the function the dataflow refreshes correctly,  using the function query 1 fails with the error as above.

 

any ideas for a work around?

 

mark_carlisle
Advocate IV
Advocate IV

As is customary with these situations, I spend time writing a question with an example and then I discover the answer.

 

The answer in this case was to untick the Enable load option on the linked entity.

 

I've tested a on demand refresh and scheduled and both worked.

Hi @mark_carlisle ,

 

I faced the same situation using one entity as parameter in my dataflow to another entity in the same dataflow, and received this error message.

 

Actually I need to enable loading of that "parameter entity" for further usage. But after reading your solution, I did a workaround by disabling the loading of the "parameter entity" and create a new entity simply by linking the previous "parameter entity" and then the refresh works, although practically they are the same!

 

It is strange that by enabling the loading of the linked entity the dataflow refresh does not work. Is it planned by Microsoft to be like that?

 

 

Best regards,

cyongt_bdf

Can you describe in more detail what you did?

  • What do you mean when you "create a new entity simply by linking the previous Parameter Entity"? Did you create a Reference to the Parameter Entity (for this purpose called "Parameter Entity_ref")?
  • You use that Parameter Entity_ref to merge into Output Entity?
  • You turn off the Enable Load for the original Parameter Entity?

The above steps don't work for me, I still get the same refresh error. The only way I can get it to work is to disable load on all Parameter Entities, whether I'm merging with the original or the _ref one.
Also, if the above were to work, is the Parameter Entity still refreshing? Since Enable Refresh is off, am I just looking at stale data?

 

Thanks

Anonymous
Not applicable

I am glad you took the time to come back and tell us what sorted the issue, because this has just helped me.

Could I just confirm, is scenario still working for you? It worked for me until around Dataflows went to General Availability and now does not.

Anonymous
Not applicable

@mark_carlisle It does work for me.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors