Reporting Environments Against Which
Reporting Tools Can Be Run

Banner Tables

Description:
The Banner software from Systems Computer Technology (SCT) consists of five integrated systems: student administration, financial aid, financial operations, human resources, and alumni/development. Banner software is based on an ORACLE relational database. In a relational database, related data items are grouped together into individual tables. Wellesley’s Banner database consists of approximately 3,000 Oracle tables. The number of tables in each Banner system is shown below.

To develop a report that uses Banner tables, the developer must be knowledgeable about the Banner tables.

Example:

To produce a simple mailing label using Banner Tables, you would need to include data from the following tables:

In addition, to generate mailing labels for a particular group of records from the database (all Resources constituents, all current students, active employees, etc.) you would need a few more tables related to the population you are seeking. To find all Resources constituents, you would need APBCONS; to find current students, you would need SGRSATT; and to find current employees you would need PEBEMPL and NBRJOBS.

Once you have the necessary tables, additional logic is needed to join just the correct records from each table. In the SPRADDR address table, many address types are stored: mailing address, campus address, vacation address, paycheck address, etc. For each address type, changes can be made over time. Each of these changes is stored in the database. So to create a mailing label for a student’s campus address, out of all the campus address records that are stored in the database, just the latest record needs to be selected.

Advantages:
When using Banner Tables, data changes are available immediately. When a report is created it does not require the underlying structure of a datamart. So if a report is needed based on data that is not already in a datamart, a report writer may be able to produce results more quickly. Report code taken from another school or from SCT could be run against Banner Tables unless we had set up Banner differently or customized Banner in some other way.

Disadvantages:
When using Banner Tables in developing reports, one or more table joins are usually required. Not only is this complex and time consuming for the report writer but it requires additional system resources each time a report is executed. In a datamart, the table joins are done once nightly. These datamart tables can be selected from repeatedly all day with no further system resources required to join the tables. When working with Banner Tables, each time a report is run, tables are joined and system resources are required. Indexes can be added to Oracle tables to speed up the time required to join tables. On Banner Tables however, any indexes that are on the tables are designed to increase the performance of the data entry process, so they are often not helpful in increasing the performance of reports.

In addition to possible performance penalties, table joins can produce incorrect results if not done properly.

Also, many of the Banner Tables include multiple records for each employee or student so extra logic is often required to select just the current record. This also requires more system resources and can produce incorrect results if an error is made.

Often during a software upgrade, the structure of a Banner table can change. The developer of the report is responsible for incorporating structural changes in each affected report.

Support:
Reports developed against Banner Tables are supported by the department that developed the report. Backup support is available from Database Systems.

Views

Description:
Views can be thought of as dynamically created tables that can contain columns from one or more tables. A view of the SPRADDR address table could be created to contain just the person’s ID number, their city, and their state. As a result, the street address, zip code and nation fields would not appear in the view. The view could also exclude all the old history so that just one row for each person would appear. A more typical, useful view would include information from both the SPRADDR address table and the SPRIDEN identification table. For each person, the view could include just their most recent first name, last name, street address, city, state and zip. An even more useful view would contain the above information from the SPRIDEN identification table, the SPRADDR address table and the student SGRSATT table. This view could contain address and identification information for just current students.

A small number of Oracle views are delivered with the Banner product. Many of these views are part of SCT’s Object Access project. These Object Access views are meant to make it easier for report writers. It has been our experience that it takes so long to retrieve data from these views that they are not usable. These views can be modified to run faster and to better suit the needs of Wellesley. We have limited experience with this.

Advantages:
Both table joins and complex logic can be included in the creation of the view, making the job of the report writer who is using the view easier. When using views, data changes are available immediately. A view can often be created more quickly and easily than a datamart. Any time there are table changes, just the view could be changed and many of the dependent reports will not require changes.

Disadvantages:
When working with Banner views, each time a report is run, tables are joined and system resources are required. In a datamart, the table joins are done once nightly. These datamart tables can be selected from repeatedly all day with no further system resources required to join the tables. Indexes can be added to Oracle tables to speed up the time required to join tables. Any indexes that are on the tables underlying the Banner views are there to increase the performance of the data entry process, so they are often not helpful in increasing the performance of reports.

Work must be done to create the views before they are available for use. Any time the structure of the Banner Tables change, the changes must be incorporated into the views.

Support:
Views can have many reports that depend on them. These must be updated as any underlying table changes occur. Views are normally supported by Database Systems.

Materialized Views

Description:
Materialized views are similar to views, however with materialized views a separate table is actually created to store the results of the query. (Please read the above description of Views). Materialized views have just started to be used in the datamart. A major advantage of materialized views is that indexes can be added, improving the response time. With traditional views, the query must be run each time the view is accessed. With materialized views, the data can be refreshed when needed only, saving system resources.

Advantages:
Please see advantage of traditional views above. Materialized views can have indexes added to improve performance.

Disadvantages:
Work must be done to create the views before they are available for use. Any time the structure of the Banner Tables change, the changes must be incorporated into the view.

Support:
Views can have many reports that depend on them. These views must be updated as any underlying table changes occur. Views are normally supported by Database Systems.

Datamart Tables

Description:
Wellesley’s Datamart currently contains reporting tables for the Resources Office , the Alumnae Office, the Human Resources, the Office of the Dean of the College, Institutional Research and selected student tables used to produce class lists. Each reporting table contains information taken from multiple Banner tables. Each night information is pulled from Banner tables, processed and placed into datamart reporting tables. For example the Human Resources EMPLOYEE Reporting Table takes data from 10 tables and includes name information, miscellaneous identifying information, employment status, vacation and sick time available. This table was created to be able to provide all the information necessary to produce reports related to general employee information. Just the current records are pulled from tables that keep history, years of service and age are calculated as well as currently available sick and vacation time. The datamart tables are kept on a totally separate database from the Banner database. It is currently refreshed (copied from Banner) once every 24 hours.

Advantages:
Reporting from a datamart table is much simpler than reporting from Banner Tables. Data has already been grouped together in anticipation of users’ needs so in many cases a report writer just has to choose fields from a single table and does not need to have any programming experience. Response time is fast and the system resources used to process a report writer’s query are minimal. This is because all the table joins and complex processing are done once during off- hours. In addition, indexes can be added to make access to the reporting tables even faster. The entire database that contains the datamart tables can be optimized to make reports run more efficiently.

Because the datamart is developed in a formal, less ad-hoc way than individual reports, there is greater quality control. There is also more formal testing done. This results in fewer errors and more reliable reports.

When reporting from Banner Tables, data in one table may be more current than another table used to create the report. Also, the same report run at two different times on the same day may produce different results if any of the data has been updated in between run times. This will not happen when reporting is done from datamart tables that are refreshed once in the middle of the night.

If the structure of the Banner Tables change, those changes are the responsibility of those maintaining the datamart. Users will not suddenly get an error message when running a report. Most of the changes can be incorporated at the datamart layer, insulating the end user and probably even the report writer from the changes. When users report against a datamart, they are less reliant on the transactional (Banner) database. If the vendor of Banner ever changed from Oracle to another database or if the college moved from Banner to an entirely different system, all the necessary changes would be made at the datamart layer. Report developers would have far fewer changes to make than those report writers working with Banner Tables.

Datamarts allow us to leverage programming skills and data knowledge. Because the logic that is often held by one person is stored in the datamart, the college is much less vulnerable when data experts leave.

Disadvantages:
Datamarts require time to develop. Before being able to use datamart reporting tables, significant work must be done by users in the departments, report writers and datamart programmers.

Datamarts are expensive to develop. In addition to requiring the time of staff in user offices and in Database Systems, computer hardware and software resources must be allocated for the development and testing of datamart tables. The skills required to develop a datamart continue to be in high demand and are often expensive for the college to obtain.

Once a datamart is created for a department, there may be requests for data that has not been placed in the datamart. In this situation, it may take a little longer to incorporate the data into the datamart than it would take to develop the report based on Banner Tables.

If a report must include data that is current to the minute the report is run, datamart tables cannot be used because they are usually current as of the data entry completed the night before.

Support:
Reports developed against datamart tables are supported by the department that develops the report. However, the support required is less than that required for a report developed using Banner Tables. Support for the Wellesley Datamart is provided by Database Systems.

Stand-alone Databases
(MS Access, Pro, etc)

Description:
Many small databases exist at Wellesley. These typically contain a more narrow set of data than does the Banner database. However, they sometimes contain data which has been downloaded from Banner. These databases generally have been developed using the same tool ( Pro, Access, Excel) that is used for data entry and reporting. However data can be exported into several formats and imported using a different tool.

Advantages:
Database development can be done as needed by an individual in the department who knows the data. Existing expertise in the software package can be utilized.

Disadvantages:
These stand-alone databases are not suitable for large applications. Depending on how they are set up, they vary in the degree to which they can be integrated with the Banner database. Therefore they are not suitable when very current data is required or when updates must be made to Banner. Complex databases should be thoroughly documented by the developer so that edits and enhancements can be made by someone other than the original developer.

Support:
Support for each product (MS Access, Excel and Pro) is available from the Help Desk. Support focuses on the use of these products rather than on the structure of the particular database tables or the content of the data. Custom database design services are not available from the Help Desk. Users who need to learn how to use these products can take a training class offered by User Services or Element K. More in-depth courses are available from outside sources.

Chart 1 - Comparison of Reporting Environments

Previous Section	Reporting Strategies Report Main Page	Next Section