The following instructions show how to establish a local connection, using Alteryx, to an Optilogic model that resides within our platform. These instructions will show you how to:
- Enable firewall access from your local machine
- Download and Install PostGres ODBC drivers needed to view Cosmic Frog models
- Connect to the Cosmic Frog model with Alteryx
- Download data from Optilogic locally in Alteryx
- Upload data to Optilogic from a local action in Alteryx
Enable Firewall Access
To make a local connection you must first open a Firewall connection between your local IP address and the specific database you are trying to access. Right click the .frog model where you want to make the connection and select "Open in Cloud Storage."
Click the button to "Authorize Current IP Address."
Connection Paths to your Database
Selecting the "Show Connection Info" button will provide the details required to connect to your personal database. The connection strings and paths required to make a connection to this database are located on this screen. Each database has a unique connection string. We have connection information for the following:
For this example, we will copy and paste the strings for the ‘PSQL’ connection. The screen should look something like the following (with database names and passwords not blacked out):
Postgres ODBC Installation
Many tools, including Alteryx, use Open Database Connectivity (ODBC) to enable a connection to the Cosmic Frog model database. To access the Cosmic Frog model, you will need to download and install the relevant ODBC drivers. Latest versions of the drivers are located here: https://www.postgresql.org/ftp/odbc/versions/msi/
The instructions below use the file: psqlodbc_13_02_0000-x64.zip and run the psqlodbc_x64.msi that are located on the bottom of the versions page.
When installing, use the default settings from the installation wizard.
Connecting to Cosmic Frog within Alteryx
At this point we have the pieces to make a connection in Alteryx. Open Alteryx and start a new Workflow. Drag the Input Data action into the Workflow and click to “Connect a File or Database.”
Select “Data sources” and scroll down to select “PostgresSQL ODBC”
On the next screen click “ODBC Admin” to setup the connection.
Click “Add” to create a new connection and then select “PostgreSQL ANSI(x64)” then click “Finish.”
Now we need to configure the connection with the information we gathered from the connection strings.
“Data Source” and “Description” allow you to name the connection, these can be named whatever you wish.
Copy the values for “Server”, “Database”, “User Name”, “Password” and “Port” from the connection string information copied from Optilogic Cloud Storage (see above).
DON’T FORGET to select “require” in “SSL Mode”
You may click “Test” to confirm the connection works or click “Save.”
Now select the new connection, in this example “Alteryx Demo Model” and click “OK”
Now we need to select the same Data Source that we just built in ODBC within Alteryx. We need to enter the username and password for the connection for Alteryx authentication. These are the same credentials used to setup the ODBC connection. Remember to use your specific model’s credentials from the Connection String in the Optilogic platform Cloud Storage page.