Are we the best data warehouse in Kansas? We are working hard in that direction. Our vice-president's request for a faster way of approving access tools for the 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.

Logical modeling, using Cayenne's GroundWorks tool for each iteration or stage that we implemented, was used. (NOTE: Do not confuse this with a data mart approach; each modeling effort for the different iterations was done with the enterprise in mind). With GroundWorks we were able to make 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 synchronized back to the logical model.

Our initial emphasis was to complete one stage at a time. Each stage involved the 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 the loading, recovery and maintenance processes. DB2 for OS/390 has served us very well. IBM has made tremendous strides with the DB2 versions released since we committed to their RDBMS back in 1990.

The clients, especially those "power users" who no longer have to rely on data processing for their reporting and cross-function analysis needs, reaped the greatest business benefits.

The actuarial, utilization 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 emphasized 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 analyzing the need for specialty tables and reporting.

OLAP: We are addressing the 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 specialized skills.

Practical Advice

  • Get a corporate sponsor that understands the value of data warehousing.
  • Educate your data warehouse administrator/architect or hire one that has been through the fire.
  • The clients are most important to the success of the project; involve them from the beginning.
  • Categorize the requirements, problems and needs and publish the proposed solutions.
  • Be absolutely realistic. Do not be too ambitious, especially if you lack the appropriate skills.
  • Do not compromise good data warehouse principles.
  • Talk to your peers--do not reinvent the wheel.
  • Logical modeling is essential.
  • Physical design for analytical processes is essential (denormalize, 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 synchronization.
  • Meta data 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 Current Data Warehouse:

1. A corporate data warehouse administrator/architect with a clear mandate that is supported by both client and data processing management.

2. A separate reporting group who has the responsibility of assisting the expert clients as well. All non-trivial querying and reports should be channeled through them.

3. Identification of "expert" clients; it is costly to allow casual users to run queries that process for hours and are then incorrect.

4. More staff to the different functions, such as data mining, OLAP and meta data.

Chronology of the Major Events:

1988 ­1990: An executive order, followed by a committee consisting of client representation that categorized 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 modeling 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. The clients were given training on the data, IBI's Focus product and basic relational theory.

1997­1998: Clients, with data processing support, have set up their own aggregate tables, and we will refine these over a period of time. We now have the task of refining our meta data by making it more interactive and dynamic. We are addressing the OLAP (On-Line Analytical Processing) 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 plowing new ground. Staffing was minimal, and the emphasis was placed on other operational projects. The success of the data warehouse is due to the cooperation and effort of many data processing and client staff members.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access