Change gateway from personal to enterprise for direct query

[Cathryn]

Hi All

I have a report that was using the personal gateway created by someone else.  I have an enterprise gateway installed that has the same datasources as the personal gateway.  From everything I read, it should switch over but it's not.  With the old report it says 'This data gateway can't be accessed by a data gateway; Server Servername; Database: warehousename'

Is it because an enterprise gateway doesn't allow direct queries?  

Thanks!

Cathryn

[Seth_C_Bauer · Accepted solution]

@Cathryn The OPD gateway is the only gateway that allows direct query... The current limitations around Direct Query are you can only connect to one database, and you can't have connections to other data sources. Also, if you created the report using "Import", you can't automatically just switch to Direct Query, you can only switch from DQ to import. You may need to re-create the report...

[Cathryn]

Thank you - so how do I change to Import and use the gateway that the Power BI Service can see?  When I go into Desktop, it won't allow me to check the option from Direct Query

[Seth_C_Bauer]

@Cathryn Can you clarify this for me?

You have a Desktop file that is using Direct Query, you've published that to the Service, you've created a datasource that exactly matches the connection you have in the Desktop in the OPD Gateway, and you don't see the OPD gateway as a gateway you can select on the dataset in the Service? - Is this accurate, or am I missing something?

Initially, it sounded like you had a report based on Import...

[Cathryn]

Yes 

Direct Query in Desktop : ServernameA & DatabaseName

I verified that the gateway has a connection called ServernameA (same case) and DatabaseName

When I view the report I get

The personal gateway is no longer available.

When I go to the dataset/dataset settings it shows I can schedule a refresh for the direct query.  

I've since recreated the whole report and changed the sources to Import instead of Direct since I'm told that I can't use direct query with anything other than a personal gateway.

But now my problem is that this report had row level security and I think I understand that I can't use RLS with Imported queries? 

This report needs to be called from salesforce passing in a filter value.  But also, I can only show the user their data. I'm getting really confused on which rules are allowed for which queries and how to implement them

[Seth_C_Bauer]

@Cathryn 

I've since recreated the whole report and changed the sources to Import instead of Direct since I'm told that I can't use direct query with anything other than a personal gateway.

DQ can only be used with the OPD gateway... it doesn't work with the Personal. 

If you built RLS in the Desktop, then it should work regardless of import or direct query.

Are you embedding this in Salesforce? There is a walkthrough for that here, but I don't know if you can apply the filter on the URL as required and outlined as possible here

If you see the OPD as a valid gateway, and you are using DQ, then it shouldn't have an option to schedule a refresh, it should tell you that it's a live datasource and doesn't require it...