Jims photo Doug's photo

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 Next Generation Data Warehouse initiative has gone through a number of developmental stages since its inception – the initial phase being to establish proof of concept by creating and implementing a data model for network usage statistics. The next phase involved the development of a dozen prototype applications across a number of functional units on campus. A third phase was a pilot project to develop a data model for graduate program and student information. And that brings us to our up-dates.

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.


            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

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.



                        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

            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.



                            David Crain, CMA
                            Manager of Finance
                            Development Office
                            University of Calgary

Continuing Education

            For Continuing Education, allocating resources across its various course offerings is a
highly involved process, one that demands a huge amount of information. The revenues and
expenditures of each course must be analyzed, along with patterns in registrations. These must be
summarized to evaluate the performance of each “team” within Continuing Education.

            The course analysis data mart provides detailed financial measures, along with highly
flexible registration statistics. Program directors are able to observe a cost breakdown of any
course to see how funds are being allocated. Reports of how, when and where students are enrolling
can be produced with unparalleled ease. Available via industry standard Cognos tools, the data
mart offers new insights into the core of Continuing Education’s business.



                            Robert Wensveen
                            Program Director & IS Manager
                            Faculty of Continuing Education
For additional information on the use of COGNOS, please contact the authors Doug Shale (dgshale@ucalgary.ca) or Jim Standen (jstanden@ucalgary.ca).

Contributions to this article were gratefully accepted from Gabriel Grey, Analyst, Information