Workflow using the On-Premise data gateway and ODBC

[Anonymous]

My situation in short. 

I've got multiple databases on servers scattered around the country. 
I'd like to use this data from these locations to make a single report (for example) and give my company some insight into this data. 

I've only managed to build reports with Power Bi Desktop using an ODBC connection from a database in the local network. This proces was quite intuitive and i started to like building reports in Power Bi. 

Since I want to use the data from the different locations, i installed the on-premise data gateway on these servers and was able to connect to an ODBC datasource as this seemed the way to go. It says the connection is succesful. And thought all would be quite straight forward from there. 

Now my on-premise data gateway is setup, i can't seem to find any information on how to actually get the data from these different data gateways and incorporate them into my reports. It's almost like i'm missing something obvious. 

I tried connecting to the data gateway from within Power Bi desktop, but it seems like there's is no option to do so or i'm just not seeing it.

 
My question is:

How am i intended to get the data from my on-premise gateway on an schedule, using an ODBC datasource and combine this date into a report. (just get some tables from these locations and let it update on an schedule)

Sorry for the long read!

[blopez11]

The gateways are used for when you publish your reports to the power bi service in the cloud

Its not for use within power bi desktop

You may find more details here https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem/

[Anonymous]

Thanks for the reply. The url you pointed me to is what I used to set up the gateway. What i'm having trouble with is how to go from here. 

[blopez11]

Take a look at https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-scheduled-refresh/

[Anonymous]

Thanks for your reply again. I took a look at the url you included and had indeed already read it. 
It didn't help me out much in understanding how i am supposed to get to my data from my different sources. 

According to what you said before, I can't use power bi desktop in combination with the on-premise data gateway. 
So i'm not sure how to proceed from here. 

[blopez11]

See Lydia's reply in http://community.powerbi.com/t5/Desktop/Publishing-dashboard-to-Office-365/m-p/79843#M33277

Maybe this will help more and seems she has more experience in this area

[Anonymous]

This made things even more unclear. 
I've already set up the on-premise data gateway. In app.powerbi.com it says everything is fine and connected. 
I havn't published any reports from powerbi desktop. 

In app.powerbi.com there are no datasets showing up and under the Get Data button there is no option to collect data from a gateway. 

Am i correct in assuming a dataset should show up once a data gateway connection is established?

Or do i actually have to make a pbix file from Power Bi desktop and publish it, and somehow the data gateway can update this published dataset? I've read something about naming a pbix file in a certain way..

I have no clue whatsoever :(... 

[v-ljerr-msft]

Hi @Anonymous,

---

---

Am i correct in assuming a dataset should show up once a data gateway connection is established?

No, once you have installed the On-premises Data Gateway, you will still need to add data sources that can be used with the gateway. After you have created the data source, it will be available to use with either DirectQuery connections, or through scheduled refresh. Please refer to this article to add all your data sources to your gateway first.

---

Or do i actually have to make a pbix file from Power Bi desktop and publish it, and somehow the data gateway can update this published dataset? I've read something about naming a pbix file in a certain way..

---

In your scenario, yes, you will need to make a pbix file, and get data from all your data sources(we can get multiple data sources to a single dataset with Power BI Desktop), and build your reports from Power BI Desktop, and publish it to Power BI service. Then the data sources you have added to your gateway can be used for scheduled refresh in this case.

Regards

[Anonymous]

@v-ljerr-msft Hi, thanks for helping me out!
I already added a datasource to my gateway. 


So for example:
i have database A on location 1. 
On my location 1 server i make a System DSN ODBC connection. i call it DATABASE_A. 
I install the On-Premise data gateway on this server. 

In app.powerbi i add the gateway and name it DATABASE_A

The datasource i also name DATABASE_A

I then get data from this location 1 server local on my workstation, open it in power bi desktop. Save it as DATABASE_A.pbix and publish it. 

It will then connect to my data gateway and refresh on schedule because the pbix i published is named exactly the same as my data gateway?

[v-ljerr-msft]

Hi @Anonymous,

The gateway name, pbix file name and the data source name don't need to be the same. When the pbix file is published to the service, it will detect the data source it needs from all the data sources within the gateways.

Regards

[Anonymous]

The answers i get seem to imply i'm asking some really basic and straightforward stuff, but actually  i'm at a total loss again... 😕 
Do you mean i need to publish the PBIX from the database servers themselves? So i have to install power bi desktop on all these different servers?!

If there is some sort of tutorial available or some basic documentation how to handle this situation i 'd be helped alot. 

[v-ljerr-msft]

Hi @Anonymous, 

Do you mean i need to publish the PBIX from the database servers themselves? So i have to install power bi desktop on all these different servers?!

No, you don't need to pubish the pbix file from the database servers. 

---

---

If there is some sort of tutorial available or some basic documentation how to handle this situation i 'd be helped alot. 

I think you need to understand how the gateway works first, and do a few test  to understand it better. I also suggest you to start your learning journey through Power BI with a sequenced collection of courses, and understand the extensive and powerful capabilities of Power BI. If you have any questions, feel free to ask.

Reference:

https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-0-0-what-is-power-bi/

http://exceleratorbi.com.au/top-10-tips-getting-started-power-bi/

https://guyinacube.com/videos/

Regards

[Anonymous · Accepted solution]

As i suspected the actual solution to my problem was very simple. 
The DSN needs to be the same on the gateway and the client from where the dataset is published. 
Very logical in hindsight. Topic can be closed.