Update on the Next Generation Data Warehouse Initiative

         Authors:  Doug Shale, Director, Institutional Analysis (right photo)
                        Jim Standen, Manager, General Information Systems, Information Technologies (left photo)

            In a previous edition of The Big Byte (Fall 2002), Adrian Ang and Bernie Cloutier presented an account of the “next generation data warehouse initiative”  http://www.ucalgary.ca/it/publications/BigByte.pdf."   In their article, Adrian and Bernie described:

• The University’s need for more and better quality data to support institutional decision making. Data must be collected in a consistent and timely manner and conformed across organizational functions within the University.
• How the need for more, high quality data can be addressed through data warehouse technologies by better integrating and standardizing the University’s data assets. One aspect of this is ensuring that data are compiled in accordance with standard, institution-wide definitions and at agreed upon dates of record.
• How data warehouse technology works to accomplish better standardization and integration of data. The Next Generation Data Warehouse technology represents a major advance over the technology used when the University first implemented data warehousing in the mid-1980s. Data from various sources are brought together, cleansed and transformed into what is referred to as a “hub and spoke” data model which facilitates local users’ accessing the data with querying tools such as MS ACCESS or COGNOS IMPROMPTU. Data can also be pulled off into multi-dimensional data bases (sometimes called “data cubes”) for further analysis and reporting using, for example, COGNOS POWERPLAY software.

Recent Initiatives and the Next Generation Data Warehouse

            Initially the Next Generation Data warehouse project was guided by an informal committee of individuals who work in functional areas that are candidates for data warehouse development. However, issues regarding the management, governance and funding of DW implementation needed to be addressed on an on-going basis. As a result, Phase IV was initiated early in 2003 and involved, among other things, setting up a governance and managerial structure for the design and development of an institutional data warehouse. A Steering Committee was formally established under the auspices of the Senior Executive Group (SEG) with terms of reference and membership approved by them. A budget was submitted for Fiscal Year 2003-04 and the University Budget Committee awarded a two year subvention to support data warehouse work.

            The Steering Committee presently consists of: Ken Barker, Academic Representative; Art Kirk, representing Financial Services; Martha Stroud representing Graduate Studies; Sandy Repic representing Human Resources; Tony Barnfather representing Information Technology; David Johnston representing the Registrar’s Office; Martin Kirk representing the Office of the VP Research; and Doug Shale from the Office of Institutional Analysis serving as Chair. The Steering Committee is supported in its work by Bernie Cloutier and Don Barker from IT. Bill Erickson, a consultant, has been retained to manage and implement the data warehouse projects commissioned by the Steering Committee.

            The Steering Committee has applied a portion of its budget allocation to develop the necessary infrastructure needed to support a full commitment to data warehouse development. Investments have been made in essential server hardware and data warehousing software (a major software acquisition being Decision Stream).

            While a governance and funding structure was being put in place, a number of data warehouse activities were initiated under the auspices of various University offices. Mention was made above of the dozen projects undertaken in Phase 1 and some of these are described in the concluding section of this article.

            In addition, extensive data warehouse development was undertaken by Finance to support the compilation of financial data required for regular reporting. Data warehouse development has also been occurring around the institutional budgeting process, primarily to facilitate integrating Excel based templates and data in the general ledger cube. This integration of information will allow for the tracking of commitments and actual expenditures relative to budget.

            Considerable development will need to be done around the data warehouse and Cognos Enterprise Planning system to further enhance the new budget tool and to provide forecasting capability in support of quarterly reporting and budget modelling.

            Another major data warehouse project nearing completion involves Human Resources/ Payroll. Data cubes are being developed that provide an analytic basis for tracking and integrating information on employee demographics, employee leaves and employee earnings and deductions.

            Both the Finance/ Budget and HR/ Payroll projects have been in process for some months and are nearing completion.

            Two relatively new projects (both of them substantial) are now in the early stages of development. One of these is in the area of research services with the objectives being to improve the tracking of cash flow and milestones during projects; to support reporting of ‘applied for’ grants versus ‘received’ grants; and to track projects through their life cycles.

            The second project builds on the initial project referred to above and extends the integration and reporting of data on graduate students. In addition to improving the quality and kind of data available on graduate students, work in this area will provide a basis for data modeling of undergraduate student data.

Benefits from the Next Generation Data Warehouse

            One of the five goals in the University’s Planning and Accountability Framework states that the U of C will “Enhance our effectiveness as a responsive and responsible organization”. A foundational step in support of this goal is developing our institutional capability to generate high value decision support information – and data warehousing is a technology and methodology for developing this capability. For example, enhanced financial reporting will result in more effective and efficient control in managing the University’s budgeting and expenditure activities. Better tracking and monitoring of research grants will not only facilitate accountability reporting to granting agencies, it will optimize opportunities to leverage additional funding off of research grants. Moreover, major benefits accrue from an enhanced institutional ability to do things that are now not possible because of limitations in current data bases and reporting systems.

                In addition to advancing institutional goals, investments in the data warehouse infrastructure are generating a considerable dollar return on investment in a number of other areas within the University. These are the subject of the rest of this article.
 

GIS TEAM - COGNOS PROJECTS

            We on the IT General Information Systems team have recently completed three Business
Intelligence projects on behalf of our clients.  The distinct solution sets have provided access to
sophisticated reporting tools to improve business efficiency.  The descriptions of the solutions
follow:

Campus Recreation

            The Campus Recreation budget analysis data mart demonstrates how multiple information
sources can be used to create a single, consistent reporting solution. Previously, budgets were
entered into an in-house database. Past revenues and expenditures were then manually imported
for comparison purposes. This was both extremely tedious and prone to error.

            The data warehouse automatically extracts Campus Recreation’s budget, along with
information from Financial Services on a daily basis. The data is cleansed, summarized and made
available through web-based Cognos reporting tools. This process has made budget forecasting
vastly easier and more reliable. Just as important, the extraction and report generation are
completely transparent, causing no disruption to the usual data entry activities.

Benefits

"COGNOS has allowed us to bring together budget and actual data from disparate data base sources in one concise and easy to access location. The end result is the ability to easily monitor our financial activity as necessary and take appropriate action as required. In addition, we are now able to apply future cost projection models, providing for better financial decision making for Campus Recreation."

                        Mark Eckert
                        Associate Director,
                        Campus Recreation

Development Office

            The general ledger data mart is a valuable resource for improving the University’s
financial decision making capability. Every relevant dollar value, from the institutional level to
individual transactions, can be analyzed across standardized metrics specified by Financial
Services.

            The power of the general ledger data mart can now be shared with individual departments.
With access to a subset of this information, the Development Office can effortlessly generate
reports as dictated by its internal accounting needs. Additionally, any report that the
Development Office creates will be consistent with what is seen by Financial Services. All this is
provided to the customer at a minimal development time, since the work of designing the general
ledger has already been completed as part of another institutional initiative.

Benefits

"The benefits of this multidimensional financial model are significant, particularly given the
Development Office is in the midst of reengineering its business practices. With the deployment
of Raiser's Edge and the realignment of fundraising and administrative resources to support an
ever growing tax receipting donation based program, the risk of over-spending on budget
commitments or spending on non value adding activities is significant.

The financial model allows us to produce a scorecard of informational sweet spots that
tells us at a glance if our spending commitments are being used to support those programs that
drive increases in donation activity. The drill down and reporting capabilities of the financial
model also allows us to distribute subsets of the scorecard to accountable managers to ensure they
see the impact of their decisions from a cost management perspective. This ability to align cost
structures to the most value adding activities is a significant step towards achieving our overall
philanthropic support targets without compromising our program cost commitments."