Developing a Data Warehouse: Processes, Outcomes, and Unforeseen Challenges and Opportunities

1. Poster presented at 2001 Educause Conference, Indianapolis, Indiana. Developing a Data Warehouse: Processes, Outcomes, and Unforeseen Challenges and Opportunities Todd L. Chmielewski – Office of Institutional Planning and Research Russell Patterson – Information Services Gerald W. McLaughlin – Office of Institutional Planning and Research DePaul University Chicago, IL

2. Abstract After obtaining a new enterprise transactional system, DePaul faced many challenges including data stored in different operational systems, in a variety of formats and platforms, and under the control of different data stewards. Our presentation will discuss the efforts to implement a centralized data management center that deals with these problems, while allowing data users access to a common set of data.

3. Decision-makers depend on reliable information to determine and set the direction, objectives, and goals for their institutions. Extensive data processing is required to derive this information (McLaughlin, Howard, Balkan, & Blythe, 1994). Data must be located and retrieved rapidly, cleaned and converted into an analyzable format, and finally, analyzed and evaluated. As such, two challenges to the data management process are ensuring efficient data accessibility and reliability (see Porter and Rome, 1995). An increasing number of institutions are converting from legacy systems to the newer integrated ERP operating systems such as PeopleSoft and SCT Banner.

4. Our institution has been engaged in such a conversion for the last several years. One of the promises of the new systems has been the provision of data and information necessary for us to better manage our institutions. It is common for decision support functions to be a low priority consideration compared to basic operations during a major information system migration. Moreover, much of the work done to ensure a successful migration of operational systems is useful for improving the integrity and availability of decision support data. Migration often raises expectations of improved access to more comprehensive and comprehensible decision support information. It soon becomes obvious that the new operational systems do not provide such information, per se. Those who market the ERP typically have examples of analysis tools such as the Balanced Scorecard and dashboard metrics.

5. It is inevitable that there will be some loss of data access and decision support capabilities immediately after an operational system migration. Failure to tend to this setback during or soon after such a migration is, at minimum, a lost opportunity. The Gap: The new ERP system was brought in to replace existing Legacy databases. These databases had evolved over a 20-year period guided by the needs of the institution, yet were rapidly becoming obsolete. Many challenges occurred while trying to regain functionality during our transition to the new system. In order to understand these challenges, a brief outline and description of the evolution of the Legacy systems is warranted.

6. Data Management History End of the 1970's - showed most institutional records kept in hard copy format, such as paper files. Enrollments, hours taught, grades, degrees awarded were all captured and recorded using typewriters. Early 1980's -our institution developed a transactional-based, electronic data gathering system called AIMS (Administrative Information Management Systems). Users could look at individual information, such as a class roster, an application, or one student’s grade history. In order to do group analyses, reports were developed by the Information Services Office based on user specifications (e.g., all seniors with a GPA better than 3.75 who were History Majors).

7. Early 1990’s - we developed web-based applications for students and staff. These applications were data driven and supported by database programs. The Information Services Office recognized the need for a relational database drawn from student data. This one year beta project became known as the ODS (Operational Data Store), later referred to as AIMS (the new version). It was the source of archival data for the ERP project, web applications, and supported the data needs of the Computer Science School. Current – Migration to ERP While an ERP system is ideal for supporting multiple users who are frequently inserting, deleting, and updating information, the system’s ability to provide efficient and reliable data is limited (HERAPAG, 2001). Given that it is essential to have timely data that is both efficient and reliable, the decision was made to develop a data management information center.

8. Managing Expectancy and Risk Why develop a centralized data management center? Regain Previous Improve Improve Functionality Efficiency Decision Making LEADS TO LEADS TO Increase Decrease Increase Profits and Revenue Expenses Market Share Figure 1. Diagrammatic representation of Bill Inmon ’s (1996) discussion of the advantage of data warehouses.

9. Regain Previous Functionality - Previous procedures for extracting, cleaning, and reporting data are either obsolete or completely irrelevant to the new system. Certain data is mission critical and must be accessed. Improved Efficiency – While initial implementation will most likely drain resources, if designed properly the warehouse will help get reliable data more quickly than constant writing queries to access data directly from the ERP. Improved Decision Making – It is generally accepted that data warehouses provide more quality data (Inmon, 1996). Given the right management information processes are in place within an organization, more data should mean more information. This information can be used to improve decision-making. Managers must monitor and measure the amount and value of information they receive and judiciously utilize it to add value to their existing knowledge base (McLaughlin, Howard, Balkan, & Blythe, 1998).

10. These three processes are not independent. Certainly an organization could regain functionality either efficiently or not, while not improving decision-making. The important point is that the organization needs to determine its needs, objectives, and priorities in order to plan the management center. There are 3 key points that need to be made once the needs, objectives, and priorities are defined: • Explain in simple terms the difference between an operational system (ERP) and a strategic system (Data Management Center). • Demonstrate how a strategic system will help efficiency and effectiveness of data management function. • Demonstrate resources currently devoted to extracting, developing, and cleaning usable data marts from the operational system.

11. In discussions with both senior managers as well as end users, planners must be careful not to raise expectations too high. A management information center is an environment that is used by data custodians, brokers, and managers. Overall the management information center may actually need increased resources in the beginning, the needed resources should be reduced. Once a management data function is in place, many organizations reassign staff time to analyzing and reporting the data. Two potential impediments may occur when developing a data management information center. First, one can focus too heavily on the resources needed to implement the system, such as software, hardware, and staff. • Such a system allows for numerous alternatives and many of the most appealing ones are not only very costly, but also take several staff members to run. • Foresight and planning are essential.

12. Second, one can focus too much on the concepts, principles, and beliefs associated with setting up the system. • Generally the process is dynamic, thus already in motion • If the process is not defined and controlled, the motion will be in the direction of decentralization • Once a certain point is reached in the process, it becomes extremely difficult to reengineer the data management function In order to build the bridge, practitioners working with data need to remember that the final result (i.e., efficient access to reliable data) is what is ultimately important.

14. Steps in building the bridge: • Progress is best made with the involvement of IR, IT, and the functional groups. We have this as part of our Management Information Group. Communication that is regular, formal, and pragmatic is essential for success. We have this as part of our Campus Community Group. • It had to be decided what approach should be taken. Using a two factors approach; Product (i.e., home grown vs. off-the-shelf) and formal/non-formal (i.e., reorganization vs. everybody helping out). Each approach has strengths and weaknesses.

15. Management data needs to be developed to meet a specific need. Initial efforts involve the back-feeding of data into former reporting structures. However, genuine progress is only accomplished when Management Information and Infrastructure is someone’s major responsibility. • There are lots of information available and several individuals who utilize the information. It is essential to document what information is needed, collected, and integrated. This documentation needs to be continually and accurately updated. However, it may be Just as beneficial to discover what data people want, but are not using because of availability, time, etc.)

16. We proposed an adaptation of off-the-shelf tools, but this was not selected because of cost (> $500k). We are currently working with a three phased approach: a). University data management function, b). Establishment of Data Marts, c). Integration into a “warehouse”. • ERP is now proposing another off-the-shelf, but the price has not been determined.

17. Lessons Learned • Identify the gaps between previous and post-ERP management data functionality • Communicate those gaps with Senior Management • Do not set expectations too high; this is a process that evolves over time • Do not get caught up in wishing for expensive technology or trying to design the perfect architecture • Work together and communicate • Make the strategy consistent with current organizational structure

18. References Higher Education Reporting and Analysis Product Advisory Group – HERAPAG, (2001). Reporting recommendations for PeopleSoft. Report presented to PeopleSoft from the Higher Education Reporting and Analysis Product Advisory Group. Inmon, W. H., (1996). Building the data Warehouse. John Wiley & Sons, Inc; New York. McLaughlin, G. W., Howard, R. D., Balkan, L. A., & Blythe, E. W., (1998). People, processes, and managing data. The Association for Institutional Research (Resources in Institutional Research 11); Tallahassee, FL. Porter, J. D., & Rome, J. J., (1995). Lessons from a successful data warehouse implementation. Cause/Effect, 43-50.