Multiple On-premises data gateways for one dataset

[Jonathan_P]

Hello,

We have a dataset that needs to get data from two separate SQL Server databases. In production, there is no place where we can install the On-premises data gateway that can access both database at the same time. Therefore, it seems that there is no way to use multiple On-premises data gateways for one dataset.

Why we can't do that?

Is it by-design with good reasons to not allow this?

Is there any workaround right now?

I found a similar thread here:

multiple enterprise gateway on one dataset

Thanks

[Seth_C_Bauer]

@Jonathan_P Currently there is a limitiation, you can't mix gateways on the same dataset. No, there is no way around it from the gateway perspective, all datasources need to connect to the same gateway.

From the SQL side, I don't understand the limitation that you can't connect to both SQL servers.. Since you have multiple in the dataset, I'm assuming you are importing the data. I have connections to thrid party SQL servers outside my domain and it works fine... What is your limitation that connecting to both doesn't work?

[Jonathan_P]

@Seth_C_Bauer, the way we work with Power BI is that we develop data models, the datasets, (and some reports based on them) for client companies. In other words, we help our clients with their DW/BI requirements. Once the data model is up-and-running, we let business users go and create more reports.

So for one client company, we needed to get data from 2 differents SQL Server databases. On our development setup, we have both databases on one SQL Server instance. We restored both on our server. However, on production, those databases reside on 2 distinct SQL Server instances. In fact, this is 2 different machines. There was no communication possible between them.

I don't know if I am clear enough and if this is a common way to work and develop with Power BI.

Any thought?

[Seth_C_Bauer]

@Jonathan_P Are the servers in different domains? Is there a reason you aren't just using both databases on the same gateway? If you are combining them in your dataset, then they would both need to be on the same OPD gateway. You just can't use different gateways...

DB and other options:

Other ways around this: Create a simple data mart / staging of the data you need from both databases in a singular location. This assumes some SSIS package running or some ETL tool. You could even use Direct Query if needed then.

Extending that would be using SSAS.

[Jonathan_P]

@Seth_C_Bauer, for your question "Are the servers in different domains?", yes there are in different domains. In fact, one machine is in a domain, the other one is on a workgroup. For your other question, "Is there a reason you aren't just using both databases on the same gateway? ", we can't because like I said there was no communication possible between the 2 SQL Server instances.

Thanks for your DB and other options mentionned. That makes sense.