Power BI has many data connectors you can use to connect to your Data Source. If your Data Source is not listed from Get Data on Power BI Desktop, you can use one of the following generic data interfaces:

• ODBC
• OLE DB
• OData
• REST APIs
• R Scripts

While working on a recent project,  I see that after adding an ODBC Data Source to Power BI Gateway using the same connection string on Power BI Desktop, the Gateway still shows ” Not configured correctly” similar to the screenshot below:

In this post, we will discuss how to configure and connect to ODBC data source(On-Premises) on Power BI Desktop and refresh on Power BI Service

There are 4 steps to complete the process

1. Install the driver for the data source and configure the ODBC Data source
2. Connect to the ODBC source from Power BI Desktop
3. Add the Datasource to Power BI Gateway
4. Publish the report to Power BI service and map the Data source to the Gateway.

Step one: Install the ODBC driver for the data source and configure the ODBC Data source

Ensure that the bit for Power BI Desktop and the driver are the same (64 bit recommended).

For this example, I am using Access database.

To create a DSN for an Access database:

• Open ODBC data source from control panel

• Select System DSN- for multiple users

• Select Microsoft Access driver (*.mdb)

• Click finish

• The ODBC Microsoft Access Setup dialog box opens.

• Enter a Data Source Name

• Click Select

• The Select Database dialog box opens.

• Browse to the drive where your data is located

• Select the database

• Click OK.

Your Access database DSN is now created.

Step two: Connect to the ODBC source from Power BI Desktop

Ensure that you are using updated version of Power BI Desktop.

To connect to ODBC data source that has been configured on Power BI Desktop:

• Select Get Data ->Other -> ODBC – Select Connect

• The DSN dialog box opens

On Power BI Desktop while 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 to see the gateway configured correctly on Power BI service after adding the data source to the Gateway.

• Change the DSN to None and add a connection string.
  As an option, you can also specify a SQL statement to execute against the ODBC driver.

• Select OK and authenticate to load or transform your data

Step three: Add the Datasource to Power BI Gateway

Ensure that you are using the updated version of Power BI Gateway.

To add the data source to the gateway:

Go to Gateway Management on Power BI Service

• Click Add Data source -> Select ODBC for data source type

• Add the same connection string and Authentication method on Power BI Desktop

• Click Apply

Step four: Publish the report to Power BI service and map the Data source to the Gateway.

To map the data source to the gateway:

• Go to settings -> Dataset

• Select the Dataset

• Expand Gateway connection

• Select Maps to and select the name of the data source

• Click Apply

You can now do On demand and scheduled refresh.

Happy reporting 🙂