Connect to Different Datasource by User

[jhcannon]

Don't know if this is even possible!

Here is what I am trying to do.  We have multiple locations each with their own database.  All database schemas are identical.  I have created reports and dashboards that I want all locations to use.  User A (Location A) logs in, runs report and it shows the data from the Location A database.  User B logs in, runs report and it shows data from the Location B database.  All users have a Pro license.  

My original plan was :

1 - Create direct query dataset from database

2 - Create gateway on each database server

3 - Connect user to gateway at their location

4 - Report would use their gateway to serve the data

After further reading, I don't think this will work.  Is there a way that I can accomplish this?

[FabioFe]

Hi @jhcannon,

what was the final solution for your problem described?

Best,

Fabio

[v-xulin-mstf]

Hi @jhcannon,

Maybe your plan will be work, what error message did you receive?

Please provide datails.

Best Regards,

Link

[jhcannon]

Sorry - just had time to really test today with someone on the other end.  We are not getting any error messages.  When he opens the report, he sees my data.  I am using Direct Query with a dataset.  I would expect it to pull the data from his gateway connection but it still shows mine - even when he refreshes.  Security wise, I don't want him to see my data or vice versa.  

My ideal solution would be that the report is located in a central workspace with everyone having access so that I can maintain the actual report file.  When he logs in to the Power BI Service and views the report from his location, the gateway on that computer (setup using his email) points to the database on that computer.  The report uses that database to show data.  He would never see my data and I would not see his.  But what it looks like is happening is his gateway is giving him access to my database.  

I think maybe I have misunderstood what the gateway does or I have set up the gateway incorrectly.  Any advice?

[v-xulin-mstf]

Hi @jhcannon,

The user will use your on-premises gateway to access your database.

Maybe you can  set permission to restrict users to build new content using your database.

Best Regards,

Link

[aj1973]

Hi @jhcannon 

The only functionality of a Gateway is to connect the dataset to the data source a nd refresh. In my understanding of your case is that you need to install a separate on-premise Gateway for each data source, duplicate the report and connect them to the 2 datasources then publish both reports to the workspace. Connect each of them dataset to the correspondent Gateway and source.

Performance wise I think it is better

Good Luck

[jhcannon]

I really don't want to have multiple copies of each report.  We're looking at possibly 12-15 different locations and 30 or more reports/dashboards.  So that would be 500 to maintain instead of 30.  At this time, there are changes / additions to reports pretty much daily.  I expect that will level off at some point in the future but for now, I would rather not have to change 15 copies of a report if I can help it.

It just seems like there should be some way to connect to a different server depending on who is logged in.

I've been playing with parameters as the servername this afternoon some.  That may be a different way to handle this.  That seems to be working when I set the parameter manually, now to figure out how to set it based on the user.

[aj1973]

@jhcannon 

Yes I didn't think you could have so many servers, but using parameters as server name is also another way and to set up user filter you will need to Implement a RLS to you report.

[lbendlin]

either user live connection with credentials pass-through, or implement RLS.