Connecting to the AAUDE Data Warehouse

You can connect to the AAUDE data warehouse in 2 ways:

Online, via the AAUDE Web Front End.
From your desktop, with a reporting application or a programming/scripting language that provides Oracle connectivity.

Web Front End

For information about connecting to the AAUDE data warehouse via the Web Front End, visit the AAUDE website (www.aaude.org), log in to the member section, and click the "Web Front End" button.

Desktop Connectivity: Windows

Oracle Driver and ODBC Configuration

Connecting to the AAUDE data warehouse from a Windows desktop computer generally involves...

Downloading and configuring an Oracle client. The Oracle client is what allows your computer to connect to the AAUDE Data Warehouse, which is an Oracle Database. Note: if you already have an Oracle Client installed as part of your institution's IT infrastructure, you do not need to install a new Oracle Client for AAUDE purposes. Simply ensure that your tnsnames.ora and sqlnet.ora files are properly configured
Configuring an ODBC (Open Data Base Connectivity) connection. The ODBC connection allows your Desktop Reporting Applications to talk to the Oracle Client.
Configuring a desktop reporting application or programming/scripting language to connect to the data warehouse. (See below for application- and language-specific examples).

Instructions for Oracle/ODBC installation and configuration:

Instructions for installing the Oracle Client and configuring an ODBC connection on Windows XP.
Instructions for installing the Oracle Client and configuring an ODBC connection on Windows Vista.

For Oracle to work correctly on your desktop computer, your tnsnames.ora and sqlnet.ora files must include approriate entries. These files can generally be found in the following location on your desktop computer:

C:\[PATH TO ORACLE]\network\admin\

The tnsnames.ora file defines database addresses and connections. Your tnsnames.ora should include the following entry (you can also download the file here):

aaude=
 (DESCRIPTION= 
  (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=TCP)(HOST=aaude.mit.edu)(PORT=1521))
  )
  (CONNECT_DATA=(SID=DWRHS))
 )

The sqlnet.ora file provides Oracle's SQL*Net application with basic configuration details. Your sqlnet.ora file should include the following entry (you can also download the file here):

AUTOMATIC_IPC=OFF
SQLNET.EXPIRE_TIME=0
TRACE_LEVEL_CLIENT=OFF
NAMES.DIRECTORY_PATH=(TNSNAMES) 
NAMES.DEFAULT_DOMAIN=world
NAME.DEFAULT_ZONE=world

Desktop Reporting Applications

Collected below are instructions for setting up various commonly used Destkop Reporting Applications for use with the AAUDE data warehouse. Were relevant, contact information is included to allow you to follow up with the person who wrote the instructions.

Application	Instructions	Contact
Hyperion/Brio	Ensure that you've installed the Oracle Client (see above). It is not necessary to set up an ODBC connection. Download the aaude.oce file, and put it in your "Open Catalog Exensions" folder.	John Scanlon 617.324.8350
Microsoft Access	Select File->Get External Data->Link Tables... In the "Files of type:" drop-down menu, select "ODBC Databases ()". Click the "Machine Data Source" tab, and select the AAUDE data source. Login with your warehouse user name and password. Select the tables to which you want to link. In general, you want to like to tables that start with WAREUSER. Click the "Save password" checkbox, so you don't have to log in every time you want to query the table(s). For each table you selected, you'll be prompted to select the Unique Record Identifier. This step is not necessary, so click "Cancel." The table(s) will now appear in your table catalog, with an arrow/globe icon next to them. This means that they are linked tables. You can now query the warehouse tables just as you would local Access tables.	John Scanlon 617.324.8350
Microsoft Excel	Under construction

Programming/Script Languages

Programing/Scripting Language	Instruction	Contact
Microsoft VBA (Visual Basic for Applications)	Under construction
Perl	Perl does not require the Oracle client or an ODBC connection The Perl DBI module must be installed For security purposes, do not store usernames and passwords in your scripts. Instead, pass the username and password as agruments at script run-time. Included below is a sample connection script: #!/usr/bin/perl #path to your perl installation use DBI; my $username = shift; my $password = shift; my $data_source = 'dbi:Oracle:host=aaude.mit.edu;sid=DWRHS;port=1521'; my $dbh = DBI->connect($data_source, $username, $password) or die "Can't connect to AAUDE Data Warehouse"; #run queries, etc. $dbh->disconnect();	John Scanlon 617.324.8350
SAS	Available on the AAUDE ftp site	Dick Schmitt

Programing/Scripting Language

Instruction

Contact

Microsoft VBA (Visual Basic for Applications)

Under construction

Perl

Perl does not require the Oracle client or an ODBC connection
The Perl DBI module must be installed
For security purposes, do not store usernames and passwords in your scripts. Instead, pass the username and password as agruments at script run-time.
Included below is a sample connection script:

#!/usr/bin/perl #path to your perl installation

use DBI;
my $username = shift;
my $password = shift;
my $data_source = 'dbi:Oracle:host=aaude.mit.edu;sid=DWRHS;port=1521';
my $dbh = DBI->connect($data_source, $username, $password) or die "Can't connect to AAUDE Data Warehouse";

#run queries, etc.

$dbh->disconnect();

John Scanlon
617.324.8350

SAS

Available on the AAUDE ftp site

Dick Schmitt

Desktop Connectivity: Macintosh

Under Construction

Warehouse Guide | e-mail aaude-warehouse@mit.edu | Warehouse home | AAUDE home