Connect to data gateway

[davishert]

I'm new to PowerBI, so forgive the question if it's elementary. I created a report in PowerBI desktop, published it to the PowerBI Service along with the data set. I then opened a data gateway to my company's server and the connection is good. I now created another report and published it to the PowerBI Service along with the data set. How do I connect my new data set with the already established data gateway. Yes, the new data set derives its information from the same server as the current data gateway. Thank you for the help.

[Seth_C_Bauer · Accepted solution]

@davishert I assume you installed the On-Premises Data Gateway. Once you have that installed, you need to go to the Power BI Service and under "Settings" (the gear) click on Manage Gateways. This will give you a list of the OPD gateways you have installed and have access to. You can use this example of creating a data source to a SQL Server as an example walkthrough. But essentially you need to create a data source with the connection strings that match exactly to the connection strings in your Desktop file. Then, publish the PBIX file and the gateway will appear as one that you can use under your dataset to refresh.

[CahabaData]

@Seth_C_Bauer  I think that was the explanation I've been looking for for awhile.  So if I understand it - your data connection made in Desktop can be made in Service.

In your explanation I noticed you first enabled the data connection in Service - and then published the report from Desktop to Service.  Does that order of events matter?  ... or can one publish to Service and then make the data connection?

[davishert]

I work for a school district and all of my data comes from one server (SclSrvr). I created a data set from SclSrvr, created my measurements, then published this data set to PowerBI Service (online). Once in PowerBI Service, I opened a data gateway for my data set to SclSrvr. I am pretty sure that this needs to be conducted in this order. 

I then created another data set from SclSrvr in PowerBI desktop, created the measurements, and published it to PowerBI Service. Now, back in PowerBI Service, all I had to do was link my new data set with the already established data gateway. 

Initially I had a problem linking my new data set to the SclSrver via the data gateway because one of my tables in my new data set was not from SclSrvr. PowerBI won't do that. You can link tables from different data sources in PowerBI desktop, but not in PowerBI Service so if your ultimate goal is to bring it to PowerBI Service then that is an important constraint to keep in mind. 

[CahabaData]

that was helpful, thanks.

On your last sentance; I understand initially your data connecting was a problem because of 1 table that was not part of your server.  However I think fundamentally PBI Service intention is to to mash up data from differing sources.  So while I don't have a good example at hand I am wondering about this point.  For instance if you had sales data from a daily sales database application and had perhaps a daily web visitor statistics from your online store - - can one make a single data set of these 2 together?

To my mind tables are tables - in Desktop one can import tables from differing sources.  Then if there is a suitable join field - you can query them together.  I am just assuming when published to Service that the data connections can be made.

[davishert]

If you can figure that out and let me know, I would be grateful. What I've learned so far is that in PowerBI Service you cannot combine datasets.  A published data set (from PowerBI desktop) with different sources won't connect to a data gateway in PowerBI Service, but I hope I'm wrong.

[Seth_C_Bauer]

@davishert@CahabaData

The order of operations is important (from my experiance). You want to make sure the data source/s exist in the OPD Gateway prior to publishing the Desktop file. That way, it automatically recognizes the data source and will allow you to use it.

To the multiple data source question. You can use multiple data sources in the Desktop, you just need to make sure that all the data sources are supported and created in the OPD gateway. Otherwise it will either not work, or default to the personal gateway.

[CahabaData]

ah ok - am learning BI myself in some regards - comfortable in desktop and making DAX calculations has been my focus - - data connection & Service are still new.  Maybe @Seth_C_Bauer or others further up the learning curve on this point will jump in & clarify.

Am surprised to hear one cannot - as in my mind I really am thinking mash up of differing data sets was what it was all about.......

[davishert]

Thank you for you assistance. After following your steps, it still wouldn't allow me to connect to the data gateway. I realized it was because one of the tables in the desktop version was one I created and not from the same data source. Once I deleted that table from the published version, problem solved. Thank you again.