Dataflow combining online and on-prem sources

[karimm]

Hi All,

I would like to know whether it is possible to create a dataflow that retrieves data from both online sources like Sharepoint Online and on-premise data. 

Asking this as I'm not able to do so.

I have a report that gets data from Sharepoint lists (mainly configuration parameters) and on-premise JIRA via JIRA API.

All my sources are organizational. I'm not getting any data from outside.

The transformations I need to do are really complex with multiple joins in a single query.

So far, I've been doing all ETLs in PowerBI Desktop itself.

After facing many times the infamous Formula.Firewall error of "step X references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.", I was looking for a solution to this and I stumbled upon Chris Webb's session about data privacy settings, I've decided to check one the alternatives he gave which is to use dataflows, since they allow "ignoring these checks" in the service.

I tried doing a test bringing one list from Sharepoint (doesn't require a gateway), and one table from JIRA (DOES require a gateway) in same dataflow, and I'm not able to get it to work...

it seems all sources must be using the gateway or not. If I enable the gateway in the dataflow's project, I get an error when refreshing the sharepoint list "The remote name could not be resolved <mycompany>.sharepoint.com" 

Is the only solution to move all sharepoint lists outside of sharepoint so that they will be on-prem (and thus be identified by the gateway)?

Thank you

[lbendlin]

In your gateway there is a setting "allow refresh of cloud data sources through this gateway".  Enable that and then try again.

[karimm · Accepted solution]

---

@lbendlin wrote:

In your gateway there is a setting "allow refresh of cloud data sources through this gateway".  Enable that and then try again.

---

Thank you for the reply.

I did have that checkbox checked and the problem was still occurring.

New findings:

I'm using a Premium workspace and i realized today that copying the queries as is from PowerBI Desktop, resulted in some computed entities as some of the "load enabled" queries were referencing each other.

Then I read the following in the PowerBI documentation about Premium capacity computed entities:

As a best practice, when doing computations on data joined by on-premises and cloud data, create a new dataflow for each source (one for on-premises and one for cloud) and then create a third dataflow to merge/compute over these two data sources.

---

---

Due to the urgency of resolving things, I have moved all settings tables to an Excel file stored on-premise.

I'm dealing with other issues now like dataflow not refreshing.

Once I will resolve that, I will try to take back the settings tables into Sharepoint and try the best practice above.