Page History

A practical guide to setting up an IUIE connection for the most common uses within SOIC

Note: the UITS KB page "At IU, how do I download Download and install the Oracle Database 11g Release 2 12c client for 64-bit Windows?Windows at IU" contains some useful data, however this installation is not optimal for most of our users.

...

1. Verify that existing system and software are 64 bit (if 32 bit, make the obvious adjustments to what follows):

   1. Check PC Info for 64 bit OS

   2. Check Tableau (Help->About) for 64 bit version
   3. Check Excel (File->Account->About Excel) for 64 bit version
2. Install Oracle Data Access Components (ODAC) – login as admin:
   1. from \\bl-soic-gemini\software\Oracle\ODAC1120320ODAC122011_x64 or Staff Drop Box\Tableau Repository\Oracle_Install\ODAC1120320ODAC122011_x64 (for 32 bit Excel use ODAC1120320_32bit)
   2. or from Oracle Download
   3. run the Oracle Universal Installer by starting the setup application
   4. in C:\app create folder C:\app\Oracle (should happen automatically using step (e) below)
   5. in Install Location change Oracle Base from C:\app\<your-username> to C:\app\Oracle
   6. in Available Product Components deselect Developer Tools for Visual Studio, Samples and Documentation
3. Copy configuration files:
   1. sqlnet.ora, tnsnames.ora from \\bl-soic-gemini\software\Oracle or Staff Drop Box\Tableau Repository\Oracle_Install
   2. to C:\app\Oracle\product\1112.2.0\client_1\Network\Admin
4. Add path and environment variable:
   1. verify that Oracle client_1 and client_1\bin have been prepended to PATH
   2. create system environment variable TNS_ADMIN=C:\app\Oracle\product\1112.2.0\client_1\Network\Admin
5. Configure ODBC DSN (Control Panel->System and Security->Administrative Tools->ODBC Data Sources (64 bit or 32 bit as appropriate)):
   1. in System DSN tab, ADD Oracle in OraClient ...
   2. use DSS1PRD.UITS.INDIANA.EDU as Data Source Name and TNS Service Name
6. Have User set their Oracle password: Oracle password  (not needed if using an Oracle departmental account)
   
   1. from one.iu.edu go to IUIE and login using ADS username & password
   2. on IUIE home page click Personalize
   3. In the Administrative Information box, change password for DSS1PRD (note: Oracle password and ADS passphrase requirements make them may be mutually exclusive)
7.  User may need to request Oracle permissions for the tables to be used
   1. from IUIE home page do a master catalog search for the table name
   2. in the Report Object Metadata page fill out the access request fields

Connect Excel to Oracle Database

1. Open Excel and select a blank sheet
2. Go to the Data tab, then Get Data -> From Other Sources → From ODBC
   1. ( older Excel) :  Go to the Data tab, then Get External Data -> From Other Sources, and select From Data Connection Wizard
3. From In the dropdown list of data sources select under From ODBC DSN, then select DSS1PRD.UITS.INDIANA.EDU
4. Connect to Oracle using Oracle password as set above
5. Choose a table from the list presented.  In the future this table will be available through Existing Connections.
6. Excel connection files are located in Documents -> My Data Sources. Connection files can be edited directly, for example to add tested SQL queries.
   • But note that there are SQL syntax differences - for example CASE - END must be parenthesized (CASE - END) to work from within Excel.

...