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.