Attention: Athena is now Bonterra. Please bear with us as we update our screenshots to match our new name
Article Contents
The purpose of this help guide is to describe how to access data from your Penelope database using a database account (provided to you by Penelope Technical Support) and using a tool such as Excel, Tableau or PowerBI.
Accessing Penelope data in this way will allow you to “pull” data directly from any table and/or view within your Penelope database, into another data analysis tool without having to use the Penelope web application.
Important Note: Mac/Apple devices are not currently supported for use with ODBC. Only Windows devices running the PostgreSQL drivers below are officially supported. You and your IT team may still be able to establish an ODBC connection on a Mac device, however we cannot provide any support or troubleshooting assistance for this as it falls outside of our scope.
Penelope Database Account
To connect to your Penelope database, you will require a database account. To request a database account please log a ticket within the Penelope Support Team.
Someone from Support Team will be in contact with you to get this setup. Once the account is created you will be provided with the connection information, including a username and password, that you will need to configure on your computer as described below.
How to Setup an ODBC Data Source in Windows
Microsoft Excel pivot tables can be a valuable tool for generating custom reports, analyzing data and exporting data to CSV or other delimited formats. An ODBC (Open Database Connectivity) data source is typically only needed for Microsoft Excel or Access as other software, such as Tableau Desktop, allows the user to connect directly to a PostgreSQL database using a database account.
By using an ODBC data source, it is possible for authorized users to export data from Penelope using pivot tables or other report building applications. You should only allow authorized users to setup ODBC data sources on their computers as this will provide access to all of the data in Penelope .
To create an ODBC data source, you will need to contact Penelope Support to set up a database account and provide you with the following information:
Server
Port
Database name
Username
Password
You will also need to determine if you have the appropriate ODBC driver installed on your computer. The ODBC driver will either be the 32-bit or 64-bit driver and must match the version of the tool into which you will be pulling data. If you are using Excel, you can determine if you’re running the 32-bit or 64-bit version of Excel by opening the “About Excel” dialog box:
If you don’t have the appropriate ODBC driver installed on your computer, you can download and install the driver by following the instructions in the next section.
PostgreSQL ODBC Driver Installation Instructions
The ODBC driver listed below is for clients using Resin 4 and Postgres 9+. If you are using Resin 3 and Postgres 8 please contact the Penelope Support Team.
To install the ODBC driver on a Microsoft Windows machine:
Unzip the driver file on to the Desktop.
Double click the Psqlodbc.
Accept the License and click Next.
You may need to restart your computer after the upgrade.bat file has finished
You can download Version 13.02 ODBC driver here:
An ODBC driver is generally only needed for Microsoft Excel or Access as other software, such as Tableau Desktop, allows the user to connect directly to a PostgreSQL database using a database account. Depending on your agency’s network and security policies you may not have permission to install software on your computer. Please contact your System Administrator if this is the case.
It is generally recommended to use the most recent driver in the list from the link above, but many users may experience performance issues with using a 13 driver. If this is the case, please switch to 12.02.
Driver Details (Self-Hosted Databases)
Self-hosted clients should enter the ODBC details listed below when configuring their ODBC connection:
Database = bonterraDB (or whatever database name you have been assigned)
Server = Server IP address
Port = 5432
SSL Mode = Require
Username – pt_user (whatever the username is provided to you by your IT for your ODBC account)
Password – password (whatever the password is provided to you by your IT for your ODBC account)
Save/OK when you are done entering this information.
Your IT team will need to make some modifications on your server (add pivot table user accounts, modify the pg_hba.conf file etc) for the ODBC driver to be able to connect. For instructions on server modifications please submit a ticket including the name of your organization and the operating system of your server.
Alert
Bonterra Software does not support the installation, configuration or use of pivot tables as part of any support packages or on-demand services.
The above instructions are for computers running recent versions of Microsoft Windows only and although it is possible to use ODBC connections on Macs for reporting purposes, Bonterra Software is unable to provide instructions or support for this.
Create an ODBC Data Source
Once you have the connection information and correct ODBC driver installed, complete the following instructions to set up an ODBC data source in Windows:
From your Window Start menu, start to type Admin and you should see Administrative Tools appear. Click on this to display the Administrative Tools.
Note: On Windows 11, the name for this section has been changed to Windows Tools.
From the list of items within Administrative Tools select ODBC Data Sources (64-bit) or (32-bit) depending on the version of the tool you are wanting to use the ODBC connection with.
From the ODBC Data Source dialog click the Add button and then from the Create New Data Source dialog click on PostgreSQL Unicode(x64) or PostgreSQL Unicode(x32), depending on your operating system. Then click the Finish button. Depending on your agency’s network and security policies you may not have permission to add an ODBC Data Source on your computer. Please contact your System Administrator if this is the case.
From the PostgreSQL Unicode ODBC Driver Setup dialog enter the connection information you were given by Penelope Technical Support in the corresponding boxes and then click on the Test button to make sure the connection is successful – if successful, Save the connection setup.
Useful Information
You can change the Data Source text (default is PostgreSQL35W) to something easier to remember such as my_penelope since you will need to remember and enter this text in Excel to complete the connection.
Ensure the SSL mode setting is changed from the default disable to require.