How to use data sources from the on-prem gateway in a report?

[MegaTrain]

I'm brand new to PowerBI, hopefully I'm just missing something obvious here.

I'm a DBA that was asked to install and configure the on-prem gateway so PowerBI on Office365 users could access some specific local data sources.

I was able to complete the install (not without difficulty), and now on app.powerbi.com I can go to Settings > Manage Gateways, and create a Data Source to an on-prem SQL server. (In case it matters, I'm using Basic (SQL) credentials, not passing through user credentials. I don't want the users to know this username/password.) Testing the data source shows "Connection Successful". I've added myself (and several other people) to the Users tab.

Now what? How do I make a report utilizing this data source?

If I click on "My Workspace", I get the "Get Data" screen, what am I supposed to click on to choose this data source?

"Apps", "Services", and "files" don't make any sense, but when I click on "Databases and More" none of the options are obviously what I'm looking for: 

• Azure SQL Database
• Azure SQL Data Warehouse
• SQL Server Analysis Services
• Spark on Azure HDInsight

Which of these contain the gateway data source I just configured? Or should there be a 5th one that isn't showing up?

Can I even do this on app.powerbi.com? Or do I need to download and use the PowerBI desktop to actually create reports?

[v-jiascu-msft]

Hi @MegaTrain,

Please mark your answer as solution. Thanks in advance.

Best Regards,

Dale

[MikeHawryluk]

Hi @MegaTrain

Since you are using an on-premise MS SQL database as your data source I suggest the following.

- Install Power BI desktop on a machine that has access to the SQL server.

- Build the report in Power BI desktop

- Create a workspace in the Power BI Service 

- Publish your report from Power BI desktop to the workspace that you have created.

- In the Power BI service, under the workspace you have created, go to datasets, refresh connection and select your gateway and refresh intervals.

Hope this helps.

Kind regards,

Mike 

[MegaTrain]

Thanks, Mike.

That method sounds workable for me, since I (as a DBA) have direct access to the SQL server, but part of the objective here was to give a handful of power users the ability to create reports from a datasource they can't otherwise query directly.

I'm still not entirely sure whether that's even possible, or whether I just haven't found the right way to make that happen. I probably just need to download the Power BI Desktop and play with it, see if it allows you to choose a gateway data source while designing.

[MikeHawryluk]

Hi @MegaTrain

You're running into a common dilemma that DBA's face.  In order for the users to build reports, they will need access to some form of the data source from Power BI desktop.  

As you say I think it's a good idea that you play with Power BI desktop and get a sense of how it connects to SQL Server and how your users will interact with it.  

All the best,

Mike

[MegaTrain]

> In order for the users to build reports, they will need access to some form of the data source from Power BI desktop.

Right, and I hoped the gateway data source I've already defined could be chosen from the Power BI desktop.

If that's not the case, if we can only take advantage of the gateway data source after the report has been created and published, then I need to rethink some things.

[MegaTrain · Accepted solution]

For what its worth, I found a couple of online tutorials with this scenario:

https://www.mssqltips.com/sqlservertip/4170/connect-to-onpremises-data-sources-with-power-bi-persona...

http://radacad.com/step-by-step-walk-through-on-premises-live-sql-server-connection-with-power-bi-en...

Both appear to use the technique Mike is suggesting: create the report in Power BI Desktop with a direct data source, and when publishing (I presume) it sees the matching server and database name, and associates the report with the gateway data source. From the second liink above:

"Save the report as something, for example; SQL DB Live Example, and Publish it. When you publish the report you will see a message saying that publishing succeeded, The Published report has been configured to use an enterprise gateway. This is good message showing that report’s data source, and enterprise gateway data source are able to find each other :). Nothing more is required then."

Well, apparently my power user is happy (at least the one that has direct database access), it did allow him to create and publish a report that we can confirm is using the gateway. We will play with it some more.

Thanks for your time, Mike.

[MikeHawryluk]

Awesome @MegaTrain Glad to see it worked out!