[1]
Golden (by Benthic Software)
Golden is a query tool for Oracle databases. It includes advanced query abilities, data editing,
and powerful import and export abilities including support for Excel and Calc spreadsheets.
Golden allows approved users access to submit SQL queries to the Administrative Information
System (AIS) and return useable data in a customizable format.
Access, Installation and Configuration
Before you begin using Golden there are three requirements:
1. Access to the RPT Database:
To request your RPT ID and password, CTRL+click on the following link:
https://eforms.siu.edu/siuforms/info/ais0101.php , or use your browser and navigate to
eforms.siu.edu. In the search box enter the form name “Request for Reporting Access
RPT”. Download and complete the form. In section 3 select ‘All Financials’ to gain access
to financial transactions. If you need special access to Human Resource or Accounts
Payable data, select the appropriate box. Because of the sensitive nature of the
information in these two modules, special access will be granted on a case-by-case
basis. Once you have completed the form and obtained the necessary signatures, mail it
to (Accounting Services MC: 6812). The form will be routed to the appropriate
approvers and you will be notified when your access is ready.
2. Installation and Configuration of the Oracle Client:
The Oracle Client is software that allows Golden to communicate with AIS. A copy of the
Oracle 11 Client can be downloaded here
(https://sql.siu.edu/downloads/Golden%20Setup.zip). Your LAN administrator should
use Appendix A below to install and configure the Oracle Client.
3. Purchase, Installation, and Configuration of the Golden Software:
The Golden software can be purchased and downloaded from
Benthicsoftware.com. Each department is responsible for the software purchase and
license storage. Be sure to download the latest 32-bit version. Your LAN administrator
should accept all default settings when installing Golden. Use Appendix B below to
complete the configuration.
[2]
Using Golden
Now that you have Golden installed and configured to work with AIS, you can begin using the
software to pull data. Golden uses Oracle SQL queries, sometimes referred to as scripts, to
request data from AIS. This guide will help you access scripts, navigate Golden, run scripts and
export data to Microsoft Excel.
Script Access
To begin using scripts visit the SQL User’s Group website (https://sql.siu.edu/scripts.html). In
the blue box on the right side labeled ‘Base SQL Scripts’, are links to several basic scripts. Click
on each script’s link, download and save it to a location accessible from your computer. All SQL
scripts will end with the file extension ‘.sql’ but most computers are not configured by default
to open this type of file. Consult with your LAN administrator to set Golden as the default
application for files ending with ‘.sql’.
Navigation
Open Golden by selecting the Golden icon from the Benthic Software folder on your start
menu.
Enter your Username and Password in the appropriate boxes. Select ‘rpt’ from the dropdown
menu next to Database. Click the Login button.
NOTE: The ‘RPT’ or “Report” database is a daily copy of the AIS database. The information
stored in RPT was posted to AIS the previous day. Each night the AIS database along with all
Usernames and Passwords is copied to RPT, and therefore require special attention when it is
time to change your password. Refer to the password change instructions below on Page 8.
[3]
You are now logged into Golden and connected to the RPT database.
At the top of the window you will see your username and the database you are connected to.
Below this is the Main Toolbar. This toolbar contains many of the standard toolbar options (i.e.
open, save & print) but also contains tools to run your script and export your data output. We
will look at these options later in this document.
[4]
Below the toolbar is the query editing area. This area will display the script you intend to run
and allow you to edit the script to fit your needs. Most users will only display and run scripts
from this area. Editing scripts takes a basic knowledge of SQL.
Beneath the query editing area is the query output grid. This area is used to display the output
of your script.
Query Output Grid
[5]
Running SQL Scripts
From the File menu select ‘Open’ then ‘Open file into new tab’. Alternatively, you can select
the ‘Open file into new tab’ icon from your toolbar.
Navigate to one of the scripts you downloaded from the SQL User’s Group page and click the
Open button. For this demonstration we will use the script named ‘transbybpanddate.sql’.
The script will be displayed in the query editing area and can be modified. NOTE: If you are not
familiar with Oracle SQL you should not make modifications to the script. Even small changes
may prevent the script from running or provide incorrect output.
Notice there is nothing shown in the query output grid at this time. This area will remain blank
until you run the script. To run the script, select ‘Script’ then ‘Run script’ from your toolbar or
select the ‘Run script’ icon.
[6]
NOTE: This script will prompt you for three variables (Start Date, End Date, and Budget Purpose)
however, many scripts are written with the variables defined and will not prompt you for input.
Enter each variable and click the ‘OK’ button. Once the last variable has been submitted, the
script will run. When the script is finished running, the query output grid will be populated with
the results.
The message ‘Done’ will be displayed in the lower left corner and the number of records
retrieved will be displayed in the lower right corner.
[7]
Exporting Data
Script results displayed in Golden can be useful for a small set of data however, to be useful
most output will require further calculations. To allow further manipulation of data,
applications like Microsoft Excel may be required. Fortunately, Golden has a built-in utility to
export data from the query output grid directly to MS Excel.
Once your script has finished running and you have results displayed in the query output grid,
select ‘Results’ then ‘Export results to Excel’.
Microsoft Excel will open and your output will be displayed in a new spreadsheet.
NOTE: If you have an Excel spreadsheet open and are in the process of editing a cell while
attempting to export data, Golden will display the following error message.
[8]
Password Change
Since RPT is a daily copy of AIS all usernames and passwords are also copied from AIS to RPT.
When you receive a message indicating your password has expired follow these steps to update
your password. NOTE: You will be updating your password in the AIS database, however this will
not change your access to AIS via the web portal. This username and password combination is
only used with Golden.
1. Open Golden and enter your Username and Password in the appropriate boxes.
2. Select ‘AIS’ from the Database dropdown menu and click the ‘Login’ button.
• You may be prompted to enter a new password at this time. If so, complete the
‘Set New Password’ box as indicated and click the ‘OK’ button. If not, go on to
step 3.
3. From the ‘File’ menu select ‘Change Password’.
4. Enter the updated password information and click the ‘OK’ button.
• You have now changed your password in the AIS database and it will be copied
to the RPT database when the daily copy is made. If you would like to run scripts
in RPT today, continue to step 5, otherwise you may close Golden.
• Note: The next time you open Golden the Database dropdown will be set to ‘ais’.
Before running scripts, you must change this to ‘rpt’ and enter your new
password.
5. Log out of AIS by selecting ‘File’ then ‘Disconnect’.
6. Log in to RPT by selecting ‘File’ then ‘Login’. Enter your Username and old Password in
the appropriate boxes.
7. Select ‘rpt’ from the Database dropdown menu and click the ‘Login’ button.
• You may be prompted to enter a new password at this time. If so, complete the
‘Set New Password’ box as indicated and click the ‘OK’ button. If not, go on to
step 8.
8. From the ‘File’ menu select ‘Change Password’.
9. Enter the updated password information and click the ‘OK’ button.
10. You have now changed the password in both AIS and RPT and will be able to run scripts
at this time.
[9]
Appendix A:
Installation and Configuration of the Oracle Client
1. Copy ‘Oracle11’ folder directly under the C:\ directory.
2. Right-Click ‘This PC’ and select ‘Properties’.
3. Choose ‘Advanced system settings’ from the list on the left.
4. In the ‘System Properties’ window on the ‘Advanced’ tab select the ‘Environment
Variables’ button.
5. Under the ‘System variables’ section scroll down and select ‘Path’.
6. Click the ‘Edit’ button.
7. Click the ‘New’ button.
8. Type the path to the Oracle files c:\Oracle11 then click the ‘OK’
button
[10]
9. Under the ‘System variables’ section click the ‘New’ button to create a new system
variable
10. Enter ‘tns_admin’ in the ‘Variable name’ field and ‘c:\Oracle11’ in the ‘Variable value’
field
11. Click ‘OK’ to save it and ‘OK’ until you are back to the ‘System’ window
12. Close the window and restart your computer
[11]
Appendix B:
Installation and First Time Configuration of Golden
1. Purchase and Download the latest 32-bit version of Golden from benthicsoftware.com.
2. Double Click the Golden setup file.
3. Follow the prompts and choose the default settings
Once Golden is installed it will create the ‘Benthic Software’ folder in your program list. Inside
this folder you will find Golden.
[12]
Setting OCI.DLL Parameter in Golden
1. Open GOLDEN.
2. At the login window click the ‘Options’ button. (Note: If you do not see
the Options button click the ‘More’ button to display it.)
3. In the ‘OCI DLL Name:’ box enter the following string: ‘C:\Oracle11\instantclient_11_2\oci.dll’
4. Click ‘OK’
[13]
Logon to Golden
1. Enter your ‘Username’, ‘Password’, and ’rpt’ in the ‘Database’ box.
(Note: The first time you open Golden the ‘Database’ box will be empty.)
Click ‘Login’
Setting the Oracle Date Format
1. While logged into Golden, select ‘Tools’.
2. From the Tools drop-down menu, select ‘Program Options’.
[14]
3. Enter the ‘Oracle Date Format’: MM/DD/YYYY HH24:MI:SS
4. Click the ‘Ok & Save as Defaults’ button.
