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.
Create ODBC connection in Power BI with Devart ODBC Drivers
Fast and widely accepted
30 days free trial
What Devart ODBC drivers
can do for Power BI?
Power BI is a feature-rich environment for developing business intelligence reports. The software supports ODBC data sources and offers many tools for transforming data into meaningful interactive visualizations. Devart provides ODBC connectors for Power BI to access data in databases and cloud applications, such as MySQL, PostgreSQL, xBase, Oracle, Salesforce or Mailchimp. With Power BI Desktop and ODBC drivers, you can retrieve entire tables or execute SQL queries that return a subset from MongoDB, FreshBooks, NetSuite, and other data sources.
The section below describes the main connection options. Power BI ODBC database connection also offers some advanced options where you can provide a SQL statement to specify what data to return, e.g. to limit the number of rows returned or to exclude unnecessary fields or columns. Expand the Advanced Options area and type in your SQL statement. You can also specify here properties for Power BI ODBC connection string.
Native connection to traditional databases and cloud-based services from Power BI using an ODBC driver. No need to use client software to establish Power BI ODBC connection and fetch your sales figures table from databases such as Oracle, MySQL, PostgreSQL, or cloud services such as Salesforce, QuickBooks, or Zoho CRM.
SQL data type mapping
Full support and mapping of data source-specific SQL data types to the standard ODBC data types. Access diverse data stores from Power BI through a unified interface with exceptional speed. You can create custom mappings between data source fields and ODBC data types.
Devart ODBC drivers use SSL / SSH and HTTPS tunneling to protect user data as it flows between a remote server and Power BI over the Internet. Tunneling allows you to send communications securely between machines.
ANSI SQL-92 standard support for cloud services
Devart ODBC drivers address one of the major challenges of retrieving data from email marketing, CRM, accounting and other cloud applications into Power BI: their lack of adequate API support for SQL-92 standard SQL commands. Our drivers are designed to get the most out of cloud service APIs.
Why pick Devart ODBC Drivers for Power BI?
Fully Unicode-compliant ODBC drivers
Unicode is the universal standard for encoding characters in most languages. Our ODBC drivers provide a Unicode-compliant way to work with strings and characters in databases: they are capable of mapping Unicode function calls and string data types to ANSI encoding.
All major versions of data stores are supported
Our ODBC drivers can be used to interact and perform SQL CRUD operations on popular traditional databases and cloud services from Power BI, such as MySQL, Oracle, PostgreSQL, Salesforce, or Mailchimp.
Regular feature updates and timely bug fixes
We frequently release new versions of ODBC drivers with new capabilities or enhancements for already existing features. Changes and new features are added to the roadmap based on customer feedback. Bug fixes and minor improvements are released as nightly builds to resolve issues in a timely fashion.
Strict compliance with Microsoft ODBC 3.51 Specification
Our ODBC drivers provide full support for the standard Open Database Connectivity API functions and data types. ODBC interface allows Power BI to connect to ODBC databases using the SQL language. Additionally, we have designed Advanced Connection String parameters that implement different types of behavior.
Exceptional SQL query performance and bulk updates
Our ODBC drivers allow you to perform bulk record update in Power BI in a single update statement while maintaining decent update speed. When you need to update a table with a million of records, you can use a single update operation which significantly reduces execution time compared to row-by-row operations.
Team of expert support engineers and community of developers
Contact our friendly knowledgeable support team for assistance if have any questions about using our ODBC drivers with Power BI, or join our forum to discuss anything related to the database application development with other professional developers.
Connect Power BI to your data source with our ODBC connectors
Our drivers provide Direct access to your databases and clouds from Power BI, which eliminates the use of database client libraries, simplifies the deployment process, and extends your application capabilities.
View all drivers
Choose Devart ODBC driver for your data source
You can try out any of these for free
Importing SQL Server Data into Power BI Through an ODBC Connection
Power BI is a polular business intelligence solution which is comprised of services, apps, and connectors that allow you to pull in raw data from various sources and create meaningful reports. To connect Power BI to a data source such as SQL Server, you can use a corresponding ODBC driver.
This tutorial explores how to connect to SQL Server and import data into Power BI Desktop using an ODBC driver. It is assumed that you have already installed and configured a DSN for ODBC driver for SQL Server.
- Run Power BI Desktop and click Get Data.
- Select the Other category in the Get Data dialog box, then select ODBC. Click Connect to confirm the choice.
- In the From ODBC dialog box, expand the Data Source Name (DSN) drop-down list and select the previously configured DSN for SQL Server
- If you would like to enter a SQL statement to narrow down the returned results, click the Advanced options arrow, which expands the dialog box, and type or paste your SQL statement.
- Click OK. If your data source is password-protected, Power BI will prompt you for user credentials. Type your Username and Password in the respective fields and click.
- Now you should see the data structures in your data source. You can preview the contents of the database objects by clicking on them.
- To load the SQL Server data into Power BI for analysis, select the needed table and click Load.
A few weeks ago I received a great tip from Simon Nuss about a Power Query feature relating to ODBC data sources that I hadn’t seen before (although it’s in Power BI Desktop and Excel 365, so it can’t be that new) and which is potentially important for the performance of data refresh and of the Power Query Editor’s preview pane. It’s only visible if you click “Advanced options” when creating your ODBC data source:
It’s the “Supported row reduction clauses” option:
What does it do? Well, first of all let’s see what happens if you don’t set it. Let’s say you have an ODBC data source that uses the Microsoft ODBC Driver for SQL Server to connect to the Adventure Works DW database. If you create a new query that uses this data source to get the contents of the DimDate table it works pretty much as you’d expect; here’s the M code:
However, if you look in Profiler when the contents of the Preview window is refreshed, you’ll see that the Power Query engine is running a query that gets the entire contents of the table:
Obviously, if you are connecting to a big table this could make the Power Query Editor slow. What’s more, as this blog post shows, the Power Query Editor only actually wants to display 1000 rows and the fact that the query is requesting all the rows from the table suggests that even basic query folding is not happening on this ODBC data source.
Now let’s see what happens if you do select something from the “Supported row reduction clauses” dropdown. As the screenshot above shows, there are four values in the dropdown that relate to the form of SQL supported by your ODBC source to only get the first N rows from a table. If you don’t know which one to choose you can click the Detect button and it will select one for you. In this case I selected TOP:
Having done this, when you connect to the same DimDate table, you’ll see that now the Power Query Editor is only requesting the top 1000 rows from the table:
Also, if you look at the M code for the query, the Odbc.DataSource function has the as-yet undocumented SqlCapabilities option set in its second parameter,
Actually it’s not quite undocumented because it is mentioned here as something that is only available if you’re using Odbc.DataSource in a custom connector; obviously things have changed.
Setting this option can make a big difference to the responsiveness of the Power Query Editor when you’re working with large tables and/or slow data sources – I saw this myself this week when working with one of my customers. It’s also going to be important if you use the Keep Rows/Keep Top Rows button on the ribbon or the Table.FirstN() M function; I suspect there are other, more obscure, reasons why it might speed refresh up even if you aren’t filtering the rows of the table but I’m not 100% sure.
One last thing to mention is that there is another undocumented function which is what, I think, the Detect button in the screenshots above uses to find out how to set the SqlCapabilities option. It’s called Odbc.InferOptions and it can be used like so:
This function returns a record containing a field called LimitClauseKind that tells you the type of limit clause that is supported:
Here’s a list of the possible LimitClauseKind values I found using #shared:
Bi odbc power
- 3 minutes to read
|Release State||General Availability|
|Products||Power BI (Datasets)|
Power BI (Dataflows)
Power Apps (Dataflows)
Dynamics 365 Customer Insights
|Authentication Types Supported||Database (Username/Password)|
Default or Custom
|Function Reference Documentation||Odbc.DataSource|
Some capabilities may be present in one product but not others due to deployment schedules and host-specific capabilities.
Before you get started, make sure you've properly configured the connection in the Windows ODBC Data Source Administrator. The exact process here will depend on the driver.
- Advanced options
- Connection string (non-credential properties)
- SQL statement
- Supported row reduction clauses
Connect to an ODBC data source from Power Query Desktop
To make the connection, take the following steps:
Select the ODBC option in the Get Data selection.
In From ODBC, select the data source name (DSN) from the Data source name (DSN) drop-down box. In this example, a DSN name of SQL Server Database was selected.
You can also choose Advanced options to enter more optional connection information. More information: Connect using advanced options
Once you're done, select OK.
If this is the first time you are connecting to this database, select the authentication type and input your credentials when prompted.
The authentication types available are:
- Default or Custom: Select this authentication type when you don't specify any credentials if you're using DSN configured with a username and password. Or, if you need to include credentials as connection string properties.
- Windows: Select this authentication type if you want to connect using Windows authentication. Optionally, include any connection string properties you need.
- Database: Select this authentication type to use a username and password to access a data source with an ODBC driver. Optionally, include any connection string properties you need. This is the default selection.
More information: Authentication with a data source
Once you are done, select Connect.
In the Navigator, select the database information you want, then either select Load to load the data or Transform Data to continue transforming the data in Power Query Editor.
Connect to an ODBC data source from Power Query Online
To make the connection, take the following steps:
From the Data sources page, select ODBC.
In the ODBC page, enter your ODBC connection string. In this example, the connection string is .
If needed, select an on-premises data gateway in Data gateway.
Choose the authentication kind you'll use to sign in, and then enter your credentials.
In the Navigator, select the database information you want, and then select Transform data to continue transforming the data in Power Query Editor.
Connect using advanced options
Power Query provides a set of advanced options that you can add to your query if needed.
|Connection string (non-credential properties)||Provides an optional connection string that can be used instead of the Data source name (DSN) selection in Power BI Desktop. If Data source name (DSN) is set to (None), you can enter a connection string here instead. For example, the following connection strings are valid: dsn=<myDSN> or driver=<myDriver>;port=<myPortNumber>;server=<myServer>;database=<myDatabase>;. The characters can be used to escape special characters. Keys for connection strings will vary between different ODBC drivers. Consult your ODBC driver provider for more information about valid connection strings.|
|SQL statement||Provides a SQL statement, depending on the capabilities of the driver. Ask your vendor for more information, or go to Import data from a database using native database query.|
|Supported row reduction clauses||Enables folding support for Table.FirstN. Select Detect to find supported row reduction clauses, or select from one of the drop down options (TOP, LIMIT and OFFSET, LIMIT, or ANSI SQL-compatible). This option is not applicable when using a native SQL statement. Only available in Power Query Desktop.|
View all page feedback
Connect to data by using Power BI Desktop generic interfaces
You can connect to a multitude of different data sources in Power BI Desktop, using built-in data connectors that range from Access databases to Zendesk resources, as shown in the Get Data window. You can also connect to all sorts of other data sources to further expand your connectivity options, by using the generic interfaces (such as ODBC or REST APIs) built into Power BI Desktop.
Power BI Desktop data interfaces
Power BI Desktop includes an ever-growing collection of data connectors that are built to connect to a specific data source. For example, the SharePoint List data connector provides specific fields and supporting information during the connection sequence that are designed for SharePoint Lists, which is the case with other data sources found in the window that appears when you select Get data > More... from the Home ribbon.
In addition, Power BI Desktop lets you connect to data sources that aren't identified in the Get Data lists, by using one of the following generic data interfaces:
- OLE DB
- REST APIs
- R Scripts
By providing the appropriate parameters in the connection windows that these generic interfaces provide, the world of data sources you can access and use in Power BI Desktop grows significantly.
In the following sections, you can find lists of data sources that can be accessed by these generic interfaces.
Can't find the data source you wanted to use with Power BI Desktop? Submit your idea to the Power BI team's list of ideas and requests.
Data sources accessible through ODBC
The ODBC connector in Power BI Desktop lets you import data from any third-party ODBC driver simply by specifying a Data Source Name (DSN) or a connection string. As an option, you can also specify a SQL statement to execute against the ODBC driver.
The following list details a few examples of data sources to which Power BI Desktop can connect by using the generic ODBC interface.
Data sources accessible through OLE DB
The OLE DB connector in Power BI Desktop lets you import data from any third-party OLE DB driver simply by specifying a connection string. As an option, you can also specify a SQL statement to execute against the OLE DB driver.
The following list details a few examples of the data sources to which Power BI Desktop can connect by using the generic OLE DB interface.
Data sources accessible through OData
The OData connector in Power BI Desktop lets you import data from any OData URL simply by typing in or pasting the OData URL. You can add multiple URL parts by typing or pasting those links in the text boxes provided in the OData feed window.
The following list details a few examples of the data sources to which Power BI Desktop can connect by using the generic OData interface.
|Power BI Desktop generic connector||External data source||Link for more information|
|OData||Coming soon||Check back soon for OData data sources|
Data sources accessible through REST APIs
You can connect to data sources using the REST APIs and thereby use data from all sorts of data sources that support REST.
The following list details a few examples of the data sources to which Power BI Desktop can connect by using the generic REST APIs interface.
Data sources accessible through R Script
You can use R scripts to access data sources, and use that data in Power BI Desktop.
The following list details a few examples of the data sources to which Power BI Desktop can connect by using the generic R scripts interface.
There are all sorts of data sources you can connect to using Power BI Desktop. For more information on data sources, check out the following resources:
- Wedding venues schaumburg
- Leviton double switch
- Whens daytona 500
- Grady white 215
- Printer prices walmart
- Simply storage orange
- Rv residential fridge
- Unemployment benefits pa
- Wild control warlock
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:
- OLE DB
- 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
- Install the driver for the data source and configure the ODBC Data source
- Connect to the ODBC source from Power BI Desktop
- Add the Datasource to Power BI Gateway
- 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)
- The ODBC Microsoft Access Setup dialog box opens.
- The Select Database dialog box opens.
- Browse to the drive where your data is located
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
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
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
- Expand Gateway connection
- Select Maps to and select the name of the data source
You can now do On demand and scheduled refresh.
Happy reporting 🙂