Connecting to the UI Data Warehouse
Tools
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/.
Multi-Factor Authentication
Starting in 2022, user accounts will be required to use Multi-Factor Authentication (MFA) to access the UI Data Warehouse. Please review "Connecting to Oracle with MFA Enabled" on the DW SharePoint site.
Drivers
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.
Snapshots
The UI Data Warehouse partitions most of its tables using snapshots. SNAPSHOT_TYPE values include, but are not limited to:
- Daily
- CALENDARWKXX
- CENSUS
- LASTDAY
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
Go HERE to set a default snapshot.
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
Server Names
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
TNSNAMES.ORA
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
- 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.