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

1
Developing a Data Warehouse Processes, Outcomes,
and Unforeseen Challenges and Opportunities
Poster presented at 2001 Educause Conference,
Indianapolis, Indiana.

• 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 students 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 1990s - 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 systems
  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


Market Share
Profits and Revenue

Expenses









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.  
13
(No Transcript)
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 someones 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 (gt
  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.