Data Warehouse

The University of Arkansas Data Warehouse is a data repository made up of over 270 tables of legacy and historical data such as financial (DART, UPS, BASIS, Payroll, etc) and academic (ISIS) data, that is separate and distinct from the mainframe production databases. Warehouse data is copied from the live, production system, summarized, transformed, and then loaded into the Data Warehouse at regular intervals. Non-programmers will find easy access to this data from their desktop workstations.

Access to the Data Warehouse requires an authorized signature from the department head (or person responsible for authorizing cost center expenditures) and is only available to academic and administrative users. No students will be authorized for access. The data warehouse is accessible from 7:00 a.m. - 6:00 p.m. weekdays, and on weekends when maintenance is not scheduled. It is advised that for weekend access you call the University IT Services Data Center at 575-2303 to verify accessibility.

To gain access to the Data Warehouse, download the form, Data Warehouse Access Agreement Form (PDF, requires Adobe Acrobat Reader) and print it. Fill out the form and check your Data Warehouse Access Software selection: Brio, SAS, or Microsoft Office. Mail the form to University IT Services at 155 Razorback Road, ADSB 220, Attention: EL ORWIG or fax to 575-4753. Data Warehouse passwords can be reset using PASSweb.

University IT Services supports and recommends using Microsoft Access, BrioQuery or SAS, but the Data Warehouse can be accessed with any ODBC (Open Data Base Connectivity) compliant software product. An ODBC driver must be installed on the customer's computer to connect to the Data Warehouse. This is typically done by the Data Warehouse team. Microsoft Access has the largest number of installs due to most customers already having MS Access on their computer, making it the most economical product to use. BrioQuery is a sophisticated, client-server ad hoc query and reporting system that provides non-technical users easy access to the valuable data stored in the Data Warehouse. With BrioQuery, you can build queries and reports quickly and intuitively by clicking icons and manipulating objects. BrioQuery displays the retrieved data in a tabular format, and also lets you build eye-catching presentations that can include graphic elements and charts. For more information on obtaining a copy of BrioQuery, contact the University IT Services Data Warehouse staff at 575-2905. SAS is the least used product at this time, due to not being as user friendly as the above products. However, SAS has come out with a new release (8.0), which appears to be more user friendly. SAS also has some other products that are being evaluated, such as SAS Reporter and SAS Enterprise Miner.

Adding a new Data Warehouse Table using MS Access

1. Go to File.
2. Select Get External Data.
3. Select Link Tables.
4. On the Link Screen, go to Files of Type: and select from the list ODBC Database.
5. On the Select Data Source screen, select Machine Data Source, then double click on DWH32.
6. Enter your password (if requested) and highlight the table(s) you want to add,
7. Click OK.
8. On the Select Unique Identifier screen, click OK.

Stopping a Data Warehouse Query

If you build a Data Warehouse query and submit it, but forget to provide any selection (or limit) criteria, a "runaway" query could be created if the table(s) have a lot of records, and you will want to stop the query.

If you are using MS Access, press the CTRL and BREAK (PAUSE) keys, if you are using Brio, press the ALT and END keys. You might have to do this three or four times to stop the query. If that does not stop the query, then press the CTRL, ALT and DELETE keys to abort your session.

Using the Most Current Versions of the Data Warehouse Tables in MS Access

Click on your Data Warehouse Icon, from your menu bar select TOOLS, then select DATABASE UTILITIES (if using Office 2000) or ADD-INS (if using an older version of Office), and select LINKED TABLE MANAGER. When the tables are displayed, select the tables you want or select "ALL"; click OK. If a table no longer exists at that location, you will be prompted for a new location. This may happen if the table has been moved from DEMO to PRODUCTION or the table has been renamed. This may be a good practice to do every month or so.

Data Warehouse Help and Training Resources

Workshops for the Data Warehouse are available with BrioQuery, MS Access, and SAS. Workshops are taught through the University Information Technology Services' workshop series. For more information, see our Technology Training web site or call 575-2905.

Copies of the Data Warehouse workshop handouts for BrioQuery and Microsoft Access are available for download in PDF format from the links below.

Data Warehouse Workshop: BrioQuery (PDF, requires Adobe Acrobat Reader)

Data Warehouse Workshop: Microsoft Access (PDF, requires Adobe Acrobat Reader)

An email based discussion list is also available so that Warehouse users can ask questions, post tips, etc. about using BrioQuery, SAS, MS Access, and the Data Warehouse, in general. We will also use this to keep you informed of related activities. To participate in the discussion list, send an email to:
listserv@listserv.uark.edu.
In the message area, type:
subscribe askdwh yourname
where "yourname" is your actual name.

Troubleshooting

No Records Returned after Data Warehouse Query

Double check what you used as selection criteria. Selection data is case sensitive. Look at a sampling of the data you are wanting. If you are using dates, check to see if you are using the same date format.

Example: If you entered 19990125 (YYYYMMDD) as the date, but the table date format is (MMDDYYYY) 01251999, you will not get any records returned. If you are using multiple tables, double check what fields you are linking on. You might want to remove all the tables and then start adding (and linking) a table at a time. This may identify the table that is causing the problem (or give you some insight as to what you were doing wrong).

Locked Up and/or Error Messages While Running Data Warehouse Queries

Sometimes when running a lot of queries (or using more than one session at a time), the query program (ie. Brio, MS Access, SAS) gets lost. Log off and get all the way out (you might have to exit Windows or turn your computer off), then log back in and try running your query again. If you are hung up and are not able to log off, hold down the CTRL, ALT and DELETE keys to end your task.

Unable to Find a Data Warehouse Table Using MS Access

If you receive an error message when trying to access a Data Warehouse table that you have used before, the table name might have been changed, you might have deleted it from your view, it might be a new table that has been added to the Data Warehouse, or the table structure might have been changed since you used it last. Re-link the table to solve the problem.

"ODBC-connection to dwh32 failed" or "ODBC-call failed" Error Messages

Contact the Data Warehouse staff as the Data Warehouse is either down, the server name is wrong, or your services file cannot be located.