Data Gateway - What Am I Doing Wrong?

[ptr]

I'm completely new to Power BI and the On-premises Data Gateway but have recently installed it to get to know the product.

The installation was successful and from within Power BI (web app - pressed the cog - manage gateways) I was able to see and configure the gateway. I also added a data source under the gateway to my SQL server and this shows as "Connection Successful." All good so far.

What I think I've failed to understand is what I do next. I thought there would be a mechanism to connect to the SQL data source in Power BI (web app) but in "Databases & More" the only options are Azure SQL DB, Azure SQL Data Warehouse, SSAS and Spark. If I select SSAS and press connect it shows "No resources found."

So what I'm trying to figure out now is whether something is not working or, more likely, I'm simply using the product wrong. I've searched the forums and seen similar questions asked but they seem to skip over the part I'm missing.

[ankitpatira]

@ptr When you click on that SSAS under Databases & More it looks for analysis services data source defined under your gateway ie if for gateway you've added data source that is analysis services (not sql database) it will show you otherwise not and that is why you're getting no resources found for SSAS. For Azure data sources you don't need gateway.

[ptr]

Ok, so how do I connect to an on-prem SQL (not Azure or SASS) data source as defined in my Gateway?

[Seth_C_Bauer · Accepted solution]

@ptr Not all the data sources available to Power BI are accessible by the Service alone. You need to download the Power BI Desktop and start there (You would want to do this anyway)

The Desktop will allow you to connect to your SQL instance. Then you can manipulate the model if needed (add measures etc) and create a report.

When you are done, you publish the report to the Service (Web). The gateway data source that you have configured will automatically recognize the data source and connection type in your PBIX (Desktop file) that you are publishing.

TL;DR: Download and use the "Desktop", publish to the Service, this is how you are able to use all your on premises data sources that you can't connect to directly from the Service (Web).

[ptr]

Thanks for that. After much banging of head on desk I finally worked out what I was doing wrong which was assuming the software worked differently than it actually does. By a process of test and elimination I found that I could...

1) From Power BI Desktop connect to my SQL server, grab a table and create a report from it. Then publish this to My Workspace.

2) In the Power BI web app add a data source in my gateway configuration to the on-prem SQL database I created the said report from.

3) Still in the web app found the dataset for the said report, went to schedule refresh, selected my gateway and schedule.

4) All done!

Phew!