In this article
Once an ODBC data source has been created in Windows you can use this to pull data from Penelope into another analysis tool such as Excel.
Creating an ODBC Data Connection
To pull data into Excel you will need to create a data connection on a worksheet using the following instructions:
Open Excel (blank worksheet) and navigate to the Data tab, in the Get External Data section select From Other Sources > From Data Connection Wizard. If you do not see From Data Connection Wizard in the list of options you can enable it from Excel > Options > Data.
NOTE: if you have a new version of excel you will have to make the legacy options available again by following the following steps:
1.1 - go to File > Options > Data and enable the Show legacy data import wizards
1.2 - once done you have saved, go to Get Data > Legacy Wizards and then From Data Connection Wizard
Select ODBC DSN from the Data Connection Wizard and click Next.
Select the name of the data source you created and click Next.
A listing of all Penelope tables and views available to pull data from will be displayed. To Expand the window drag the bottom right-hand corner as the list is quite long. At this point you should refer to the Standard Views and Data Dictionaries (rpt schema views) or Penelope ERD to get a better understanding where specific Penelope data resides. Note: You can use the scroll bar to find the table/view you would like to pull data from.
Once you have selected a table of view (from the previous list) click Next and then click Finish.
Select the worksheet and beginning cell where you would like to place the data and click OK. This will pull the data into a table in Excel. Note: It is recommended to leave the default Table format selected and use the resulting table to create any other pivot tables or charts needed for your analysis.
This data connection is now a live view of your Penelope data (e.g. if you make a change in Bonterra and then click Refresh from the External Table Data section, you will see the change reflect in your Excel table).
Updating an ODBC Data Source/Excel Connections
If you have existing Excel workbooks with data connections to your Penelope database there may be a need (on some occasion) to update one or both of the ODBC data sources in Windows and/or data connection/s in Excel. This would be necessary for the following situations:
The SQL database account that was originally set up to work with these reports needs to be changed.
Bonterra software has made changes to the server on which your database resides that impacts a configuration setting for connecting to Penelope (e.g. moving from self-hosted to SaaS, although this should rarely occur).
The following instructions explain how to ensure both your ODBC data source and Excel connections are working correctly and together if a change is required:
To change the account on the ODBC data source or to edit the configuration settings, follow the Create an ODBC Data Source instructions and instead of adding a new data source you can edit the settings of an existing data source by clicking the Configure button. Depending on your agency’s network and security policies you may not have permission to create/edit ODBC data sources on your computer. If this is the case please contact your System Administrator.
Update the settings as required and re-test the connection to ensure a successful message is displayed and then click the Save button. Note: You do not need to change the Data Source Name (my_penelope in the below example as this name is used in your Excel workbook).
Once the data source is successfully connecting, open any existing Excel workbooks that use the same data source and check all Excel data connections within each workbook. To open existing connections from the Data tab click on the Connections button in the Connections section of the ribbon.
In the Workbook Connections dialog box, each item in the list represents a connection to the data source. Select an item and click on the Properties button.
In the Connection Properties dialog box, switch to the Definition tab to view the text in the Connection String field. You can drag the bottom right corner to expand the window so it’s easier to see the entire text if needed.
Important Information:
You may see a long version of the connection string that includes configuration settings -already set up in your ODBC data source- explicitly stated in the string (i.e. database, server, port etc) – This will need to be changed.
You may see just DSN=<data source name> – if the name matches the name configured in your ODBC data source in Windows – You do not need to change anything.
You may also see the database name configured in the Command Text SQL statement – The database name should be removed.
Adjust the above fields to simplify the Connection String to just specify the DSN=<data source name> and the Command Text to remove the database name (if needed) and click on the OK button to save the changes.
Note: The Command Text field contains the SQL statement used to query data and will be different depending on the table/s or view/s you are pulling data from and any other conditions, parameter etc you have added to filter the data you are pulling.