How to Connect to an ODBC Data Source From Power BI

[Anonymous]

There are plenty of ODBC drivers, both open source and commercial, for almost any data source. For users who are not familiar with the ODBC technology, I’ll briefly explain what it is: ODBC is a standard application programming interface that allows applications such as Power BI, Microsoft Excel, and hundreds of other apps that support ODBC connectivity, to access data in various databases and cloud applications. By cloud applications, I mean the likes of MailChimp, Salesforce, BigCommerce, etc. The driver serves as an intermediary between the data source and the application you want to pull the data into, and is really easy to install and configure. 

I’ll show you how to load data from the PostgreSQL database into Power BI with the help of the corresponding ODBC driver, but the steps covered here are almost the same for other databases, feel free to replicate them for other data sources.  For the purpose of this post, I’ve created a ‘cars’ table in my Postgres database and populated it with mock data. 

In Power BI Desktop, click Get Data, then select Other > ODBC category. Click Connect.

In the From ODBC dialog box, expand the Data Source Name (DSN) drop-down list and select the DSN that you’ve configured for your data source. Optionally, you can enter a SQL statement to execute against the ODBC driver in the Advanced options -- for example, if you want to filter or sort the data in the table rather than to load the entire dataset. Click OK.  If your data source is password-protected, Power BI will prompt you for the username and password. Type them into the respective fields and click OK. 

With the above SQL statement executed, I’ve received only one record in the results.

If I don’t specify any SQL statement, Power BI will retrieve the metadata from the Postgres database and offer you to select the table to load. You can preview the data by selecting the needed table.

Click Load to import the data into Power BI to work on it. Power BI doesn’t display the loaded data in a grid by default, it only creates models and identifies relationships in the data, which it caches in-memory for reports. 

Let’s say we want to create a line and stacked column chart that will display how many car models each manufacturer has in our database. In the right pane, select the needed columns - id, manufacturer, and model, and the needed chart type. 

As a result, we get the following chart, we get the following chart.

An ODBC driver takes over the task of data retrieval, allowing you to concentrate on the data analysis. 

[newpi]

Hi @Anonymous ,

Thank you for the post. I'm trying to do something similar and connecting to MYSQL with an optional SQL statement. However, I'm getting the following error of No database selected.

It works fine when I don't add an optional SQL statement and select doing the 2nd method.

[rjhale]

@newpi I'm running into the same problem.  Did you ever get this resolved?  

[newpi]

Yes @rjhale . 
Make sure you download the connectors.
Use Unicode connector and establish the connection in your systems first.

When you write the query in that box, make sure you mention the dataset name before the table name. i.e select * from dataset.table

I used SQL workbench to work through this and also installed the connectors

[rjhale]

Thanks for the suggestion.  I had setup a local DSN, but I wasn't specifying the database.  Once I specified the correct database name in the DSN, the custom SQL started working as expected.  

[s-roberts]

My ODBC connection works from the local desktop but not in the PBI service.
I have setup a gateway and added the datasource to the gateway but getting an error:

ODBC: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Would the connection string have to be different in the service?

In the PBI Desktop it is simply: = Odbc.DataSource("dsn=User DSN Name", [HierarchicalNavigation=true])
In the Service would it be something like: 

"driver=Driver Name; DSN=User DSN Name; data source=DB Name"

[Anonymous]

Hello @s-roberts,

I have the same problem as you, have you found a solution to connect to ODBC on powerbi service.

THANKS

[SandeshPatil21]

How to create below visualisation in power BI

[ayodhya1998]

Could the same procedure be used to retrieve data from the Navision 2016 database? I've discovered that Odata webservices are the most commonly recommended approach for transferring data from Nav 2016 to Power BI. Are there any pros and cons to these methods?

[BobMarf]

As an alternative you can use Skyvia Integration: Skyvia offers a cloud-based integration solution for Power BI. It provides a user-friendly interface for setting up and managing data flows, which could be an easier and more efficient alternative to ODBC drivers or custom-developed solutions.