Ref: 022802
A data warehouse is a repository of information specifically designed to make the extraction and analysis of data simple and efficient. Traditional production systems, such as a Student Information System, are designed to optimise transactional processing. The features which increase the efficiency of these systems are generally those which also make it difficult to extract data simply and without major impact on production databases. For example, in order to optimise update operations in a production system data redundancy is minimised. This makes extracting data complex because it is necessary to access and link a number of tables to retrieve the required data. This linking also creates a severe load on the production databases.
Data warehouses enable information to be collected from a number of sources and stored in a central area effectively integrating systems which may be quite disparate in nature. They are designed to optimise the extraction rather than the input of data. They are generally read-only and may be updated less frequently than operational systems. In order to boost performance and increase ease of access there may be significant data replication.
A high proportion of data warehouses fail. Often this is because they have been developed for the wrong reasons. A data warehouse is not simply an interesting IT challenge - it is not even mainly that. The most important principle in data warehousing is that the project should be driven by a need to produce results for the end user and not by a desire to create a sophisticated IT system.
At the University of Queensland the Management Information Section (MIS) which is part of the Planning Secretariat began developing a data warehouse at the end of 1995.
The Section, then known as the Statistical Information Section, was not an IT section. It was a user rather than a developer of systems. The decision to develop a data warehouse arose from an urgent need to access data easily and efficiently. When a fairly simple query caused the payroll run to collapse it became clear that some action was necessary. The decision that the MIS should undertake the development was based on the Section's understanding of the outcomes required.
The project has been challenging but it has also been exciting and most of all, it has been fun. Not because of the IT aspects, although these have been interesting, but because of the interaction with users and their positive responses. User excitement and gratitude can be extremely seductive. The higher education environment often produces operational systems which are not particularly well regarded by users. Much of this is to do with lack of resources either for the systems themselves or for IT staff development. A data warehouse can stand outside this and avoid many of the constraints of the operational systems. It also provides an ideal environment for IT staff to focus on the business needs of the university and to gain a wider understanding of its activities and goals.
Many organisations have valid concerns about the potential costs of developing a data warehouse. It is common to read articles where managers justify not undertaking such development for financial reasons. There is a lot of hype in the IT industry generated mainly by software and hardware developers. Reactions to this hype tend to fall at two ends of the spectrum. Some managers get confused and become overwhelmed. They simply walk away. It all looks too difficult and certainly too expensive both in terms of staff and of hardware and software resources. Others become excited - at last, something interesting to add to the IT strategic plan. They either throw resources at it or spend a long time evaluating expensive software tools for data extraction, data validation, meta data storage etc. They prepare lengthy business plans and needs analyses for the organisation. In the meantime users' real needs are unmet and they remain disempowered.
Ignore the hype! Data warehousing is an area where the limited resources in the higher education environment can actually work in our favour. Most universities in Australia and Europe cannot afford to expend large sums on `non-essential' IT systems unless they are sure that they are going to bring real benefits to the organisation. It compels us to focus upon outcomes and upon user needs.
Shortly after we began to `roll-out' the first release of our student data collection on the data warehouse MIS staff attended a seminar on data warehousing run by Digital Computing. The speaker, David Brisbane, has been involved in data warehousing consultancy for a number of years. David dealt with a range of issues but particularly stressed what he called `the wooden bridge approach'. As he put it `there is no point in building the Sydney Harbour Bridge if users do not like the view when they get to the other side'.
I would like to use the wooden bridge theme to explain what I believe to be some of the critical success factors in the development of a data warehouse.
* Design
It may appear paradoxical but the best way to satisfy user needs is not
to undertake an extensive user needs analysis before commencing development of
a data warehouse. Such analyses tend to be lengthy and may raise unrealistic
expectations. It is more productive to develop a prototype and get user
reaction before the first release. Although it is important to keep
integration issues in mind do not attempt to develop an organisation-wide data
warehouse from the outset. Choose an area in which there is a real business
need.
Since the MIS was both a major user as well as a developer of the data collections within the data warehouse the Section had a reasonably good understanding of user needs. We started with the student data collection because we knew that this was an important area for Faculties and Departments and it was one with which we were familiar. Only when we had developed a prototype did we invite comment from users. It was easier to get productive feedback when we were able to demonstrate how the system would work.
* Construction
In order to keep the process as simple as possible and to provide value to the
user within an acceptable timeframe it is better initially to use software and
database systems with which you are familiar. One of the most important
features of a successful data warehouse is an efficient method of data transfer
from the production systems. This is particularly important for those data
collections which are updated frequently. If you use the same operating system
and database on the data warehouse as are currently used on your other major
systems you have the benefit of a simpler transfer process as well as available
skills in database administration and other areas. If your systems have
software that is disparate or not suitable for a data warehouse then you may
need to introduce a new database or operating system but it is better to avoid
this in the initial development where possible. Similarly it is not necessary
to purchase an expensive extraction tool.
* Support
Strong support is one of the most critical success factors in data warehousing.
There are a number of areas from which this support is important:
It is vital to have a `sponsor' from the Senior Executive, preferably someone with funding responsibility. In our case since MIS is part of the Planning Secretariat and reports directly to the Secretary and Registrar we are able to keep him up-to-date with progress. Where it has been necessary to get support for an initiative that has funding or policy implications it has been possible to do this quickly. The Planning Secretariat also works closely with the Vice-Chancellor's senior executive staff. This means that the MIS is informed of the policy directions of the University and related information requirements which can be fed directly into the data warehouse development.
Developing a data warehouse must be handled with care and sensitivity. Owners of the data are likely to have concerns about confidentiality and security issues. It is important to involve them as much as possible in the development process. They can provide valuable advice on data availability and on user needs. At the University of Queensland the IT support for the operational systems has been devolved to the appropriate administrative area (for example the student systems support group is part of Student Administration). MIS has involved the owners in different ways according to the resources available. With the development of our research data collection we provided the IT resources to extract and manipulate the data from a fairly old Rbase system while the Office of Research Services put a lot of effort into replacing missing data and revising their procedures to accommodate better the extraction of data at unit record level. With staff data the Director of Personnel Services provided IT resources to work with us on the development of this collection. This has proved successful both from a system and staff development point of view. Both Offices feel some ownership of the data collections.
While I believe that the user/developer model has the best chance of success many universities administrative structures may not lend themselves to this approach.
If the data warehouse development is to be undertaken by a central IT area it must be user driven. This will involve a close working relationship with the planning area or other appropriate group. In the reverse case, as in our situation, it is important for the planning area not to work in isolation from other IT groups. In addition to keeping aware of developments likely to impact upon the data warehouse we receive advice and support on issues such as database administration and capacity planning.
While a data warehouse has the potential to bring great benefit to an organisation it is a non-essential system. If users do not see value in it they will not support it. It is important that they feel a sense of ownership.
Rather than have a formal release of the system we had a number of informal demonstrations. We contacted individual Faculties and invited representatives from each of their departments to a demonstration with an upper limit of 12 on the attendees at each session. At these sessions we explained briefly what a data warehouse was and showed them what it could do. The message that we tried to convey was that this was a system designed by users for users. Their input was essential. The sessions were as interactive as possible. We had six of these sessions and many visits from Heads, Deans and other staff where we would sit and discuss their requirements over a cup of coffee. Users constantly ring or e-mail us with feedback or to seek advice. User groups have also been established.
* Easy Access
If it is not easy to access information on the data warehouse users will either
ignore it or, more likely, will ring up expecting you to provide the answers
for them. The interface to the data warehouse should be intuitive and
user-friendly for a wide range of non-IT staff. It should also be flexible and
accommodate ad-hoc queries.
We chose Business Objectsô, an ad-hoc query and reporting tool, as our interface because of its user-friendly nature and functionality.
Much of this advice is not new: the importance of prototyping and reiteration, the need for the data warehouse to be user rather than IT driven, and to have support from the top as well as from the owners of the data. But perhaps, as we did initially, you may dismiss it as obvious and search for some other more complicated and more expensive formula. If you concentrate on the fundamentals you will find that the project gains its own momentum and you will have at least the early stages of a successful data warehouse enabling you to make an informed judgement on whether additional resources are necessary and how they should be spent.
An Executive Information System (EIS) allows senior management and other staff to access a wide range of summarised data electronically. It is generally a stand-alone system but may use data automatically extracted from a data warehouse. Its most important design features are ease of use and visual impact. It should be intuitive so that managers can use it with little or no training. Many of these systems will have drill-down and slice and dice capabilities similar to more comprehensive decision support systems (DSS) designed for planning and other administrative staff. While a DSS may trade-off some ease of use for increased functionality it is important that this is not the case with an EIS. An EIS has to be extremely easy to use and will probably have to be tailor-made for the purpose.
Most senior executives want `value-added' reports provided either in hard copy or electronically. They need and expect advice on data and trends. This can be provided much more simply than by designing an EIS for the purpose. A Planning Officer can use an On-line Analytical Processing (OLAP) or other tool to extract and manipulate data and then incorporate this into a report. Reports can be set up using a tool like Business Objectsô and e-mailed regularly or posted to a Web-site.
If you develop an EIS you face the challenge of designing a system which is suitable for use by executives but which must provide sufficient additional benefit to the organisation to justify the cost of development and of ongoing maintenance.
The University of Queensland EIS has been developed using Microsoft Accessô with a Visual Basicô interface. The screens are attractive but not elaborate. The Home Page which has the University crest surrounded by an arch of six individual arches is appealing for its visual impact as well as its simplicity. One senior executive likes it so much that he keeps it on his screen as a background. Maps showing students' permanent domicile and country of birth have been added. Data access is easy and flexible with an on-line help facility and a glossary of definitions.
I could spend some time discussing executive information systems in greater detail. Unfortunately space is limited so I have chosen to focus on data warehouses. While a successful data warehouse can give an organisation a competitive edge an EIS is the icing on the cake. If resources are limited forget about developing a system for executives. They and the university will benefit much more from a well-designed data warehouse with supporting commercial reporting tools. Above all, whatever you develop, focus on outcomes and ignore the hype.
Copyright EUNIS 1997 Y.E.