PowerBI + Snowflake: ODBC Connection: DirectQuery

Question

we are evaluating both products, Snowflake as a data warehouse and PowerBI as the visualisation platform for dashboarding / reporting needs.

We have a requirement to use the Snowflake with the AWS PrivateLinks, which make the out of the box tools that come with PowerBI Desktop (and Service) as useless when user wants to use the SSO.

So currently I've setup the ODBC connection using the Snowflake's ODBC driver and Windows ODBC Settings. When I use the Get Data -> ODBC -> MyNewConnection I can't see the option of DirectQuery.

I'd like to ask for help if you know how can I setup the DirectQuery type of connection with the ODBC Connector to Snowflake ?

Answer 1

ODBC connections don't support DirectQuery. There a probably a couple of options:

1. Build your own. Use the Data Connector SDK to build your own extension that enables DirectQuery
2. Use the Progress ODBC DataDirect ODBC driver. Details can be found here

However, before you go down either of these routes (and assuming you haven't already done this) I would talk to your Snowflake account manager to see if there is any way of getting PowerBI Snowflake native connections to work with your setup

Answer 2

Correct, if you are using PowerBI SSO option in the PowerBI service based on this, Privatelink is not supported .

If you have PowerBI Gateway, then the Privatelink should work.

Use of the Gateway would not allow authenticator=externalbrowser and the only option is Username and Password to login. The authenticator=externalbrowser is only allowed for PowerBI Desktop when used with ODBC generic driver but then you will lose Direct Query option as you noticed.

If these options are limiting, feel free to open a feature request with Microsoft to support Privatelink through PowerBI SSO.