Supplemental Data Engine Presented by:

Georgia Summit 2011

Andy Kearney Kimberly Saving-Sherman SunGard Higher Education G Georgia i Summit S it 2011

1

Introduction - Supplemental Data Engine - Functional • Supplemental Data Engine (‘SDE’) is a simple way to add data fields to Banner forms in Banner 8.0 without customization • Today’s session will cover the terminology used for SDE and p provide a step p by y step p overview for setup p and use of SDE

Georgia Summit 2011

2

Agenda - Supplemental Data Engine - Functional •

Overview • • •

•

Set Up of Supplemental Data Engine • • • •

•

Add tables to GTVSDTB Define Discriminators Define Attributes Generate DDL

Use of Supplemental Data Engine • • • • •

•

What is Supplemental Data Engine? Why Use Supplemental Data Engine? Supplemental Data Engine Terminology

Add Supplemental Data Where did the data actually go? Attributes view View raw data Maintenance of Orphaned Records

Question and Answer Session Georgia Summit 2011

3

Overview of SDE

Georgia Summit 2011

4

Supplemental Data Engine – What is SDE? Supplemental Data Engine is a simple way to add data fields to Banner forms in Banner 8.0 without customization • Allows All d data t tto b be stored t d th thatt is i nott partt off existing i ti Banner B Data D t Model M d l • New data is related to existing tables without altering the underlying database model pp values • New table stores all supplemental • Provides upgrade independent additions and alterations • Users can enter supplemental data from most forms that uses a database table where supplemental data rules have been defined (not views or BANSECR owned tables) Georgia Summit 2011

5

Why Use Supplemental Data Engine? Supplemental Data Engine is the Solution to these Challenges: •

A student has four names names, but the Banner table will only allow three names to be stored.

•

The institution wants to store multiple translations of a course description, but the Banner table only allows one value to be entered for a course description.

•

The institution wants to store comments on an address type but the form does not have a comment field.

•

The institution wants to provide the name and contact information to users who may have questions on rules set up in Banner

•

y others… And many

Georgia Summit 2011

6

SDE Terminology - Supplemental Data Window The user interface that is used to display, insert, update and delete Supplemental Data

Georgia Summit 2011

7

SDE Terminology – Lightbulb Icon Grey - SDE has not been enabled

Bright without rays – SDE enabled but no data entered

Bright with rays – SDE enabled and data is present

Georgia Summit 2011

8

SDE Terminology - Discriminator Codes and Methods • Discriminator Codes are not necessary if only one row is permitted to be entered for a Supplemental Data Item • Discriminator Codes may be used when defining Supplemental Data Attributes (not required) • Di Discriminator i i t Codes C d have h associated i t d Methods M th d (Index, Select or User Defined) • Methods control how the SDE window will behave when users are entering Supplemental Data • Discriminator Codes determine the number of rows allowed to be entered t d for f a Supplemental S l t lD Data t It Item • Discriminator Codes may determine the prompt text displayed to users on the Supplemental Data Window (if %DISC% is used)

Georgia Summit 2011

9

Georgia Summit 2011

10

SDE Terminology - Supplemental Data Attributes GOASDMD • Metadata (Data about Data) • Determine Characteristics of Supplemental Data Values • Define How Supplemental Data Fields appear to user in SDE Window • Define How Supplemental Data Values are stored in the tables • Copy Attributes of Supplemental Data Items from one table to another • Generate DDL for use in Self Service and Reports • Apply DDL to database Georgia Summit 2011

11

Georgia Summit 2011

12

The Tables • The are a few new tables: GOBSDCT GOBSDDC GOBSDTB GORSDAM GORSDAV GORSDDV

Georgia Summit 2011

stores views to be generated stores Discriminators stores the SDE enabled Banner table names stores Metadata (rules for data being extended) stores t th the data d t value l stores user defined Discriminator values

13

GOBSDTB (SDE enabled Banner Tables) GOBSDTB_TABLE_NAME GOBSDTB_OWNER GOBSDTB_ACTIVITY_DATE GOBSDTB_USER_ID GOBSDTB_PK_DYNSQL GOBSDTB_DATA_ORIGIN GOBSDTB_CREATE_SOURCE

Georgia Summit 2011

NOT NULL VARCHAR2(30) NOT NULL VARCHAR2(30) NOT NULL DATE NOT NULL VARCHAR2(30) VARCHAR2(2000) VARCHAR2(30) VARCHAR2(30)

-Primary Key -Banner Table Owner

-Primary Key Dynamic SQL

14

GORSDAM (stores Metadata) GORSDAM_TABLE_NAME GORSDAM_ATTR_NAME GORSDAM_ATTR_TYPE GORSDAM_ATTR_ORDER GORSDAM_ATTR_REQD_IND GORSDAM_ATTR_DATA_TYPE GORSDAM_ATTR_PROMPT GORSDAM ACTIVITY DATE GORSDAM_ACTIVITY_DATE GORSDAM_USER_ID GORSDAM_ATTR_DATA_LEN GORSDAM_ATTR_DATA_SCALE GORSDAM_ATTR_INFO GORSDAM_ATTR_BASE_COL GORSDAM_SDDC_CODE GORSDAM_DATA_ORIGIN GORSDAM_CREATE_SOURCE

Georgia Summit 2011

NOT NOT NOT NOT NOT NOT NOT NOT NOT

NULL NULL NULL NULL NULL NULL NULL NULL NULL

VARCHAR2(30) VARCHAR2(30) VARCHAR2(1) NUMBER(3) VARCHAR2(1) VARCHAR2(30) VARCHAR2(50) DATE VARCHAR2(30) NUMBER(8) NUMBER(2) VARCHAR2(1024) VARCHAR2(30) VARCHAR2(10) VARCHAR2(30) VARCHAR2(30)

-Primary Key -Primary Key -S=Substitute, A=Add Attribute -Display order of Attributes -Y/N -VARCHAR2,DATE,NUMBER -Prompt to show on Form

-Size of the Attribute -Number of decimal places -Default hint or help Text -BASE Column Substituted for -Associated Discriminator

15

GORSDAV (stores the data value) GORSDAV_TABLE_NAME GORSDAV_ATTR_NAME GORSDAV_DISC GORSDAV_PK_PARENTTAB

NOT NOT NOT NOT

GORSDAV_VALUE GORSDAV_ACTIVITY_DATE GORSDAV USER ID GORSDAV_USER_ID GORSDAV_DATA_ORIGIN GORSDAV_CREATE_SOURCE

NOT NULL SYS.ANYDATA NOT NULL DATE NOT NULL VARCHAR2(30) VARCHAR2(30) VARCHAR2(30)

Georgia Summit 2011

NULL NULL NULL NULL

VARCHAR2(30) VARCHAR2(30) VARCHAR2(64) VARCHAR2(512)

-Primary Key -Primary Key -Primary y Key y -Primary Key and PK of the row in Parent Table -Attribute data value

16

GORSDAV (stores the data value) GORSDAV_TABLE_NAME GORSDAV_ATTR_NAME GORSDAV_DISC GORSDAV_PK_PARENTTAB

NOT NOT NOT NOT

GORSDAV_VALUE

NOT NULL SYS.ANYDATA -Attribute data value

GORSDAV_ACTIVITY_DATE GORSDAV USER ID GORSDAV_USER_ID GORSDAV_DATA_ORIGIN GORSDAV_CREATE_SOURCE

NOT NULL DATE NOT NULL VARCHAR2(30) VARCHAR2(30) VARCHAR2(30)

Georgia Summit 2011

NULL NULL NULL NULL

VARCHAR2(30) VARCHAR2(30) VARCHAR2(64) VARCHAR2(512)

-Primary Key -Primary Key -Primary y Key y -Primary Key and PK of the row in Parent Table

17

ANYDATA Type • GORSDAV_VALUE is of type

SYS.ANYDATA

• A column of this type Contains an instance of a given type Contains a description of the type (is self-describing) • Specific to the initial release of SDE, ANYDATA may contain: VARCHAR2 DATE NUMERIC

Georgia Summit 2011

18

Set Up of Supplemental Data Engine

Georgia Summit 2011

19

Step 1 - Identify Table on GTVSDTB • GTVSDTB is the validation form which identifies all Tables which may have Supplemental Data • Supplemental Data may not be added to any table until it is identified on this form • Primary Key is shown for Table entered; if Table does not have a primary key one can be entered

Georgia Summit 2011

20

• Screen print

Georgia Summit 2011

21

Step 1 – Add Table to GTVSDTB

Identify a form where you wish to add supplemental data

Georgia Summit 2011

22

Step 1 – Add Table to GTVSDTB

• Determine what table is behind the field you’d like to add Supplemental Data to by putting your cursor in the field and choosing g Help>Help (Item ( Properties))

Georgia Summit 2011

23

Step 1 – Add Table to GTVSDTB

• •

• •

Open GTVSDTB and query for your table If it does not yet exist on GTVSDTB add it by doing a record insert, typing in (or searching for) the table name and tabbing out of the table name The Description and Primary Key field will default in S Save your changes h and d exit it GTVSDTB

Georgia Summit 2011

24

Step 2 – Define Discriminators (GOADISC) Enter a Discriminator Code (if needed) • Code will be used later when attributes are defined Choose a Discriminator Method • Index • Select • User Defined

Georgia Summit 2011

25

Step 2 – Define Discriminators (GOADISC) Discriminator Methods - Index Setup

• • • •

Code - Unique and Descriptive Description Method - ‘Index’ Validation - maximum number of entries for an Attribute (blank indicates no maximum)

Use

• Use %DISC% in Prompt on GOASDMD to include i incremental t l numbers b in i Supplemental S l t l Data D t Window Wi d (i.e. Comment 1, Comment 2, Comment 3)

Georgia Summit 2011

26

Step 2 – Discriminator Method - Index

Georgia Summit 2011

27

Step 2 – Define Discriminators (GOADISC) Discriminator Method - Select Setup

• • • •

Code - Unique and Descriptive Description Method - ‘Select’ Validation Enter a valid SELECT statement Enter a valid table name and select the Edit button L Leave field fi ld empty t and d select l t Edit button b tt for f wizard i d

Use

%DISC% in i Prompt P t on GOASDMD will ill di display l the th description d i ti values from the Select Statement (i.e. Comment in English, Comment in Spanish, Comment in Dutch)

Georgia Summit 2011

28

Step 2 – Discriminator Method - Select

Georgia Summit 2011

29

Step 2 – Discriminator Method - Select

Georgia Summit 2011

30

Step 2 – Define Discriminators (GOADISC) Discriminator Methods – User Defined Setup • Code C d - Unique U i and d Descriptive D i ti • Description • Method - ‘User Defined’ • Validation Select the Edit button or Next Block to Values Enter desired values Use • %DISC% in Prompt on GOASDMD will display the description user defined values (i (i.e. e Contact Name, Name Contact Email, Email Contact Phone Number)

Georgia Summit 2011

31

Step 2 – Discriminator Methods – User Defined

Georgia Summit 2011

32

Step 3 – Define Supplemental Data Attributes (GOASDMD)

Attributes Att ib t • Define how Supplemental Data is stored in the tables • Define how Supplemental Data Items will appear in the Supplemental Data Window Maintenance • Copy py Attributes to Another Table • Generate DDL and apply to database to create views

Georgia Summit 2011

33

Step 3 – Define Supplemental Data Attributes (GOASDMD) Define how Supplemental Data is stored in the tables • • • • • • •

Sequence (also affects Supplemental Data Window) Identifier Add or Substitute data in a view Data Type (Varchar2, Date or Number) Length (default 4000 varchar2, 22 number) Precision (decimal places) Base Column to Substitute (if Substitute is chosen)

Georgia Summit 2011

34

Step 3 – Define Supplemental Data Attributes (GOASDMD) Define how Supplemental Data Items will appear in the Supplemental Data Window • • • •

Promptt P Data Required Indicator Hint Text Discriminator

Georgia Summit 2011

35

Example of multiple attributes for a table

Georgia Summit 2011

36

Example of user defined discriminators

Georgia Summit 2011

37

Results of multiple attributes for a table and their order

Georgia Summit 2011

38

Step 3 – Define Supplemental Data Attributes (GOASDMD) Maintenance • Copy Attributes to Another Table • Generate DDL and apply pp y to database to create views

Georgia Summit 2011

39

Step 3 – Define Supplemental Data Attributes • • •

Access GOASDMD A GOASDMD, ttype your ttable bl name in i the th table t bl name field in the keyblock Next Block and complete, at least, the ‘Identifier’ and ‘Prompt’ fields Experiment with a variety of combinations of Data Action, Data Type, Length, Precision, Base Column, Prompt, Required Indicator, Hint Text and Discriminator

Georgia Summit 2011

40

Step 4 - Generate DDL • Access GOASDMD and type your table name in the keyblock • Navigate to the ‘Generate DDL’ Tab • Click the ‘Generate’ button, then click the ‘apply’ button

Georgia Summit 2011

41

Use of Supplemental Data Engine

Georgia Summit 2011

42

Add Supplemental Data •

Open the form and navigate to the field where you wish to add Supplemental Data – notice that the SDE light bulb icon is now enabled

Georgia Summit 2011

43

Add Supplemental Data •

Click the lightbulb to open the SDE window window, enter a comment and save – notice the lightbulb now has ‘rays’ indicating that data exists for this field.

Georgia Summit 2011

44

Where did the data actually go? – Generate DDL

Georgia Summit 2011

45

Where did the data actually go? – Apply DDL

Georgia Summit 2011

46

Attributes View • Script generated by option on form GOASDMD Position cursor in ‘Generated DDL’ and press EDIT

Georgia Summit 2011

47

Description of new view

Georgia Summit 2011

48

View data from new view

Georgia Summit 2011

49

View raw data

Georgia Summit 2011

50

View raw data using SYS.ANYDATA methods

Georgia Summit 2011

51

Maintenance of Orphaned Records • If parent records are removed, any Supplemental Data associated with them remains in the database if trigger did not g get properly y applied • Run SDE Inquiry and Delete (GUSMDID) in Audit mode to review records • Run SDE Inquiry and Delete (GUSMDID) in Update mode to remove e o e orphaned o p a ed records eco ds

Georgia Summit 2011

52

SDE Inquiry and Delete (GUSMDID)

Georgia Summit 2011

53

SDE Inquiry and Delete (GUSMDID)

Georgia Summit 2011

54

Summary • Supplemental Data Engine (‘SDE’) is a simple way to add data fields to Banner forms in Banner 8.0 without customization

Georgia Summit 2011

55

Questions & Answers

Georgia Summit 2011

56

Thank You! Andy Kearney A d K [email protected] @ dh Kimberly Saving-Sherman [email protected]

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

Georgia Summit 2011

57