Connecting to the UI Data Warehouse
Once you have permission to connect to the data warehouse, you'll need to decide what tool you'll use to query the data. The tool you use will likely depend on why you're connecting. You could use Excel, Access, Microsoft's SQL Server Integration Services, DBVisualizer (DBVis) or other SQL utilities.
To install DBVis go to https://www.dbvis.com/.
Depending on what tool you use, you'll need to install drivers on your computer that will allow you to connect to an Oracle database server. Check the documentation for your selected tool to determine which drivers you need to install.
You may also need to configure an ODBC connection if you're using Access or Excel.
The UI Data Warehouse partitions most of its tables using snapshots. SNAPSHOT_TYPE values include, but are not limited to:
Each snapshot type has a SNAPSHOT_QUALIFIER.
Daily - For each DAILY snapshot, the qualifier is the date the snapshot was taken in the following format YYYYMMDD
CALENDARWK snapshots each have the year as the SNAPSHOT_QUALIFIER
CENSUS - the session serves as the SNAPSHOT_QUALIFIER
LASTDAY - the session serves as the SNAPSHOT_QUALIFIER
Oracle Query Writing
Depending on what tool you use, you'll use different syntax to query the data warehouse. Getting back the "right" data depends on providing Oracle with the correct commands, so understanding how retrieve the data is critical.
Simply trying to pull back all rows of data in a table can cause performance issues. Ensure that you're familiar both with the query language you intend to use, as well as the snapshot scheme used by the data warehouse (outlined above), before you connect. Specifying the snapshot types and qualifies you want to work with is critical to creating performant queries.
Making the Connection
There are two servers that contain data warehouse data.
- DWTEST - test server ... a good place to try out new queries without impacting the production system
- DWPROD - live data warehouse server
The TNSNAMES.ORA file - which contains the connection details for the Oracle servers - is available at \\iowa.uiowa.edu\shared\uidata\psoft\oracle.
Other Helpful Links
Here are some other links you may find helpful.
- Data Warehouse Wiki
- Data Warehouse Data Models
- Connecting Microsoft Access to Oracle Databases
- Cannot connect to Data Warehouse using Oracle ODBC - Password includes special characters
- Microsoft Access ODBC Call Failed
Still Have Questions?
The Help Desk supports connecting to the UI Data Warehouse. The Help Desk contact information is available at https://its.uiowa.edu/contact.