Dataflow Gateway brings wrong table

[Anonymous]

Hi there,

I'm trying to set up an on premise gateway to refresh data to a dataflow. I need a gateway because, altought the data source is a cloud PostgreSQL, PBI ask for a gateway for refreshing it. I have several tables on the same server, and they have the same structure, but different data (they are data from few different clients of the same SaaS).

The issue is, after I've added all the different sources on the gateway, when I query trought dataflow, it sometimes try to bring the data from wrong table! 

Here It follows, step by step:

- 1st I've connected all data sources on my gateway. All them are from the same server and DB, but have different credentials.

Then, I create a dataflow for each data source, connecting trought the gateway.

The first one runs smoothly. Refreshes perfectly either. Please note that the column enterprise ID "2" match with table name customer "2". Everything fine until now.

The issue starts when I add the other tables. I connect from the proper dataset, in the same server of the 1st one but with new credentials, and I can see the right data on power query, but as soon as I refresh the data flow, they return an error: "The key didn't match any rows in the table", that normally could easly be solved.

If I try to click on "source" to remake the path to the data, the connector ask me server and database, but no for credentials, and end by returning me to the wrong data. It give me data from the 1st table, customer 2, not customer 530. Writing the query doesn't solve this.

When checking the dataflow gateway settings for customer 530, I realize that the "maps to" field does not point to customer 530, but to customer 2. I can change that back to customer 530 and apply, but then If I refresh the page, it backs to customer 2. 

Since that, I'm not able to refresh data thats not data from the first table I've setted up. 

Can you help me? 

[v-yangliu-msft]

Hi  @Anonymous ,

1. As far as the document knows......the cloud data source refresh does not require a gateway, you can manage the configuration of these data sources by using the data source credentials section in the dataset settings. You can check the document to see if there are other data sources in it.

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data

https://community.powerbi.com/t5/Service/PostgreSQL-to-Power-BI-Service-without-on-premise-gateway/m-p/600487

2. Check whether the server and db name of the data source match.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yangliu-msft, I know the document, but it doesn't work this way. Thank you any way.

The second link you sent actually explains why I can't use PostgreSQL without a gateway. All the workarounds involve exporting postgre data to other sources, like excel or MySQL, so we still having the fact that we need gateway to connect with postgre.

The first link with documentation seems good, altought my gateway management page seems to work slightly different from how I guess it should work.

In this documentation it says: "Make sure you map the correct data source definition to your data source. As the above screenshot illustrates, gateway admins can create multiple definitions on a single gateway connecting to the same data source, each with different credentials".

I simply can't do that. Let's say I have a single database with two different credentials, A and B, each one for a different table. If set up the dataflow and the gateway to go trought A, it works fine. Then I set up B, works fine either, but A breaks, because the A dataflow now want to use the gateway mapping trought B. It seems to change the data source settings automatically to the newer connection with the same "address", but then it can't fetch data due to credentials issue. If I select "maps to A", seems to change, but if I refresh the page, it map back to B again. I can't change that. I can add as much credentials as I want for the same database on my gateway, but only one credential may connect properly, others break (empty tables or credential issues).

If I try to fix A trought dataflow source step, I put the server and DB info, but I can't put user and password (it have the ones from the gateway), it goes all the way trough data, and end by feching B data for my A dataflow. (Yeah it gave me some trouble once I pushed this data into dashboards, the columns match perfectly).

If I'm not missing something really obvious, looks like a bug, but I'm using the free version, so I have no access to support.

Thank you again

[Anonymous]

You have precisely described my issue.

I'm experiencing this with on-prem SQL Server. I have two different SQL logins with separate access privileges to different tables.

I want to create two dataflows that use specific logins on the same server and database. I cannot select a login to use with a particular dataflow. When I enter credentials for one of them the other breaks and throws "The key didn't match any rows in the table". It makes sense because it has changed the previous setting and now it cannot see the table.

Creating different data source in gateway configuration didn't help either. It looks like you can select credentials in gateway config but it has no effect.

I'm thinking about using server alias or IP to differentiate the server definition in gateway configuration, which seems to be an overkill.

Please update, if you have found a solution.

Thanks,

[Anonymous · Accepted solution]

I found the reason.

It's not possible to do it. It's a limitation of dataflow. Period.

"Dataflows using gateway data sources do not support multiple credentials for the same data source"

https://docs.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-features-limitations

[jeffshieldsdev]

You shouldn't need a gateway for cloud-based sources. Have you tried authenticating in the Data Source Credentials section of dataflow settings and not selecting a gateway?

Also, when you add a data source to the gateway and authenticate, you don't need to enter the credentials again authenticating from the dataflow.

[Anonymous]

On premises gateway is needed for PostgreSQL cloud data sources. It shuldn't, but it does.