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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
linas
Frequent Visitor

DataSet cannot see On-Premises Data Gateway

I have:

1. On-Premises Data Gateway which has an ODBC (driver={PostgreSQL Unicode(x64)}) Data Source added. (Test All Connections works well)

2. PowerBI desktop file which has a DataSet added (GetData -> ODBC).

    a) DataSet's 'Data Source Name' (DSN) = Gateway's DSN 

    b) Connection string = Gateway's Connection string

    c) Authentication credentials = Gateway's authentication credentials

 

Now, if I go to powerbi.com and try to 'Schedule Refresh' to my DataSet, 'Use Data Gateway' option is disabled (greyed out). However the 'Personal Gateway' is selected and "Your data gateway (Power BI – personal) is offline. To set refresh scheduling, make sure your gateway is online." is displayed.

 

What I'm missing here to get my DataSet and DataGateway talking? 

1 ACCEPTED SOLUTION
linas
Frequent Visitor

Figured! Appareantly when working in PowerBI Desktop and creating a DataSet based on ODBC you must select '(None)' from the Data Source Name (DSN) dropdown and paste your connection string to 'Connection String' textbox under Advanced options section (see screenshot below).

 

Again, make sure you use same connection string when adding a new DataSource under Gateway in your powerbi.com.

2017-03-30 05_45_09-.png

 

View solution in original post

12 REPLIES 12
davidSIT
Frequent Visitor

As an update to this issue, as of now I'm able to connect successfully to data sources using by simply specifying the data source name (DSN=) in the connection string.

 

I'm not sure what caused these problems I experienced, however, I reinstalled the on-premises gateway back when I had this issue and it seemed to start working.

 

I'd say if you want to keep your ODBC data sources simple to use, specify the table directory and ensure you're using a 64-bit System DSN.

My solution to this in the end was that the dataset was using CSVs, which weren't pointed to under the data gateway.

 

Once I did this I was able to use on-premises, basically make sure all your data sources are referenced under your gateway settings.

Hi,

 

I have a similar problem. On Desktop I have 2 connections, one to SQL and one to CRM Online (Dynamics).

I cant configure a connection for SQL on the Gateway, but noch for CRM Online... So how can I match the Gateway to the Dataset?

 

Thanks,

Philip

linas
Frequent Visitor

Figured! Appareantly when working in PowerBI Desktop and creating a DataSet based on ODBC you must select '(None)' from the Data Source Name (DSN) dropdown and paste your connection string to 'Connection String' textbox under Advanced options section (see screenshot below).

 

Again, make sure you use same connection string when adding a new DataSource under Gateway in your powerbi.com.

2017-03-30 05_45_09-.png

 

I'm trying this solution though i'm unsure what my databasename= is, would it be the name of the folder that my .dbf files are located in?

 

I'm not sure if this is included in the dbq= string or not either?

 

I'm having the same issue as described in your topic but i'm not using PostgreSQL, i'm using Microsoft Access dBase driver, i've created a System DSN that points to the dbf directory as well.

linas
Frequent Visitor

Okay, let me rephrase the question a bit:

 

Did anyone at any point of time manage to get a DataSet with underlying ODBC\PostgreSQL connection updated through Data Gateway? If so, would you be able to share steps? 

Ik have the same problem here with a Pervasive ODBC connection.

In Power BI I have only one datasource (only the ODBC connection).
I have the latest Power BI Data Gateway and a succefull connection on Power BI Service gateway.

 

But; if i publish my report, the dataset will not connect to the gateway. 😞

 

In Power BI Desktop I have created a connection with the DSN.
The gateway on Power BI Service uses a connection string like "Driver={driver};ServerName=server;dbq=database".

I tried to use this connection string in Power BI Desktop, but it won't connect in Power BI Service. 

I hope someone could help us! Thnx.

@linas

@wouterzijlstra

Have you tried going to the gear at the top right of the screen and clicking "Manage Gateway"? From there you would add the data source by filling in the information. In addition, this is where you would find the data source to see if there is a reason the gateway is not finding it. Errors show up as yellow caution signs after the data set name. Then, when you click on the dataset, it will give more information. My most frequent error is that the mashup container failed to connect. Still working on that error with Microsoft. support.

If it is still causing problems, verify the connection string through the advanced editor in the desktop.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

In your PowerBI Desktop, rather than set your data source as the Gateway, set it as you normally would as if you aren't using the gateway.  When you publish it into the web, your gateway will correctly see the database and bridge the connection for you.

Thanks for your reply. I think that is exactly how I'm using PowerBI desktop. I'm quite new to PowerBI, sorry if I'm using incorrect terms here, but when I create my DataSet in PowerBI Desktop I simply click GetData -> ODBC, and tchen fill in my PostgreSQL connection details (again - DSN, Connection String and Authentication details match the ones on Data Gateway's setup @PowerBI.com). And I think I don't even have an option to select like 'Gateway' in PowerBI desktop, unless it is hidden under different name.

@linas Do you have any other data sources in the Desktop file other than the ODBC one?

Do you have the latest versions of the gateways?


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Seth_C_Bauer Correct, a single DataSource. Gateway is downloaded from powerbi.com today, I assume it should be latest (Service Properties tab shows Product Version 14.16.6228.1)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors