a community of learning course id 602 sungard summit 2007 | sungardsummit.com 1 luminis database...

SUNGARD SUMMIT 2007 | sungardsummit.com 1

A Community of LearningCourse ID 602

Luminis Database Schema Unplugged

Presented by: Douglas Riga Xavier University

March 21, 2007Course ID 602

2Course ID 602

Introduction

• Luminis and uPortal uses a normalized data schema to store information for:• Channels• Targeted Announcements• Group/Course Studio Information

• This data can be difficult to utilize and examine

• During this session we’ll look at how the data is stored, it’s relation to other data sources, and some ways we can utilize this data

3Course ID 602

What We’re Going To Cover…

• How is the Database Used?• A Look at Tables by Function• Understanding the Data Organization• The Luminis LDAP Directory• Groups/Courses• Channels• Reports

4Course ID 602

Assumptions

• Some familiarity with SQL and database structures

• Oracle as the Database Backend• Discussions should generally apply to SQL Server-based

systems

• Luminis Version III.3.1• Data structures have remained fairly consistent through

last several upgrades



How is the Database Used?

6Course ID 602

How is the Database Used?

• The database is used for a lot of the targeted information found within the portal• Groups / Courses• Channel Information• Layouts• Targeted Announcements• Imported Groups

7Course ID 602

The Database vs. LDAP

• The database duplicates some LDAP data• Course Membership• User Information

• The database references some LDAP data• Targeted Content Channels

8Course ID 602

Schema Overview

6 Primary Table Groups in uPortal Schema• GT – group/course tools• IG – imported groups• MB – message boards• TA – targeted announcements• UPC – uPortal channel data• UP – uPortal (layouts, channels, groups, stylesheets)



A Look at the Tables by Function

10Course ID 602

GT Tables (Group/Course Tools)

• Manages all aspects of the Group & Course Tools functionality

• GT_GROUP – General group/course information

• GT_USER – Group tools user information

• GT_MEMBER – User-to-group relationships & group permissions

NOTE: Will cover these tables in more depth

later in session

11Course ID 602

IG Tables (Imported Groups)

• Stores Imported Group information

• IG_GROUP – basic imported group info (name, expiration, etc.)

• IG_MEMBER – Group to Username cross-reference

• IG_X_TA – Cross-reference for imported groups used in targeted announcements

12Course ID 602

MB Tables (Message Boards)• Message board data used by group /

course tools

• MB_FORUM – Relates each message board to a specific group or course

• MB_FORUMTOUSER – User membership in each message board

• MB_MESSAGE… – Message info (body stored separately)

• MB_TOPIC – Topic info to allow relating messages to threads

• MB_USER – Group User # to Display Name cross-reference

13Course ID 602

TA Tables (Targeted Announcements)• Manages targeted announcement

data

• TA_ATTRIBUTE – delivery attributes

• TA_MANAGE – list of TAIDs that can be managed in GUI

• TA_MESSAGE – message, subject, delivery info

• TA_X_EMADDR – email addresses for targeted email announcements

• TA_X_USER – Username to TAID cross-reference

14Course ID 602

UP Tables (uPortal)

• Used for a variety of uPortal data

• UP_CHANNEL…• Channel information, parameters, &

subscribability (will cover later)

• UP_ENTITY…• Java object information

• UP_GROUP…• Channel categories

• UP_LAYOUT… / UP_SS… / UP_USER…• Used to store general & user specific

layout information

15Course ID 602

UPC Tables (uPortal Channels)

• Used to store data used by various uPortal channels

• Luminis uses the bookmarks channels

• UPC_BOOKMARKS• The “original” Bookmarks channel

• UPC_BOOKMARKS_NG• The Bookmarks Plus channel

• Bookmark data is stored as XML in Long fields

16Course ID 602

Misc Tables

• Used to store last used sequence numbers

• DB_SEQUENCE• Message board sequence

numbers (users, msgs, etc.)

• ID• Targeted Announcements• Imported Groups



Understanding the Data Organization

18Course ID 602

Understanding the Data Organization

• The uPortal schema is a highly normalized architecture that necessitates the use of numerous tables for most queries

Course ID Course Name Department Name UserID Role50504.200605 Counseling Chdrn: Play Therapy Education John Doe doej Student93261.200609 Psychological & Achieve Testin Education John Doe doej Student93284.200609 Drug Counseling Education John Doe doej Student13977.200701 General Chemistry II Lab Chemistry John Doe doej Instructor

UserCourse

List

GT_USERUSER_NAME

DISPLAY_NAME

GT_CATEGORYCATEGORY_NAME

GT_GROUPBOM_COURSE_ID

GROUP_NAME

GT_MEMBERMEMBER_TYPE

Category ID

Group ID

User ID

LimitQuery By

Group Type

LimitQuery By

Group Status

LimitQuery By

Member Type



The Luminis LDAP Directory

20Course ID 602

The Luminis LDAP Directory

• Plays a role in:• Course Memberships• Targeted Channel Definitions

• Course Memberships• Stores membership and basic info in both the database

and LDAP• Mismatches between sources can result in an inability to

access course• Some data is specific to each data source

• Targeted Channels• Database stores basic identifying data and a pointer to the

LDAP definitions

21Course ID 602

Course Memberships

• Database• GT_USER – entry for each user in Group/Course tools

system• GT_MEMBER – lists group memberships for each user• GT_GROUP – course information (name, dept, etc)• Doesn’t store section ID (e.g. ENGL-101-01)• Listing here allows access to course tools

• LDAP• Stores list of members (doesn’t include TAs or Guests)• Stores course info including section ID• cptool references this list not the database• Listing here causes course to appear in schedule channel

(ex. TAs and Guests)

22Course ID 602

Targeted Channels

DATABASEChannel Param

LDAPChannel Object

LDAP Section Object /

Container

LDAP Sub-Section Object

LDAP Section Object /

Container



• The database point to the LDAP via the chanLoc parameter

• The channel LDAP object contains a list of sections contained within the channel

• The LDAP section object / container contains access information & parameters

• Subsections are “contained” within their parent section objects



Groups / Courses

24Course ID 602

Groups / Courses• GT_APPLICATION – lists grouptool

apps and flags for required, delegatable, available

• GT_CATEGORY – lists cat & dept titles and identifies app (course or group)

• GT_CHAT… – tables used for chat app

• GT_FOLDER/GT_FILE/GT_PHOTOlists file/photo info seen in grouptools and pointer to filesystem location

• GT_GROUP – main group table. Contains basic identifying info

25Course ID 602

Groups / Courses (cont’d)• GT_GROUP_APPLICATION – lists

availability, etc. for each app within each group

• GT_INFO – lists pre-join info for those groups that have it

• GT_LINK – lists all links referenced to each group/course

• GT_MEMBER – cross-reference list of members to groups with permissions within group

• GT_NEWS – lists all news posts referenced to each group/course

26Course ID 602

Groups / Courses (cont’d)• GT_PROPERTY – contains group

and member policy statements

• GT_REQUEST… – contains information entered for a group request

• GT_SEQUENCE – contains next available sequence number for categories, groups, info, news, photos, links, folders, and files

• GT_USER – contains user-specific information (username, email, display name, etc.)

27Course ID 602

Group Table Relationships

GT_GROUPGROUP_IDGROUP_NAMEGROUP_TITLEGROUP_TYPEGT_STATUSCATEGORY_IDACCESS_ROLESDISK_QUOTABOM_COURSE_IDADMIN_BLOCKEDDATE_CREATEDPARENT_GROUP_IDFOLDER_IDUSER_IDFEATURED_PHOTOGUEST_PHOTOFEATURED_LINKACCESS_CRITERIAREQUEST_COMMENTGROUP_DESC_HAS_HTMLGROUP_DESCGROUP_DESC_FORMATTED

GT_MEMBERUSER_IDGROUP_IDDATE_CREATEDLAST_VISITGT_STATUSFAVORITE_GROUPMEMBER_TYPEJOIN_COMMENTHOME_PAGE_URLHOME_PAGE_TITLEADMIN...

GT_CATEGORYCATEGORY_IDPARENT_IDCATEGORY_NAME

GT_FILEFILE_IDGROUP_IDFOLDER_IDUSER_IDFILE_NAMEDATE_CREATEDDATE_MODIFIEDGT_STATUS

GT_PHOTOPHOTO_IDGROUP_IDUSER_IDPHOTO_TITLEPHOTO_CAPTIONPHOTO_CAPTION_FORMATTEDPHOTO_CAPTION_HAS_HTMLDATE_CREATEDGT_STATUS

GT_FOLDERFOLDER_IDGROUP_IDPARENT_IDFOLDER_NAME

MB_FORUMIDCRNTERMROOTTOPICIDTOPICCOUNT

MB_FORUM...FORUMID...

GT_USERUSER_IDUSER_NAMEDATE_CREATEDUSER_ROLEGT_STATUSDISPLAY_NAMEEMAIL_ADDRESS

Group ID

Group ID

Folder ID

Group ID

Forum ID

Group ID - CRN

Folder ID

Category ID

LeaderUser ID

Group ID

User ID

LDAPcn

objectClasspdsCourse

pdsCoursAndSectionIDpdsCourseTimepdsCourseTitlepdsDepartment

pdsIDpdsInstructorpdsSection

pdsStudentspdsTerm

BOM Course ID - CN

28Course ID 602

Decoding Some Fields

Member TypesCourses

I – InstructorS – StudentT – Teaching Asst *G – Guest *

GroupsL – LeaderD – Delegated LeaderM – Member

* Not stored in Course LDAP object

Group TypesC – CourseR – RestrictedH – HiddenP – PublicA – Admin RestrictedG – Consolidated Courses

Group StatusA – ActiveD – DisabledR – In Queue for Approval



Channels

30Course ID 602

Channels• UP_CHANNEL – Lists basic

channel info (incl. name, type, std params)

• UP_CHANNEL_PARAMStores channel specific parameters for those channels requiring them

• UP_CHANNEL_SUBSCRIBABLECan the channel be subscribed to?

• UP_CHAN_TYPE – lists each type of channel that can be setup in Luminis

31Course ID 602

Channel Table & LDAP RelationshipsUP_CHANNELCHAN_IDCHAN_TITLECHAN_NAMECHAN_DESCCHAN_CLASSCHAN_TYPE_IDCHAN_PUBL_IDCHAN_PUBL_DTCHAN_APVL_IDCHAN_APVL_DTCHAN_TIMEOUTCHAN_EDITABLECHAN_HAS_HELPCHAN_HAS_ABOUTCHAN_FNAMECHAN_SECURE

UP_CHANNEL_PARAMCHAN_IDCHAN_PARM_NMCHAN_PARM_DESCCHAN_PARM_VALCHAN_PARM_OVRD

UP_CHANNEL_SUBSCRIBABLECHAN_IDCHAN_SUBSCRIBABLE

Chan ID

Chan ID

LDAPdn

pdsHtmlLocationNameobjectClass

cnpdsHtmlChannelList

createTimestampmodifyTimestamp

creatorsNamemodifiersName

subschemaSubentry

cn=upc_##,ou=Location,ou=Content,o=school.edu,o=cp

LDAPdn

pdsHtmlIDobjectClass

pdsHtmlShownpdsHtmlTitle

pdsHtmlPropertypdsHtmlTarget



subschemaSubentry

pdsHtmlID=########,ou=Channel,ou=Content,o=school.edu,o=cp

LDAPdn

pdsHtmlIDpdsHtmlEncodedDatapdsJavaClassNamepdsHtmlImageActive

pdsHtmlTitlepdsHtmlOrder

pdsHtmlAdminClassobjectClass



subschemaSubentry

pdsHtmlID=########,pdsHtmlID=########,ou=Channel,ou=Content,o=school.edu,o=cp

Chan Parm Val – CNonly applies to

targeted content channels

pdsHtmlChannelListTo

pdsHtmlID

LDAP Container

32Course ID 602

Notes About Channel Data

• CHAN_PARM_VAL – for targeted content channels the database contains a upc_## value that references a corresponding cn=upc_## value in the LDAP

• pdsHtmlChannelList – contains a semicolon separated list of pdsHtmlID numbers that reference the section content entries

• pdsHtmlEncodedData• Filesystem file stored at $CP_ROOT + file reference

Most is stored in a subdirectory called ‘content’( /opt/luminis/content/… on our system )

• URL



Reports

34Course ID 602

Group Overview Report• Lists all groups by category with leader names, leader email

addresses, creation date, type, number of members, status, last visit date, and group description

• Includes overview with number of groups per category and number of groups in each status

35Course ID 602

Group Overview Report (cont’d)select UGG.GROUP_NAME,

UGGNAM.GRP_PT_NAME, UGC.CATEGORY_NAME,

UGGML.DISPLAY_NAME, UGGML.EMAIL_ADDRESS, to_char(UGG.DATE_CREATED,'mm/dd/yyyy') "DATE_CREATE", case UGG.GROUP_TYPE when 'H' then 'Hidden'when 'R' then 'Restricted'when 'P' then 'Public'when 'A' then 'Admin Restricted'else 'Course'end "GRP_TYPE", case UGG.GT_STATUS when 'A' then ' 'when 'D' then 'Disabled'when 'R' then 'Queued'end "GRP_STAT", UGCNT.COUNT_GRP_USERS, to_char(UGLVIS.GRP_LAST_VISIT,'mm/dd/yyyy') "LAST_VISIT", replace(replace(dbms_lob.substr(UGG.GROUP_DESC,500), chr(10), ' '), chr(13), ' ') "GRP_DESC", replace(replace(dbms_lob.substr(UGG.REQUEST_COMMENT,500), chr(10), ' '), chr(13), ' ') "REQ_COM"

from UPORTAL.GT_GROUP UGG inner join UPORTAL.GT_CATEGORY UGC on UGC.CATEGORY_ID=UGG.CATEGORY_ID inner join (select UGG.GROUP_ID, count(UGM.USER_ID) "COUNT_GRP_USERS" from UPORTAL.GT_GROUP UGG inner join UPORTAL.GT_MEMBER UGM on UGM.GROUP_ID=UGG.GROUP_ID where ((UGG.GROUP_TYPE = 'H') or (UGG.GROUP_TYPE = 'R') or (UGG.GROUP_TYPE = 'P') or (UGG.GROUP_TYPE = 'A')) group by UGG.GROUP_ID order by UGG.GROUP_ID) UGCNT on UGCNT.GROUP_ID=UGG.GROUP_ID inner join (select UGG.GROUP_ID, max(UGM.LAST_VISIT) "GRP_LAST_VISIT" from UPORTAL.GT_GROUP UGG inner join UPORTAL.GT_MEMBER UGM

36Course ID 602

Group Overview Report (cont’d) on UGM.GROUP_ID=UGG.GROUP_ID where ((UGG.GROUP_TYPE = 'H') or (UGG.GROUP_TYPE = 'R') or (UGG.GROUP_TYPE = 'P') or (UGG.GROUP_TYPE = 'A')) group by UGG.GROUP_ID order by UGG.GROUP_ID) UGLVIS on UGLVIS.GROUP_ID=UGG.GROUP_IDleft outer join (select UGG.GROUP_ID, UGG.GROUP_NAME "GRP_PT_NAME" from UPORTAL.GT_GROUP UGG where ((UGG.GROUP_TYPE = 'H') or (UGG.GROUP_TYPE = 'R') or (UGG.GROUP_TYPE = 'P') or (UGG.GROUP_TYPE = 'A')) order by UGG.GROUP_ID) UGGNAMon UGG.PARENT_GROUP_ID=UGGNAM.GROUP_IDinner join (select UGG.GROUP_ID,

UGML.DISPLAY_NAME, UGML.EMAIL_ADDRESSfrom UPORTAL.GT_GROUP UGGinner join (select UGM.GROUP_ID, UGU.DISPLAY_NAME, UGU.EMAIL_ADDRESSfrom UPORTAL.GT_MEMBER UGMinner join UPORTAL.GT_USER UGU on UGM.USER_ID=UGU.USER_IDwhere UGM.MEMBER_TYPE = 'L') UGMLon UGML.GROUP_ID=UGG.GROUP_ID where ((UGG.GROUP_TYPE = 'H') or (UGG.GROUP_TYPE = 'R') or (UGG.GROUP_TYPE = 'P') or (UGG.GROUP_TYPE = 'A'))) UGGMLon UGGML.GROUP_ID=UGG.GROUP_ID

where ((UGG.GROUP_TYPE = 'H') or (UGG.GROUP_TYPE = 'R') or (UGG.GROUP_TYPE = 'P') or (UGG.GROUP_TYPE = 'A'))order by UGC.CATEGORY_NAME,

UGG.GROUP_NAME;

37Course ID 602

Course Overview Report• Similar to Group Overview Report• Requires importing of some data from other sources• Displays section, distance learning system, name, department,

creation date, last visited by instructor, number of files & links, number of students & instructors, and instructor name & email

38Course ID 602

Course Overview Report (cont’d)select UGG.BOM_COURSE_ID, substr(UGG.BOM_COURSE_ID,7,4) "GRP_YEAR", substr(UGG.BOM_COURSE_ID,11,2) "GRP_TERM", RDC.DC_SECTION "DC_SECTION",

decode(RDC.DC_DL_FLAG, 'Blackboard', 'BB') "DL_FLAG", UGG.GROUP_NAME, UGC.CATEGORY_NAME, to_char(UGG.DATE_CREATED,'mm/dd/yyyy') "DATE_CREATE", to_char(UGLVIS.GRP_LAST_VISIT,'mm/dd/yyyy') "LAST_VISIT", NVL(UGCNTL.COUNT_LINKS,0) "NUM_LINKS", NVL(UGCNTF.COUNT_FILES,0) "NUM_FILES", NVL(UGCNTS.COUNT_STU_USERS,0) "NUM_STU", UGCNTI.COUNT_INST_USERS "NUM_INST",

UGGMI.DISPLAY_NAME, UGGMI.EMAIL_ADDRESSfrom (UPORTAL.GT_GROUP UGG inner join UPORTAL.GT_CATEGORY UGC

on UGC.CATEGORY_ID=UGG.CATEGORY_ID) left outer join (select UGG.GROUP_ID,

count(UGM.USER_ID) "COUNT_STU_USERS" from UPORTAL.GT_GROUP UGG

inner join UPORTAL.GT_MEMBER UGM on UGM.GROUP_ID=UGG.GROUP_ID where UGG.GROUP_TYPE = 'C' and UGM.MEMBER_TYPE = 'S' group by UGG.GROUP_ID order by UGG.GROUP_ID) UGCNTS

on UGCNTS.GROUP_ID=UGG.GROUP_ID inner join (select UGG.GROUP_ID, count(UGM.USER_ID) "COUNT_INST_USERS" from UPORTAL.GT_GROUP UGG

inner join UPORTAL.GT_MEMBER UGM on UGM.GROUP_ID=UGG.GROUP_ID where UGG.GROUP_TYPE = 'C' and UGM.MEMBER_TYPE = 'I' group by UGG.GROUP_ID order by UGG.GROUP_ID) UGCNTI

on UGCNTI.GROUP_ID=UGG.GROUP_ID inner join (select UGG.GROUP_ID, max(UGM.LAST_VISIT) "GRP_LAST_VISIT"

from UPORTAL.GT_GROUP UGG inner join UPORTAL.GT_MEMBER UGM on UGM.GROUP_ID=UGG.GROUP_ID where UGG.GROUP_TYPE = 'C'

39Course ID 602

Course Overview Report (cont’d) and UGM.MEMBER_TYPE = 'I' group by UGG.GROUP_ID order by UGG.GROUP_ID) UGLVIS on UGLVIS.GROUP_ID=UGG.GROUP_IDinner join (select UGG.GROUP_ID, UGMI.DISPLAY_NAME, UGMI.EMAIL_ADDRESSfrom UPORTAL.GT_GROUP UGGinner join (select UGM.GROUP_ID, UGU.DISPLAY_NAME, UGU.EMAIL_ADDRESSfrom UPORTAL.GT_MEMBER UGMinner join UPORTAL.GT_USER UGU on UGM.USER_ID=UGU.USER_IDwhere UGM.MEMBER_TYPE = 'I') UGMI on UGMI.GROUP_ID=UGG.GROUP_ID where UGG.GROUP_TYPE = 'C') UGGMIon UGGMI.GROUP_ID=UGG.GROUP_ID left outer join (select UGG.GROUP_ID, count(UGL.LINK_ID) "COUNT_LINKS" from UPORTAL.GT_GROUP UGGinner join UPORTAL.GT_LINK UGL on UGL.GROUP_ID=UGG.GROUP_IDwhere UGG.GROUP_TYPE = 'C'group by UGG.GROUP_ID order by UGG.GROUP_ID) UGCNTL on UGCNTL.GROUP_ID=UGG.GROUP_ID left outer join (select UGG.GROUP_ID, count(UGF.FILE_ID) "COUNT_FILES" from UPORTAL.GT_GROUP UGG

inner join UPORTAL.GT_FILE UGF on UGF.GROUP_ID=UGG.GROUP_ID where UGG.GROUP_TYPE = 'C' group by UGG.GROUP_ID order by UGG.GROUP_ID) UGCNTF on UGCNTF.GROUP_ID=UGG.GROUP_ID

left outer join RIGAD.DUMP_COURSES RDCon UGG.BOM_COURSE_ID = RDC.DC_COURSE_ID

where UGG.GROUP_TYPE = 'C‘ and UGG.GT_STATUS = 'A‘ and substr(UGG.BOM_COURSE_ID,7,6) IN ('200601')order by GRP_YEAR, GRP_TERM DESC, DC_SECTION;

40Course ID 602

Simple Channel Usage Report

• Shows number of subscribers for each channel• Can show who specifically is subscribed to each channel

41Course ID 602

Simple Channel Usage Report (cont’d)/* Number of Unique Subscribers to Each Channel */SELECT uc.chan_id "Channel ID",

chan_name "Channel Name", COUNT(distinct user_name) "Subscribers"FROM uportal.up_layout_struct ULS, uportal.up_channel UC,

uportal.up_user UUWHERE uc.chan_id = uls.chan_id AND uls.user_id = uu.user_id AND uu.user_name NOT LIKE '%-lo' AND uc.chan_id NOT IN (1, 10, 19, 92, 99, 203)GROUP BY uc.chan_id,

uc.chan_nameORDER BY COUNT(distinct user_name) DESC;



Summary

43Course ID 602

Summary

• The uPortal Database schema is comprised a large number of normalized tables that require a lot of cross-referencing to gather usable information

• Data used for courses and targeted channels spans both the database and LDAP

• Understanding how the tables interrelate is key to finding data and gaining understanding of what can be found in the database

44Course ID 602

Questions & Answers

45Course ID 602

Thank You!

Douglas [email protected]

Please complete the online class evaluation formCourse ID 602

Schema screen shots were taken from TOAD for Oracle from Quest Software (http://www/quest.com/).

SunGard, the SunGard logo, Banner, Campus Pipeline, Luminis, PowerCAMPUS, Matrix, and Plus are trademarks or registered trademarks of SunGard Data Systems Inc. or its subsidiaries in the U.S. and other countries. Third-party names and marks referenced herein are trademarks or registered trademarks of their respective owners.

© 2006 SunGard. All rights reserved.

a community of learning course id 602 sungard summit 2007 | sungardsummit.com 1 luminis database...

Documents