Hi all,
I'm trying to configure a scheduled refresh on an SQL query for a client's databse, using the power BI on premises data gateway. I haven't gotten it to work, though, as the data source I'm specifying in the desktop version does not seem to be recognized as the data source that is included in the data gateway, despite my consistent use of the same dsn, username and password. I haven't specified any connection strings anywhere, apart from in the data gateway where I am specifying the driver used and the database location. I've taken the following steps:
- I've added the database as an ODBC data source, specifying a dsn "dataName", using the standard password and username included in FireBird for authentication;
- I've asked the IT organization to whom the workspaces are outsourced to install the on-premises PowerBI data gateway. They did, and listed my e-mail address as administrator. They also set up a scheduled refresh for a local file, and I was able to verify that this worked;
- I've added a data source in the data gateway called "dataName", entering "dataName" as data source name, ODBC as connection type, specifying driver and database location in the connection string, using the basic verification method, and entering the standard username and password used in Firebird for authentication;
- I created a data set in Power BI, in which I am specifying "dataName" as dsn, and the Firebird standard password and username as credentials under basic identification;
- I uploaded the data set into the web service, expecting a connection to be made based on the congruence of the dsn entered for the data source in the data gateway and the dsn specified in the uploaded file;
- I tried to schedule a periodic refresh by going to the 'schedule refresh' dialog in the web service. When I attempt a manual refresh, I get an error relating to the Personal Data Gateway which, as far as I know, I am not using. Looking at the settings for the scheduled refresh, two settings deviate from the connection which the IT contractor made to the local file as a test (as mentioned above).
- First, Power BI is telling me I have not installed or configured a data gateway for the data set I am trying to refresh. The program is telling me to configure an existing gateway, or install a personal gateway.
- Second, under data source references, Power BI web service is telling me that the data source cannot be kept up to date because the references are not correct. It suggests I edit my references, and try again. It does list ODBC here, but when I click 'edit references' and enter the standard username and password using basic authentication and the dsn I've been using all along and click OK, the dialogue screen freezes up, and nothing happens. I have to abort the operation by closing the screen, apparently without my references being edited successfully.
I'm kind of confused by this behavior of Power BI - there seem to be a plethora of options to specify with regards to credentials and connection strings to use, and there's no way of telling whether an error like this is due to one or several of the credentials I've provided, the specific combination of drivers, an issue of version compatibilities between the desktop and web service, or the sequence of installation of the various elements in the power BI package.
My best guess at this point is that either I'm not using the proper credentials at one or more points in the process, or there's a compatibility issue caused by a prior, unreported installation of the personal gateway when the on premises data gateway should have been installed right away. As both the official support pages and the in-program help function offer little to no recourse, I'm hoping someone here can help me troubleshoot.
Bests, and many thanks in advance,
Evert
