Administrative affairs

March 30, 2011

Enterprise Data Warehouse, financial ‘cube make it easier to work with data at the UW

Two important developments are making it easier for users to access and analyze data at the UW: The growth and maturity of the Enterprise Data Warehouse, a central repository of institutional data; and the development of enterprise reports and the financial “cube,” a tool that permits data analysis in multiple dimensions.

These tools are critical elements in providing campus users with consistent and accurate information necessary to support effective decision-making.

“These tools facilitate decision making without requiring sophisticated programming,” says Kelli Trosvig, interim vice president and vice provost for UW Information Technology. “This results in greater efficiency, in reports that can be customized and accessible on demand, with data that has been verified across the institution.”

“With the recent budget cuts and changes in funding, the reports have been very helpful in identifying funding changes and expense trends,” says Tom Sparks, financial administrator for the College of Engineering.  “We use them to identify potential problems with deficits, and they have helped smooth our biennium closing processes. Overall, the reporting function helps me be more financially service-oriented to help with questions from my dean, department chairs and administrators.”

Four major divisions of data are found in the Enterprise Data Warehouse: information concerning students, human resources, finances and payroll. Beginning in 2012, the extensive set of data maintained by the Office of Planning and Budgeting will become part of the warehouse. This is the source of key institutional data used for analysis and reporting both within the UW and to external agencies, including the state legislature.

The warehouse provides an improved way to access institutional information from a single, secure source, and over time should reduce the need for individual units to develop their own data systems (known as “shadow systems”), saving them significant time  and resources. Already, the number of users accessing information from the data warehouse has grown significantly, increasing tenfold within the last 18 months, to about 5,000 registered users (see graph).

The warehouse will play an important role as the University moves to implement Activity Based Budgeting. In the future, the warehouse will provide departments with ways of analyzing data across subject areas, enabling them to identify historical trends and make longitudinal analyses. In a rapidly-changing environment, these tools will make it possible to develop projections, compare different scenarios, and forecast outcomes of various policies.

The reports available through the data warehouse answer frequently asked data questions; they can be customized by individual unit. The reports were developed chiefly by the users of the system, with UW Information Technology (UW-IT) providing the user-friendly environment conducive to report development and publication. The reports reside on a central server and reduce the need for custom report creation.

“Our challenge was that the source databases were designed for processing transactions but are not structured for reporting,” says Anja Canfield-Budde, senior manager of decision support services in UW-IT. “The new tools operate on top of new, efficient data warehouse databases, delivering information faster, and with more flexibility and greater security. What weve done is to develop ideas and meet demands that came to us from users.”

The financial cube is another new tool that is making it easier for users to access and analyze data. For those who are not professional analysts, the most important fact to understand is that the folks on the UW-IT EDW team have developed this tool to facilitate complex financial data analysis without the need for custom programming, which had been the norm. In fact, the standard Excel worksheet has become the delivery system for the cube. Cells within the spreadsheet can be sliced and diced in ever-finer divisions through a simple drag-and-drop.  Easy-to-use reports are delivered via a handy web interface.

“The financial cube allows users to ask questions about revenue and expenses by unit, and then to break this down – by account number, fiscal year, type of account, job class, salary. And these manipulations can be accomplished quickly,” says Ann Wunderlin, senior analyst with decision support services in UW-IT. Most users require just a little training in using the financial cube, she says.

Cube users develop their own reports and create their own data questions. Within the cube framework they can create sophisticated analyses using relatively simple tools. Coming soon will be a brand new Activity Based Budgeting cube that will enable users to analyze data such as enrollment by college, year, major, degree and credit hour.

“One of the nice things about the financial cube is that it empowers end users to create their own analytics,” says Denis Brasfield, senior computer specialist in the School of Public Health.  “One of our department administrators has used the cube to create a custom budget summary spreadsheet for her department. In the past, this would have languished on a long list of school-level decision support projects.

“Until more cubes are created to encompass the totality of business questions, we can only use the existing cube to answer a relatively small range of business questions. The financial cube still has room to grow, but this is a great first step.”

Trosvig promises that the data warehouse will continue expanding its reporting capabilities. “The next big waves will be around research metrics and advancement data. We want to give departments better tools to address their current data needs and to incorporate that information into their planning.”