Can the PowerBI per capacity Service access a Snowflake ODBC connection WITHOUT Data Gateway

[codeaholic]

Can the PowerBI per capacity Service access a Snowflake ODBC connection WITHOUT the Enterprise Data Gateway

Hi, I have a question about using the PowerBI service per capacity model. All my users are local in the same domain as me and can share my DSN with them. According to Microsoft, PowerBI paginated reports support ODBC --> https://docs.microsoft.com/en-us/power-bi/paginated-reports/paginated-reports-data-sources

Is it possible for me to create a "paginated report" talking to the Snowflake ODBC data source and share that report via the PowerBI service to other users?

How do I make this happen?
The report works fine on my local computer and using my local System DSN and drivers.

The issue is getting this to work once it is published to the PowerBI Service.

[codeaholic]

There is no .pbix file!  This is the new PowerBI Report Builder using DirectQuery via ODBC.   So there is a .RDL file.  The Enterprise Data Gateway says that it is for "on-premise" data sources.  Snowflake is a SaaS database--it is on not on-premise.  Also PowerBI states that it supports ODBC "as is" which is the technology that the Snowflake Driver uses.

So my question is very basic, can the Power BI Services use ODBC via direct query to a SnowFlake data source without the Enterprise Data Gateway or not and that assumes every user has the Snowflake Driver installed and the same DSN?  Can users see the data in real time from the Power BI Service and not cached data from the original user but from their user account that they logged in as which is in Azure AD of which Snowflake recognizes as one of his ODBC security mechanism?  For this to work, the Power BI service has to be smart enough to see that it cannot connect with the ODBC System DNS it has because it has no password and it must prompt the user for the username and password to use to access the data from SnowFlake or use the credentials that the user had to login with to the PowerBI service.  Is it smart enough to do that or not?  It seems Microsoft is advertising one thing in their docs, but in practice it is false advertisement since you saying it really needs the Enterprise Data Gateway.  

[collinq]

Hi @codeaholic ,

I don't believe that you mentioned that this was Report Builder and not "desktop".  That said, the statement is still true.  Yes, an ODBC connection can reach the Snowflake database, and you do not need a gateway for cloud to cloud.

[codeaholic]

How?  Any documentation showing that?  I have a contractor that says no you can't do that...

[codeaholic]

That is true.  I did leave out that very important fact out.  So our contractor says this will not work.  You must have the Data Gateway as there is no way for the PowerBI service to see the login credentials of the actual user looking at the report and is not smart enough to prompt for that at report data query time as it tries to access the data.  

That doesn't sound like my experience with other Microsoft products where if one authentication mechanims fails, usually the Microsoft software will go look for it or request it at run time so it can finish up the request. 

How can I get documentation to prove this?

[collinq]

Hi @codeaholic ,

So here are two links on how to setup Snowflake with ODBC.

https://docs.snowflake.com/en/user-guide/odbc.html

This link shows how to set it up and then shows the gateway (which I know you do not want to do):

https://www.fourmoo.com/2020/02/06/connecting-to-snowflake-db-in-power-bi/

This link specifically talks about REport Builder (and, again, half the article is about the gateway piece):

ODBC support available for Paginated Reports in public preview | Microsoft Power BI Blog | Microsoft...

As I mentioned, and perhaps that is what your contractor is mentioning, without having a gateway you can't auto schedule refresh.  You have to have everybody do it themselves or you (or the owner) open, refresh and publish every day.

[codeaholic]

Thanks.

I already read the First and Third link and they don't really answer my question.

The second link once again talks about having to use the Gateway.

Also, if there is no way for a user to get to the actual report at run time, than they are right, you need the gateway.

[collinq]

Hi @codeaholic ,

The gateway is designed to allow you to share without having to have the pieces/bits installed on each computer - the gateway server/computer would have to have the DSN and then all would work.  And, the key point of the gateway is for refreshing the data.  Therefore, to share this with everybody else and NOT use the gateway then you have a couple options.

The first one is that you just refresh it when you want and then you publish it to the service.  And then, you can share the report/workspace like normal.  It just won't refresh for anybody but you and they will all see the output based on your last refresh.

The second option is to make sure that everyone has the exact same DSN and other settings and then you can either share the .pbix file with them, or as per your request, you can still publish it and then give them full rights to it and they can download it or refresh it as they need.