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 EMR databases Perl, SQL-Server, Access OIPR databases ODBC, SQL-Server, DTS Cognos
Need for portal delivery
Decision: Acquire a standard ETL and BI
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
Data
•
spreadsheet maximum 65K rows
•
hard to learn data structures!
•
awkward outer joins
?
?
Low hanging fruit!
What users did PS-Query 338 users
Data
Access
Data
Datamart approach PS-Query 338 users
Data
Datamart Access
Data
You can combine!
PS-Query 338 users
Data
Datamart
Links to tables
Access
Local tables Data Links to spreadsheets 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
DP701A_BestContactData DP701B_DePaulDegrees DP701C_Enrolled DP701D_AbleReg DP701E_StudentPlans DP701F_GPA DP701G_Advisees DP701H_VisaData DP701I_CourseSched DP701J_CreditHoursSummary DP701K_FirstLatestTerm DP701L_AdmissionStatus DP701M_StudentHist DP701N_PotentialGrads DP701P_PersonalDemog DP701Q_BestEmail DP701R_Roster DP701S_AllStudents DP701T_StuGroups DP701X_GroupProgPlanRef DP701Y_PSids_SSN DP701Z_TermRef
ROWCOUNT LASTLOADDATE LASTLOADTIME
445534 10/16/03 10:13 AM 73159 10/16/03 98940 10/16/03 79141 10/16/03 09:29 AM 09:37 AM 09:19 AM 104131 10/16/03 195673 10/16/03 38593 10/16/03 22422 10/16/03 93816 10/16/03 09:53 AM 09:43 AM 09:09 AM 09:01 AM 09:15 AM 36279 10/16/03 195673 10/16/03 60767 10/16/03 30681 10/01/03 6570 08/04/03 429880 10/16/03 165774 10/16/03 90116 10/16/03 205821 10/16/03 98235 10/16/03 503 10/16/03 438503 10/16/03 374 05/20/03 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 Adhoc_ Get_Best_Name .mdb
PS_ ADDRESSES 359,000 rows Adhoc_ Get_Best_Address .mdb
PS_ PERSONAL_ PHONE 366,000 rows Adhoc_ Get_Phones .mdb
PS_ EMAIL_ ADDRESSES 182,000 rows Adhoc_ Get_Best_Email .mdb
DP150_Best Name 305,000 rows DP100_Best Address 294,000 rows 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
FACSTAFF .mdb
3,902 rows
Adhoc_ DP701A_ Get_BestContact Data From CBORD Query ROOM_ ASSIGNS .XLS
Adhoc_ Get_DormRooms .mdb
DP180_ DormRooms 2,560 rows
Formation of the DP701A_BestContactData Table DePaul SA Adhoc Datamart
DP701A_ BestContact Data
305,000 rows n:\IAS_ADHOC_GROUP\SA_Datamart_Feeder_Backups\DP701A_Formation_colors.vsd J.Janossy 11/28/01 Rev.3 2/21/02
•
Clear documentation
•
Clear naming convention
•
Create simple data structures
•
Interpret coded values for use
•
Use common tools, common skills
Goals
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 Access Examples Training Documentation
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.
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 Scope User reaction Table list Extraction Table loading
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.
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 View control Access policy Request form
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.
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 How to...
Linking to PS-Query spreadsheets Frequently asked questions Reports
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.
Labels
To Home Page
Datamart Training Happy campers in classroom 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.
Suggested preparation ODBC connection Links to training sites Schedule
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.
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 Data explanations Table formation SQL
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.
To Home Page
Datamart Documentation Gino Kao Programmer, Infrastructure Group
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.
Data origin documentation
Table definitions Data explanations Table formation SQL
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.
To Home Page
Documentation
Datamart Documentation Gino Kao Programmer, Infrastructure Group
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
Table definitions Data explanations Table formation SQL
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.
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!”
DePaul University
Copyright 2003 DePaul University Chicago, Illinois USA www.depaul.edu
Mike Medin
ID Card Services Office
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.”
Marcelo Lanzarotti
Information Services Division
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!”
Jennifer Hoover
College of Liberal Arts and Sciences “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.”
DePaul University
Copyright 2003 DePaul University Chicago, Illinois USA www.depaul.edu
“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!”
DePaul University
Copyright 2003 DePaul University Chicago, Illinois USA www.depaul.edu
Doug Murphy
Senior Assistant Dean School for New Learning
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!”
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
Current
Data Stores Enr Reports TEAMS Elite SQL Server Housing DB SQL Server ETL Daily Enrollment SQL Server Specialized Research DBs PeopleSoft Oracle PERL, T-SQL, Stored Procs Daily Admissions SQL Server Specialized Adm DBs Adm Reports Enrollment & Marketing Research Enrollment Management DePaul University Specialized Reports ETL T-SQL & Stored Procs
Future
Research & Report Oriented EMR Data Warehouse SQL Server
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
PowerAnalyzer Reporting Web Server PowerCenter ETL
Multiple schemas PowerCenter Client Tools
PowerAnalyzer Meta Data Repository PowerCenter Meta Data Repository HR Finance SA Non-PS Warehouse
ETL BI
Portal delivery!
EPM
!