Connecting to Optilogic With Microsoft Power BI

The following instructions show how to establish a local connection, using Power BI, 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
• Create the ODBC connection to your computer
• Connect to the Cosmic Frog model with PowerBI

Enable Firewall Access

To make a local connection you must first open a Firewall connection between your current IP address and the Optilogic platform. Navigate to the Cloud Storage app – note that the app selection found on the left-hand side of the screen might need to be expanded. Check to see if your current IP address is authorized and if not, add a rule to authorize this IP address. You can optionally set an expiration date for this authorization.

If you are working from a new IP Address, a banner notification should be displayed to let you know that the new IP Address will need to be authorized.

Connection Paths to your Database

From the Databases section of the Cloud Storage page, click on the database that you want to connect to. Then, click on the Connection Strings button to display all of the required connection information.

We have connection information for the following formats:

• JSON
• URL
• PSQL
• JDBC
• LIBPQ
• NET
• PSYCOPG2
• SQLALCHEMY

To select the format of your connection information, use the drop-down menu labeled Select Connection String:

For this example, we will copy and paste the strings for the ‘PSQL’ connection. The screen should look something like the following:

You can click on any of the parameters to copy them to your clipboard, and then paste them into the relevant field when establishing the PSQL ODBC connection.

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/releases/

From here, click on the latest parent folder, which as of June 20, 2024 will be REL-16_00_0005. Select and download the psqlodbc_x64.msi file.

When installing, use the default settings from the installation wizard.

Set up the ODBC Connection

Within Windows, open the ODBC Data Sources App (hint search: “ODBC” in your Windows spotlight search).

Click “Add” to create a new connection and then select “PostgreSQL ANSI(x64)” then click “Finish.”

Enter the details from your Cloud Storage connection — (hint: click to copy/paste)

• Data Source = free form to name the connection
• Description = free form to describe the connection
• Server = Host
• Database = DBName
• User Name = User
• Password = Password
• Port = Port
• SSL Mode = “require”

You may click “Test” to confirm the connection works or click “Save.”

Make the Connection in Power BI

Open Power BI and select “Get data from another source”

Enter “ODBC” in the Get Data window and select connect

 

Select your Database connection from the dropdown and click OK

Enter your username and password one last time from the Cloud Storage page

 

Select the tables you wish to see and use within PowerBI

Create Dashboards of Data from Cosmic Frog tables!