A Description of Administrative Report Coding,
Documentation and Datamart Standards

General Technical Coding Standards

As reporting becomes more decentralized and as report developers in one office find themselves supporting code that was developed by staff from another office, it becomes extremely important that all report developers follow the same general coding rules. When creating a report, a report developer should always do so as though s/he is creating it to be used and modified by someone else. A report d eveloper should ask, "Would another report developer or modifier easily understand what I am doing here"? If not, then a comment should be inserted. It is also important that some basic information about a program be included either at the beginning of a report (SQL or PL/SQL) or in a designated comment section (Oracle Reports, Oracle Discoverer). As a bi-product of these coding documentation standards, Database Systems will be able to create a common report library to document all of the institutional reporting taking place on campus.

We are also looking to automate as much of the process as possible. It is possible, for example, that by placing comments in designated parts of the report code structure, that these comments can be pulled out and made part of a general reporting library without additional effort by report developers. We will not ask report developers to document code in more than one place. If a report developer logs a report into the report database/library, he or she will not also be required to put anything other than the report name in the code itself. Examples of well documented code can be found in Appendix A.

General information should be included in all programs. This information should include:

Comments within code should be used to highlight unusual features or methods in coding or to describe the different sections of code.

Variable names in code should be meaningful. Variable names like "temp1" are not descriptive and rarely helpful. As demonstrated in the PL/SQL example in the Appendix, a variable name like "home_atyp_code" makes the code easier to read and follow.

In the Appendix of this report, you will find samples of code which demonstrate good general information, useful comments and good variable names.

Reporting Database/Library

Reports on the Wellesley Campus are now being stored in multiple locations, using multiple tools, by multiple report developers. We therefore need a mechanism to log and track institutional reporting on campus so that:

1. Report developers can find out about the existence of reports that may save them time and effort.
2. Managers can determine the number and types of reports that their offices support
3. Central systems' staff can manage the resources required to support the reporting done on campus.

In some cases the information should be pulled into this database by Oracle (when Oracle tools are used for reporting) in other cases report developers would need to supply information through a browser interface.

The fields of information that could be stored in the reporting library should be:

A repeating table will be created to track the maintenance of reports. This table will store the following fields:

There will also be three validation tables created to capture software, the cycle and the departments so that the data entry of the following fields will contain uniform sortable information:

Documentation Standards

Each department should have a hard copy or electronic (or both) document which describes the technically driven processes and reports that are required at predictable intervals. This Cycle Support Documentation should be ordered in terms of daily, weekly, monthly, semesterly, yearly and periodic processes or reports.

Other important information that should be included in each Cycle Support Document should be:

This document is not only useful in the day-to-day technical environment but can also serve as a planning document. If, for example, the College does a major upgrade of software during the summer, a department can quickly check the Cycle Support Document to determine what reports and processes will be needed immediately following the upgrade to be sure that they are tested first.

We recommend that where ever possible, that technical documentation be web-based. The advantages to web based documentation is its ease of use from almost anywhere on campus (and sometimes off campus). Also, links to other documentation can be created within a document, allowing the reader to get more detail if needed.

Tracking / backing up desktop reports

Reports that are saved on desktop computers are, like any file, susceptible to accidental deletion or corruption due to hardware or software failure. Backup of these files is the responsibility of the department and the individual user who created the report. Information Services recommends frequent backup of user-created files (not installed software programs). Backup information and recommendations can be found at http://www.wellesley.edu/Computing/Backup/backup.html. Each department should determine a policy for the organization and frequency of backups. If files are thoughtfully organized and backup is routinely practiced, the process (copying files to a floppy or zip disk) will be quick and easy. Daily backups are recommended when frequent updates or edits are made, especially when the data would be hard to recreate. Very important data should be backed up on more than one other disk or on a file server. Files that are updated less frequently might be backed up weekly. In any case, if you do not need to keep all of the older data, it is a good idea to alternate your backup between two disks. In other words, keep a "mother" and a "grandmother" copy of the file so that if something was edited in error, the next most recent backup is still available. At backup time, always overwrite the oldest copy with the new backup. Be sure that each disk has a label indicating the names of the files it contains as well as the date of the backup.

If your data is confidential or sensitive you should be sure that it is not accessible to anyone who is not authorized to see it. Backup disks containing confidential data should be stored in a secure location. A copy of important archival data should be kept off-site in another building.

Datamart Development Standards

All report modifiers, data miners and report generators will access the datamart via the reporting tables. Two other kinds of tables are found in the datamart, materialized views and staging tables. These are used in the creation of reporting tables. Report developers may have access to staging tables in the development and testing instance of the datamart so that they can assist in the development of report table logic and test data and logic in the staging tables. However, report developers will not be allowed access to materialized views or staging tables to create reports in the production instance of the datamart. If a report is created in the development or test instance of the datamart which uses data from a materialized view or a staging table; that report will not be usable in the production instance of the datamart until the report logic is incorporated into a reporting table(s).

All datamart code should be tuned for optimal performance before it is moved to the production environment. This entails using an "explain plan" to determine if the code is using appropriate indexes, table scans, and parallel processing to execute in the shortest period of time. In most cases, Database Systems will use explain plans to evaluate new code before moving it into production.

Datamart Security Standards

Every staging table in the datamart has a departmental steward. (i.e., Someone in a department who cares about the data in the staging table and ensures its accuracy and integrity. The departmental steward also makes decisions about what other offices/departments can have access to data in the staging tables they care for).

If a reporting table is being developed by a department from a staging table over which that department is the steward, no written authorization will be required. (i.e., If the Alumnae Office wants to create reporting tables from an Alumnae Office staging table, no written authorization will be required.) However, If a reporting table is being developed by a department using columns from a staging table over which it is not the steward, that department must get written permission from the departmental steward for those specific columns (or fields) of data. (i.e., If the report developer from the Alumnae Office wants to create a reporting table using a column of information from a Human Resources staging table, written authorization from the H.R. steward would be required). This permission must be acquired before the new reporting table can be put into the production instance of the datamart.

Once a column from a staging table has been approved by the staging table steward for use, the report developer can grant access to the reporting table to any report user without returning to the original staging table steward. (Similar to the rights granted to Banner Class owners) It is therefore up to the report developers to use and distribute information carefully. Similarly, once a column from a staging table has been approved by the staging table steward for use, the report developer can use the information in this reporting table to create additional reporting layers with tools such as Oracle Discoverer. (For example, if a report developer receives permission by the general staging table steward to use social security number in a reporting table, the report developer can then use information in that reporting table to create subsequent end user layers for reporting without returning to the original staging table steward.) Once again, the report developer must be sure that the information is used and distributed carefully.

If a report developer has access to a particular Oracle table on the production instance of Banner, they will be permitted to have access to a materialized view of that table on the datamart development and test instances without submitting further paperwork. If a report developer would like to have access to a table or view in the datamart that they do not have in the production instance of Banner, they will need to submit paperwork with written permission from the Banner table/data steward . (Forms created for this purpose can be found on the CWIS at http://www.wellesley.edu/Banner/lan/rolemod.html).

Back to Dbsystems