An Introduction to Banner Glossary of Terms
Banner - An Oracle-based software system from SCT. Banner is marketed to colleges and universities and has modules that include:
· Alumni/Development
· Financial Aid
· Finance
· General
· Payroll
· Human Resources
· Student
Included in the Banner system are Oracle database tables where data is stored and Oracle Forms where data is entered. Also included are programs written in C and COBOL that are used to do more complicated processing such as loading and updating data during Payroll and Accounts Payable processing and loading data from outside sources in Financial Aid and Admissions.
Business Logic - The sequence of events that take place behind any business process.
Database - A collection of data stored together as a unit. Databases are useful for storing data and making it available for retrieval. Within the database, data is organized into different tables. Each table has columns and rows.
Datamart - A database that is designed for reporting and querying. The datamart database is separate from the transaction database (Banner database). Data is derived from the data in the transaction database and can also include data from other sources. It may also contain historical data. A datamart is more narrow in focus than a data warehouse. The purpose of the datamart is to enhance reporting for a particular segment of users or for a particular department rather than for an entire enterprise or college. Unlike the transaction database, fewer tables need to be joined in a datamart, more indexes are available to speed data retrieval, data is often stored in summarized form and one data item will exist in more than one table.
Departmental Steward - A person designated by a department who cares about the data that pertains to that department and ensures its security and integrity. The departmental steward also makes decisions about what other offices/departments can have access to the departmental data. For example, the departmental steward in the Registrar's Office may allow a user in the Post Office or a user in the Housing Office to view some information about current students.
Drilling - When viewing a report or the results of a query, drilling is the act of moving from one data item to a related data item. Usually the data items are arranged in a hierarchy. Drilling down involves moving from a top level, summary view of the data to a more detailed data item, usually moving down the hierarchy. Drilling up involves collapsing the hierarchy, moving up from the detailed data item to the top level, summarized view of the data.
Explain Plan - A SQL statement that generates a report that shows how Oracle plans to execute an SQL query and retrieve the data being requested. The Explain Plan is used by Oracle developers to diagnose a poorly performing SQL query. The explain plan shows whether Oracle is using an index to find the data or having to do a full table scan, what kind of index it is using, and a performance cost estimation for performing the query. The cost estimate can be compared to an explain plan for a differently written query which produces the same results.
Functions - A program written in PL/SQL that perform a particular task. A function is nearly identical to a procedure except a function always returns a value. For example, if you have a function called GRADUATING_CLASS and you use that function and specify an alumnae's id number, the function would return the graduating class (1930, 1992, etc.) of the alumna.
IAS - Oracle Internet Application Server, an integrated software package that provides a key piece of the infrastructure necessary to bring Oracle databases to the Web; IAS includes:
o Portal Services: software designed to collect, manage and serve-up personalized software services in a Web browser.
o Data Caching: much of the work of accessing data is offloaded from the database to IAS, enabling Web sites to respond faster
o Web/HTTP Services: based on the standard Apache web server, provides greater reliability and scalability for all Web applications
o Business Intelligence Services: access to Oracle Discoverer and Oracle Reports
o Component Services: allows software developers to easily build applications from pre-built, Java-based components
o Forms Services: serves Oracle Forms while minimizing network traffic
Index - A database object that is associated with a table and helps speed the access to data in the table. For example, to retrieve all the information from the SPRIDEN identification table for just one ID, ORACLE would have to read every row in the SPRIDEN table, starting at the beginning, until it finds the ID you are looking for. This is because data in tables are not stored in any particular order. If an index were added to the SPRIDEN table using the ID column, the index would store the IDs in numeric order. ORACLE could use this index, find the matching ID quickly and return the correct row to the user. Indexes are also used with table joins.
Instance - A technical term used to refer to a database in a way that includes all of its technical components (the disk files that hold the tables, the part of the computer memory allocated for the database and the Oracle software that is necessary to manage the database). It is advisable to have two instances of Banner database: a Production instance and a Pre-Production instance. Each instance has different data and different programs. A few times a year, all the data is copied from the Production instance to the Pre-Production instance. This is done so that those using the Pre-Production instance can have fresh data for testing. As changes are made to the Banner or Oracle software, the changes are installed first on the Pre-Production instance and then on the Production instance.
Materialized View - Similar to a view but the data which is returned from the query is actually stored in a separate table. Materialized views are most often seen in datamarts to store summary tables and pre-joined tables. They must be refreshed when the data in the underlying tables is changed. When the materialized view is created, you can specify the query used to build the view and when it is to be refreshed: on demand or automatically when changes are saved to the underlying tables. Unlike views, materialized views actually take storage space. Materialized views can also have indexes to improve the speed of any query against the materialized view.
In most datamarts, there is a materialized view for each table in the Banner database. This materialized view is basically a copy of the Banner table. These materialized views are set to be automatically updated or refreshed once every 24 hours. They are refreshed incrementally, meaning only changes or additions made since the previous refresh are brought into the view.
Metadata - Data that describes the data in a database. It includes the names of tables, columns and indexes, how the data items are used, business definitions, relationships between data, how the data has been processed as it enters the datamart and information about how current the data is.
Oracle - A software vendor that supplies the software that manages the database under the SCT Banner software.
Packages - Groups of related PL/SQL procedures and functions that are stored together as a single unit in the database.
Procedures - A program written in PL/SQL that perform a particular task. A procedure can be executed from SQL*Plus, or Oracle Forms or a procedure or trigger.
PL/SQL - A complete programming language based on SQL; PL/SQL is required to write more complicated programs that can be written in SQL; PL/SQL offers a programmer the ability to control the flow of a Program and include variables and error-handling procedures. PL/SQL is required to create procedures, database triggers, packages and functions. Also, the programming done in many other Oracle tools, such as Oracle Forms and Oracle Reports is based on PL/SQL. If you need to work on a group of records but you process each record individually, you cannot use SQL, but must use PL/SQL.
Query - A request for information from tables in a database. When retrieving information from an Oracle database, you can use the SQL SELECT command to specify which fields and which rows you want to retrieve from which table(s).
Relational Database - ORACLE is a relational database. A relational database has several characteristics that makes it different from the kinds of databases and other file storage systems that came before it. These same characteristics increase the complexity of querying or reporting from the database
o Each data item is stored in only one location; someone's mailing address is stored in one table; every data entry form and report looks at this one table; all changes are made in this one table;
o The database is designed once, with an emphasis on the relationships between data. For example, the Banner database was designed to allow more than one type of address, so additional address types can be added at any time without altering the design.
o To query related data, data must often be pulled from multiple tables; tables are joined together via a common field(s)
Repeating Table - A table that has more than one row where the key column(s) is repeated. Many of the tables in the Banner database are repeating tables because they store historical information. For example, when changes are made to a person's address, the old row is kept and a new row/record is added in the ADDRESS table. Repeating tables represent a particular challenge to report developers because often times special care must be taken to select just the most recent record.
Reporting - A broad term used to describe pulling information from a database (querying) and formatting the output so that it is easy to read when printed, displayed on the web or downloaded into a software package such as Excel.
Report Developer - Someone who can write and debug fourth and fifth generation reporting tools. This person understands the structures of a database and can design smaller databases. Report developers are skilled with reporting wizards but also understand the SQL or SQL-like code underlying report wizards and are able to make modifications not possible through the wizards. Report developers can join multiple tables or views to create new tables or views and they are able to capture complex business logic in packages or functions.
Report Modifier - Someone who can make modifications to existing reports. These modifications might include: changing sort order, removing or adding columns (fields), changing the selection criteria, joining an additional table(s). Report modifiers are typically less concerned with the structure of the underlying database or the coding language that incorporates complex business logic as they are with changing the basic look and formatting of a report.
Report Viewer - Someone who can execute or view pre-developed reports. A report viewer understands the needs and information in each report and can correctly answer prompts that are required by the reports.
SCT - Systems and Computer Technology Corp. SCT is the vendor of the Banner database and all of its related software.
SQL - Structured Query Language. A standard programming language used to interact with a relational database. SQL allows you to retrieve data from a table, insert, update and delete data. SQL*Plus is Oracle's version of the SQL language and includes extra functionality not part of standard SQL. SQL*Plus is used as a very simple reporting tool.
Example:
1. A simple example of a SQL statement that retrieves information from the SPRIDEN table:
SELECT
id, last_name, first_name
FROM
spriden
A01531433 / Smith / Kate
A01531421 / Myers / Ann
A01531411 / Jones / Ruth
2. A simple example that retrieves information from the SPRIDEN table for just 1 person:
SELECT
id, last_name, first_name
FROM
spriden
WHERE
id = 'A01521421'
A01531421 / Myers / Ann
Table - The object in a database that contains data. Data stored in a table is organized into rows (one row for each record) and columns (one column for each data element or field).
Example:
One of the most popular tables in the Banner database is the SPRIDEN (Person Identification) table. It contains the following columns:
- ID number (ID)
- Last name (LAST_NAME)
- First name (FIRST_NAME)
- Middle Initial (M_I)
- Banner internal ID number (PIDM)
A01531433 / Smith / Kate / I / 30000002
A01531421 / Myers / Ann / L / 30000006
A01531411 / Jones / Ruth / A / 30000003
A01531422 / Adams / Sarah / E / 30000007
Table Join - A formal specification of which column(s) in a row in one table should be matched to a column(s) in a row in another table.
Example:
When you are requesting information about a particular student or group of records, the information is often stored in more than one table. These tables must be joined correctly to give the correct information. To retrieve identification information for a particular student, you need to join columns from the SPRIDEN and SGRSATT tables. Assuming that each student has one record in each of these tables, you just want to retrieve those two records. You need to make sure that you don't get a record from SPRIDEN for one student and then get a record from SGRSATT from another student. When you enter the query command, you need to specify the fields you want from each table and then specify the join statement: "where the ID field in the SPRIDEN table is equal to the ID field in the SGRSATT table". Another example might be looking for a student's records from a particular year, so we would include in the join the ID field and also the year field.