DS User Acceptance Testing

1
Financial Aid Increment Data Education

2
About This Lesson

• Background
• Business Objects training (108, 5i) covers the
  tool
• Users also need to know how data is structured in
  the Data Warehouse and how it can be accessed
• Covered
• Business Objects Universes
• Sample reports
• Data models
• QA
• Not Covered
• Using the Business Objects tools
• InfoView, WebIntelligence, Business Objects 5i
• Operational processes and using Banner
• UI-Integrate published reports and letters

3
Agenda

• Accessing the Data
• Data Warehouse Environment
• EDW Model Walkthrough
• Business Objects Universes
• Data Groupings
• Metadata
• Contacts
• QA

4
Accessing the Data

• Business Objects Universes
• WebIntelligence and 5i access data through
  Universes
• Provides a layer of business logic over the
  database
• Graphic representation of database tables and
  columns using business names includes object
  definitions
• Drag and drop objects onto reports
• Business Objects tools are supported by Decision
  Support
• Direct Access
• Connect using other tools (e.g. SAS/PC, Crystal
  Reports)
• Direct access to database tables (via security
  views)
• Can be more complex to use
• Other tools are not supported by Decision Support
• Requesting Access
• Users will request access through USCs
• Decision Support does not decide who gets access

5
Data Warehouse Environment

• Data Marts
• Designed for particular use
• Subset of data
• Combines data in simpler structure
• Apply business rules
• Fast and easy to use
• Slice and dice counts

• EDW
• Core repository of data
• Multiple subject areas
• Very flexible but complex structure
• Track change history
• Day old data

6
EDW Model Walkthrough
7
EDW Model Walkthrough

• EDW Model Overview
• General Person
• History tracking effective date, current info
  indicator
• Aid application
• RCRAPP ESAR
• Needs analysis
• Applicant Status (RORSTAT) contribution, need
• Budget Components
• Resources
• Financial aid funds and awards
• Fund and balance info
• Awards and Awards by term
• Disbursement schedule
• Loan application and processing
• Loan application, loan disbursement, prom note
• Loan certification
• Lender data
• Direct loan account statement

8
EDW Model Walkthrough

• EDW Model Overview
• Satisfactory Academic Progress
• User defined fields
• Banner 1 record per person (per yr), 200
  columns
• EDW 1 record per person per UDF (per yr), 10
  columns
• Student employment
• Financial Aid holds
• Only contains Financial Aid holds, not other
  types
• NSLDS
• General Student
• Effective term based, not a record for every term
• Multiple active records, depends on point in
  time
• History tracking effective date, current info
  indicator
• Student Registered Hours
• Financial Aid Enrollment

9
EDW Model Walkthrough

• Year-based vs. Term-based records
• Year-based
• Aid application
• Needs analysis
• Funds and awards
• Disbursement (has term attribute)
• User Defined Fields
• Financial Aid holds
• Student Employment
• Loan info
• Term-based
• Awards
• SAP
• Financial Aid Enrollment and Student Registered
  Hours
• Date-based
• General Person

10
EDW Model Walkthrough

• General Student record
• Effective term based (not a record for every
  term)
• Effective Term in record is starting term for
  range of terms
• Effective Date may be misleading
• Current Info Indicator needed to identify active
  record for the term range
• Implications for queries
• Duplicate rows possible
• Conditions are essential for accurate results
• Business rules must be built into query or report
• Term and Year need to be specified
• General Student records
• Need business rules to match appropriate General
  Student record to Financial Aid records
• For term info max (eff term lt aid term)
• For year info max (eff term lt ROBINST eff term)
• EDW vs. data mart
• EDW must be flexible to support many different
  type of reporting
• Business rules to map student to FA built into
  Awards data mart

11
EDW Model Walkthrough

• Timing Examples
• Student History
• Awards by Year

EDW_ID EFF_DT Exp Date Cur Student Effective Term Major
101 09/15/02 09/20/02 N Fall 02 ACCY
101 09/21/02 12/31/9999 Y Fall 02 FIN
101 03/10/04 12/31/9999 Y Fall 04 ECON
101 09/15/02 12/31/9999 Y Fall 07 N/A
EDW_ID EFF_DT Applicant Year Fund Offer Amt
101 04/25/02 0203 Pell 1000
101 04/15/03 0304 Pell 1000
101 04/30/04 0405 Pell 2000
12
EDW Model Walkthrough

• Timing Examples
• Student History
• Awards by Year

EDW_ID EFF_DT Exp Date Cur Student Effective Term Major
101 09/15/02 09/20/02 N Fall 02 ACCY
101 09/21/02 12/31/9999 Y Fall 02 FIN
101 03/10/04 12/31/9999 Y Fall 04 ECON
101 09/15/02 12/31/9999 Y Fall 07 N/A
EDW_ID EFF_DT Applicant Year Fund Offer Amt
101 04/25/02 0203 Pell 1000
101 04/15/03 0304 Pell 1000
101 04/30/04 0405 Pell 2000
13
EDW Model Walkthrough

• Timing Examples
• Student History
• Awards by Year

EDW_ID EFF_DT Exp Date Cur Student Effective Term Major
101 09/15/02 09/20/02 N Fall 02 ACCY
101 09/21/02 12/31/9999 Y Fall 02 FIN
101 03/10/04 12/31/9999 Y Fall 04 ECON
101 09/15/02 12/31/9999 Y Fall 07 N/A
EDW_ID EFF_DT Applicant Year Fund Offer Amt
101 04/25/02 0203 Pell 1000
101 04/15/03 0304 Pell 1000
101 04/30/04 0405 Pell 2000
14
EDW Model Walkthrough

• Timing Examples
• Student History
• Awards by Year

EDW_ID EFF_DT Exp Date Cur Student Effective Term Major
101 09/15/02 09/20/02 N Fall 02 ACCY
101 09/21/02 12/31/9999 Y Fall 02 FIN
101 03/10/04 12/31/9999 Y Fall 04 ECON
101 09/15/02 12/31/9999 Y Fall 07 N/A
EDW_ID EFF_DT Applicant Year Fund Offer Amt
101 04/25/02 0203 Pell 1000
101 04/15/03 0304 Pell 1000
101 04/30/04 0405 Pell 2000
15
Business Objects Universes

• EDW Financial Aid Office Ad hoc
• Detailed ad hoc reporting/analysis of all
  Financial Aid data in EDW
• Accessible to Financial Aid Offices and IR only
• Maps EDW structure high flexibility highly
  complexity
• Does not resolve association of General Student
  to Financial Aid records for a year or term
• Only current records for General Student (per
  term range)
• Includes
• Basic General Person and General Student
• Aid application and needs analysis
• Financial aid funds and awards (including
  disbursement)
• Loan application and processing
• Satisfactory Academic Progress
• User defined fields
• Student employment
• Financial Aid holds
• NSLDS
• Not included
• Change history of General Student record
• Mapping of Budget Components to Budget Groups

16
Business Objects Universes

• Things to know
• Click the on code objects to get descriptions
• Count objects
• Count () counts all records in query (e.g. of
  awards)
• Headcount distinct person count (EDW_PERS_ID)
• Fund Code list of values (LOV)
• For Conditions on Fund Code, selecting LOV
  prompts for Fund Source to narrow list
• Avoiding duplicate rows
• Issue when combining data with different time
  basis
• Report parameter Avoid duplicate rows
  aggregation
• Can hide rows you want (e.g. multiple award rows)
• Can hide rows you dont want
• What time object to use?
• Applicant year code
• Term
• Student Effective term

17
Business Objects Universes

• How to get the max (term lt target term)?
• Max term and lt target term can be used
  independently
• To combine, have to use workaround
• Add Condition on Student Effective Term
• Use In list operator
• Use Calculation operand
• Select an Object Student Effective Term
• Select a Function Maximum
• Define the Level of Calculation Globally
• Synchronize your Calculation For each value of
  one or more objects (use EDW_PERS_ID, not UIN)
• Set the Number of Values to Compare All values
• Modify report SQL
• Add line to final Where clause
• and edw.t_student_hist.term_cd_eff lt lttermgt
• Check the Do not generate SQL before running
  checkbox

18
Data Groupings

• Fund Type
• Discretional Tuition Waiver, Discretionary Fee
  Waiver, Fee Waiver, Grant, Loan, Scholarship,
  Statutory Fee Waiver, Statutory Tuition Waiver,
  Tuition Waiver, Work
• Fund Source
• Federal, State, Institutional, Departmental,
  Other
• Student Type
• Student Type Group Beginning, Transfer, Readmit
• Citizenship
• Citizenship Type Group Domestic, International
• Racial Ethnic
• Racial Ethnic Group Minority, Other
• Racial Ethnic Reporting Group Group A, Group B
• Student Level
• Level Group Credit, Non-credit
• Level Credit Group Degree, Non-degree
• Level Degree Group Undergrad, Graduate-Profession
  al
• Graduate/Professional Group Graduate,
  Professional

19
Data Groupings

• Residency
• In-State Indicator Y/N
• In-State Tuition Indicator? Y/N
• Residency Group being reviewed
• Report Examples

20
Metadata

• Logical Data Model
• High-level business view of the data
• Shows the actual tables and views with business
  names
• Useful for determining what data is available.
• Physical Data Model
• Actual representation of physical tables and
  views in database
• Actual physical table names are used
• Key reference when writing queries directly
  against the database
• Data Dictionary
• Definitions for all of the tables in the
  Warehouse
• Includes column names and descriptions, table
  keys, valid values
• Useful for finding out more about specific table
  or column
• Source-to-Target Mappings
• Show each table and column in EDW and indicate
  the table and column in the source system
• Show source in the EDW for data mart tables and
  columns
• Uses physical table and column
• Useful for tracking data back to the source

21
Metadata

• Usage Notes
• Background about the environment and general info
• Refresh rates
• Table joins
• Change history tracking
• How deleted data is handled
• Use of indexes in the EDW
• Data access summary
• Data groupings

22
Contacts

• AITS Help Desk
• Chicago [phone]
• Urbana [phone]
• Springfield [phone]
• DS Website
• www.ds.uillinois.edu

23
QA

• Questions?