Connecting Alteryx to PostgreSQL and Sharing Via Gallery – A Guide

Blog | December 18, 2023 | By Jaspreet Kaur, Abhishek Ranjan

Introduction to Alteryx and PostgreSQL Integration

Alteryx and PostgreSQL Connection

Building Workflows for Alteryx and PostgreSQL

Connecting data sources to Alteryx is crucial for unlocking the full power of data analytics. While building workflows may seem straightforward, seamlessly publishing and sharing reusable connections on the Alteryx Gallery adds a layer of complexity to the task. In this blog, we’ll walk you through the steps to connect Alteryx Designer to PostgreSQL, building an end-to-end workflow. Plus, we’ll look at how to create a sharable Gallery Data Connection to enable smooth collaboration without exposing database credentials.

Read along as we explore the technical nitty-gritty behind data connectivity and accessibility in Alteryx.

Sharing Alteryx Workflows in the Gallery

Optimizing Analytics with Alteryx and PostgreSQL

Overview of Alteryx and PostgreSQL Integration

Part 1: Connecting Alteryx to PostgreSQL

Step 1: Install Alteryx and PostgreSQL Driver

Connecting Alteryx to PostgreSQL and Sharing Via Gallery - A Guide - 001
Figure-1

Step 2: Configure the Input Data Tool

  • Open a new workflow in Alteryx Designer and drag & drop the “Input Data” tool from the toolbar onto the workflow canvas.
  • Click on the “Connect a file or Database” drop-down and select “Data Sources”.
  • Choose “PostgreSQL” from the list of available databases.
list of available databases
Figure 2

Step 3: Enter PostgreSQL Connection Details

Provide the necessary connection details:

  • Click on the “ODBC Admin” under the PostgreSQL Connection tab.
under the PostgreSQL Connection tab
Figure 3
  • Select “System DNS” and then click “Add” and then select “Simba PostgreSQL ODBC Driver” then click on Finish.
Simba PostgreSQL ODBC Driver
Figure 4
  • A new Window will pop up for connection settings (Fig-5). Enter the following details.
    • Data Source Name: A descriptive name for the connection.
    • Server: Enter the Server Name.
    • Port: Enter the Port number.
    • Database: Enter the PostgreSQL database name.
    • Username and Password: PostgreSQL login credentials.
  • Click “Test Connection” to ensure the details are accurate. If successful, click “OK” to close the window.
Click Test Connection to ensure the details are accurate.
Figure 5
  • Select the New Data Source “PostgreSQL_Alteryx_Connection” and click OK.
PostgreSQL_Alteryx_Connection and click OK
Figure 6

Step 4: Select the Desired Table

  • Enter your credentials (use the same credential as used in Fig-5).
Select the Desired Table
Figure 7
  • In the Input Data tool configuration window, choose the table or write SQL Query for the table you want to work with.
  • Click on Test Query then click OK.
Click on Test Query then click OK
Figure 8
  • Below is the sample workflow.
Below is the sample workflow
Figure 9

Step 5: Publish the Workflow on Alteryx Gallery

  • Click on the File and select save as -> Alteryx Server and then select the server on which you want to publish.
Publish the Workflow on Alteryx Gallery
Figure 10
  • Click on save to publish on Alteryx Gallery.
Click on save to publish on Alteryx Gallery
Figure 11
  • When we try to publish it, we faced the below error.
When we try to publish it, we faced the below error
Figure 12

Note:

  • To resolve this error, we need to make a data connection on Alteryx Gallery. To achieve this, follow the below steps.
  • If you have to publish your workflow on the Alteryx Gallery, then download the PostgreSQL Driver and create a data connection on the Alteryx Gallery instead of Alteryx Designer. 

Part 2: Creating and Sharing a Gallery Data Connection

Alteryx allows users to create and share data connections via Alteryx Gallery, promoting collaboration and streamlined data access.

Step 1: Create a Data Connection

  • In Alteryx Gallery, click on the “Data Connection” and select “Add Data Connection.”
Create a Data Connection
Figure 13
  • Under “Data Connections,” select connection type “Other” and provide the Connection Name.
Under Data Connections, select connection type
Figure 14
  • Enter the Connection String in the below format:
odbc:DRIVER={Simba PostgreSQL ODBC Driver};UID={User Name};PWD=Password;DATABASE={Database Name};SERVER={Server Name};PORT={Port Number}
  • Click on the Save.

Step 2: Share the Connection

  • Click on the connection that you want to share with other users.
Share the Connection
Figure 15
  • Select the “+ Add User” and enter the username then click on the “Add”.
Select the plus add user
Figure 16
  • Now go to the Alteryx Designer bring the “Input Data” tool to the canvas and click on the “Connect a file or Database” drop-down.
  • Select the Gallery from the “Data Connection” tab. You can choose the desired data connection from the list.
Select the Gallery from the Data Connection
Figure 17

Alteryx and PostgreSQL Guide for Workflow Creation

Alteryx and PostgreSQL: Tips and Best Practices

Alteryx Designer and PostgreSQL Driver

Creating and sharing database connections on the Alteryx Gallery streamlines workflows for teams by providing easy access to data sources without sharing sensitive credentials. As you incorporate more diverse data and users into your Alteryx ecosystem, be sure to leverage the Gallery for its security and collaboration capabilities.

With the power of Alteryx and PostgreSQL combined, the analytics possibilities are endless. Try out these steps yourself to see how publishing connections can take your workflows to the next level. And as always, happy analyzing!

About the Author
Data visualization and analytics professional with a proven track record of delivering impactful business solutions using Tableau dashboards. Thrives on transforming complex data into clear and actionable insights.
Jaspreet KaurBI Analyst - Visual Analytics | USEReady
About the Author
Seasoned BI Analyst with proficiency in Tableau Desktop, Alteryx, Power BI, Oracle, and Tableau Prep. An adaptive and agile learner with a penchant for exploring the latest technologies.
Abhishek RanjanAssociate BI Analyst - Visual Analytics | USEReady