A Data Warehouse--The Best Buy for the Money Copyright CAUSE 1994. This paper was presented at the 1994 CAUSE Annual Conference held in Orlando, FL, November 29- December 2, and is part of the conference proceedings published by CAUSE. Permission to copy or disseminate all or part of this material is granted provided that the copies are not made or distributed for commercial advantage, that the CAUSE copyright notice and the title and authors of the publication and its date appear, and that notice is given that copying is by permission of CAUSE, the association for managing and using information resources in higher education. To copy or disseminate otherwise, or to republish in any form, requires written permission from CAUSE. For further information: CAUSE, 4840 Pearl East Circle, Suite 302E, Boulder, CO 80301; 303-449-4430; e-mail info@cause.colorado.edu A DATA WAREHOUSE--THE BEST BUY FOR THE MONEY Leonard J. Mignerey The Catholic University Of America Washington, D.C. ABSTRACT Most, if not all IT professionals in university environments are operating in the extremely stressful reality of shrinking resources and expanding demands for service. Coupled with this dynamic is an increasingly rapid technology cycle. If demand could be held level this factor alone would be exerting extreme pressure on the IT environment. The beginning of the paper briefly describes why Data Warehouse technology is a smart investment, in both resources and technology, and why it provides added value to the user community. The Catholic University of America's (CUA) experience in successfully implementing a pilot Data Warehouse project is also described. A DATA WAREHOUSE--THE BEST BUY FOR THE MONEY CUA's MIS department is continuously trying to answer the "Where do we go from here?" question. To accomplish this, current technology is constantly analyzed to separate the fact from the hype. Once the facts are established, a determination is made as to where our very limited development dollars should be spent to provide the greatest possible service improvement to the user community. Our goal can be stated as rule: Provide the greatest short term Information Technology (IT) improvement to the user community within the long term context of building an IT architecture that will be capable of evolving to the technologies of the future. Data Warehouse technology fits this metric. This technology is based on the premise that there are two fundamental types of data existing within any enterprise. The first and most widely understood is termed operational data. Operational data is the data that directly supports the business functions and for which the majority of applications have been written since business programming became a practice. The second type of data is informational data. This is the data that supports the decision-making process of an organization and as a specific form of data, it is not as well understood as operational data. Many organizations have not as yet made the distinction between the two data forms. W.H. Inmon (1993), in his landmark work, Building the Data Warehouse (QED Press, Wellesley, Massachusetts), offers the following definition of a data warehouse: "A data warehouse is a subject-oriented, integrated, time- variant, non-volatile collection of data in support of management's decision making process."...The driving force behind the evolution to the data warehouse is the need to gain informational access as opposed to operational access to corporate data. Operational access means access to the current state of specific instances of data .... Informational access, by contrast, implies access to large volumes of data for higher level assessment, planning and strategic decision-support activities. (Ferrara and Naecker, 1993, pp. 26-28) Differentiating operational data from informational data dictates a fundamentally different design criteria for the operational database versus the data-warehouse database. An operational relational database is (theoretically) built according to the rules of the first three normal forms. In brief, data is stored in its elemental form, there is no redundant storage of data, and any required data that does not represent an elemental data element is derived from an amalgamation of elementary-data elements. Data can be both extracted from and stored into the database. In general, the database is optimized for the update process not the extraction process. The source of the operational data is generally from interactive on-lines and the operational database is designed with great care. The functional processes of the enterprise are supported by the implemented structure, which is built according to the rules of the standard Software Development Life Cycle (SDLC). On-going revisions to the basic data structure are not part of the plan. The data warehouse is not built to support the functional process of the enterprise. It is built to facilitate the use of information. The source of data for the data warehouse is the operational database, which is optimized for the extraction process. In fact, the data warehouse can only be updated by the operational database; it is a read-only resource. Unlike the operational database, the normal-form rules do not apply and any de-normalization in the design that will facilitate the information-gathering process is acceptable. Therefore, fields containing summarized and other forms of derived data are perfectly acceptable. "Most access of the warehouse is at the higher levels of summarization. These levels contain less data than the lower levels do and are indexed on many fields. The lower levels of data are indexed on only a few fields" (Inmon and Kelley, 1993, p. 38). Furthermore, the design is iterative in nature. Since a warehouse does not support a suite of update applications it is not dependent on a pre-defined data structure; and, because the warehouse assumes the predominance of ad-hoc usage, design changes can be made as the need becomes apparent. Therefore, there is minimal impact resulting from design-change requests because only the interface between the two databases is affected. The first step in data warehousing is to simply create a specialized, replicated database that is optimized for the "what- if" informational needs. The only additional technology needed for this step is a method to perform the extraction of data from the operational database into the data warehouse including the appropriate machinations for aggregation. Although it is certainly possible to develop this interface, there are a number of commercially-available solutions from the major database vendors. The data warehouse is ideal as a centrally-maintained, distributed resource. The user community can help design it and is then free to use Rapid Application Development (RAD) technology to build its own applications for access, with the support and encouragement of the IT staff. This is a significant role reversal for the IT and user communities-- IT is doing the data entry and the user community is building the tools to use the data! At CUA we decided to build a prototype data warehouse. The first step was to identify a pilot group. A pilot group should have certain characteristics, the primary one being an active interest in the concept of a data warehouse. Furthermore, the members of this group must be willing to set aside time in their schedules to participate in the process. The choice of members for this group is very important because a successful pilot project sets the precedent for enhancements and expansions that will follow the project into its production phase. The success or failure of the pilot project will influence resource allocation for additional data-warehouses around the campus. At CUA, three individuals, the Registrar, the Enrollment Management Analyst for Admissions & Financial Aid, and the Assistant Director for Financial Aid were asked if they would be interested in participating in a data-warehouse pilot project; all three accepted. These individuals do not have comparable positions on the university's organization chart, but they each had an active interest in more efficient data access for reporting purposes. Two additional individuals, the Director of Financial Aid and the Assistant Registrar lent support and suggestions to the group out of general interest for the project. The first phase of the warehouse project focused on the immediate improvement of the reporting capabilities available to the pilot group. With the exception of this pilot project, CUA's operational database serves as the sole source of ad-hoc reporting on administrative data. This database contains 100 tables and 921 data domains; it was designed to support the functional processes of the university rather than the decision/planning processes. Although the functional structure provides the ability to perform ad-hoc reporting, it is not the ideal structure for report generation. In a normalized database, data stored as elementary data elements, serves the on-line update applications very well. However, the query process is complex for even simple types of extractions, such as extracting a translation of a code along with, or instead of, the code itself. Extracting code translations is one of CUA's biggest problems with ad-hoc queries and provides a prime motivation for building a data- warehouse. For multiple translation retrievals, one must make the database treat the INDIVIDUAL_CODE table as if it were a series of separate tables, each containing the values for a specific CODE_TYPE. Each reference to the INDIVIDUAL_CODE table must have a unique name or alias. For some "real-life" queries, this can quickly reach a level of complexity that is too intricate for the user, an ad-hoc query tool, and eventually the system itself. Retrieval times shoot up into hours rather than minutes; some retrievals have run for more than a day in a test environment before having to be terminated. An additional problem with using CUA's operational database as a query resource relates to the large amount of data that it stores. In private industry the operational database is a relatively small entity designed to control daily functioning. For example, once the widget is manufactured, sold and paid for, the operational database does not need to track it. In this environment the data-warehouse is the larger of the two databases. It is designed to provide a resource for historical data, and management uses it for analysis and planning. A university environment is the exact opposite of most industries and is also opposite the general concept of data- warehousing. In a university environment, data stays active on a student for many years; so the historical database is the operational database. On-line programs quite often access, and sometimes update student data from prior semesters. Also, the operational database contains preparatory information for future semesters. Management, however, does most of its analysis on the current and future academic years, and is only rarely interested in the full historical database. The historical data often present an un-necessary level of complexity for managements queries. DISCUSSION OF PROJECT General Discussion of VAX Data Distributor Because CUA participates in Digital Equipment Corporation's (DEC) Campuswide Software License Grant (CSLG) program, and because CUA is already using DEC's Rdb for our operational database; DEC was the prime candidate as a source to provide a tool to implement the data-warehouse project. Included in this program, which allows CUA to use much of DEC's software for one low fee, is a product titled VAX Data Distributor (VDD). The following description from the VDD documentation provides a general overview of the product: Data distributor makes data available to users and applications at multiple sites in a network. From a source database, Data Distributor enables you to perform the following tasks: Transfer an entire source database or a subset of that database. The target of the transfer can remain on the same processor or can be on a remote processor. Create a target database that maintains a relationship with the source database. By maintaining this relationship, Data Distributor can periodically update the target database to reflect any changes made to the source database. Transfer data from multiple source databases into a single target database. Schedule transfers for future, automatic execution. (DEC, 1993, p. 10) Conceptually, VDD does not do anything that could not be done manually by an experienced database administrator. The strength of the product lies in its ability to automatically generate all the database code that is necessary to create and maintain a target database, the contents of which are based on the contents of a source database. It can be thought of as a 4-gl for database administration. Based on a set of user-supplied requirement definitions, it generates complex database code. The VDD process of creating and/or maintaining a target database from a source database is called a Transfer. There are two fundamental types of Transfers: extraction and replication. Both types of Transfers can be done on demand, or they can be based on a defined schedule. Extraction Transfers create a complete new target database each time that the Transfer executes. A replication Transfer only transfers those data items that have changed (insert/update/delete) since the last Transfer process executed. The replication Transfer was initially considered to be the superior choice because the total transfer time should theoretically be shorter than an extraction Transfer on a database that does not generally experience heavy updating. Except for some pre-defined periods, CUA's database fits this criteria. However, further research into the replication Transfer revealed enough negative characteristics that, at least for the pilot project, the extraction method was chosen. The two major drawbacks were: a) the performance impact on the operational database, and b) the replicated tables had to match exactly the source tables, eliminating the possibility of moving the translation values from the INDIVIDUAL_CODE table to the same level as the coded values (denormalizing). Creating a Transfer Once the extraction method was chosen, the process of creating a workable Transfer began. The first step was to create the necessary VIEWS on the source database that would create the TABLES on the target database. The VIEWS needed to incorporate three criteria (a) they needed to contain matching translation fields for the requested coded fields, (b) they needed to contain only those student records from and including the first semester of the 1993-1994 academic year, and (c) they needed to exclude any records that had been marked as deleted. The relationship between three key tables; CORE_DATA, ACADEMIC_CORE, and PROSPECT, presented a problem for the Transfer process. These are the three parent tables to all the other tables for the Admissions system, the Registration system and the Financial Aid system. There is a row in the CORE_DATA table for every student who is represented anywhere in the database. It is the basic table that contains fields like NAME, TITLE, etc. The ACADEMIC_CORE table contains basic academic data like SCHOOL, MAJOR, CUMULATIVE_AVERAGE, etc. The PROSPECT table contains basic Admissions data like SAT_SCORES, HIGH_SCHOOL, etc. Many of the VIEWS required logic that would include a data row if the student was represented in either the ACADEMIC_CORE table OR the PROSPECT table. When this limiting logic was combined with other criteria in the WHERE clauses, some selection processes took over an hour to start returning data. It was necessary to create a special-purpose "driver" table in the source database to solve the problem. All records in the database for a particular student are related by a special-purpose field that contains a unique number, ID_SYNTHETIC. To solve this processing problem a table (WH_DRIVER_AD_FA_RG) was created that contained a single field, ID_SYNTHETIC for those records that met the limiting "OR" condition. Because this statement contains only one field and because there are no other conditions added to the WHERE clause, this selection starts returning rows almost immediately. The source database can actually load the 59,801 qualifying rows in 00:05:49. The remainder of the selections now include only an equality match to this driver table. The previously complex WHERE conditions are now simplified to: WHERE some_table.ID_SYNTHETIC = WH_DRIVER.ID_SYNTHETIC AND {any other conditions specific to the table} With this method, the selections written for the 20 tables requested by the user group all started returning rows in 00:02:00 or less. To use this method, the driver table must be re-created before every Transfer. Fortunately VDD provides for user- controlled Transfer pre-processing and Transfer post- processing. VDD permits the definition of a Prologue command procedure and an Epilogue command procedure. These command procedures can contain any valid commands that can normally be executed in either Digital Command Language (DCL) or interactive SQL. In this situation, a prologue command procedure was created to perform the following steps: I. Drop the existing special driver table (WH_DRIVER_AD_FA_RG) II. Create a new driver table III. Load the new driver TABLE based on the above explanation IV. Create a unique index based on the sole field, ID_SYNTHETIC With this process in place, the environment was established to create an actual test Transfer using the tables and fields requested by The Group. VIEWS for each of the twenty tables were created on the source database and a simple epilogue procedure was written to place indexes on the target tables after the Transfer was complete. The most translations requested by the Group for any single table were six on the ACADEMIC_CORE table. Once all syntactical problems were corrected, the Transfer was initiated. The total process took approximately 00:01:45 to complete. Refining the Transfer After the initial prototype proved to be functional, The Group was reconvened. Each member was handed a packet identifying the tables and fields that were contained in the new data-warehouse and a list of questions designed to further refine the design. They were asked to review the material and return all suggestions within a week. When the materials were returned, the following refinements had been requested: I. A number of additional translations had been requested, in particular five additional translations had been added to the ACADEMIC_CORE table, raising the total number of translations on this table to eleven. II. A number of tables had been further refined to contain fewer fields. III. Two tables, the ADDRESS table and the FA_ALLOCATION table, were asked to be de-normalized. The ADDRESS table as designed in the operational database contains seven possible address types for each individual. To retrieve a particular address the user has to, (a) cross the ADDRESS table with the ADDRESS_DATA table, (b) specify the correct ADDRESS_TYPE code, and (c) specify a linking field, ADDRESS_NUMBER. The Group was only interested in two of these address types for the data-warehouse. Therefore, two new tables were created: CURRENT_ADDRESS and PERMANENT_ADDRESS. Addresses can be retrieved directly from them without any crossing. The FA_ALLOCATION table contains the dollar figures indicating how much money a student is to receive in aid per semester. If the user wants to retrieve all allocations for a particular academic year the FA_ALLOCATION table had to be crossed over itself three times to retrieve the data. The data-warehouse FA_ALLOCATION was de-normalized to contain parallel fields for FALL, SPRING, and SUMMER all in the same row. These changes were added to the VIEW definitions that had been initially defined and the Transfer was re-run. Eight hours later the Transfer was manually terminated without having run to completion. When the log files of previous runs were compared to this run, the transfer time on the ACADEMIC_CORE table had increased from 00:12:35 to 02:31:59. When the transfer time for the table was divided by the number of records transferred it was evident that the data- record transfer rate had dropped from 46 records/second to 3.04 records/second. Obviously, the additional translations that now had the ACADEMIC_CORE table crossing the INDIVIDUAL_CODE table over itself 11 times, had reached some critical mass. CUA's systems manager was consulted and the machine resource Input/Output statistics were reviewed for a test Transfer that included only the ACADEMIC_CORE table. From reviewing system performance statistics it was evident that all the activity was absorbed with the database's attempt to resolve the translations, while very little actual data was being accessed. The multiple crosses of the INDIVIDUAL_CODE table were the obvious source of the problem. The first attempt at a correction was to create, on the source database, individual tables for each of the required translations. These individual translation tables contained only those code values that matched the code type of a specific translation. The idea was based on the assumption that Rdb would have an easier time loading values from many small tables than it would with loading values from one large table that had many virtual copies of itself. Since the ACADEMIC_CORE table contained the most translated fields, it was chosen as a bench-mark test table. A special Transfer process was defined to create a target database containing just ACADEMIC_CORE. The WHERE condition in the SQL code replaced the multiple crosses of INDIVIDUAL_CODE with 11 equality conditions, each satisfying a single translation value. This extraction method produced a transfer time on the ACADEMIC_CORE table of 04:33:28 and data record transfer rate of 1.6 records/second, which was surprisingly worse performance than the use of the single INDIVIDUAL_CODE table. It was next theorized that the performance problem may be related not only to the large number of crosses or tables in the select statement, but to characteristics of VIEWs that may not be encountered if native TABLEs were used as the source for the Transfer. Although VIEWS appear to the user as a TABLE, they do not actually exist until a selection request is made against them. However, another VDD limitation was encountered at this point. In the Transfer definition statement, a SELECT statement can be used on a TABLE to filter the data that is actually transferred to the target TABLE. However, a SELECT statement used in this manner is restricted; only the TABLE being transferred can be named in the SELECT statement for that TABLE. Since the WH_DRIVER_AD_FA_RG table was necessary to make the selections start returning rows in a reasonable time-frame, this had a major impact on the project. If the driver table was to be used, VDD syntax dictated that VIEWS must also be used. The next idea was to remove all processing involving the code translations from the source database VIEWS. The INDIVIDUAL_CODE table would be added to the list of tables in the Transfer and re- created on the target database. Once the Transfer process had completed, the epilogue command procedure would then create individual code_translations on the target database. The transferred data tables would then be ALTERED on the target database to contain fields for the required translations. The tables would then be UPDATED on the target database using these individual code tables as the source for the translated values. Another test Transfer was defined for ACADEMIC_CORE. The select statement only contained a cross with the driver table and a few fields to force the use of an existing index. This time the Transfer of ACADEMIC_CORE took only 00:04:16 and had a record transfer rate of 127 records/second and the system performance statistics now showed a much more balanced I/O picture. This method was then extended to all 20 tables that were to be part of the pilot data-warehouse. The full Transfer ran with a total elapsed time of 02:58:24. The elapsed times for the individual components of the Transfer were: I. Elapsed time for the prologue command procedure--00:08:16 This included the time to drop, create, and reload the special driver table. II. Elapsed time for the actual Transfer procedure--01:27:04 This included the time to create and load the 20 tables in the target database that were specified in the source database. III. Elapsed time for the epilogue command procedure-- 01:23:04 This included the time to: A. create indexes on all the new tables, B. create and load the individual translation tables, C. alter the data tables to contain short and long translation fields, D. update the data tables with the actual translations, E. create and load the VALID_FIELD_VALUE_LOOKUP table. This is the table that gives the users an on-line dictionary resource for all the valid coded values and their translations. A Transfer schedule was then created and the process was scheduled to run every day at 22:00:00. The Transfer log was examined each morning for any reported errors until all syntactical errors had been removed from the Transfer. A review session was then scheduled with The Group for their first hands-on experience with the data-warehouse. RESULTS Response to the product was very good. The Group felt that the data-warehouse demonstrated all the requirements they had asked for in the design stage of the project. They were particularly pleased with the ability to look up code values and as expected, the existence of the translation at the same level as the coded fields was very well received. The user group has now been given access to the warehouse and they are in the process of evaluating it. They have been reminded that this is an iterative process of building successively better prototypes and that they should feel free to be critical of the product. The project will continue past this initial delivery and it should evolve into a production system within a fairly short time period. CONCLUSIONS The successful completion of the data-warehouse pilot project and the pilot group's enthusiastic response to it has demonstrated that it is a needed resource at CUA. In a recent interview for Forbes magazine, Michael Hammer makes an interesting comment about the nature of work. "Work is the way in which we create value for customers, how we design, invent and make products, how we sell them, how we serve customers" (Karlgaard, 1993, p. 70). It is an extremely important concept. It is increasingly easy for managers of technology to lose touch with the "added value to the customer" component of the job. It is exceptionally easy for those managers to justify technological change from a technological perspective, and it is often difficult for them not to. The rate of technological change is so great that significant amounts of time are spent figuring out how to maintain functioning systems as technology continously changes out from under them. The data-warehouse adds value to the CUA user community. It provides users a way to perform a portion of their work more quickly and easily. The data-warehouse is also in line with current technology trends. As the distributed computing, client-server paradigm evolves, the issue of information retrieval must be totally re- evaluated. In many respects the industry is still attempting to do flat-file reporting against relational databases. In the future we will need to develop technology that can abandon the process of examining retrieved data for information, and instead will be intelligent enough to automatically provide the end product (information) to the appropriate clients, be they silicon or carbon-based. Reference List: Digital Equipment Corpopration, DEC data distributor handbook. Marlboro: Digital Equipment Corporation, 1993. Ferrara, R., & Naecker, P. A., The data warehouse: A giant step forward. DEC Professional, 1993, 12(11), 26-39. Inmon, W. H., & Kelley, C., Rdb/vms: Developing the data warehouse. Boston: QED Publishing Group, 1993. Karlgaard, R., Interview: Mike Hammer. Forbes ASAP, September,1993, pp. 69-75. 11/10/94 LJM