(ASP) Data Model Overview - Computing & Information Technology

www.sungardhe.com

Analyze Student Progress Data Model Overview

Objective  To present the Analyze Student Progess Model  Overview – the big picture  Data Model Structure

 Extract Transform Load Overview  Business Concepts Details  Institutional Values

www.sungardhe.com

2

Index

ASP – Academic Time ASP – Student Filters ASP – Student Status Indicators ASP – Student Retention Status ASP – Student Academic Period Measures ASP – Academic Period First Attended Attributes ASP – Student Multi Year Measures ASP – Student ASP – Academic Study ASP – Student Enrollment ASP – Academic Performance ASP – Student Course Filters ASP – Student Course ASP – Person ASP – Demographic ASP – Student Address ASP – Academic Outcome ASP – Activity ASP – Advisor Assignment ASP – Athletic ASP – Banner Communication ASP - Contact

ASP – Financial Aid Information ASP – Hold ASP – Post Secondary School ASP – Secondary School ASP – Test ASP – Internal Keys ASP – Report Default Selections ASP – Academic Year Range ASP – Institutional Values

www.sungardhe.com

3

Index

Analyze Student Progress

Overview – the big picture

www.sungardhe.com

4

Index

Business Concept

Business need addressed Which courses / instructors have the best past fail rates?

Analyze Student Progress Business need addressed  Analyze a student’s academic progress and how it relates to a student’s outcome or retention

For Advisors, which advisees need help? Compare overall student body performance with all athletes with a particular sport. Based on meeting patterns, which sections have the best past fail rate? How are grades distributed across courses based on student classification.

Which programs are doing the best job of graduating students on time? What group or cohort of students should retention strategies focus on?

Who is “at-risk” on our campus? What is the relationship between students’ academic qualifications and subsequent first year college performance and retention to second year?

www.sungardhe.com

5

Index

Analyze Student Progress Cognos Package This package consists of EDW data preconfigured, logically grouped and joined for ease of reporting.

www.sungardhe.com

6

Index

Snapshot Analyze Student Progress Cognos Package Snapshots – freeze data at periodic points in time to allow creating cubes and scorecards to do longitudinal analysis

Note: this business concept looks exactly like its counterpart with the addition of an event Events – user defined to describe a specific point in time like end of term, census date, monthly

www.sungardhe.com

7

Index

Student Retention Performance Data Model Structure

www.sungardhe.com

8

Index

Operational Star Operational Enterprise System – Banner • Continuous data back through time and up to the minute to OPERATE the enterprise Operational Data Store – ODS •Continuous data back through time and up to the last refresh (a few hours old) to report from to OPERATE the enterprise Operational Stars – EDW (Enterprise Data Warehouse) •Continuous data back through time and up to the last refresh (a few hours old) to report from to OPERATE the enterprise •Reformated for performance and strategic reporting •Trend, summary, detail reporting

www.sungardhe.com

9

Index

Operational Star - Analyze Student Progress Aggregate table WAT_STUDENT_PROGRESS • Derived from other operational stars bringing together (aggregating) into one super star all the data necessary to power the business concept • Enhanced performance • Ease of use

www.sungardhe.com

10

Index

Loading Data into the Warehouse First, set up some institutional values EDW Extract Parameters

• Parameters set up in the Admin UI to give the data institutional values •What terms to load •What default values do we want for… •What codes mean a student should be excluded from retention counts •What test codes map to test descriptions in the model •Etc. • Must be set up before data is loaded • See the spreadsheet

www.sungardhe.com

11

Index

Banner Performance Reporting and Analytics (BPRA) Data Flow with Oracle Streams Data Warehouse

Banner

BRM

Oracle Streams (Change Data Capture Mechanism)

Legacy

Performance Management Applications

Recruiting & Admissions

Student Retention

Advancement Performance

www.sungardhe.com

12

Index

Banner Performance Reporting and Analytics (BPRA) Data Flow with Oracle Streams

Legacy

Banner

Advance

Oracle Streams (Change Data Capture Mechanism)

Operational Staging Area

Operational Data Store Reporting Tools

Legacy ‘ ETL ETL (OWB) (OWB)

Composite Tables

Reporting Views

Performance Management Applications

Recruiting & Admissions

Banner ‘ Student Retention

Advance ‘ Advancement Performance *Contains staging tables and all Banner objects for ODS (Change tables, triggers, Composite Views)

* Extract Transform Load (ETL) * Oracle Warehouse Builder (OWB)

www.sungardhe.com

13

Index

Banner Performance Reporting and Analytics (BPRA) Data Flow with Oracle Streams

Legacy

Banner

Advance

Oracle Streams (Change Data Capture Mechanism)

Operational Staging Area

Operational Data Store Reporting Tools

Legacy ‘ ETL ETL (OWB) (OWB)

Composite Tables

Reporting Views

Performance Management Applications

Recruiting & Admissions

Banner ‘ Student Retention

ETL (OWB)

D Advance ‘ *Contains staging tables and all Banner objects for ODS (Change tables, triggers, Composite Views)

D F

D

D

OLAP Tools

Advancement Performance

Enterprise Data Warehouse * Extract Transform Load (ETL) * Oracle Warehouse Builder (OWB)

www.sungardhe.com

14

Index

Student Retention Performance Extract Transform Load Overview

www.sungardhe.com

15

Index

Existing Enterprise Data Warehouse ETL for RAP and SRP ODS SCHEMA

GENERAL STUDENT

EDW STAGING SCHEMA

TABLE FUNCTION

INPUT TABLE

CLEAN TABLE

ERROR TABLE

WKEYS TABLE

(IF ERRORS)

IA ADMIN SCHEMA

EDW PRESENTATION SCHEMA

ENROLLMENT Person

ADMIN TABLES Demo graphic

LEARNER CURRICULUM

META DATA TABLES

Retention Time

STUDENT STAR Student

www.sungardhe.com

Academic Study

16

Index

Student Course – Example Trace Student Course data in Banner comes from tables… History - SHRTCKN Registration - SFRSTCR Transfer - SHRTR% ODSSRC ODSMGR -

ODSMGR ODSMGR EDWSTG EDWMGR EDWMGR -

Streamed Banner Tables Composite Views – one for each of History, Registration, Transfer AS_STUDENT_COURSE_HISTORY AS_STUDENT_COURSE_IN_PROGRESS AS_STUDENT_COURSE_TRANSFER Mappings – load data from these views into the Composite Table Composite Table – MST_STUDENT_COURSE Table Function – EDW_STUDENT_EXTR. f_get_student_course Extracts data from Composite Table to build operational star Operational Star – WFT_STUDENT_COURSE Aggregating Mappings - build aggregate entries EDWMGR.EDW_STUD_PROGRESS_AGG_INSERT EDWMGR.EDW_STUD_ENGAGEMENT_AGG_INSERT

www.sungardhe.com

17

Index

Student Retention Performance Business Concepts Details Analyze Student Progress

www.sungardhe.com

18

Index

Analyze Student Progress Package Items  Ordered by most likely to be used  Items

Icons

 Query Subjects - Dimensions  Folders - Used to group items  Measures - Facts like counts, rates, etc.  Filters - Predefined common filters

 Query Items – element values

www.sungardhe.com

19

Index

Analyze Student Progress What data is in this business concept?  Banner data only  Student / term entries  With a SGBSTDN general student record  An additional term is added for future terms if permitted to register  Through the end of the matriculation if closed  Through SOATERM readmit term if it exists - then won’t go past that unless registered for a term  Through the end of time if no SOATERM readmit term  Term patterns can be specified by LEVEL or ALL or COLLEGE or PROGRAM or CAMPUS or MULTI_SOURCE  prevent adding terms into the future not associated with the student  See the EDW Extract Parameter – ACADEMIC_PERIOD_PATTERN

 Attributes about the student or student / term combination

www.sungardhe.com

20

Index

Analyze Student Progress Which students are included? Look at the headcounts. Student Headcount Enrolled Headcount Registered Headcount Student Not Registered Headcount Withdrawn Headcount Graduated Headcount Excluded Headcount

www.sungardhe.com

21

Index

Analyze Student Progress Which students are included?

www.sungardhe.com

22

Index

ASP – Academic Time Includes attributes relevant to time frames - academic year, academic period and related attributes. They set the time frame for most comparison reporting done in the package. The aggregates use academic time as part of their primary key. •Academic Period Type - Derived from Academic Period Description

•Dimension table – WDT_ACADEMIC_TIME •Source – STVTERM

www.sungardhe.com

23

Index

ASP – Student Filters The student level filters are commonly used to filter reports. Student level is associated with the student’s primary program in query subject Academic Study. Note: Parameter Maps can be viewed in Framework Manager (FWM) and values set in Admin UI – a method of assigning values one time and then referencing that value from everywhere else in the model so changes to it only need to be done in one place – the parameter map. Parameter map values impact reporting but not the data being loaded into the stars. Parameter map values are defined in the Admin UI.

www.sungardhe.com

24

Index

ASP – Student Status Indicators Includes a set of indicators that may be used to divide and classify student numbers and to analyze students by common attributes, for example, students with housing assignments, applying for an outcome (degree), new students for the academic period, etc. These indicators are associated with an academic period. •Applied For Outcome Ind – any degree entry on SHRDGMR with a DEGS code with an award status ind of “P” -- MSVDGMR - SUBSTR(SOKODSF.F_APPLIED_FOR_DEGREE (SHRDGMR_PIDM),1,1) •Cooperative Education Ind – if SGRCOOP entry for term and level MST_STUDENT_WORK_EXPERIENCE - MSKFUNC.F_CHECK_COOP_IND(S.PERSON_UID, S.ACADEMIC_PERIOD, S.PROGRAM_LEVEL) •Developmental Course Ind - registered for course with attribute in extract parameter list for term - MST_STUDENT_COURSE_ATTRIBUTE - see EDW_STUDENT_EXTR – EDW_EXTRACT_PARAMETER •Enrolled Ind – if one of these records exist for term then enrolled – SFBETRM (form SFAREGS SFBETRM_ESTS_CODE is “Y” for STVESTS_EFF_HEADCOUNT on STVESTS), SHRTTRM (form SHAINST), SFRAREG (form SFAREGS) - SOKODSF.F_ENROLLED_THIS_TERM •Graduated Ind – outcome awarded in any academic year 0 – see EDW_WAT_STUDENT_PROGRESS_EXTR •Major Change Count – number of distinct majors for student level where program primary ind is Yes •Academic Periods Undeclared – count of terms where major is one listed in the EDW EXTRACT Parameter Major_Code

• Dimension Table – WDT_ACADEMIC_STUDY • Fact Table – WAT_STUDENT_PROGRESS • Source – EDW_STUDENT_EXTR.F_GET_STUDENT – MST_BASE_STUDENT - AS_CURRICULUM_FOS – SORLCUR •See EDW_STUDENT_EXTR – get changed_major_count etc.

www.sungardhe.com

CONTINUED onto next slide

34

Index

Analyze Student Progress

www.sungardhe.com

35

Index

ASP – Student Enrollment Includes overall enrollment data that includes attributes such as academic period first and last attended, initial enrollment status date, current enrollment status and date, enrolled by census date ind, etc. Measures include student summarized course activity such as the number of courses registered, dropped and withdrawn, passed and failed, etc. •Attended - means registered for at least one course •Enrollment Status – SFBETRM_ESTS_CODE •Enrolled Age Range and Order – Age Range parameter map Admin UI •Enrolled By… - MST_ENROLLMENT – AS_ENROLLMENT – currently NULL •Registration Reason – SFBETRM_RGRE_CODE •Institution Attending - AS_ENROLLMENT_HISTORY.transfer_to_institution – SHRTTRM_SBGI_CODE_TRANS •Withdrawal Reason – AS_ENROLLMENT_HISTORY - SHRTTRM_WRSN_CODE •Leave Reason – SGBSTDN_LEAV_CODE •Academic Periods Attended – Count of academic periods with Registered Ind Yes relative to reported academic period •Total Academic Period Attended Count – relative to all time and does not change when reporting academic period changes •Dimension Table – WDT_ENROLLMENT_STATUS •Fact Table - WAT_STUDENT_PROGRESS •Source – MST_ENROLLMENT – AS_ENROLLMENT – SFBETRM – AS_LEARNER_CURRICULUM_FOS •See EDWSTG.EDW_ENROLL_EXTR.F_GET_ENROLLMENT

www.sungardhe.com

36

Index

ASP – Academic Performance Includes attributes and measures that identify the student's progress toward their educational goal (positive or negative). Measures include all credits (Attempted, Passed, Earned) and GPA as assigned by the course section grades, GPA (cumulative, academic year and academic period (term). Attributes include Academic Standing (beginning and end of academic period), Combined Academic Standing and Progress Evaluation. The measures are differentiated by levels by filtering on level. •Ranges - Admin UI Parameter Map and cleansing rule •Thresholds - user defined in GPA THRESHOLD cleansing rule •Combined Academic Standing – SHRTTRM_CAST_CODE •Progress Evaluation – SHRTTRM_PREV_CODE •Academic Year GPA – cumulative GPA for terms in reporting academic year •Cumulative Credits – cumulative through reporting academic period •Cumulative GPA – cumulative through reporting academic period •Dimension Table – WDT_STUDENT_PERFORMANCE •Fact Table – WAT_STUDENT_PROGRESS •Source – AS_ACADEMIC_STANDING - SHRTTRM - SHRLGPA

www.sungardhe.com

37

Index

ASP – Student Course Filters Commonly used filters to filter student courses. If not used then all courses are presented. •Course Source Type History •Course Source Type Registration •Course Source Type Transfer •See parameter map Student Course

to only view historical courses to only view current courses to only view transfer courses

www.sungardhe.com

SHRTCKN SFRSTCR SHRTR%

38

Index

ASP – Student Course Includes all student courses in progress, completed and transferred with student specific details such as the final grade and credits. Attributes include course data such as course identification, subject, course number, section detail like schedule type, instructional method, session and meeting days and times, primary instructor, initial registration status date, current registration status and date. Measures include course headcounts and rates, credits and GPA. •Course indicators - relative to a student •Developmental Student Course Ind - Attributes set in the EDW EXTRACT PARAMETER – DEV_COURSE_ATTRIBUTE •Registered Census Date Ind – when SSBSECT_CENSUS_ENRL IS NULL THEN NULL when SFRSTCR_ADD_DATE