Getting Started in Banner Reporting

The first thing one must understand before even thinking about building a report is the underlying data structure from which one must extract the information required. In Baseline Banner, there are over 4,000 objects that store various pieces of data. The Operational Data Store (ODS) and Enterprise Data Warehouse (EDW), while they have far fewer objects, contain their own set of storage objects, all of which one must understand before being able to produce a valid and accurate report. So, with close to 5,000 database objects to choose from (assuming one has access to both the ODS and EDW), how does one figure out where to get the data they need? Well, the goal of this posting is to give you a good idea of where to start.

Deciding where to get your data.

As mentioned above, SunGard provides three areas from which you can draw information for reporting: Baseline, the ODS, and the EDW. Below, I have included a blurb about why you might choose to use one over another. Happy hunting!

Baseline is the database architecture that everyone typically thinks of when they say "Banner". It is the thing that stores the hundreds (if not thousands) of transactions that occur daily at your institution and what is typically used for reporting (at least until recently). The key question when deciding to use Baseline vs. the ODS for standard reporting is whether or not the report needs to contain real-time data. The ODS is cloned nightly so any reports drawing data from it are inherently a day old. For things like up-to-the-minute enrollment statistics and current financial statements, Baseline may be required.

The Operational Data Store (ODS) is SunGard's answer to the difficulties that arise when using Baseline for reporting. It is a series of database views that "flatten" the information stored in Banner Baseline. This makes getting a report of logically related information much easier as one can get all the required information from one reporting view instead of having to grab disparate pieces of data from multiple sources (in the case of Baseline, database tables).

The Enterprise Data Warehouse (EDW) is SunGard's solution for analytical reporting. There is a new movement in business intelligence circles towards more dynamic, analytical, database tools for reporting that can be used for real decisions. This new area of reporting has requirements that are completely different from "standard" reporting: aggregated information, data organized in ways that make it easier to analyze, and most importantly, historical data (used for trend analysis).

Source ID'd, now for the detail!

So, you've figured out the source for your report. Now you have to find the individual objects (tables and/or views) and even column fields within the object that contain the data you need. As with the section above, this one is organized by source, so if you're looking for more information on reporting against Baseline, click here, the ODS, click here, and the EDW, click here.

Baseline Detail

SunGard uses a naming convention for Baseline objects that is not very useful EXCEPT in the following ways:

1. Object names are always 7 characters long
   
2. The first letter corresponds to the module
   
   
   • S = Saturn (Student Related Information)
     
   • G = General (Information Common Across Modules)
     
   • F = Finance
     
   • R = Financial Aid
     
   • T = Student Accounts
     
   • N = Position Control (HR Information)
     
   • P = Payroll
     
   • A = Advancement (Alumni and Development)
     
   
   
3. The second two letters if "TV" identify a validation table. So, if you're looking for a validation table with academic term (student information), the first three letters of the object will be "STV = S - Saturn, TV - Validation Table"
   
4. The last four characters are a little harder to nail down, but the majority of the time they indicate the type of data stored. For example, the last four characters for the validation table for academic term (as discussed above) are "TERM". Put that together with #3 and you get STVTERM. Likewise, the validation table for student major is STVMAJR, and for financial accounts, FTVACCT.
   
5. Another little nugget of information is in regards to the third character. If it's an R, that means that the object has "repeating" records. That means that there is more than one record per person or other key data element. For example, SFRSTCR is the repeating table for course registration information (S=Student, R=Repeating, STCR=STudent Course Registration), and contains a record for each course a student is taking (the student is repeated). On the other Hand, if the third character is a B, that means it's a base table and will contain NO repeating rows for the key data element. NBBPOSN is the base table for position information and contains only one record per position and suffix combination. Student, Employee, and Advancement all have a base table that one must be in to be considered each respective entity type:
   
   
   
   • APBCONS - Contains a record for EVERY constitutent
     
   • PEBEMPL - Contains a record for EVERY employee
     
   • SGBSTDN - Contains a record for EVERY student
     
   
   If an individual does not have a record in SGBSTDN, they are NOT considered a student, etc.
   

So with the above information, it is my hope that you will have the tools you need to get started reporting against Banner Baseline tables. Additionally, don't forget that the Banner Forms can give you hints regarding the source of the data they contain. To find those hints, click into a data field of interest, click "help", then "dynamic help query". The window that ensues contains the TABLE and the FIELD in which that data element is stored. There is a good chance that related information on the same form resides in the same table. Good luck!!!

ODS Detail

The key to the ODS is the metadata provided by SunGard during the purchase process. To locate the documents associated with your institution's metadata, ask your Banner administrator(s). Depending on your starting point, there are a couple different ways to go about navigating the documents contained in the ODS metadata files:

1. If you know the source in Baseline, use the "source reports" to find its location in the ODS:
   
   
   
   • On the main page, choose the module that contains the data from the links on the left side.
     
   • Scroll to the very bottom of the subsequent page where it says "Source Reports".
     
   • In the list of source reports, locate the name of the table in which the data element in question is stored in baseline and click its link.
     
   
   
2. If you don't have the starting point specified above, start in the ODS Concept Diagrams provided in the ODS metadata:
   
   
   
   • Starting on the main page of the metadata, click the link to the concept diagrams at the bottom center of the page.
     
   • Using the menu on the left, navigate through the different functional modules and select areas related to the data for which you are looking.
     
   
   

EDW Detail

As I mentioned above, the EDW is built with a completely different reporting methodology in mind. As such, it is organized in what are referred to as "star schemas". These schemas are designed to make reporting aggregated, measured data easier. As shown in the article linked above, star schemas are organized in what are known as "fact" and "dimension" tables. The fact tables contain the "measures" while the dimensions contain the "buckets" by which you wish to measure the "measures":). Clear as mud, I know. So, what can you do with this information? Well, first of all, you should only be using the EDW if you are trying to report measured data. If that's the case, there is a series of metadata documents (very similar to the ODS metadata) that will help in figuring out where to get the data you require.