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 Q&A 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 Q&A

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

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 <= aid term) For year info: max (eff term <= 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 Which General Student record to use?

12. EDW Model Walkthrough Timing Examples Student History Awards by Year Which General Student record to use? For 0203: Max (Eff Term <= Fall 02) = Fall 02, so Major used will be FIN

13. EDW Model Walkthrough Timing Examples Student History Awards by Year Which General Student record to use? For 0203: Max (Eff Term <= Fall 02) = Fall 02, so Major used will be FIN For 0304: Max (Eff Term <= Fall 03) = Fall 02, so Major used will be FIN

14. EDW Model Walkthrough Timing Examples Student History Awards by Year Which General Student record to use? For 0203: Max (Eff Term <= Fall 02) = Fall 02, so Major used will be FIN For 0304: Max (Eff Term <= Fall 03) = Fall 02, so Major used will be FIN For 0405: Max (Eff Term <= Fall 04) = Fall 04, so Major used will be ECON

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 don’t want What time object to use? Applicant year code Term Student Effective term

17. Business Objects Universes How to get the “max (term <= target term)”? ‘Max term’ and ‘<= 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 <= ‘<term>’ 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-Professional 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: (312) 996-4806 Urbana: (217) 333-3102 Springfield: (217) 333-3102 DS Website: www.ds.uillinois.edu

23. Q&A Questions?