banner extracts
Download
Skip this Video
Download Presentation
Banner Extracts

Loading in 2 Seconds...

play fullscreen
1 / 54

Banner Extracts - PowerPoint PPT Presentation


  • 397 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Banner Extracts' - ryo


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide2

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 extracts1
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

slide4

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
slide5

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
slide6

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
slide8

Three ways to run an extract:

    • 1. Transit RAD30
    • 2. Web On-demand refresh
    • 3. Batch script from the command line or cron/at
slide9

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
slide11

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
slide12

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
slide13

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

slide14

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
slide15

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…
slide16

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
slide17

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;

slide18

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;

slide19

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
slide20

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;

slide21

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
slide22

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

slide23

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.
slide24

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
slide25
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.
slide26

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
slide27

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>

slide28
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)
slide29

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.

slide30
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
slide31

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).
slide32

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
slide33

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
slide34

Banner Extracts

UCX-CFG020 “BANNER” Configuration

slide35

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

slide36

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”.
slide37

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
slide38

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
slide39

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 [email protected]

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)
slide40

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

slide41
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
slide42

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};
slide43

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.

slide44

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
slide45

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
slide46

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

slide47

Banner Extracts

  • Override Configuration (UCX-STU385)
  • After modifying UCX you must re-bridge students!
slide48

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
slide49

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)
slide50

Banner Extracts

Dynamic SQL Definitions (UCX-BAN080)

slide51

Banner Extracts

Custom Data (UCX-SCR002)

slide52

Banner Extracts

Command Line

  • Extract modes:

Advisor Course

Equiv Staff

Student UCX

  • Run - bannerextractmode
  • SQL used to select students/advisors/staff
    • Run bannerextractextractname [scriptname.sql]
    • File of IDs bannerextractextractname idfile.ids
  • Schedule using your preferred job scheduler (e.g. cron, at)
slide53

Summary of SQL Used

  • $LOCAL_HOME/sql/banner*
    • Used to SELECT the pool of IDs for Banner Extracts
  • $ADMIN_HOME/common/bannerextract.config –
    • Used to EXTRACT data from Banner for the pool of IDs
  • UCX-CFG080
    • Used to extract CUSTOM pieces of data from Banner for the audits (IF rules, Audit headers, AID audits)
  • If Banner tables not used by DegreeWorks are used in ANY of the above SQL files make sure to get access granted to the DegreeWorks users by the client’s DBA
slide54

Documentation Related to the Banner Extract

  • DGW_Technical_Guide_Banner_Considerations
  • DGW_Technical_Guide_Banner_Data_Mapping_for_BIF
  • DGW_Technical_Guide_UCX
    • BAN001, BAN002, BAN003, BAN080
    • CFG020 – “BANNER”, “REFRESH”, “SEARCH”
    • EQUIV Tables - CFG070, CFG073, CFG074, CFG078
ad