Accessing data from a data source\data gateway

[aquarm]

Hello.

We have set up an on-premise data gateway on our data server.

I then went in and created two data sources connected to this gateway.

The first is a folder connection which points to a series of excel files. The second is a SQL connection to our database, and a series of tables held within.

When I access the appl.powerbi.com website, and click on Manage Gateways, I can see the gateway and both of my data sources.

I can not find any way to actually *access* these data sources however, and use them within a workspace\report\etc...

When I click on "get data" at the bottom, the gateway connection is not included under any of the categories. 

How do I actually access the data we have pointed the data gateway too?

Thank you,

Anthony

[otravers]

Forget about that Get Data link in the Power BI service, you'll want to download Power BI Desktop and create your dataset with it, connecting directly to your SQL database and folder. The gateway comes into play when you publish the data model in the service in order to process refreshes.

[aquarm]

That's not what I asked.

The reports we have built are already built in the app.powerbi site. Not the desktop. We are using that specifically because it ties into our portal dashboard. Switching to the desktop version for this task is not an easy option.

Second, the entire point of the on premise gateway is being able to use the app with on prem data. So I should be able to ask how to do that without being told to do it a totally different way.

[otravers]

Anthony, I think there are a few misconceptions there. First off, the gateway has nothing to do with reports, it's used to refresh dataflows and datasets. Reports are built on top of datasets, they don't connect to source data directly.

If you already have a dataset (and the reports depending on those datasets, but again reports are not directly relevant here) deployed to app.powerbi.com, go to that dataset's refresh settings where you'll be able to setup the gateway and data source credentials, but you won't be able to add sources that are not already in the report.

Third, I'm well aware of the data gateway's purpose. You can access your data sources via the gateway in the Power BI service in dataflows, but you'll still need to ingest and model that data in a dataset to build reports, which is typically done in Power BI Desktop.

Finally, I'm not Microsoft I'm just another user giving you free help about how Power BI actually works. If you have an opinion about how Power BI should work, I'm the wrong recipient for that.

[aquarm]

Re-reading your reply, this seems to be the issue:
"but you won't be able to add sources that are not already in the report."

I can not find any method of adding sources to the power bi service from our on prem server. And yet, one of our users managed to do it and doesn't know how.... So I know it is possible.

In his workbook, he managed to link to a folder of excel files that reside on our company share drive. He did this by setting up a personal gateway.  The problem is, as a personal gateway, it does not share to others when you publishes his reports. So, as the system admin (and not a power bi user), I have been asked to convert it to a standard datagateway, and assist this user in repointing their data files to a shared\organizationally available copy of these files that reside son the company share drive.

Does that make any more sense? Again, this is to be accomplished via the power bi service rather than the desktop app. And after 2 hours of clicking various buttons, I can not figure out how to add a data source that is not a cloud database.

[otravers]

Your colleague used Get Data > Files in the service. This is extremely limited in terms of supported sources, doesn't let you do actual data modeling, and doesn't work with relational database sources. You might as well forget that this exists for most intents and purposes, like I told you in my first message.

Why do you think Microsoft mentions Power BI Desktop all over that Get Data screen in the service, just to piss you off?

[aquarm]

Ok, so, I have the gateway setup.

and I have the data source setup.

But I can not figure out how to then access the data source, as per my original message.

Quoting my original post:
"When I access the appl.powerbi.com website, and click on Manage Gateways, I can see the gateway and both of my data sources."

So, I can see the data sources I have made. I have not found any method of actually accessing them. 

It appeared your original solution was to just not use the data gateway, and instead just connect directly to the file source. But that is confusing, as the data gateway exists for a reason, so for someone to just say "don't use it" doesn't really make sense. If I misunderstood you, then I suppose there is a deeper miscommunication at play.

My frustration comes in that more often then not, when requesting help for exactly how to accomplish something, most folks online don't actually answer that question and instead tell you to do it a whole other way. Believe me, I can easily make a direct connection to the data through the app. But that doesn't do me any good if we are trying to use power bi as a service here. And the data gateway *exists* for this reason, as far as I can tell.

[otravers]

You're the source of your own frustration and you did indeed misunderstand me. I gave you specific feedback on where and when the gateway is relevant and where data ingestion is done in the Power BI ecosystem. I never said not to use the gateway in the service, I said that data modeling is done in Power BI Desktop, i.e. any serious work that starts with "Get Data". From the Desktop app you publish in the Power BI service which will be able to connect to your sources via the data gateway. Microsoft may add data modeling to the Power BI service at some point but it's only available in datamarts at this point (and you should probably disregard that to keep things simple).

Read again carefully my two posts, you're confused about how Power BI is actually architected and it will be impossible to help you further unless you stop projecting that "I'm not actually answering your question" and try to actually engage with the substance of my answers about how Power BI actually works.

The following document is a good place to start understanding the various Power BI entities and how they're related to each other:

https://docs.microsoft.com/en-us/power-bi/fundamentals/service-basic-concepts