Connecting

Setup ODBC to the Oracle EDW server

http://www.ds.uillinois.edu/how_to.asp#Oracle

1. Align Passwords
2. Install Driver
3. Create ODBC Connection

Select Tool

Product	Pro	Con	Uses	Notes
Toad	free version table/view definitions quick query tool oracle query language	free version doesn't have graphical query builder expensive (tho site license may be in the works) higher skill level required	exploring  edw data structure troubleshooting queries and data oracle development	http://www.toadsoft.com/
Excel	graphical query tool common software	user refreshing data must have odbc clearance	query design report design and delivery delivering report to user
SQL Server	data transform service makes setting up "jobs" easy linked oracle server option	costs skill level in maintaining server	query design warehouse setup extract automation local database suport
Oracle	site license	skill level high	query design warehouse setup extract automation local database support
MS Access	common software easy to link multiple data sources and query across them	sql syntax translation problems frequent	query design warehouse setup local database report design and delivery	DS How to Link Tables via Access
SAS
SPSS
Crystal Reports				DS How to Connect via Crystal

Tips on Connection Code

The below is not definitive or comprehensive... but rather highlights some alternative connection mechanisms.

SAS

Also see DS How To: http://www.ds.uillinois.edu/Docs/HowTo_GeneralDW_ConnectwSAS.pdf

PC-SAS code to connect and submit SQL to be processed by Oracle, and some sample code to join three tables and pull all fields from each:

-----------------------------------------------------

connect to oracle (path=DSPROD01 user=jdoe password=xxxxxx);

create view test1 as

(SELECT *

FROM EDW.T_FA_AWARD_BY_TERM A, EDW.T_FA_FUND F, EDW.T_PERS_HIST P

where A.FA_FUND_CD = F.FA_FUND_CD and A.TERM_CD='120038' and A.EDW_PERS_ID = P.EDW_PERS_ID and P.PERS_CUR_INFO_IND='Y'

);

data test2 ;

set test1;

proc contents;

-------------------------------------------

SQL Server: Adding an Oracle linked Serve to SQL Server 2000

This setup may allow for easier querying across the two platforms, allowing mingling of local and EDW Oracle data.

GEN_SQLServerOracleLinkedServer.doc

 

Microsoft Access: Pass-Thu queries in MS Access

Also see DS How To: http://www.ds.uillinois.edu/Docs/HowTo_GeneralDW_ConnectingwAccess.pdf

But first, the WHY:

A pass-thru query literally hands the query off to the destination server for processing.  This keeps the processing and sql syntax on the destination server.  Advantages are as follows...

• Syntax and Power: the sql syntax is server specific (Oracle pl sql in the case of the EDW). This means you can utilize Oracle specific sql which can be more powerful.
• Portability: Business Objects queries can be pasted in and run as-is given appropriate permissions. Or queries can be ported to SQL DTS jobs without the headache of untangling from the Access syntax.
• Speed: My very unscientific comparison of a query run through Access and then Access-pass-thru showed passthru query was 2x as fast

BUT:

Unfortunately there is no query gui in MS Access for pass-thru queries.

Steps to Create a Pass-Thru Query in MS Access:

1. from query design view select: Query > SQL Specific > Pass-Thru
2. from query design view select: View > Properties
3. hit the ellipse after the "ODBC Connection String" field and select the EDW - Production ODBC connection you hopefully already have.

At this point, you're set to go and can paste in a query and run it. Note that electing to save password/connection settings can leave your password in the properties of the query in full glory.

If you have the EDW server as a linked server in MS SQL, you can run passthrough queries as well. See the previous section on "How to: Add Oracle Linked Server to SQL Server"