Multiple Non-Clustered Gateways and Automatic Datasource Choices

[AnthonyMCUBI]

Hello,

At work, my team and I have gotten used to a behavior of Power BI service where creating a new dataflow will also automatically choose the correct SQL Server datasource, if a datasource with the necessary server/database combination exists and is unique.

As an example, I created a new dataflow called Account, and the datasource was mapped without any additional work done by me after dataflow creation.

By that I mean the "Maps to:" field was populated for me. "ANTHONYM - SQLCLR" is the only datasource that maps to the server/database combo that the dataflow needed, so I believe that's why it's automatically picked. Having two datasources that map to the data source means I don't get the automatic mapping.

The above is a preface to the issue we are facing. For the above, we only had one gateway. We recently added another gateway, and some team members use it while the rest use the first one. Now, the aforementioned behavior still applies to the first gateway in the list, but it does not work for the second gateway. As in, no automatic datasource mapping occurs if the second gateway is used. By "list" I mean the list of gateways under the Settings -> Manage Gateways list. The gateway at the top works as we expect, while the rest of the gateways do not (we have also tried 3 gateways during testing, and the second and third gateway did not behave as desired).

We'd like to know how to keep the automatic datasource mapping behavior, even with multiple gateways. We upload these dataflows programmatically, and do not specify a particular gateway and/or datasource to use, so if there is an option for that it may solve the irregularities we're running into, although currenlty we're not aware of a way to do that.

[v-deddai1-msft · Accepted solution]

Hi @AnthonyMCUBI ,

Based on my test, it is the same to you.  I didn't find any option or solution for automatic mapping to the datasource. I would  suggest create an idea for it in  https://ideas.powerbi.com/forums/265200-power-bi-ideas to improve the power bi.

Best Regards,

Dedmon Dai

[GilbertQ]

Hi there

Unless you are clustering the Gateways together as soon as you have multiple gateways you will have to choose which gateway to use.

The reason is that you can have the same data source on multiple gateways. Now with your multiple gateways the Power BI Service does not know which one to select. So it leaves it up to the user to configure this.

[AnthonyMCUBI]

---

@GilbertQ wrote:

Hi there

Unless you are clustering the Gateways together as soon as you have multiple gateways you will have to choose which gateway to use.

The reason is that you can have the same data source on multiple gateways. Now with your multiple The reason is that you can have the same data source on multiple gateways.

---

We did not have datasources under more than one gateway. Each gateway has it's own disjoint set of datasources. We assumed your quote was the case, actually, until we did further testing. Gateway 1 can have 0 datasources, and using Gateway 2 will still result in no automatic datasource mapping.

If it's just a "which gateway do I look for datasources under" problem, is there a way to tell Power BI programmatically when creating the dataflow? We use the C# API, and the PostImportFileWithHttpMessage function. https://docs.microsoft.com/en-us/dotnet/api/microsoft.powerbi.api.v2.imports.postimportfilewithhttpm...

Thank you,

Anthony