student admin data reporting from peoplesoft sa depaul university: three years, three (concurrent)...
TRANSCRIPT
Student Admin Data Reportingfrom PeopleSoft SA
DePaul University:Three years, three (concurrent) approaches!
Jim Janossy and Russ PattersonDePaul Information Services
Ed Schaefer, Enrollment Management Research
• 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
Presenters and Panel
• 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!
Cutting to the chase...
• 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
Three level of approach
• 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...
Student datamartAccess, SQR
EMR databasesPerl, SQL-Server, Access
OIPR databasesODBC, SQL-Server, DTSCognos
Leads to...
Decision:
Acquirea standard ETL and BI
Need for portal delivery
Need for administration via web
Need for dashboards
Credits acquired with EPM
Reporting Reporting instanceinstance
PeopleSoft Student PeopleSoft Student Admin systemAdmin system
Common base: Common base: Reporting Reporting instance is ainstance is afull copyfull copy of ofthe on-linethe on-linesystemsystem
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
LimitationsLimitations
• spreadsheet maximum 65K rowsspreadsheet maximum 65K rows• hard to learn data structures!hard to learn data structures!• awkward outer joinsawkward outer joins
PS-QueryPS-Query 338 users338 users Data
?
?
Low- Low- hanging hanging fruit!fruit!
What users didWhat users did
PS-QueryPS-Query 338 users338 users Data
AccessAccessData
Datamart approachDatamart approach
PS-Query PS-Query 338 users338 users Data
DatamartDatamart
AccessAccessData
You can combine!You can combine!
PS-QueryPS-Query 338 users338 users Data
DatamartDatamart
AccessAccess Data
Links to spreadsheets
Links to tables
Local tables
Data
!
DatamartDatamart
AccessAccessData
• spreadsheet maximum 65K rowsspreadsheet maximum 65K rows• hard to learn data structures!hard to learn data structures!• awkward outer joinsawkward outer joins
Why datamart?Why datamart?
Student datamart Student datamart functionalfunctional look look
• 22 tables22 tables
• Enrolled student dataEnrolled student data
• Assemble data for convenient useAssemble data for convenient use
• Focus on 20% to meet 80% needsFocus on 20% to meet 80% needs
• Row control by ACAD_GROUPRow control by ACAD_GROUP
TABLENAME ROWCOUNT LASTLOADDATE LASTLOADTIMEDP701A_BestContactData 445534 10/16/03 10:13 AMDP701B_DePaulDegrees 73159 10/16/03 09:29 AMDP701C_Enrolled 98940 10/16/03 09:37 AMDP701D_AbleReg 79141 10/16/03 09:19 AMDP701E_StudentPlans 104131 10/16/03 09:53 AMDP701F_GPA 195673 10/16/03 09:43 AMDP701G_Advisees 38593 10/16/03 09:09 AMDP701H_VisaData 22422 10/16/03 09:01 AMDP701I_CourseSched 93816 10/16/03 09:15 AMDP701J_CreditHoursSummary 36279 10/16/03 09:28 AMDP701K_FirstLatestTerm 195673 10/16/03 09:24 AMDP701L_AdmissionStatus 60767 10/16/03 09:09 AMDP701M_StudentHist 30681 10/01/03 11:01 AMDP701N_PotentialGrads 6570 08/04/03 2:00 PMDP701P_PersonalDemog 429880 10/16/03 09:26 AMDP701Q_BestEmail 165774 10/16/03 09:05 AMDP701R_Roster 90116 10/16/03 09:05 AMDP701S_AllStudents 205821 10/16/03 09:04 AMDP701T_StuGroups 98235 10/16/03 09:31 AMDP701X_GroupProgPlanRef 503 10/16/03 09:01 AMDP701Y_PSids_SSN 438503 10/16/03 09:26 AMDP701Z_TermRef 374 05/20/03 03:00 PM
PeopleSoft operator id and PeopleSoft operator id and passwordpassword
UsageUsage
Student datamart Student datamart technicaltechnical look look
• Separate Oracle databaseSeparate Oracle database
• Oracle define, create, securityOracle define, create, security
• Extract data with Access or SQRExtract data with Access or SQR
• Load with SQR or SQL*LoaderLoad with SQR or SQL*Loader
• Pilot first, engage users, perfect itPilot first, engage users, perfect it
Example: Best Contact Example: Best Contact Data TableData Table
ADDRESSESADDRESSES
NAMESNAMES
PERSONAL_PHONEPERSONAL_PHONE
EMAIL_ADDRESSESEMAIL_ADDRESSES
HR: employeesHR: employees
CBORD: dorm assignsCBORD: dorm assigns
Barat dorm assignsBarat dorm assigns
DP701ABest Contact Data
FromCBORDQuery
Adhoc_Get_Best_Address
.mdb
PS_NAMES
370,000 rows
PS_PERSONAL_
PHONE366,000 rows
PS_EMAIL_
ADDRESSES182,000 rows
PS_ADDRESSES359,000 rows
Adhoc_Get_Best_Name
.mdb
DP150_BestName
305,000 rows
DP170_Phones
274,000 rows
DP160_BestEmail
76,000 rows
DP100_BestAddress
294,000 rows
Adhoc_Get_Phones
.mdb
Adhoc_Get_Best_Email
.mdb
Adhoc_DP701A_
Get_BestContactData
EMPLOYEES.XLS
MAILPREP.EXEFACSTAFF
.mdb3,902 rows
DP701A_BestContact
Data305,000 rowsROOM_
ASSIGNS.XLS
Adhoc_Get_DormRooms
.mdb
DP180_DormRooms2,560 rows
From HumanResources
Query
Formation of theDP701A_BestContactData Table
DePaul SA Adhoc Datamartn:\IAS_ADHOC_GROUP\SA_Datamart_Feeder_Backups\DP701A_Formation_colors.vsd J.Janossy 11/28/01 Rev.3 2/21/02
Counts of rows in tables are as of11/26/01 and are approximate, toestablish a perspective only
OneOnedatamart table!datamart table!
GoalsGoals
• Clear documentationClear documentation
• Clear naming conventionClear naming convention
• Create simple data structuresCreate simple data structures
• Interpret coded values for useInterpret coded values for use
• Use common tools, common skillsUse common tools, common skills
For table building . . .For table building . . .
Common toolsCommon tools
Common skillsCommon skills
Common centsCommon cents
ODBC
MS-Access
SQR
SQL*Loader
Oracle roles
Common toolsCommon tools
Common skillsCommon skills
Common centsCommon cents
Existing skills
MS-Access
Excel
Basic PC training
Views by college
For users . . .For users . . .
Common toolsCommon tools
Common skillsCommon skills
Common centsCommon cents
For DePaul . . .For DePaul . . .
Low cost
Short lead time
Low overhead
Meets 80% need
In place now
For more info . . .For more info . . .
See the DePaulSee the DePaul datamart web site at datamart web site at
www.depaul.edu/~datamartwww.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.
Questions?
Overview Access Examples Training Documentation
If the information at the selection buttons above still leaves you with questions oryou 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!
Questions?
Intent Table list Table loading
If the information at the selection buttons above still leaves you with questions or you needadditional help, please e-mail Jim Janossy, Russ Patterson, or Gino Kao. To Home Page
User reactionScope Extraction
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.
Questions?
View control Request form
If the information at the selection buttons above still leaves you with questions or you needadditional help, please e-mail Jim Janossy, Russ Patterson, or Gino Kao. To Home Page
Tom Paetsch, Data AdministratorEnrollment Management
Details Access policy
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!”
Questions?
Linking toPS-Query
spreadsheets
Frequently asked questions
Labels
If the information at the selection buttons above still leaves you with questions or you needadditional help, please e-mail Jim Janossy, Russ Patterson, or Gino Kao. To Home Page
Cheryl Barkby, AnalystID Card Services
Samples How to... Reports
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.
Questions?
ODBC connection Schedule
If the information at the selection buttons above still leaves you with questions or you needadditional help, please e-mail Jim Janossy, Russ Patterson, or Gino Kao. To Home Page
Happy campers in classroom training!
Suggested preparation
Links to training sites
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.
Questions?
Table definitions
Table formation
SQL
If the information at the selection buttons above still leaves you with questions or you needadditional help, please e-mail Jim Janossy, Russ Patterson, or Gino Kao. To Home Page
Gino KaoProgrammer, Infrastructure Group
Data explanations
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.
Questions?
Table definitions
Table formation
SQL
If the information at the selection buttons above still leaves you with questions or you needadditional help, please e-mail Jim Janossy, Russ Patterson, or Gino Kao. To Home Page
Gino KaoProgrammer, Infrastructure Group
Data explanations
Data origin documentation
DocumentationDocumentation
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.
Questions?
Table definitions
Table formation
SQL
If the information at the selection buttons above still leaves you with questions or you needadditional help, please e-mail Jim Janossy, Russ Patterson, or Gino Kao. To Home Page
Gino KaoProgrammer, Infrastructure Group
Data explanations
SQR source code download
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!”
Mike MedinID Card Services Office
Tanicha HartCollege 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 UniversityCopyright 2003 DePaul University
Chicago, Illinois USA www.depaul.edu
Charles MooreSchool for New Learning
Marcelo LanzarottiInformation Services Division
“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!”
“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.”
DePaul UniversityCopyright 2003 DePaul University
Chicago, Illinois USA www.depaul.edu
Jennifer HooverCollege 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 UniversityCopyright 2003 DePaul University
Chicago, Illinois USA www.depaul.edu
“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!”
Mark McMurraySchool of Computer Science, Telecommunication, and Information Systems
“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!”
Doug MurphySenior Assistant Dean
School for New Learning
DePaul UniversityCopyright 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
• 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
Enrollment & Marketing Research
• 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
EMR Information Needs
PeopleSoft
Daily Enrollment
Daily Admissions
ETL
Oracle PERL,T-SQL,
Stored Procs
AdmReportsSpecialized
Adm DBs
EnrReports
TEAMSElite
HousingDB
Current Future
SQL Server
SQL Server
EMRData
Warehouse
T-SQL &Stored Procs
SpecializedResearch
DBs
SpecializedReports
Research & Report
Oriented
ETL
SQL Server
SQL Server
SQL Server
Enrollment & Marketing ResearchEnrollment ManagementDePaul University
Data Stores
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
• 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 Planningand 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
Office of Institutional Planningand Research (OIPR)
WarehouseWarehouse
PowerCenterMeta DataRepository
PowerAnalyzer Meta DataRepository
Multiple schemas
HRHR
FinanceFinance
SASA
Non-PSNon-PS
PowerCenter Client Tools
PowerAnalyzerReporting
Web Server
PowerAnalyzerReporting
Web ServerPowerCenter
ETLPowerCenter
ETL
EPM
BI
ETL
Informatica Data WarehousePowerCenter/PowerAnalyzer + PS EPM
Portal delivery!
!