1 / 54

Banner Extracts

Banner Extracts. Banner Extracts. Purpose of the Banner Extracts Extract data from the Banner database that is required by DegreeWorks and load the data into the RAD, DAP and/or SHP databases. Banner versions currently supported: 7 and 8. Banner Extracts. UCX Tables. DegreeWorks

ryo
Download Presentation

Banner Extracts

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Banner Extracts

  2. Banner Extracts Purpose of the Banner Extracts • Extract data from the Banner database that is required by DegreeWorks and load the data into the RAD, DAP and/or SHP databases. • Banner versions currently supported: 7 and 8

  3. Banner Extracts UCX Tables DegreeWorks Data Storage Banner Database Web Server CGI/Sockets DegreeWorks Server Banner Server Course Equivalency ETS Data and UCX Course Data Student Data Extract Real Time Batch Batch Requests Web Requests Transit/cron Users

  4. Banner Extracts • Extract Process Flow • Select desired student population • SQL file creates file of IDs …or… • Use Selection criteria to create file of IDs • Run Banner Extract and Bridge – (RAD30) • Read mode (e.g. Student, Advisor …) • Process each ID – (BAN40/banstudent) • Read SPRIDEN (primary-mst) • Read SORLCUR/SORLFOS (degree-dtl) • Read SFRSTCR, SHRTCKN, SHRTRCE (class/transfer-dtl) • Load (bridge) data – (RAD41/radbridge) • Hash used to check for data changes • Generate Audit Processor (DAP22) called to process students with data changes

  5. Current Banner Extracts • STUDENT – All Student oriented data • COURSE - Course data (rad_course_mst) • EQUIV – Equivalencies (dap_eqv_crs_mst/CFG070) • UCX (only 15 tables are extracted into DW) • ADVISOR/STAFF • rad_primary_mst, shp_user_mst, dap_user_mst

  6. Order of Processing • UCX – load the UCX tables from Banner • COURSE – load the rad_course_mst • EQUIV – load the dap_eqv_crs_mst • Must load UCX-CFG074 Reused Course Keys first • STAFF – load staff for PC products/web • ADVISOR – load the advisors into the rad_primary_mst, shp_user_mst, dap_user_mst • STUDENT – load few test students to start and then determine the pool of students that are desired in DegreeWorks

  7. Banner Extracts

  8. Three ways to run an extract: • 1. Transit RAD30 • 2. Web On-demand refresh • 3. Batch script from the command line or cron/at

  9. Extracting Student Data • By Student • On Demand • Transit • Select 1 student • Script/cron job • bannerextract student studentid • Select a group of students • Transit • Use selection criteria • Script/cron job • bannerextract student idfile_of_student_ids.ids • Defined SQL job • Transit • Checkbox Use Default SQL • Script/cron job • bannerextract student

  10. Batch – RAD30JOB • Access via Transit • Banner Selection criteria only available for STUDENT • BA02 - Student ID • BCLG - College code • BCON - Concentration codes • BDEG - Degree code • BLVL - Student Class Level code • BMAJ - Major codes • BMIN - Minor codes • BSCH - Level code • BSTS - Student type code • BB05 - End term • BB06 - Matric term • BB07 - Admit term • BB11 - Grad term • BB12 - Academic cat-yr term

  11. Invoking Other Modes via Transit - RAD30 • COURSE – ALWAYS extracts ALL records • ADDs record if NOT found, otherwise it updates record • EQUIV - ALWAYS extracts ALL records • DELETES ALL dap_eqv_crs_mst RECORDS, then recalculates equivalencies from scratch and ADDs all new records (rewrites CFG070) • UCX – ALWAYS extracts ALL records • Configuration flag in CFG020 BANNER: “Add UCX Only” • If = ‘Y’ then UCX records are only added if NOT found, otherwise the record is skipped. • If = ‘N’ then the each UCX table extracted is first deleted and then added entirely from Banner

  12. Banner Extracts On-demand Refresh • Banner data is refreshed (button) • SDREFRES key in the SHPCFG file • Clicking button always performs refresh • Timeout is ignored • Hash checked but date still updated • No audit is run • Good for troubleshooting issues BC 16

  13. Invoking the Banner Extracts from the Command Line • $ bannerextract extract_type <id or sql file> • Extract_type • student • advisor • staff • course • ucx • Id file • Example - student.ids • SQL file • If no ID or SQL file included, then default SQL file will be used

  14. Script: $IFAS_HOME/scripts/bannerextract • The “bannerextract” command line script may be used to run any of the Banner extract programs instead of Transit/RAD30. • This script MUST NOT BE RUN from the $IFAS_HOME/sql directory (contains the local SQL files). • The bannerextract sql just select the pool of records (e.g, selected by ID Codes, Course Keys) that will be extracted from Banner. They have nothing to do with the actual data being extracted. • The $ADMIN_HOME/common/bannerextract.config file is used to actually extract the data from the Banner database. More later on this topic…

  15. Student • To run the student extract using bannerstudents.sql file in the $LOCAL_HOME/sql directory: • $ bannerextract student • You may also specify a different sql file in the $LOCAL_HOME/sql directory: • $ bannerextract student somestudents.sql • You may also specify a file of student IDs in the $LOCAL_HOME/sql or current directory: • $ bannerextract student somestudents.ids • For testing purposes you may also supply a single student ID instead of a file name: • $ bannerextract student 1234567

  16. Banner Extract Population • Select active pool of students • local/sql/bannerstudent.sql SELECT DISTINCT(SPRIDEN_ID) FROM SPRIDEN, SGBSTDN B WHERE B.SGBSTDN_STST_CODE IN (SELECT STVSTST_CODE FROM STVSTST WHERE STVSTST_REG_IND = 'Y') AND B.SGBSTDN_TERM_CODE_EFF = (SELECT MAX(C.SGBSTDN_TERM_CODE_EFF) FROM SGBSTDN C WHERE C.SGBSTDN_TERM_CODE_EFF <= '200710' AND C.SGBSTDN_PIDM = B.SGBSTDN_PIDM AND B.SGBSTDN_LEVL_CODE IN ('UG', 'GR', 'LA')) AND B.SGBSTDN_TERM_CODE_CTLG_1 >= '200610' AND SPRIDEN_PIDM = B.SGBSTDN_PIDM AND SPRIDEN_CHANGE_IND IS NULL ORDER BY SPRIDEN_ID;

  17. STUDENT: $LOCAL_HOME/sql/bannerstudents.sql -- Change this select stmt to be whatever you want to select the pool -- of students you want pulled into DegreeWorks SELECT DISTINCT(SPRIDEN_ID) FROM SPRIDEN, SGBSTDN B WHERE B.SGBSTDN_STST_CODE IN (SELECT STVSTST_CODE FROM STVSTST WHERE STVSTST_REG_IND = 'Y') AND B.SGBSTDN_TERM_CODE_EFF = (SELECT MAX(C.SGBSTDN_TERM_CODE_EFF) FROM SGBSTDN C WHERE C.SGBSTDN_TERM_CODE_EFF <= '200710' AND C.SGBSTDN_PIDM = B.SGBSTDN_PIDM AND B.SGBSTDN_LEVL_CODE IN ('UG', 'GR', 'LA')) AND B.SGBSTDN_TERM_CODE_CTLG_1 >= '200610' AND SPRIDEN_PIDM = B.SGBSTDN_PIDM AND SPRIDEN_CHANGE_IND IS NULL ORDER BY SPRIDEN_ID;

  18. Advisor • To run the advisor extract using banneradvisors.sql file in the $LOCAL_HOME/sql directory: • $ bannerextract advisor • You may also specify a different sql file in the $LOCAL_HOME/sql directory: • $ bannerextract advisor someadvisors.sql • You may also specify a file of advisor IDs in the $LOCAL_HOME/sql or current directory: • $ bannerextract advisor someadvisor.ids • You may also supply a single advisor ID instead of a file name: • $ bannerextract advisor 9876543

  19. ADVISOR: $LOCAL_HOME/sql/banneradvisors.sql -- Change this select stmt to be whatever you want to select the pool -- of advisors you want pulled into DegreeWorks select distinct spriden_id from spriden, sgradvr, sgbstdn b where spriden_change_ind is null and spriden_pidm = sgradvr_advr_pidm and (b.sgbstdn_stst_code in (select stvstst_code from stvstst where stvstst_reg_ind = 'Y') and b.sgbstdn_term_code_eff = (select max(c.sgbstdn_term_code_eff) from sgbstdn c where c.sgbstdn_term_code_eff <= '200710' and c.sgbstdn_pidm = b.sgbstdn_pidm) and b.sgbstdn_levl_code in('UG', 'GR') and b.sgbstdn_term_code_ctlg_1 >= '200410') and b.sgbstdn_pidm = sgradvr_pidm order by spriden_id;

  20. Staff • To run the staff extract using bannerstaff.sql file in the $LOCAL_HOME/sql directory: • $ bannerextract staff • You may also specify a different sql file in the $LOCAL_HOME/sql directory: • $ bannerextract staff somestaff.sql • You may also specify a file of staff IDs in the $LOCAL_HOME/sql or current directory: • $ bannerextract staff somestaff.ids • You may also supply a single staff ID instead of a file name: • $ bannerextract staff 7654321

  21. STAFF: This file is not delivered – you may create your own: $LOCAL_HOME/sql/bannerstaff.sql Normal practice is to create a staff.ids file of SPRIDEN IDs (and passwords) and use it as input: $ bannerextract staff staff.ids

  22. Selected UCX Tables • To run the ucx extract using a list of DegreeWorks UCX tables listed in a file with a “.ids” extension in $LOCAL_HOME/sql (do not use with cron): • $ bannerextract ucx someucxtables.ids • The $LOCAL_HOME/sql/someucxtables.ids” file might contain tables (do NOT include the UCX_): • STU352 • STU560 • STU563 • In this case ONLY these 3 UCX tables will be re-extracted from Banner and loaded into the appropriate UCX_STU### table.

  23. Deleting IDs • Run deleteid to remove unwanted ID codes from the DegreeWorks database using the bannerdeleteids.sql file in the $LOCAL_HOME/sql directory (Warning: do not use with cron! ): • $ bannerextract deleteid • You may also specify a different sql file in the $LOCAL_HOME/sql directory: • $ bannerextract deleteid somedeletes.sql • You may also specify a file of IDs in the $LOCAL_HOME/sql or current directory: • $ bannerextract deleteid somedeletes.ids

  24. Client SQL Configuration File • $ADMIN_HOME/common/bannerextract.config • Allows the data to be extracted from Banner to match how you are storing data in Banner • Every table with a SELECT clause in the extract uses bannerextract.config • The sequence is as follows: • RAD30 selects the pool of ID codes, Course keys, etc. • The extract uses the bannerextract.config file to extract the data for each IDs/Keys passed by RAD30.

  25. Examples from bannerextract.config ## SPRIDEN - Individual (student/advisor/staff) ## • # Standard is to only select by the SPRIDEN_PIDM # and a SPRIDEN_CHANGE_IND of NULL – # but you may add to the WHERE clause if needed # --- but don't forget the final ANDSPRIDEN-from: FROM SPRIDEN aSPRIDEN-where: WHERE # a.SPRIDEN_PIDM = <individual's pidm># AND a.SPRIDEN_CHANGE_IND IS NULL

  26. Examples from bannerextract.config (cont…) ######### SORLCUR - CURRICULUM (ban40) ########## SORLCUR must be a; AND is required at the end of WHERESORLCUR-from: FROM SORLCUR aSORLCUR-where: WHERE a.SORLCUR_CACT_CODE = 'ACTIVE'SORLCUR-where: AND a.SORLCUR_SEQNO =SORLCUR-where: (SELECT MAX(b.SORLCUR_SEQNO) FROM SORLCUR bSORLCUR-where: WHERE b.SORLCUR_PIDM = a.SORLCUR_PIDMSORLCUR-where: AND b.SORLCUR_PRIORITY_NO = a.SORLCUR_PRIORITY_NOSORLCUR-where: AND b.SORLCUR_LMOD_CODE = 'LEARNER') SORLCUR-where: AND# a.SORLCUR_PIDM = <students-pidm>

  27. Password Setup in bannerextract.config • 3 Keywords: • PASSWORDSTU – for students • PASSWORDADV – for advisors • PASSWORDSTF – for staff • A concatentated password (shp_access_code) can be generated using a SQL statement using whatever tables the client wants to use. • If no valid password is generated (blank) then a random password will be generated (using alphabetic and numeric characters – 10-bytes)

  28. Examples from bannerextract.config (cont…) ####### PASSWORDSTU - WEB Password (ban40) ######### # This special SQL is used to create the piece of data to be loaded # into the shp_access_code as the SHP password for Students. # The SELECT clause MUST only return 1 data value. # If the value is BLANK or no valid record is found a RANDOM # 10-byte alphanumeric password will automatically be generated. # The = {STU_PIDM} MUST be somewhere in the WHERE clause. # The {STU_PIDM} will be replaced with the real PIDM processed.

  29. Examples from bannerextract.config (cont…) ######### PASSWORDSTU - WEB Password (ban40) ########### # DO NOT DELETE THE ENTRIES BELOW!!! LEAVE THE KEYS AND # BLANK OUT EVERYTHING AFTER THE COLON IF A RANDOM # PASSWORD IS TO BE GENERATED!!! • PASSWORDSTU-select: SELECT RPAD('S' || • PASSWORDSTU-select: RPAD(a.SPBPERS_DRIVER_LICENSE,6) || • PASSWORDSTU-select: RPAD(a.SPBPERS_ETHN_CODE,1) || • PASSWORDSTU-select: RPAD(a.SPBPERS_BIRTH_DATE,2) || • PASSWORDSTU-select: RPAD(a.SPBPERS_LEGAL_NAME,4),14) password_key • PASSWORDSTU-from: FROM SPBPERS a • PASSWORDSTU-where: WHERE a.SPBPERS_PIDM = {STU_PIDM} • PASSWORDSTU-orderby: ORDER BY SPBPERS_ACTIVITY_DATE DESC

  30. Added “password” field to the Staff “.ids” file in the $LOCAL_HOME/sql/ directory • Optional – if password included after the Staff ID (starts in byte 11) it will be used as the shp_access_code for that staff member. • (NOTE: the PASSWORDSTF entries in bannerextract.config must be left blank for this password to be used).

  31. Banner Extracts • Configuration Decisions • Email • Repeat Policies • Repeatable Policies • Equivalencies • Cross Listed Courses • Skip classes (do not import to DegreeWorks) • Class Flag Overrides (e.g. In-Progress, Insufficient) • Identify Student/Advisor/Staff Population to Load • Custom Banner Data

  32. Settings • Change UCX-SCR001 DegreeWorks Field Names to Banner Field Names: • Status to Student Type • School to Level • Level to Student Class Level • Set the UCX-CFG020 flags/values: • “BANNER” record • “REFRESH” record • “SEARCH” record

  33. Banner Extracts UCX-CFG020 “BANNER” Configuration

  34. Banner Extracts • Repeat Policy SHRTCKN_REPEAT_COURSE_IND : I – always include E – skip or include? A – skip or include? • LAST credits and grade points count in DegreeWorks. Earlier occurrences are forced to “insufficient” and do NOT count. • BEST credits and grade points count in DegreeWorks. All other occurrences go to “insufficient” and do NOT count. • All occurrences count in the credits and GPA calculations in DegreeWorks. Classes will be applied where they fit. TG-40

  35. Banner Extracts • Repeat Policy (continued) • All sets of grades and grade points count for GPA calculations. ONLY the credits from the LAST occurrence count in DegreeWorks. All other occurrences go to “insufficient”. • All occurrences of the repeated class will be listed on the DegreeWorks audit where they could apply (all occurrences will stay grouped together by DegreeWorks). All sets of grades and grade points are used in the GPA calculation, but only credits for the class with the BEST grade are counted. The BEST grade is also used for MINGRADE checks. • Keep THIS occurrence of the class. This repeat policy is used when none of the five policies above fit your site’s requirements. All other occurrences should be marked with a “0” Repeat Policy and will go to “insufficient”.

  36. Banner Extracts • Repeat Policy (continued) • Best to use Policy “B” for all indicators: • Excluded classes show in Insufficient but do not affect the GPA • Averaged classes show in Insufficient but do affect the GPA • Included classes apply to rules as normal classes. • Repeatable Courses • Courses that can be taken several times for credit (e.g. Music Lessons, PE activity classes, etc.) • Repeatable Option defines how to correctly extract courses that are truly repeatable (i.e. not repeated for a better score) • Documentation • Banner Registration • DGW Technical Guide Banner Data Mapping for BIF – Class Repeats / Multiple Occurrences section • DGW Technical Guide UCX

  37. Banner Extracts • Repeatable Courses • N – DO NOT check the SCBCRSE_REPEAT_LIMIT or SCBCRSE_MAX_RPT_UNITS • L – Check the SCBCRSE_REPEAT_LIMIT only (Default if Repeatable Option BLANK) • U – Check the SCBCRSE_MAX_RPT_UNITS only • B – Check Both the SCBCRSE_REPEAT_LIMIT and SCBCRSE_MAX_RPT_UNITS • I – Include the class credits in the SCBCRSE_REPEAT_LIMIT and SCBCRSE_MAX_RPT_UNITS before checking for repeatable classes using the SCBCRSE_CREDIT_HR_LOW

  38. Settings • Load “ATTRIBUTE” into UCX-SCR002 FOR Student Attributes. These values are available for use with the “WITH” keyword in Scribe. • If (Attribute = HONR) then 15 Credits in ENGL 4@ Label “15 upper-division credits required”; • Load “ATTRIBUTE” into UCX-SCR044 FOR Class Attributes. These values are available for use with the “WITH” keyword in Scribe. • 5 Credits in ENGL @ (With Attribute = HONR)

  39. Banner Extracts • Equivalencies • Course that has changed Discipline and/or Number within an institution • Extract equivalencies from SCREQIV only? Equivalency: DegreeWorks will apply MATH 102 to the Math 110 requirement for students who took MATH 102 in 200610 Catalog year course takenStudent catalog year new course 200610 MATH 102 @ MATH 110

  40. Equivalences • Two scribing options: • 1 Class in MATH 110; # was 102 • 1 Class in MATH 110 {Formerly102}; • Banner Extract EQUIV loads UCX-CFG073 • 1. Launch Banner Extract and Bridge (RAD30) for the EQUIV mode. • 2. Set the UCX-CFG020 DAP13 Process Equivalences = Y. • 3. Launch Parse Blocks (DAP16) • Your requirements now use the new course number

  41. Banner Extracts • Cross-listed Courses • Courses that are the same course offering but with different course numbers • Two scribing options • 1 Class in MATH 101, PHIL 101, STAT 101; • 1 Class in MATH 101 {Hide PHIL 101, STAT 101};

  42. Banner Extracts • Cross-listed Courses UCX-CFG073: MATH 101 cross-listed with PHIL 101 MATH 101 cross-listed with STAT 101 MATH 102 cross-listed with PHIL 102 Scribe: BEGIN MaxCredits 9 in MATH 101, 102; 5 Credits in MATH 101 , 102 Label "My rule 1"; END.

  43. Banner Extracts • Cross-listed Courses • Banner Extract EQUIV loads UCX-CFG073 • 1. Set the CFG020 BANNER Cross List in SCREQIV = Y. • 2. Launch Banner Extract and Bridge (RAD30) for the EQUIV mode. • 3. Set the CFG020 DAP13 Process Cross-Listings = Y. • 4. Launch Parse Blocks (DAP16) • Your requirements will now be cross-listed aware

  44. Banner Extracts • Skip Classes: • By SCHD_CODE (UCX-BAN001 and UCX-BAN002) • By GMOD_CODE (UCX-BAN003) • By Subject Code (Discipline) (UCX-STU352) • Or simply change bannerextract.config

  45. Banner Extracts • Override DegreeWorks Class Flags (UCX-STU385) • By Banner Level, Grade Type (Mode) and Grade: Audit Flag Incomplete Flag Insufficient Flag Passed Flag In Progress Flag Pass/Fail Flag Withdraw Flag Final Grade Number Only set the flags you want to override Leave a flag blank if you want to use the Banner value

  46. Banner Extracts • Override Configuration (UCX-STU385) • After modifying UCX you must re-bridge students!

  47. Why Use UCX-BAN080? Allows you to extract “custom” pieces of data into DegreeWorks that are not extracted by the standard student extract program. 3 Types of Records: • AID – used in Financial Aid audits • Data is loaded into the rad_aid_dtl • CUSTOM – used in IF statements on audits • Data is loaded into the rad_custom_dtl • REPORT – used on audit headers • Data is loaded into the rad_report_dtl

  48. Banner Extracts • Extracting additional Banner data • Extract values from any table by PIDM • Stored by use: • Custom data stored in: rad-custom-dtl • Report data stored in: rad-report-dtl • SureCode entry • Dynamic SQL Definitions (UCX-BAN080) • Custom Data (UCX-SCR002)

  49. Banner Extracts Dynamic SQL Definitions (UCX-BAN080)

More Related