Banner data is stored in normalized Oracle tables; in contrast, the data in the IDR data warehouse is highly denormalized or “flattened.” Normalized data is appropriate for transactional systems (like Banner), while denormalized data is much better suited for ad-hoc querying. The IDR data warehouse, like Banner, consists of objects in Oracle databases.
Very briefly, the steps to get Banner data loaded to the IDR data warehouse include:
Banner data does not map 1-to-1 to the IDR data warehouse. Some of the fields are calculated; some are contextual; etc. Each piece of data is reviewed in order to decide what should be copied. Decisions must be made about how data should be grouped (packages) and what data is sensitive and needs to be restricted to a subset of consumers.
- The Extract/Transform/Load (ETL) developers in Information Services write processes to stream the data from Banner to the Operational Data Store (ODS - This is sometimes called the “IDR data warehouse.”) The transformation process is complex and there is a great deal of validation work to ensure the data that ends up in the data warehouse is accurate. Ellucian, from whom we purchased Cognos and who sells Banner, provides some generic procedures. However the procedures must be modified to the extent that we have modified our Banner installation. The Banner Finance module has been modified the least, which is one of the reasons why it was chosen as the first module with which to work. In comparison, the Banner Student module is expected to take considerably longer since it has been modified considerably to meet the business needs of the UO.
- Authorization to the report packages is provided by Cognos and not by Oracle. This means that security roles have to be set up in Cognos once the data owner identifies the access rules.
- System health check processes have to be developed so that report writers can be notified as soon as possible if any of the many nightly ETL processes fail. While some of this can be automated, some can’t. The IDR team works closely with the data owner, the finance functional user test group and the Banner module lead to iteratively review and refine the process.
- Performance can be, and has been, an issue at every step of the way. The database and Cognos administrators, ETL developers and programmers address tuning issues are they are brought to their attention.
All of the above steps will need to be done for each of the Banner modules before all of the data is in the IDR data warehouse.