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

[mark_carlisle]

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.

[otravers]

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.

[sxt173]

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]

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.

[BruceT]

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 · Accepted solution]

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.

[cyongt_bdf]

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

[sxt173]

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]

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

[mark_carlisle]

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]

@mark_carlisle It does work for me.