Contact Support

Help & Support

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/.

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

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.

Here are some other links you may find helpful.

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.