The Treasury Office has been working on an information systems project that will improve our ability to effectively and efficiently deliver and process data that is crucial to one of our primary responsibilities: reporting on the performance of the University’s endowment and invested operating funds.

To monitor and report on the University’s investments we currently use a complicated set of linked Excel spreadsheets—developed years ago when the funds managed were much smaller and the investment strategy less complex—including one “Master” that contains historical data all the way back to 1989. This is an enormous amount of data, and with every passing month when a new batch of data is loaded, the Master becomes more fragile and our data more prone to being lost. The size of the file also makes it very slow to work with.

Our search for a solution led us to the data warehousing concept. A data warehouse is a relational database management system (RDBMS) that stores data permanently—in a data warehouse, data is never overwritten. “Relational” means that every piece of data has its place in relation to the other pieces of data—these relationships are the key to the system and make it possible to extract data to use for analysis and reporting as well as allowing easy changes to the data.

In September of 2001 the Treasury Office began to build its own data warehouse. We have gone through standard data warehouse project phases, including identifying and documenting our business requirements, determining the relationships between pieces of data and designing the database, building the database and the automated data loads, testing the data to make sure it is good, and selecting a user interface that will allow us to do analysis and reporting on the data stored in the warehouse. In all phases, the staff works closely with consultants who are responsible for the technical aspects of the project.

The Treasury Data Warehouse (DW) addresses all the issues we have with the current system:

• DW can securely accommodate huge amounts of data.
• 95% of data is loaded into the DW automatically, taking virtually no staff time and minimizing the potential for human error.
• Making changes to the structure of the data is easy and risk free: the data warehouse is designed to be flexible and grow as we need it to grow.

We expect that the data warehouse will have many other benefits that have yet to be explored, but it has clearly improved our existing process. Our work with data is more productive, which makes our analysis and reports better, which in turn provides senior management with good information upon which to base decisions.

This kind of project cannot be successful without the participation of staff in various roles: Susan Ball & Judy Peterson (Senior Associate Treasurers) are project sponsors, Garth Reistad (Associate Treasurer), Laura Lai (Financial Reporting Analyst), and Randy Lewis (Alternative Assets Operations & Reporting) are subject experts and also the primary users of the data. Cathy Billings is the project manager.