OBIEE - Snapshots

Jobs are scheduled to run nightly to give flexibility so we can see how student data looks at a point in time. The snapshots we have are used for Enrollment, Student Financials’ six-week Academic Fee Income Report, Admissions Weekly and Student Enrollment Categories.  FALL 2014 is the first available 10-day term within the BI environment.

Current working Snapshot Subject Areas:

UWP – Snapshot Admissions – Student Admissions data such as term, application, programs & plans, test scores, high school and residency information. This data helps build pieces of the Tenth Day Enrollment Reports dashboard.

UWP – Snapshot Student Career and Records – Student Records data such as term, career, program and ethnicity information. This data helps build pieces of the Tenth Day Enrollment Reports dashboard.

UWP – Snapshot Student Enroll and Records – A combination of Student Enrollment and Records data such as term, plateau, less than full time and overload enrollment, tuition charges and refunds and Financial Aid/Waiver information. This data helps build the Academic Fee Income Report used during six-week.

UWP – Snapshot Student Records Budget Model – Derived from UWP Snapshot Enroll and Records with limited dimensions and additional calculated fields. Includes double-majors.

The subject areas consist of Type 2 Slowly Changing Dimensions and one or more Facts.

What’s a Dimension? A table in the star schema of the data warehouse that stores and categorizes data, such as people, items or other objects, in order to enable users to answer business questions.

What’s a Fact? Tables that help quantify activities and typically consist of numerical values as it relates to the Dimensions.

What’s a Type 2 Slowly Changing Dimension? A new row is captured for the new value and the existing row is maintained for historical and reporting purposes. This enables you to track the history of updates to your dimension records. There are 3 columns that help identify the historical and current data:   

EFF_START_DT: Current date for the record loaded and is NOT the same as the records’ effective date (EFFDT)

EFF_END_DT: Effective end date for the current record (31-DEC-99 = 31-DEC-2099)

CURRENT_IND: Current indicator flag. Typically ‘Y’ or ‘N’

Before the change:

After the change:

With a Type 2 Dimension, we are able to handle such information as students in a double major:

**Special Note: The intention of the snapshots is to give overall student count data, but maintain the capability to report detailed historical data. Should a requirement involve the need for such detailed reporting, special filters may need to be included within the analyses or incorporated at the OBIEE repository level. Some of these special filters may already exist in the Document Detail. Here are a few examples:

FERPA Flag – Y/N

Unit Taken Progress Flag – Y/N:  cast(case when F_UWP_SR_SNPSHT.UNT_INPROG_GPA + F_UWP_SR_SNPSHT.UNT_INPROG_NOGPA + F_UWP_SR_SNPSHT.UNT_TAKEN_GPA + F_UWP_SR_SNPSHT.UNT_TAKEN_NOGPA > 0 then 'Y' else 'N' end as CHARACTER ( 40 ))

Completion Term Flag – Y/N: CASE WHEN "Enterprise Warehouse"."D_TERM_SNP_COMPL"."Completion Term Code" >= "Enterprise Warehouse"."Dim Term Snap"."Term Code" OR
"Enterprise Warehouse"."D_TERM_SNP_COMPL"."Completion Term Code" = '-' THEN 'Y' ELSE 'N' END

Need help?

If you have questions, please contact the ITS Help Desk at 608.342.1400 or helpdesk@uwplatt.edu.  You may also visit the Help Desk on the first floor of the Karrmann Library.