Accessing the ODS thorough ODBC

Some tools, particularly those produced by Microsoft, access databases through a method known as Open Database Connectivity or ODBC.





General information

You will need to have Oracle client software installed.

NOTE: If you do not already have the Oracle Client software installed, we have an installer available. Please first read the Client Installer Instructions, and then run the Oracle Client Installer.

If you already have Discoverer Desktop or the Information Warehouse  installed, you already have most of the necessary pieces in place. However, you will need to:

  1. Locate the tnsnames.ora file on your PC and rename it to tnsnames.old.ora.
  2. Then download the current tnsnames.ora file.  This file contains the latest connection information for the Information Warehouse, ODS and SIS. 
  3. Save this file to the same location as your current tnsnames.ora file, making sure that the file does not have a .txt extension.

Note: you may find you have multiple tnsnames.ora files on your PC. Any in a sample directory may be ignored. Otherwise it is best to download the new file and replace all the tnsnames.ora files you find.

Fortunately, the more current versions of Windows typically have this driver included. To find out whether you have the driver, click Start, Settings, and Control Panel. Double click the Administrative Tools folder, then the ODBC Data Sources Control Panel. When it comes up, select the Drivers tab. Make sure the Oracle ODBC Driver or the Microsoft ODBC for Oracle is listed.

Assuming the driver is present, you must configure a ODBC Data Source for the ODS. Select the System DSN tab of the ODBC Control Panel. If you are not using Windows NT and you do not have Administrator privileges, you may not be able to use this tab. If so, use the User DSN tab.

Click the Add button. Select the Microsoft ODBC for Oracle driver (or Oracle ODBC Driver) and click Finish.

For Data Source Name enter ODS. You can really name it anything you like; just remember what you name it. You need not enter anything in the Description field. In User Name enter your ODS Database ID. In Server (or Service Name, depending on which driver you are using), enter iods. Now click OK and you have finished creating your Data Source.

Now you can connect to the ODS using Microsoft Access or Excel (using the add-in program Microsoft Query), or other ODBC tools.

Please see the following notes for further information on how to connect to the ODS through Excel or Access. Please note, we cannot teach you to use the tools through this page; we are only providing information on connecting to the database.



From the Excel menu, choose Data, Get External Data, and then New Database Query. You will be presented with the Choose Data Source dialog box. Choose the ODS data source you created above. Depending on your familiarity with Query, you may or may not wish to uncheck the Use Query Wizard box. When you click OK, you will be presented with a login box. After logging in, you will see the Add Table box. Click the Options button. Make sure the Views box is checked, and all the other boxes are unchecked. Then click OK. This will prevent unnecessary system items being included in your list of available tables. You should only have to do this the first time.

The process may differ slightly for you if you are using an older version of Excel. These instructions were based on Excel 2000.


Once you have created or opened your database, make sure you are on the Tables tab. Right-click, and choose Link Tables. The Link dialog box will now be displayed. In the bottom left area of the box, click on the down arrow next to the Files of Type field. Scroll down until you see ODBC Databases(), and select it. You will now see the Select Data Source box. Depending on the version of the ODBC Administrator installed on your PC, this will either be a single list, or the box will have two tabs. If you have the tabbed version, select the tab Machine Data Sources . Select the ODS data source and click OK. You should now get the login screen. Once you log in, it will generally take several moments to display the Link Tables dialog box. This is because the list is quite long. In addition to the many views you may wish to use, the list includes a large number of system objects that you do not need and some of which you cannot use. Unfortunately, Access does not provide a way to exclude these other objects. The views you need will begin with either APPS (if it is a U.Va. custom object), or with NOETIX_SYS (if it is a Noetix view). If you press the n key, Access will automatically jump to that portion of the list. Select the table or tables to which you wish to link. Linking for the first time may take a while; do not select more tables than you need. You will now see the Select Unique Records Identifier dialog box. Click cancel; the box will appear once for each table you selected--click cancel each time. (If you do choose items in this box, your queries may product unpredictable results.) You will now see the ODS tables you selected listed in the Tables section of your database, and can use them just like you would Access tables.