A practical guide to setting up an IUIE connection for the most common uses within SOIC
Note: the UITS KB page "Download and install the Oracle 12c client for Windows at IU" contains some useful data, however this installation is not optimal for most of our users.
Note: These instructions assume that the OS and all related software are 64 bit. If the user is constrained, for example, to use 32 bit MS Office, install 32 bit ODAC.
Install and configure Oracle client software on Windows
- Verify that existing system and software are 64 bit (if 32 bit, make the obvious adjustments to what follows):
Check PC Info for 64 bit OS
- Check Tableau (Help->About) for 64 bit version
- Check Excel (File->Account->About Excel) for 64 bit version
- Install Oracle Data Access Components (ODAC) – login as admin:
- from \\bl-soic-gemini\software\Oracle\ODAC1120320_x64 or Staff Drop Box\Tableau Repository\Oracle_Install\ODAC1120320_x64 (for 32 bit Excel use ODAC1120320_32bit)
- or from Oracle Download
- run the Oracle Universal Installer by starting the setup application
- in C:\app create folder C:\app\Oracle (should happen automatically using step (e) below)
- in Install Location change Oracle Base from C:\app\<your-username> to C:\app\Oracle
- in Available Product Components deselect Developer Tools for Visual Studio, Samples and Documentation
- Copy configuration files:
- sqlnet.ora, tnsnames.ora from \\bl-soic-gemini\software\Oracle or Staff Drop Box\Tableau Repository\Oracle_Install
- to C:\app\Oracle\product\11.2.0\client_1\Network\Admin
- Add path and environment variable:
- verify that Oracle client_1 and client_1\bin have been prepended to PATH
- create system environment variable TNS_ADMIN=C:\app\Oracle\product\11.2.0\client_1\Network\Admin
- Configure ODBC DSN (Control Panel->System and Security->Administrative Tools->ODBC Data Sources (64 bit or 32 bit as appropriate)):
- in System DSN tab, ADD Oracle in OraClient ...
- use DSS1PRD.UITS.INDIANA.EDU as Data Source Name and TNS Service Name
- Have User set their Oracle password:
- from one.iu.edu go to IUIE and login using ADS username & password
- on IUIE home page click Personalize
- In the Administrative Information box, change password for DSS1PRD (note: Oracle password and ADS passphrase requirements make them mutually exclusive)
- User may need to request Oracle permissions for the tables to be used
- from IUIE home page do a master catalog search for the table name
- in the Report Object Metadata page fill out the access request fields
Connect Excel to Oracle Database
- Open Excel and select a blank sheet
- Go to the Data tab, then Get External Data -> From Other Sources, and select From Data Connection Wizard
- From the list of data sources select ODBC DSN, then select DSS1PRD.UITS.INDIANA.EDU
- Connect to Oracle using Oracle password as set above
- Choose a table from the list presented. In the future this table will be available through Existing Connections.
- 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.
Connect Tableau to Oracle Database
- Under Connect, To a server, select Oracle
- enter DSS1PRD.UITS.INDIANA.EDU as Server
- provide username and Oracle password
- On the Data Source page, enter a Schema IN ALL CAPS (ex: DSS_RDS)
- Enter a Table name or partial search string IN ALL CAPS (ex: SR_STU_, Starts with)
Install and configure Oracle client software on Mac OSX
(to be continued ...)