Spanning the Reporting Abyss

1
Spanning the Reporting Abyss

• A Reporting Strategy
• using functional tables
• in a data warehouse
• UM Reporting Task Force
• March 13, 2003

2
About the Presenter

• Art Brooks, Dir ADP UMR
• 12 years in Admissions and Registrars Office
• 24 years in administrative computing
• 16 years involved with data warehousing
• Participant in the development, implementation
  and for several years the support of current SIS
• Involved at various levels with SFA, HR, Univ.
  Advancement and other systems
• Participant in the design and a user of the data
  warehouse and functional tables.

3
Basic Perspectives

• 1. A core system is a corporate foundation NOT
  an entire solution!
• 2. Reporting can only be successful when there
  is a solid database foundation.
• 3. Trying to be the solution to everyones
  needs, satisfies no one.

4
Many dimensions of reporting

• A. Vendor provided
• 1. Payroll
• 2. Purchasing
• 3. Grade reports
• B. External agency requirements (may be vendor
  supplied)
• 1. State mandated
• 2. Federal Mandated
• 3. Grant applications

5
Additional dimensions

• C. University-wide
• 1. Budget planning
• 2. Miscellaneous reporting
• D. Campus unique
• 1. Phone directories
• 2. Lists and labels
• 3. Miscellaneous
• 4. Ad hoc

6
Multiple Data Sources

• Need to weigh requirement for currency of data,
  then apply to appropriate data source.
• A. Real time (small percentage and must be
  justified) (production system)
• 1. On-line registration
• 2. Account balances
• 3. Leave information
• B. Close of business day (majority of usage)
  (data warehouse)
• 1. Phone directories
• 2. Mailings of any kind
• 3. Standard reports
• C. Frozen data (data warehouse)
• 1. External agency reports
• 2. Longitudinal reports
• 3. Statistical analysis

7
Corporate Systems

• PeopleSoft
• 1. Finance
• 2. Human Resources
• 3. Student
• Other corporate systems include (but not limited
  to)
• 1. Advance (University Advancement)
• 2. fsaAtlas (Sevis reporting) (INS)
• 3. Loan Management System

8
Data WarehouseA logical reporting solution

• The most logical arena where corporate systems
  and campus unique systems can be assembled to
  support the business needs of the organization.
• Since NO vendor provides an all-inclusive suite
  of software for the Universitys business needs
  in totality, a data warehouse is the sole
  solution.
• The data warehouse needs to be viewed as the
  foundation for daily business reporting. (i.e.
  administrative core systems need to be viewed as
  the source for accurate data, a data repository,
  and the data warehouse the distributor of that
  collective data.)

9
Data WarehouseOut-house over In-house(Out
of core system vs In core system)

• Maintained outside the core system reduces the
  politics of the data contained.
• Allows for the definition to be molded according
  to the needs of the local entity.
• Provides greater flexibility in structure.
• Provides for the potential of local control.
• Makes it easier to incorporate local data/systems
• Keeps consistency in the event of a major change
  in the core system.

10
UMR Administrative Systems Staff1 director, 5
programmers to provide some level of support for

• Affirmative Action
• Bookstore
• Admissions
• Budget
• Cashiers
• Chancellors Office
• Deans offices
• Faculty Evaluations
• Food Service
• Grants

• Human Resources
• Institutional Research
• International Affairs
• KUMR radio
• Registrars Office
• Residential Life
• Student Affairs
• Student Financial Aid
• Student Loans
• University Advancement

11
Summary of UMRs Data Warehouse Experience

• 1. August 1986 initiated the campus data
  warehouse effort.
• 2. January, 1988 introduced first activity on
  Gopher. (precursor to the Web) (electronic class
  rolls)
• 3. Fall, 1998 functional table concept
  formulated.
• 4. May, 1999 functional table concept first
  applied with University Advancement system.
  (Advance)
• 5. March, 2000 impact of PeopleSoft on
  reporting inventoried (applications/reports
  ceasing to function)
• A. Approximately 3,000 reports
• B. 26 applications
• 6. October, 2000 began translating PeopleSoft
  data into legacy format.

12
Functional Table Concept

• A reporting strategy that focuses on formatting
  the report, NOT selecting the data. It utilizes
  the creation of event oriented, functional
  tables.
• GOAL to produce reports with ZERO table joins
  and ZERO where statements.
• Basic principle SIMPLIFICATION

13
Functional Table Concept was developed to

• 1. Empower the users
• 2. Simplify the data structure
• 3. Reduce report development time
• 4. Reduce processing time for the server (quicker
  response)
• 5. Improve programmer efficiency
• 6. Provide another tool for reporting

14
(No Transcript)
15
Basic Process

• User makes request to functional lead, including
  a draft of the desired report, if possible.
• The design team (functional lead programmer)
  defines a unique table that may be a combination
  of several core system tables, or core tables and
  local tables.
• That new table pre-selects data and eliminates
  the need for such selects as
• 1. Location (campus)
• 2. Effective date
• Report is written by user/functional
  lead/programmer

16
Usage with PeopleSoft

• 1. Determined the UMR or UM data warehouse, by
  definition could be viewed as a set of functional
  tables.
• 2. Basic premise if the legacy data could be
  converted to PeopleSoft, then the PeopleSoft data
  could be translated to a functional table.
• 3. Have NEVER stated 100 of the PeopleSoft data
  could be translated.
• 4. Perspective if SOME of the applications or
  reports can be retained, then more time is
  available for staff to work on new requirements.

17
Misunderstanding

• All you are trying to do is keep the legacy
  system running to avoid a new system!
• NO!
• Programming resources are scarce.
• Offices MUST keep running in the transition to a
  new system.
• NO vendor can supply more than a base set of
  reports with their system.
• We are trying to keep our reports running until
  replacement reports can be prepared.
• Without their daily reports, offices will either
  be seriously impaired or cease to function.
• The technique is used with other applications on
  a daily basis.

18
(No Transcript)
19
Introduction of Hybrid Tables

• After further experience and discussion it was
  realized the functional tables could be
  hybridized to satisfy specific reporting needs
  and to provide a transitional bridge to the
  future.
• Definition a hybrid functional table is one
  that has data derived from disparate systems.
  (normally legacy and PeopleSoft)
• Hybrid tables can become transitional tables.
• With time, hybrid tables can become normal
  functional tables. (When the legacy data is no
  longer required, the columns cease to be filled.)

20
(No Transcript)
21
In Production -- NEW

• 1. Admissions reports (on the Web or on demand)
• 2. Admissions edits (compares PeopleSoft data
  with legacy to identify discrepancies between
  systems)
• 3. Faculty grant reports on the Web
• 4. Institutional Research analytical tables
• 5. Vacation/Sick Leave reporting
• Other uses (non-PeopleSoft)
• 1. Advance system (hundreds of reports)
• 2. Miscellaneous reports

22
In Production -- Legacy

• 1 ChemTrack (campus chemical tracking system that
  requires faculty data from HR)
• 2 CIS account creation and maintenance (HR data)
• 3. Graduate Teaching report (legacy data from SIS
  and Cashiers with HR data from PeopleSoft)
• 4. HR frozen tables.
• 5. Miscellaneous lists and labels from HR
  (required minor tweaking for length of line)
• 6. PRO (new student pre-registration) (in
  production using translated PS data since Jan,
  2001)(starting THIRD year)
• 7. UIDS (HR data. NOT in production, but ready
  for testing)

23
In Production -- Hybrid

• 1. Affirmative Action (for 2003 report, data came
  from PS 2002 HR and legacy 2001 HR)
• Chancellors summer mailing to newly admitted and
  currently enrolled students (address data from PS
  for admits and SIS for currently enrolled.)
• 3. Chancellors Christmas cards (HR and Retiree
  from PS and University Advancement from Advance
  system)
• 4. Exam data (PS and SIS)
• 5. SEVIS reporting (Admissions data from PS and
  enrolled data from SIS)
• 6. Web phone directory (HR from PeopleSoft and
  student from SIS)

24
PeopleSoft Modules Involved

• 1. Admissions in production
• 2. HR in production
• 3. SA still testing and developing. Have been
  able to get results from over 150 legacy reports.

25
Disadvantages to Functional Tables

• 1. A nightly refresh window MUST be considered
  and tactical decisions made.
• 2. Documentation may be more difficult to
  establish and maintain
• 3. Knowledge of the base system is lost (blind
  faith on programmer creating the table)

26
Advantages of the Functional Table Strategy

• 1. Simplicity in data presentation and
  development.
• 2. Reports run significantly faster.
• 3. Changes in data standards much more adaptable
  (One place to make changes in data interpretation
  and NOT in every report.)
• 4. Provides a transitional bridge from legacy to
  new system
• 5. Provides a greater potential to empower the
  users.
• 6. Reporting accuracy improved and many potential
  errors removed.
• 7. Reporting consistency is greatly enhanced

27
Plus

• 8. Shorter learning curve (no requirements on the
  part of the report writer to learn the core
  system data structure or methodology)
• 9. Allows for continuation of longitudinal
  studies
• 10. Allows for the creation of hybrid tables and
  thereby creating a TRANSITIONAL BRIDGE to SPAN
  the REPORTING ABYSS.

28
(No Transcript)
29
Questions?