Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MegaTrain
New Member

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

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?

1 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.

View solution in original post

7 REPLIES 7
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @MegaTrain,

 

Please mark your answer as solution. Thanks in advance.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MikeHawryluk
Helper I
Helper I

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 

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.

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

> 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.

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.

Awesome @MegaTrain Glad to see it worked out!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.