class of ‘55 room, van pelt library – february 22, 2012

DW Student Data User Group Meeting

Class of ‘55 Room, Van Pelt Library – February 22, 2012

Student Data User Group 2/22/2012 - Agenda

• Updates to Count Students reports Presented by Fran Seidita & Janis Tofani,

Budget Office

• Universe Joins & Query Filters

• Miscellaneous Announcements

History of these QueriesWhat’s IncludedWhy Might You Use ThemGoals for the UpdateLogic ChangesDesign ChangesKnown IssuesUpcoming ChangesPotential Future Enhancements

Updates to Count Students reports

Count Students…v5

2004: Count PhD students2006: Expanded to count all students2007: Tuition Distribution live2009: PhD tuition simplification2011: FTE concept2012: This update

History

Count Students Queries Revised:Count Students as of Today v5Count Students as of Census v5 (coming next

week)Count Students with Tuition Billed v5Count Students with no Tuition Billed v5

Not revisedCount PhD Students with Tuition FundedCount PhD Students with stipendsTD reports

Count Students…v5

What’s Included

Census ReportingTuition Distribution Did We Bill Everyone?

Count Students…v5

Why Use Them?

Census Reporting

If you run Count Students as of Today on the day after the Registrar’s weekly hardcopy census report is dated, it will match the hardcopy.

If you run Count Students as of Today on the day after the official census day, it will match Count Students as of Census.

Example: This Friday is the census. Susan will save the Count Students as of Today for us on Saturday and that will match Count Students as of Census.

Why Use Them? Count Students…v5

Tuition Distribution

If you run Count Students as of Today on the day of TD, the WCUs will match the TD extract.

Note: the Count(PennID) will not match unless you screen out the people who are enrolled only in courses with zero course units. They’re not active in Count Students (because course units = 0) but they are in TD (because they’re enrolled in a course section, often to get blackboard access while finishing incompletes).

If you run Count Students with Tuition Billed on the day of TD, the dollars billed and applied will match the TD extract.


Did We Bill Everyone?

If you run Count Students as of Today and Count Students with Tuition Billed and Count Students with no Tuition Billed, all on the same day, all the students who are active as of Today should appear either in tuition billed or in not billed. If this is not true, call us!

Note: this query uses custom SQL because it basically does a Count Students as of Today not in list Count Students with Tuition Billed, and Webi couldn’t construct that join across universes. Don’t touch the query panel or you’ll break the logic, and it won’t be obvious that you did. A copy of the SQL is included in the last tab in case you forget (so you can paste it back in).

Question: should we make it more obvious when it’s broken?


Goals for the Update

Performance improvementsConsistency improvementsTake advantage of new featuresSustainability improvements

Count Students…v5

Performance improvements

Count Students with no Tuition Billed wouldn’t run

Count Students with Tuition Billed ran sometimes

Some query conditions were defined twice (in query panel, in BRS Combined universe), causing poor performance. Removed the query filter on

degree-order=1 major-minor order = 1 major-minor type= M

Goals for the Update Count Students…v5

Consistency improvements

Different tabs in each query ie. different reports in each Webi document

Tabs were in different order ie. different report order from one Webi document to the next

Report columns in different order Tabs weren’t numberedFilters were different in each query


Take Advantage of New Features

Optional Prompts for DivisionDon’t (unchecked) reset contexts on refreshSimple filters allow us to replace several

reports with one report and filters.


Sustainability improvements

Make reports usable onlineWhen printing is necessary, make reports

print on minimum pieces of page


Logic Changes

New Matrics is now Matriculant StatusStill defined only in fallFixed logic flaw that meant you had to filter on both New

Matric and AD/DD: now you can just filter on New MatriculantFixed filter to stop counting no shows (filtered out disappeared)Values:

New Matriculant Entry term this term or this year (so includes students who arrived

previous spring or summer) AD or DD

Visitors, Transfers and Returnees Entry term this term or this year (so includes students who arrived

previous spring or summer) Not AD or DD (via Else) Not CP (new “Continuing PhD” entry action)

Continuing StudentsEveryone else

Count Students…v5

Logic Changes, continued

Type of Program was originally based on the degree in Degree Pursual

Instructional Program (DIV-DEGREE-MAJOR-SPGM) is based on the degree in degree term

Usually, but not always the same, especially for PhD students getting a masters

Last year, we changed Type of Program to use (DT), but people continued to trip over the degree (DP) which was still showing in the results

Effective v5, No longer using or showing Degree (DP)

Count Students…v5

Design Changes

Stopped using fancy features that confused peopleUsing Crosstabs: too many columns, no column

headingFine-tuning break behaviorFine-tuning text on column headings or totalsMaking sections (folds went several versions ago)

Started using default formatting wherever possibleHeadings are all in the headerFootnotes are all in the footerDefault formatting

Numeric with one decimal point on FTEs, WCUs Number with no decimal points on counts and dollars

Count Students…v5

Known Issues

WCUs is not yet defined in the universes

WCUs is slightly off from TD, especially for courses that needed currency conversions. We haven’t gotten to the bottom of it yet, but we think it’s rounding.

Variables defined in the queries and in the three universes and in TD need to be kept in synch manually

Count Students…v5

Upcoming Changes

PhD term counter later this weekWill be added to census for first time this

SaturdayNew version of Count Students as of Census to

follow next week

Nursing Accelerated Program later this fiscal yearCurrently considered traditional

undergraduatesWill be considered special undergraduates (like

LPS undergraduate degree students) starting FY13

Type of Program definition to change in June.

Count Students…v5

Potential future enhancements

Revise TD Reports in similar wayMake versions designed to show to show

counts across multiple years or terms.Figure out how to count students in joint

degree programs partially in one school and partially in the other school, to match the way the home school tuition is split.

Others?

Count Students…v5


• Updates to Count Students reports



Universe joins and query filtersOld way: previous versions of our database

(Oracle) didn’t care if we put inefficient duplication of statements into the query.

New way: the new version behaves dramatically better if we avoid query filters that do the same things that the universe is already doing.

Universe joins – what are they?WHERE( DWST.BRS_DETAIL_V.PENN_ID=DWADMIN.DEGREE_TERM_ALL_V.PENN_ID(+) and

DWST.BRS_DETAIL_V.TERM=DWADMIN.DEGREE_TERM_ALL_V.TERM(+) and DWADMIN.DEGREE_TERM_ALL_V.DEGREE_ORDER(+) = 1 )

AND ( DWADMIN.MAJOR_MINOR_TERM.TERM=DWADMIN.DEGREE_TERM_ALL_V.TERM and

DWADMIN.MAJOR_MINOR_TERM.DEGREE_ORDER=DWADMIN.DEGREE_TERM_ALL_V.DEGREE_ORDER and DWADMIN.MAJOR_MINOR_TERM.PENN_ID=DWADMIN.DEGREE_TERM_ALL_V.PENN_ID and DWADMIN.MAJOR_MINOR_TERM.DP_LINK_ID=DWADMIN.DEGREE_TERM_ALL_V.DP_LINK_ID and DWADMIN.MAJOR_MINOR_TERM.MAJOR_MINOR_TYPE='M' and DWADMIN.MAJOR_MINOR_TERM.MAJOR_MINOR_ORDER=1 )

Disclaimer: this example may vary in style and format from the current universe picture…

Universe joins and query filters example: BRS Combined universe “old way”

Old query filters contain statements that repeat statements already created for you automatically by the universe joins:

Universe joins and query filters example: BRS Combined universe “new way”

Remove all the things that duplicate what the universe already does for you:

Example of a universe diagram


• Updates to Count Students reports



Miscellaneous Announcements

Non-resident alien – Change to calculation to be consistent with OIP and Institutional Research definition.

Old way: Based on US citizenship, any Visa other than permanent resident

New way: Based on US citizenship, “Sponsored Student” Visa

With new calculation:Not considered non-resident alien unless they have a “sponsored student visa” type and US citizenship.

Someone with non-US country of citizenship, without a “sponsored student” Visa will not be considered a non-resident alien.

Non Resident Alien– now using the “sponsored” visa definitionpreviously new logic

IF Visa in the Person table is NOT EQUAL TO ‘PR’

AND

IF Country_of_Citizenship is NOT EQUAL TO blank OR ‘US’

Then we set Non_Resident_Alien in the Person table to ‘Y’

IF Visa in the Person table is EQUAL TO ANY OF THE FOLLOWING:

F1, J1, F2, J2,H1ANDIF Country_of_Citizenship is

NOT EQUAL TO blank OR ‘US’

Then we set Non_Resident_Alien in the Person table to ‘Y’

Miscellaneous Announcements

• Use of dummy SSN in Course Section Instructor (SRS 132 screen) causing issues for downstream systems. • Practice of using blanks, all 9s, or arbitrary fake SSN ‘000001234’ • Looks like valid record in Penn Community – person becomes eligible for eval• Occurs in 29 records in last 4 terms , over 1,000 records with SSNs starting with

00

• Graduate Admissions Collection documentation available. Select “Graduate Admissions” from the Student data collection homepage http://www.upenn.edu/computing/da/dw/GraduateAdmissions/index.html

• New Public Folder for Academic Planning Worksheet collection, new REQBUILD Course Lists report , prompts for Owner Division, Owner Dept, pattern match for name.

• Some schools/centers are expanding their subfolders – contact DA if interested

• Student data contact person for schools/centers –> publish on DW web. • Firefox 11 released – A reminder about supported browsers for Webi.

• See the Query tools and FAQ for workaround pages for details.

http://www.upenn.edu/computing/da/dw/GraduateAdmissions/index.html

http://www.upenn.edu/computing/da/dw/querytools.html

http://www.upenn.edu/computing/da/dw/querytools.html

http://www.upenn.edu/computing/da/bo/webi/qna/iv_webi_HTTPStatus400.html

Wrap-up Any questions?

Next Meeting - August Topics you would like to see covered? Contact me if you would like to volunteer to present

Thank you for coming!

class of ‘55 room, van pelt library – february 22, 2012

Documents