1 / 31

Banner and External Application Integration

Banner and External Application Integration. William Boyd. Introduction. Presenter: William Boyd, Senior Software Developer @ Liberty University , Learning Management Systems team LU: located in Lynchburg, VA ~12,000 on-campus and ~58,000 online students. LU and Bb.

ferris
Download Presentation

Banner and External Application Integration

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 and External Application Integration William Boyd

  2. Introduction • Presenter: William Boyd, Senior Software Developer @ Liberty University, Learning Management Systems team • LU: located in Lynchburg, VA • ~12,000 on-campus and ~58,000 online students

  3. LU and Bb • ~50,000 – 55,000 users actively using Bb this fall. • We host Bb on a load balanced cluster with 6 app servers and 4 db servers. • Bb is used by our residential students, but is the primary learning delivery platform for online students

  4. Overview • Automated data capture from Banner for snapshot automation. • Building Block for posting final grades from Blackboard into Banner. • Queries for presenting Blackboard data in a widget for an external portal.

  5. Liberty University:Banner Integration Needs • Banner = our “master of record” • Users • Courses • Registrations/Enrollments • Grades/Transcripts • Records of all these need to be maintained in Banner, but we need to integrate with Bb

  6. Integration: Banner to Bb • Since Banner is our master of record, our integration begins with the feed of data from Banner to Bb • We use Bb Snapshot to feed users, courses, and enrollments from Banner to Bb • A java process runs nightly, querying the data from Banner and building the snapshot files which are then imported by Bb Snapshot

  7. Integration: Banner to Bb • We are quickly outgrowing the snapshot-style data feed • ~350k users, ~32k courses, ~750k student enrollments and 74k instructor enrollments) • takes almost 40 minutes to run the entire process (the bulk of which is Bb snapshot importing the data files)

  8. Integration: Banner to Bb • Whatever method is used to import data into Bb from an external system, it is crucial to be able to associate that data with the master of record data later on • Allows other integration points dealing with that data down the road • Luckily, Bb provides a way to do this

  9. Your Friend, the batch_uid • batch_uid is a property of users and courses in Bb. • When data is fed to Bb, Put an external key in that field that will identify the record it came from on the master of record system (such as Banner) • Snapshot external_person_key and external_course_key go into batch_uid on users and course_main tables respectively • Accessible through Building Blocks API: User.getBatchUid(), Course.getBatchUid()

  10. Your Friend, the batch_uid • For our Banner – Bb Integration • We put the Banner student id in Bb’s batch_uid for users (ex. 160023) • in Banner it is saturn.spriden.spriden_id • all users have one, not just students • Banner doesn’t have a surrogate key for courses, but the unique identifier for courses is saturn.ssbsect.ssbsect_term_code and saturn.ssbsect.ssbsect_crn. • These are fixed length, so we concatenate them for the course batch_uid in Bb (ex. 20113020006)

  11. Cross-Listed Banner courses • We use course cross-listing in Banner, which introduces a complication. • Cross-listing involves two course sections in Banner which share resources (professor, meeting location/time, and most importantly Bb course shell). • This means we are tasked with mapping two or more courses in Banner to one course shell in Bb.

  12. Cross-Listed Banner courses • At all integration points, cross listed courses will need to be treated differently. • So make batch_uid follow a particular format so cross listed courses can be identified programmatically on the Bb side • In Banner, a set of cross listed courses is identified by saturn.ssbxlst.ssbxlst_term_code and saturn.ssbxlst.ssbxlst_xlst_group • We chose this as a batch_uid for cross listed courses: term_code + “XLST” + xlst_group(ex. 201130XLST1C)

  13. Example of Passing Data back to Banner:Post Final Grades building block • Final grades need to be recorded in Banner, but primary faculty interface is Bb (gradebook, etc.) • Post Final Grades building block provides a page which allows faculty to enter a letter grade and submit that data to Banner • Uses course batch_uid and user batch_uid of students to map back to the appropriate record in Banner

  14. Post Final Grades:Screenshot

  15. Another Integration Example:LU Portal Bb Widget • LU Portal: a portal page that provides several “widgets” which pull data from a multitude of systems (similar to iGoogle) • Includes a Blackboard widget to give students quick access to Bb information

  16. Portal Bb Widget: Integration • Banner user/course identifiers available to the portal, uses batch_uid to find corresponding Bb data • Then, query Bb tables directly for announcements / assignments / grades / discussion boards / etc. • Open database initiative: Bb database schema documentation

  17. LU Portal Bb Widget • Organizes information by course:

  18. Course List Query:a user’s list of courses SELECT users.pk1 AS users_pk1, course_main.pk1 AS crsmain_pk1, course_users.pk1 AS course_users_pk1, course_main.course_id, course_main.course_name, course_main.batch_uidcourse_batch_uid, course_users.role, users.user_id FROM bb_bb60.users JOIN bb_bb60.course_users ON course_users.users_pk1 = users.pk1 JOIN bb_bb60.course_main ON course_users.crsmain_pk1 = course_main.pk1 WHERE course_users.row_status = 0 AND course_main.row_status = 0 AND course_main.available_ind = 'Y' AND users.user_id= lower(?) AND substr(course_main.batch_uid, 0, 6) = ? AND course_users.role = ?

  19. LU Portal Bb Widget • View of one course

  20. LU Portal Bb Widget • Announcements view

  21. Announcements Query:announcements by course SELECT announcements.crsmain_pk1, course_users.users_pk1, course_users.role, course_main.batch_uidcourse_batch_uid, announcements.start_date, announcements.end_date, announcements.subject, announcements.announcement FROM bb_bb60.announcements JOIN bb_bb60.course_main ON announcements.crsmain_pk1 = course_main.pk1 JOIN bb_bb60.course_users ON course_users.crsmain_pk1 = course_main.pk1 WHERE course_users.row_status = 0 AND course_main.row_status = 0 AND course_main.available_ind = 'Y' AND announcements.start_date <= sysdate AND (announcements.end_date is null or announcements.end_date >= sysdate) AND announcements.crsmain_pk1=? AND course_users.users_pk1 = ? AND course_users.role = ? ORDER BY announcements.start_datedesc

  22. LU Portal Bb Widget • Assignments view

  23. Assignments Query:assignments by course SELECT course_main.pk1 AS crsmain_pk1, gradebook_main.pk1 AS gradebookmain_pk1, course_users.users_pk1, course_users.role, gradebook_main.title, gradebook_main.description, gradebook_main.due_date, gradebook_main.date_modified, gradebook_main.position, course_users.enrollment_date FROM bb_bb60.gradebook_main JOIN bb_bb60.course_main ON gradebook_main.crsmain_pk1 = course_main.pk1 JOIN bb_bb60.course_users ON course_users.crsmain_pk1 = course_main.pk1 WHERE course_users.row_status = 0 AND course_main.row_status = 0 AND course_main.available_ind = 'Y' AND gradebook_main.calculated_ind = 'N' AND gradebook_main.visible_ind = 'Y' AND gradebook_main.crsmain_pk1= ? AND course_users.users_pk1= ? AND course_users.role = ? ORDER BY gradebook_main.position

  24. LU Portal Bb Widget • DB Forum Posts view

  25. DB Forum Query:Part 1 – ‘conference’ DB’s SELECT conference_owner.owner_pk1 AS CRSMAIN_PK1, forum_main.pk1 forummain_pk1, forum_main.name AS FORUM_NAME, conference_owner.owner_table, MAX(LAST_EDIT_DATE) AS LAST_POSTED, 0 AS RECENT_COUNT FROM bb_bb60.conference_owner, bb_bb60.conference_main, bb_bb60.forum_main, bb_bb60.msg_main WHERE conference_owner.owner_pk1 = ? AND conference_owner.owner_table = 'COURSE_MAIN' AND conference_owner.pk1 = conference_main.conference_owner_pk1 AND forum_main.confmain_pk1 = conference_main.pk1 AND msg_main.forummain_pk1 = forum_main.pk1 GROUP BY conference_owner.owner_pk1, forum_main.pk1, forum_main.name, conference_owner.owner_table

  26. DB Forum Query:Part 2 – ‘group’ DB’s SELECT conference_owner.owner_pk1 AS CRSMAIN_PK1, forum_main.pk1 forummain_pk1, forum_main.name AS FORUM_NAME, conference_owner.owner_table, MAX(LAST_EDIT_DATE) AS LAST_POSTED, 0 AS RECENT_COUNT FROM bb_bb60.conference_owner, bb_bb60.conference_main, bb_bb60.groups, bb_bb60.group_users, bb_bb60.forum_main, bb_bb60.msg_main WHERE groups.crsmain_pk1 = ? AND groups.pk1 = conference_owner.owner_pk1 AND group_users.groups_pk1=groups.pk1 AND group_users.course_users_pk1 = ? AND conference_owner.owner_table = 'GROUPS' AND conference_owner.pk1 = conference_main.conference_owner_pk1 AND forum_main.confmain_pk1 = conference_main.pk1 AND msg_main.forummain_pk1 = forum_main.pk1 GROUP BY conference_owner.owner_pk1, forum_main.pk1, forum_main.name, conference_owner.owner_table

  27. LU Portal Bb Widget • Grades view

  28. Grades Query SELECT gradebook_main.pk1, gradebook_main.crsmain_pk1, gradebook_grade.COURSE_USERS_PK1, gradebook_main.title, gradebook_main.description, gradebook_main.due_date, gradebook_main.date_added, gradebook_main.position, gradebook_grade.average_score, gradebook_main.POSSIBLE, gradebook_grade.MANUAL_SCORE, gradebook_grade.MANUAL_GRADE, gradebook_log.ATTEMPT_CREATION AS DATE_MODIFIED //modified 2010-11-16 jmc FROM bb_bb60.gradebook_grade JOIN bb_bb60.gradebook_main ON gradebook_grade.gradebook_main_pk1 = gradebook_main.pk1 JOIN bb_bb60.course_users ON course_users.pk1=gradebook_grade.course_users_pk1 JOIN bb_bb60.gradebook_log ON gradebook_log.gradebook_main_pk1 = gradebook_main.pk1 AND gradebook_log.user_pk1=course_users.USERS_PK1 WHERE gradebook_grade.COURSE_USERS_PK1 = ? AND gradebook_main.crsmain_pk1 = ? AND gradebook_main.calculated_ind = 'N' AND gradebook_main.visible_ind = 'Y' AND gradebook_grade.average_score IS NOT NULL ORDER BY gradebook_main.position

  29. Faculty Contact Info Query SELECT crsmain_pk1, staffinformation.sirtitle, staffinformation.first_name, staffinformation.last_name, staffinformation.email, staffinformation.phone, staffinformation.office_hours, staffinformation.office_address, staffinformation.biography FROM bb_bb60.staffinformation WHERE crsmain_pk1=? AND ( first_name IS NOT NULL OR last_name IS NOT NULL OR email IS NOT NULL OR phone IS NOT NULL OR office_hours IS NOT NULL OR office_address IS NOT NULL )

  30. Questions

  31. Please provide feedback for this session by emailingDevConFeedback@blackboard.com. The title of this session is: • Banner and External Application Integration

More Related