Are we the best data warehouse in Kansas? We are working hard in that direction. Our VP`s request for a faster way of approving access tools for clients began in 1989 as an ad hoc common data platform and evolved into a full-blown data warehouse project.
It was built using the same principles as successful data warehouses are being built with today. Earlier in the process, we had to fight against vendors who pushed the virtual data warehouse theory, where query access was permitted directly against operational data.
We employed logical modelling, using Cayenne`s GroundWorks tool for each iteration or stage that we implemented. (NOTE: Do not confuse this with a data mart approach; each modelling effort for the different iterations was done with the enterprise in mind). With GroundWorks we could changes to the logical model and those changes were reflected in our physical implementation of the data warehouse.
We were impressed by the product`s ability to form a one-to-many relationship between the data model and multiple physical implementations of that model. In addition, the physical design implementation could be synchronised back to the logical model.
Our initial emphasis was to complete one stage at a time. Each stage involved detail data for at least one subject area. We started with claims data and now have five years` worth available. Our current warehouse includes contract counts, group, eligibility and financial data, to name a few.
Cayenne`s Production DBA for DB2 tool was used extensively in our physical design effort. Its "built-in" intelligence assisted us a great deal in our design efforts, considering our expertise level at the time. We used custom-written Cobol programs for our extract and transformation processes. BMC`s DB2 Plus utilities proved invaluable for loading, recovery and maintenance. DB2 for OS/390 has served us very well.
The clients, especially those "power users" who no longer have to rely on DP for reporting and cross-function analysis needs, reaped the greatest business benefits.
The actuarial, utilisation and review and marketing clients are our main users. As we worked on the various stages, we continued to refine and modify the data already implemented in previous iterations. This was essential. We emphasised the importance of quality data in our data warehouse to ensure clients` confidence.
We are now placing emphasis on the following:
- Refinement : We are dealing with outstanding performance problems, adding more value to tables and analysing the need for specialty tables and reporting.
- OLAP : We are addressing clients` needs for these technologies.
- Data mining : There is interest in this but clients have been reluctant to attempt to use this technology due to the cost and also the need for specialised skills.
Practical advice
- Get a corporate sponsor that understands the value of data warehousing.
- Educate your data warehouse administrator/architect or hire one who has been through the fire.
- Clients are most important to the success of the project; involve them from the beginning.
- Categorise the requirements and problems and publish proposed solutions.
- Be absolutely realistic. Do not be too ambitious, especially if you lack appropriate skills.
- Do not compromise good data warehouse principles.
- Talk to your peers - do not reinvent the wheel.
- Logical modelling is essential.
- Physical design for analytical processes is essential (denormalise, employ the star schema if your RDBMS allows it, use lots of indexing).
- Be careful of theorists who swear that a data warehouse can be built in unrealistic time frames. Data scrubbing, data cleansing and obtaining clients` agreements demand time and effort.
- The technical issues are by far the easiest to achieve; pay lots of attention to ensure that your warehouse solution is a "client" data warehouse and not a "technology" one.
- Use consultants wisely. Do not let individuals come in and redirect your data warehouse project. This can be very costly or even fatal.
- Do the project in stages. Have client-approved deliverables at realistic intervals. Long, drawn-out projects tend to lose support.
- Beware of "experts" who tout the deployment of data marts first and then link them together as a better alternative. There is a big difference between stages of a data warehouse project and a data mart. Data marts should feed from the data warehouse as they are normally department-specific. If they don`t, be prepared for problems such as the ones arising from the use of different extract and transformation rules, differences in scrubbing and cleansing as well as synchronisation.
- Metadata is the very heart of the data warehouse. Pay special attention to it.
- A data warehouse must be dynamic; if not, it will soon be a big white elephant.
Changes needed in our data warehouse:
- A corporate data warehouse administrator/architect with a clear mandate that is supported by both client and DP management.
- A separate reporting group with responsibility for assisting the expert clients as well. All non-trivial querying and reports should be channeled through them.
- Identification of "expert" clients; it is costly to allow casual users to run queries that process for hours and are then incorrect.
- More staff to the different functions, such as data mining, OLAP and metadata.
Chronology of major events:
1988-1990: An executive order, followed by a committee consisting of client representation that categorised the problems into: 1) those that needed immediate attention; 2) those that we planned resolving with the data warehouse; and 3) those that required fixes to the operational systems.
1991-1996: A plan that called for an iterative approach of five stages was developed. Each stage involved data modelling using Cayenne`s GroundWorks, physical design using Cayenne`s Production DBA for DB2, extract and transformation, scrubbing, cleansing and loading of the detail data and client testing. Clients were given training on the data, IBI`s Focus product and relational theory.
1997-1998: Clients, with DP support, have set up their own aggregate tables, and we will refine these over time. We now have the task of refining our metadata by making it more interactive and dynamic. We are addressing the OLAP and data mining requirements of various clients.
Our data warehouse project was developed over a longer period of time due to a number of factors. We were ploughing new ground. Staffing was minimal, and emphasis was placed on other operational projects. The success of the data warehouse is due to the cooperation and effort of many DP and client staff members.
* This article originally appeared in DM Review* Cayenne Software is represented in South Africa by Software Futures, a company in the Computer Configurations Holdings group.
Share
Editorial contacts