What is ODBC

Open Database Connectivity (ODBC) is a set of database access APIs that connect to the database, prepare, and then run SQL statements on the database.

An ODBC driver application can access non-uniform data sources, such as spreadsheets, comma-delimited files, and almost any other DBMS (database management systems) such as SQL Server, Oracle, MYSQL, Intersystem etc…

Of course, there are pros and cons to using ODBC over a native connector, especially on the performance side, but the advantage of ODBC is that it allows you to connect to data sources for which there’s no available connectors or sometimes with fewer constraints.

Installing ODBC driver on the gateway

In this post, I will show how to install and use the ODBC driver for InterSystem which is a database that supports both SQL and NoSQL highly focused on healthcare or financial services, but there are plenty of other ODBC drivers out there for all sorts of databases.
For example, Databrick has also an ODBC connector that you can use to access Databrick through a gateway, this is useful for example if Databrick is using a private connectivity while your Power BI tenant does not use the private link. (Fabric private endpoint at workspace level can now solve this kind of issue as well).

I downloaded the ODBC driver from the IRIS website and I usually prefer downloading ODBC drivers directly from the official database provider to ensure compatibility and security but some websites list all the ODBC drivers.

You can also install the ODBC driver on the machine where PBI is installed so you can connect to the ODBC server locally and from the Power BI portal.

Configure the DSN (Data Source Name)

To configure the DSN on your machine, search for “ODBC” in the start menu or find it under Administrative Tools.

Once the ODBC Data Source Administrator is open, select System DSN and choose the driver you want to set up. If the driver isn’t visible, check that it’s properly installed, or you may also need to reboot your machine.

In my example, I configured a data source for the IRIS database. Make sure to give the DSN a meaningful name, as you will need this later to configure the gateway connection in the Power BI portal.

Depending on the specific driver and data source, you may need to provide additional details than the one shown below, you usually need to know the server IP, database name, port, and credentials.

Network port to open

To open the right port you will need to refer to the documentation of the database that you are working with:

• SQL Server typically uses port 1433 for TCP
• Oracle often uses ports 1521 and 1630
• IRIS generally uses port 51773

However, these settings can vary based on the database configuration, so check with your DBA or network team if needed.

Configure the data source in Fabric with DSN

Once you’ve configured the ODBC driver and the DSN on the gateway machine you’ll need to configure the datasource connection in Fabric.

Now the trick is to pass the DSN name as a connection string or server name such as “dsn=your DSN”
Be aware that Fabric is case-sensitive when auto-mapping database names. So always ensure consistent capitalization between the published Power BI file and the data source connection in the gateway, for pipeline it’s less a problem since you’re manually selecting the connection to use.

Configure the pipeline using the data source created

After configuring the gateway data source, the new connection should appear in the dropdown list of the Copy Data activity in Fabric. From there, you can select a table, write an SQL query, or a dynamic query, just like with any other connection types.

Configure Power BI Connection

To access the ODBC data source using the Power BI desktop and from your local machine, you need to repeat the steps above to configure the DSN. This time, you can use “User DSN” instead of “System DSN.” In any case, they will both work, but if it’s a company computer, you may not have permission to create a system DSN on it, so it’s best to use “User DSN” anyway.

Always make sure the DSN name matches the one configured on the Power BI data source connection to enable auto-binding between the dataset and the gateway data source. You can, of course, configure multiple DSNs, use parameters, and leverage CI/CD pipelines to programmatically change the DSN for each environment.

Conclusion

As we’ve seen, accessing and configuring an ODBC data source for Fabric is fairly straightforward. While it’s generally best to use native connectors, ODBC can sometimes offer more flexibility, especially when accessing data sources that don’t have a native Power BI connector. Even though Power BI now supports a wide range of connectors…