Connecting Azure Analysis Services to a PostgreSQL Database

What you need before you start

Azure Analysis Services can read data from PostgreSQL, but the connection requires three things in place: a PostgreSQL database that Analysis Services can reach over the network, credentials with read access to that database, and the PostgreSQL ODBC driver installed on the machine running Analysis Services. If any of these is missing, the connection will fail at a specific point, and you'll know which one.

The connection itself happens through a data source you create inside Analysis Services, not through the Azure portal. You define the connection once, and then any model in that Analysis Services instance can use it. PostgreSQL connections are treated the same way as SQL Server or other relational databases — Analysis Services doesn't care which database engine sits behind the ODBC driver.

Key Takeaways

Azure Analysis Services connects to PostgreSQL through an ODBC driver, which must be installed on the machine or virtual machine running Analysis Services.
Your PostgreSQL server must allow inbound connections from the Analysis Services machine, either through a firewall rule or by being on the same network.
You create the connection inside Analysis Services by adding a data source with the PostgreSQL ODBC connection string, not through Azure settings.
The PostgreSQL user account you use needs SELECT permission on the tables you want to import, but does not need administrative rights.
Test the ODBC connection on the Analysis Services machine itself before you try to use it in a model, because that's where the driver actually runs.

Installing the PostgreSQL ODBC driver on your Analysis Services machine

Analysis Services runs on a Windows Server virtual machine in Azure (if you're using the cloud version) or on an on-premises server. That machine needs the PostgreSQL ODBC driver installed — the one from the PostgreSQL project itself, not a third-party version. Download it from postgresql.org/ftp/odbc/versions and choose the version that matches your Windows installation (32-bit or 64-bit). If you're unsure which one your Analysis Services instance uses, check the Analysis Services installation folder — it will be either Program Files or Program Files (x86).

Run the installer and accept the defaults. The driver installs into the Windows ODBC Data Source Administrator, which you'll use in the next step. After installation, restart the Analysis Services service so it can see the new driver. If Analysis Services is running as a service account (which it usually is in production), make sure that account has permission to use the ODBC driver — this is normally automatic, but if you hit permission errors later, this is where to look.

Setting up the ODBC connection string

Open the ODBC Data Source Administrator on the Analysis Services machine. On Windows Server, search for "ODBC" in the Start menu and open the 64-bit version (or 32-bit if that's what Analysis Services uses). Click "Add" under the System DSN tab — this creates a connection that any service on the machine can use, not just your user account.

Select the PostgreSQL driver from the list and click Finish. A configuration window opens. Fill in the fields: Server (the hostname or IP of your PostgreSQL server), Database (the name of the database you want to connect to), User Name (a PostgreSQL user with read access), and Password. Leave Port as 5432 unless your PostgreSQL server uses a different port. Click Test to verify the connection works — if it fails here, the problem is either network access, credentials, or the PostgreSQL server itself, not Analysis Services.

Once the test passes, give the DSN a name you'll recognize — something like "PostgreSQL-Production" or "PostgreSQL-Analytics". Write this name down; you'll use it when you create the data source in Analysis Services.

Creating the data source inside Analysis Services

Open your Analysis Services project in Visual Studio or SQL Server Data Tools. In Solution Explorer, right-click the Data Sources folder and select New Data Source. The Data Source Wizard opens.

On the first page, click New to create a new connection. In the Connection Manager dialog, select "ODBC" from the provider dropdown. In the Connection String field, type the DSN name you created in the ODBC Administrator — for example, DSN=PostgreSQL-Production. Do not type the full connection string with server and password; the DSN already contains all of that.

Click Test Connection. If it fails, go back to the ODBC Administrator on the Analysis Services machine and verify the test there still passes. If the ODBC test passes but this one fails, the issue is usually that the Analysis Services service account doesn't have permission to use the DSN — in that case, create a User DSN instead of a System DSN and run Analysis Services under your own account temporarily to test.

Once the connection test passes, click OK and finish the wizard. The data source now appears in your project and is ready to use in a model.

Configuring network access from Analysis Services to PostgreSQL

If your PostgreSQL server is on-premises and Analysis Services is in Azure, or if they're on different networks, you need to allow traffic between them. On the PostgreSQL server, check the pg_hba.conf file (usually in the PostgreSQL data directory) and verify that it allows connections from the Analysis Services machine's IP address. A line like host all all 203.0.113.45/32 md5 would allow the machine at 203.0.113.45 to connect using password authentication.

If both are in Azure, place them on the same virtual network and subnet if possible — this avoids firewall configuration. If they must be on different networks, create a Network Security Group rule on the PostgreSQL server's subnet that allows inbound traffic on port 5432 from the Analysis Services machine's IP or subnet.

If you're testing from your own machine first, add your machine's IP to the PostgreSQL firewall rules temporarily, test the ODBC connection, and then remove it once you've confirmed the setup works.

Importing tables and building the model

Once the data source is created and tested, you can import tables from PostgreSQL into your Analysis Services model the same way you would from SQL Server. In Visual Studio, right-click the data source and select Import Tables, or use the Table Import Wizard. Select the tables you want to include — Analysis Services will read their structure and copy the data into the model's in-memory cache.

The first import can take a while if the tables are large, because Analysis Services is reading all the data over the network. After that, you can set up a refresh schedule so the model stays current with changes in PostgreSQL. The refresh runs on the Analysis Services server itself, so it uses the ODBC connection you configured.

If an import fails with a permission error, the PostgreSQL user account doesn't have SELECT permission on that table. Ask your PostgreSQL administrator to grant it: GRANT SELECT ON table_name TO username;

Troubleshooting common connection problems

If the ODBC test passes but Analysis Services can't use the connection, the issue is usually the service account. Analysis Services runs as a service account (often something like NT SERVICE\MSOLAP$INSTANCE), and that account needs permission to use the ODBC DSN. The simplest fix is to create a User DSN instead of a System DSN, but in production you should add the service account to the ODBC permissions. Check the ODBC Data Source Administrator on the Security tab.

If the ODBC test itself fails, verify the PostgreSQL server is running and listening on the port you specified. On the PostgreSQL server, run netstat -an | grep 5432 (on Linux) or check Services on Windows. If the port isn't listening, PostgreSQL isn't running or isn't configured to accept network connections. Check the PostgreSQL log file for errors.

If the connection times out, the firewall is likely blocking traffic. Verify the PostgreSQL server's firewall allows the Analysis Services machine's IP on port 5432. If both are in Azure, check the Network Security Group rules. If both are on-premises, check the Windows Firewall on the PostgreSQL server or any network firewall between them.

Frequently Asked Questions

Can I use a PostgreSQL connection string instead of a DSN?

Yes. Instead of creating a DSN in the ODBC Administrator, you can type the full connection string directly in Analysis Services: Driver={PostgreSQL ODBC Driver};Server=hostname;Port=5432;Database=dbname;Uid=username;Pwd=password;. This is useful if you want to avoid creating a DSN on every machine, but it stores the password in the connection string, which is less secure.

What if my PostgreSQL server requires SSL encryption?

Add SSLMode=require to the ODBC connection string or DSN configuration. The PostgreSQL ODBC driver supports SSL connections. If the server uses a self-signed certificate, you may need to add SSLMode=allow instead, though this is less secure.

Can Analysis Services read from PostgreSQL views?

Yes. Views appear in the Import Tables list just like regular tables. The PostgreSQL user account needs SELECT permission on the view, not on the underlying tables.

How often does Analysis Services refresh data from PostgreSQL?

You control this by setting a refresh schedule in the model. You can refresh manually, on a schedule (hourly, daily, weekly), or on demand through TMSL or PowerShell. Each refresh re-reads the data from PostgreSQL and updates the in-memory cache.

What if I need to change the PostgreSQL password later?

Update the password in the ODBC Data Source Administrator on the Analysis Services machine, or in the connection string if you're using one instead of a DSN. The change takes effect the next time Analysis Services connects to PostgreSQL.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.