Transcript Slide 1

Student Admin Data Reporting
from PeopleSoft SA
DePaul University:
Three years, three (concurrent) approaches!
Jim Janossy and Russ Patterson
DePaul Information Services
Ed Schaefer, Enrollment Management Research
Presenters and Panel
• Jim Janossy - DePaul Information Services
– Student datamart development and popularization
• Ed Schaefer - Enrollment Management and
Marketing, Reporting and Research
– Informatica ETL and BI decision and future DW
• Russ Patterson - DePaul Information Services
– Informatica ETL and BI decision and future DW
Cutting to the chase...
• Start simple and stay focused
• Establish a naming convention early
• Prototype and pilot first
• Keep security simple (ACAD_GROUP)
• Roll out useful things as you go
• “Market” to users, do ongoing training
• Get your documentation onto the web!
Three level of approach
• Student datamart
– PS table extracts packaged for easy use by 160 casual users
– Aim: day-to-day meat-and-potatoes selection and contact data
for the enrolled and active student population
– Not intended for statistical reporting
• Enrollment Management Research databases
– Current statistical reporting and census captures
– Admission reporting and day-to-day college admin stats
– Marketing measures and EM decisionmaking
• OIPR databases
– Moderate to long term statistics and trends
– IPEDS reporting
Leads to...
• Cadre of willing users
• Increased user self-service willingness and
capability
• Ability to integrate SA, HR, Finance
• Staged replacement of initial products
• Justification for a higher level ETL and BI
Leads to...
Credits
acquired
with EPM
Need for
administration
via web
Student datamart
Access, SQR
Decision:
Acquire
a standard
ETL and BI
EMR databases
Perl, SQL-Server, Access
OIPR databases
ODBC, SQL-Server, DTS
Cognos
Need for
portal
delivery
Need for
dashboards
Common base:
Reporting
instance is a
full copy of
the on-line
system
Reporting
instance
PeopleSoft Student
Admin system
Three level of approach
• Student datamart
– PS table extracts packaged for easy use 160 casual users
– Aim: day-to-day meat and potatoes selection and contact
data for the enrolled and active student population
– Not intended for statistical reporting
• Enrollment Management Research databases
– Current statistical reporting and census captures
– Admission reporting and day-to-day college admin stats
– Marketing measures and EM decisionmaking
• OIPR databases
– Moderate to long term statistics and trends
– IPEDS reporting
Limitations
PS-Query
338 users
• spreadsheet maximum 65K rows
• hard to learn data structures!
• awkward outer joins
Data
?
?
Lowhanging
fruit!
What users did
PS-Query
338 users
Access
Data
Data
Datamart approach
PS-Query
338 users
Data
Datamart
Access
Data
You can combine!
PS-Query
338 users
Datamart
Links to
tables
Local
tables
Data
Data
Links to
spreadsheets
Access
Data
!
Why datamart?
• spreadsheet maximum 65K rows
• hard to learn data structures!
• awkward outer joins
Datamart
Access
Data
Student datamart
functional look
• 22 tables
• Enrolled student data
• Assemble data for convenient use
• Focus on 20% to meet 80% needs
• Row control by ACAD_GROUP
TABLENAME
ROWCOUNT LASTLOADDATE
DP701A_BestContactData
445534 10/16/03
DP701B_DePaulDegrees
73159 10/16/03
DP701C_Enrolled
98940 10/16/03
DP701D_AbleReg
79141 10/16/03
DP701E_StudentPlans
104131 10/16/03
DP701F_GPA
195673 10/16/03
DP701G_Advisees
38593 10/16/03
DP701H_VisaData
22422 10/16/03
DP701I_CourseSched
93816 10/16/03
DP701J_CreditHoursSummary
36279 10/16/03
DP701K_FirstLatestTerm
195673 10/16/03
DP701L_AdmissionStatus
60767 10/16/03
DP701M_StudentHist
30681 10/01/03
DP701N_PotentialGrads
6570 08/04/03
DP701P_PersonalDemog
429880 10/16/03
DP701Q_BestEmail
165774 10/16/03
DP701R_Roster
90116 10/16/03
DP701S_AllStudents
205821 10/16/03
DP701T_StuGroups
98235 10/16/03
DP701X_GroupProgPlanRef
503 10/16/03
DP701Y_PSids_SSN
438503 10/16/03
DP701Z_TermRef
374 05/20/03
LASTLOADTIME
10:13 AM
09:29 AM
09:37 AM
09:19 AM
09:53 AM
09:43 AM
09:09 AM
09:01 AM
09:15 AM
09:28 AM
09:24 AM
09:09 AM
11:01 AM
2:00 PM
09:26 AM
09:05 AM
09:05 AM
09:04 AM
09:31 AM
09:01 AM
09:26 AM
03:00 PM
PeopleSoft operator id and
password
Usage
Student datamart
technical look
• Separate Oracle database
• Oracle define, create, security
• Extract data with Access or SQR
• Load with SQR or SQL*Loader
• Pilot first, engage users, perfect it
Example: Best
Contact Data Table
ADDRESSES
NAMES
PERSONAL_PHONE
EMAIL_ADDRESSES
HR: employees
CBORD: dorm assigns
Barat dorm assigns
DP701A
Best Contact Data
PS_
NAMES
370,000 rows
PS_
PERSONAL_
PHONE
366,000 rows
PS_
ADDRESSES
359,000 rows
Adhoc_
Get_Best_Name
.mdb
Adhoc_
Get_Best_Address
.mdb
DP150_Best
Name
305,000 rows
PS_
EMAIL_
ADDRESSES
182,000 rows
Adhoc_
Get_Phones
.mdb
DP100_Best
Address
294,000 rows
Adhoc_
Get_Best_Email
.mdb
DP170_
Phones
274,000 rows
DP160_Best
Email
76,000 rows
From Human
Resources
Query
Counts of rows in tables are as of
11/26/01 and are approximate, to
establish a perspective only
One
datamart table!
EMPLOYEES
.XLS
MAILPREP.EXE
Adhoc_
DP701A_
Get_BestContact
Data
FACSTAFF
.mdb
3,902 rows
From
CBORD
Query
DP701A_
BestContact
Data
ROOM_
ASSIGNS
.XLS
Adhoc_
Get_DormRooms
.mdb
DP180_
DormRooms
2,560 rows
305,000 rows
Formation of the
DP701A_BestContactData Table
DePaul SA Adhoc Datamart
n:\IAS_ADHOC_GROUP\SA_Datamart_Feeder_Backups\DP701A_Formation_colors.vsd J.Janossy 11/28/01 Rev.3 2/21/02
Goals
• Clear documentation
• Clear naming convention
• Create simple data structures
• Interpret coded values for use
• Use common tools, common skills
For table building . . .
Common tools
Common skills
Common cents
ODBC
MS-Access
SQR
SQL*Loader
Oracle roles
For users . . .
Common tools
Common skills
Common cents
Existing skills
MS-Access
Excel
Basic PC training
Views by college
For DePaul . . .
Common tools
Common skills
Common cents
Low cost
Short lead time
Low overhead
Meets 80% need
In place now
For more info . . .
See the DePaul
datamart web site at
www.depaul.edu/~datamart
Data access blossoms with DePaul’s datamart!
The DePaul University Student Datamart contains data about currently enrolled students
extracted from the PeopleSoft student administration system and staged for quick
retrieval to meet adhoc information reporting needs. The datamart is provided for use as
a regular resource to administrative personnel in the university community. This web site
provides background information, instructions for gaining access to the mart, and
documentation for the 23 datamart tables.
Overview
Questions?
Access
Examples
Training
If the information at the selection buttons above still leaves you with questions or
you need additional help, please e-mail Jim Janossy, Russ Patterson, or Gino Kao.
Documentation
To Table of Contents
Overview: How the Datamart grows!
DePaul Information Services began analysis and construction of the student datamart in
2001 after implementation of the PeopleSoft student administration system. The
datamart is housed as a special collection of data tables in an Oracle database. Users gain
access to the datamart via ODBC connection and typically use Microsoft Access as their
data extraction and reporting tool. The core of datamart users is 50 administrative
personnel in all nine college of the university. Follow the flowers to see how the
datamart grows!
Intent
Questions?
Scope
User reaction
Table list
If the information at the selection buttons above still leaves you with questions or you need
additional help, please e-mail Jim Janossy, Russ Patterson, or Gino Kao.
Extraction
Table loading
To Home Page
Access to the Datamart
Access to the student datamart is provided to administrative personnel whose job
responsibilities require the ability to acquire and use student information in their daily
work. All datamart users must be authorized PeopleSoft system users, and in addition
must file a request for mart access. Datamart users in college offices receive access to
data for students enrolled in their respective colleges, while executive department users
can access student data across the university.
Tom Paetsch, Data Administrator
Enrollment Management
Details
Questions?
View control
If the information at the selection buttons above still leaves you with questions or you need
additional help, please e-mail Jim Janossy, Russ Patterson, or Gino Kao.
Access policy
Request form
To Home Page
Information Extraction Examples
“Since gaining access to the student datamart, I have been able to do information
extractions I previously had to depend on programmers to do. Using the mart has made
it much faster and easier for me to get data I need!” says Cheryl Barkby of DePaul’s ID
Card Services Division. “I typically need to identify the enrolled student population that
meets requirements for the U-Pass program, and obtain their addresses and process
interface files to the CTA. The datamart really helps me do my work!”
Cheryl Barkby, Analyst
ID Card Services
Samples
Questions?
How to...
Linking to
PS-Query
spreadsheets
Frequently asked
questions
If the information at the selection buttons above still leaves you with questions or you need
additional help, please e-mail Jim Janossy, Russ Patterson, or Gino Kao.
Reports
Labels
To Home Page
Datamart Training
The student datamart was designed specifically to ease the burden of data access to a
complex student administration system. A major effort in the design was directed
toward extracting and staging the data that experience has shown most college offices
need to conduct their day-to-day work effectively. In order to help college office
administrative personnel use the datamart effectively, we’re providing a number of
training resources in collaboration with ongoing Human Resources office software skills
training.
Happy campers in classroom training!
Suggested
preparation
Questions?
ODBC connection
If the information at the selection buttons above still leaves you with questions or you need
additional help, please e-mail Jim Janossy, Russ Patterson, or Gino Kao.
Links to training
sites
Schedule
To Home Page
Datamart Documentation
The content of each datamart table is documented in a spreadsheet that shows the
column name, format of the column, and the PeopleSoft table and column from which
the data is drawn. You’ll also find access here to explanations of certain data columns as
well as the SQL used to extract and form the datamart tables. Review, comments, and
suggestions concerning this documentation and table formation logic is welcome.
Gino Kao
Programmer, Infrastructure Group
Table
definitions
Questions?
If the information at the selection buttons above still leaves you with questions or you need
additional help, please e-mail Jim Janossy, Russ Patterson, or Gino Kao.
Data explanations
Table
formation
SQL
To Home Page
Datamart Documentation
The content of each datamart table is documented in a spreadsheet that shows the
column name, format of the column, and the PeopleSoft table and column from which
the data is drawn. You’ll also find access here to explanations of certain data columns as
well as the SQL used to extract and form the datamart tables. Review, comments, and
suggestions concerning this documentation and table formation logic is welcome.
Gino Kao
Programmer, Infrastructure Group
Data origin
documentation
Table
definitions
Questions?
If the information at the selection buttons above still leaves you with questions or you need
additional help, please e-mail Jim Janossy, Russ Patterson, or Gino Kao.
Data explanations
Table
formation
SQL
To Home Page
Documentation
Datamart Documentation
The content of each datamart table is documented in a spreadsheet that shows the
column name, format of the column, and the PeopleSoft table and column from which
the data is drawn. You’ll also find access here to explanations of certain data columns as
well as the SQL used to extract and form the datamart tables. Review, comments, and
suggestions concerning this documentation and table formation logic is welcome.
SQR source
code download
Gino Kao
Programmer, Infrastructure Group
Table
definitions
Questions?
If the information at the selection buttons above still leaves you with questions or you need
additional help, please e-mail Jim Janossy, Russ Patterson, or Gino Kao.
Data explanations
Table
formation
SQL
To Home Page
What do users say about the student datamart ?
“We’ve found that while we can look up students one
at a time online using PeopleSoft, we can use the
datamart to access data to get lists of enrolled
students and related information, without having to
ask for special programming in each case!”
Tanicha Hart
College of Liberal Arts and Science
“We’re using the student datamart to identify incoming
freshmen and prepare mailings to them. We have
conducted in-house datamart training sessions and find
that getting people up to speed on datamart access is
easy and quick to accomplish!”
Mike Medin
ID Card Services Office
DePaul University
Copyright 2003 DePaul University
Chicago, Illinois USA www.depaul.edu
DePaul University
Copyright 2003 DePaul University
Chicago, Illinois USA www.depaul.edu
“The datamart lets us retrieve student information to
meet adhoc requests from many users quickly and
efficiently. We handle over 300 requests a year using
the mart, and this is only a small part of what we do in
this area of information services.”
Charles Moore
School for New Learning
“The datamart lets us get data for operational reporting
and analysis that we just couldn’t get before! And it has
given me new opportunities to learn modern data access
techniques and presentation. My new skills have
allowed me to grow in areas that are also essential for
higher education achievement. Everyone can benefit
from the Datamart's user friendly interface!”
Marcelo Lanzarotti
Information Services Division
Jennifer Hoover
College of Liberal Arts and Sciences
DePaul University
Copyright 2003 DePaul University
Chicago, Illinois USA www.depaul.edu
“As a frequent user of the Student Datamart tables for nearly
a year, I find it a highly reliable, integral and overall
indispensable resource for generating a diverse collection of
student reports. Moreover, the datamart immensely reduces
turnaround time for my report requests. Reports that formerly
required three or four separate queries in PeopleSoft Query
can now be completed right in Access by way of the datamart
tables, often from only one query! Within the College of LA&S
departments now receive more detailed and accurate
quarterly reports about their students. Usage of the student
datamart played a large part in these reporting
improvements.”
“The datamart is very useful to SNL in our day-to-day
operation since it provides a fast and convenient way to
extract data we need for decision-making. We look
forward to using the datamart even more to meet many
of our needs for information about our classes and
students!”
Mark McMurray
School of Computer Science, Telecommunication, and
Information Systems
“The student datamart is a great tool for our information
gathering needs. The aggregated data allows for the
creation of much simpler queries than can be written in
PeopleSoft Query. We can create easy-to-access queries
and reports that are much simpler to understand, change
and run for users of different skill levels. Whether it is
targeted mailings or analyzing student history, we are
continually finding new uses for the datamart that allow us
to better serve our student population!”
Doug Murphy
Senior Assistant Dean
School for New Learning
DePaul University
Copyright 2003 DePaul University
Chicago, Illinois USA www.depaul.edu
Three level of approach
• Student datamart
– PS table extracts packaged for easy use 160 casual users
– Aim: day-to-day meat and potatoes selection and contact data
for the enrolled and active student population
– Not intended for statistical reporting
• Enrollment Management Research databases
– Current statistical reporting and census captures
– Admission reporting and day-to-day college admin stats
– Marketing measures and EM decisionmaking
• OIPR databases
– Moderate to long term statistics and trends
– IPEDS reporting
Enrollment & Marketing Research
• Department in Enrollment Management (EM)
– Integrates traditional enrollment services (admission and
financial aid, for example) with our university’s marketing and
communication activities, as well as alumni and career networks
– EM’s Goal: Improve and enhance DePaul’s competitive market
position and prominence in Chicago, the nation, and the
international community
• EMR’s Goal: Provide timely information that is
valuable to understanding and enhancing DePaul's
market position and prominence
– Reporting
– Research
EMR Information Needs
• Reporting
– Admissions
• Yield Reports
• Prospect Reports
• Mailing Lists
– Enrollment
• Weekly Enrollment Comparisons
• Daily Enrollment Reports
• Research
– Prospect Analysis (prospects to enrolled)
– Market Analysis (program success)
• Enrollment Data Capture began in 1990
• Admission Data Capture began in 2000
• Some Research/Reports required data from both captures
– Trick was getting the two to tie-out
– And getting the data to the requestor quickly
Future
Current
Data Stores
Enr
Reports
TEAMS
Elite
Research &
Report
Oriented
SQL Server
Daily
Enrollment
SQL Server
Housing
DB
Specialized
Research
DBs
ETL
ETL
SQL Server
Specialized
Reports
SQL Server
Daily
Admissions
SQL Server
PeopleSoft
Oracle
PERL,
T-SQL,
Stored Procs
T-SQL &
Stored Procs
Specialized
Adm DBs
Enrollment & Marketing Research
Enrollment Management
DePaul University
Adm
Reports
EMR
Data
Warehouse
Three level of approach
• Student datamart
– PS table extracts packaged for easy use 160 casual users
– Aim: day-to-day meat and potatoes selection and contact data
for the enrolled and active student population
– Not intended for statistical reporting
• Enrollment Management Research databases
– Current statistical reporting and census captures
– Admission reporting and day-to-day college admin stats
– Marketing measures and EM decisionmaking
• OIPR databases
– Moderate to long term statistics and trends
– IPEDS reporting
Office of Institutional Planning
and Research (OIPR)
• Census Files created from Oracle via ODBC
and SQL-Server DTS
• Integrated Finance, Student, Instruction,
and Faculty data serves as official stats
• Student data set is consistent with
historical categories at aggregate level
• Student census files coordinated with EMR
• Reports developed in varying degrees of
sophistication (reports to OLAP cubes)
Office of Institutional Planning
and Research (OIPR)
• Cognos is used against SQL-Server
• Reports have 3 levels:
– OLAP driven static web reports with no
interactivity (put up reports quickly)
– OLAP driven web reports with drop
downs. (customized web reports with
interactivity)
– OLAP interactive browser: fully analyze
data from basic browsing to data mining
Informatica Data Warehouse
PowerCenter/PowerAnalyzer + PS EPM
PowerCenter
ETL
BI
PowerAnalyzer
Reporting
Web Server
Multiple schemas
PowerCenter Client
Tools
PowerAnalyzer
Meta Data
Repository
Portal
delivery!
PowerCenter
Meta Data
Repository
HR
Finance
Warehouse
SA
Non-PS
ETL
EPM
!