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:
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."
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.
Benefits
"The use of a Cognos DataMart/DataCube that combines Continuing Education
course,
enrollment and related financial data is
providing the incentive to plan and develop new business
applications based on enhanced reporting
in the areas of process improvement and productivity
evaluation. More specifically, top-down
and bottom-up financial analysis of our courses and
programs provides our Program Directors
and Team Leaders with much more accurate and relevant
data when planning and evaluating fiscal
viability...and our goal of total cost based reporting on a
per course and per course hour basis will
soon be realized. As a result of increased
speed/responsiveness in pulling data from
the Cognos DataMart/DataCube, plans are underway to
replace/rebuild business reporting applications
which were originally developed using Microsoft
Access."
For additional information on the use of COGNOS, please contact the authors Doug Shale (dgshale@ucalgary.ca) or Jim Standen (jstanden@ucalgary.ca).Robert Wensveen
Program Director & IS Manager
Faculty of Continuing Education
Contributions to this article were gratefully
accepted from Gabriel Grey, Analyst, Information
Technologies.