Tennessee Board of Regents Operational Data Store – Data Replication

Presented by Greg Turmel & Pamela ClippardTennessee Board of Regents

Monday, April 11th 2011Session 1.3

Time: 9:15a – 10:00a

IT Symposium 2011

Abstract: 

This presentation will explore and discuss technical and functional differences seen when implementing the SunGard Oracle Operational Data Store version 8.2.1, its impact to Banner as a source system, and the expectations for securing quality data feeds into the Enterprise Data Warehouse version 8.2. 

The open discussion segment of the session will attempt to answer many of the questions surrounding the expected changes from current replication methodologies used in ODS 8.1 reporting processes. 

ODS 8.2.1 Data Replication

ODS 8.2.1 Data Replication

Where is the value? 

Data Replication services provide a core business process for Research Reporting. 

This version reduces the existing frustrations and high impact on transactional systems (Banner).

It can be a key part of daily operations instead of Banner. 

Value increases when used for longitudinal analysis and trending for analytical decision support.

Managing Business Processes:Defining the scope of the problem and support it with research.Leveraging trend analysis opportunities.

Decision Support based on key performance indicators: evaluating available courses of action.selecting a course of action.implementing the selection.

Continuous improvement: Evaluating and monitoringAchieving a “Total Quality management” model.

ODS 8.2.1 Data Replication

http://home.pacbell.net/johanes/Applied_research_journal.htm

ODS 8.2.1 Data Replication

Determining the resource scope will center on: 

Time constraints on personnel or requester...[e.g.] Institution, State, THEC/US Higher Ed.

The availability of data...[e.g.] No current collection process defined.

The nature of the decision that must be made…[e.g.] Student Retention/Graduation.

The value of the research in relation to its costs…[e.g.] Funding.

Purpose: Financial responsibility is key to any mission and should be a core strategy in data replication.

Governance:Workflows should involve approvals by required personnel.

Structure: Reporting designs and access levels should maintain the integrity of a reporting system.

Policies: Clarify and redefine budgetary policies and develop additional accounting policies. [e.g.] SAN

Reference: http://www.nacubo.org/Business_Officer_Magazine/Current_Issue/February_2011/Put_Data_in_the_Drivers_Seat.html

ODS 8.2.1 Data Replication

Processes: Specific changes should result in more timely, detailed financial reporting.

Culture: All changes should be made with due consideration of the overall campus culture.

Infrastructure: Changes should support features and functionality of both new/existing software tools.

Information: Changes to any structures should be based on information‐timely relevant data provided in a format that enables campus leaders to make informed decisions in response to emerging trends.

Reference: http://www.nacubo.org/Business_Officer_Magazine/Current_Issue/February_2011/Put_Data_in_the_Drivers_Seat.html

ODS 8.2.1 Data Replication

Pre-Streams : ODS 8.1 Link Administration

Bann

er Transactio

nal System

Redo.log Groups

Use

r or p

roce

ss in

put

Propagation Streams ApplyQueue Queue

Streams Capture

Chan

ges

Query

Write

Tx-composite

Com

plex

Joi

n

Ope

ratio

nal D

ata Store

Composite Views

OAV: Object Access Views

Tx-composite

MST* mappings

Streams Administration: ODS 8.2.1

Database Server # A Database Server # B

Summary

• Banner Table Changes and the Redo Logs

Streams Capture 

• Replicated Banner Tables for Staging

ETL – Load, Delete, Update • ODS Tables 

And Views

Reporting Views & Tables

Streams Propagation

ODS 8.2 

Streams Apply

Banner Production

Streams Administration: ODS 8.2.1

Streams Capture:   Reads the database redo logs. Collects DML and DDL changes that have been made on the Banner source table

Streams Propagation:  The process moves changes (LCR) from the Banner source DB to the ODS target DB

Streams Apply: Takes the changes into the ODS stage tables. Matches and updates the modified record in the ODS target table

Error Queue:  Low level streams data errors encountered can be reviewed and reprocessed. 

Replication Terminology

LCR —Logical Change Record  Includes the old and new values from the source table for each record column replicated.

ODS Apply Error— An error recorded when the old record value in the source table cannot be matched in the target table

Re‐stage Process— A target table is dropped from the ODS database which allows the staging process to re‐create that target table.

Note: When target tables are re‐staged – any insert_ods_change triggers for that table need to be re‐created!

Replication Terminology

Replication Advantages:

Banner [e.g.] SOKODSF,  ODS Triggers, Views removed.Banner data changes captured in redo logs.Reduced impact to Banner operations [e.g.] CPU, Memory, processes, back up schedules…

Replication Disadvantages:

Staged tables and Views/Materialized Views can no longer be used for daily reporting in Banner.Banner patching break/fit support changes radically.New streams capture process to support.

Transactional Source system (Banner)

Replication Advantages:

ODS objects are all on the down line system with minimal exceptions. [e.g.] Blobs (img file), clobs… use a DB_LINK.Minimized impact to student registration, grade posting, or payroll [e.g.] Views supporting ODS are gone in Banner. Changes are propagated from Banner redo logs, not triggers that interfere with other baseline processes.No more bulk loads against Banner scheduled: impacting Banner backups, Database Statistics jobs, or the data quality in the loads due to a disabled trigger.Faster refresh of ODS, near continuous feeds. [e.g.] Smaller redo logs now equates to quicker refresh rates. 3 sets x 100 meg is less efficient (slower) than 6x50 meg.

Data Store - Target system (ODS)

Data Store - Target system: (ODS)

Replication Disadvantages:

Staged tables can’t be used for daily reporting. [e.g.] Briefly contains only the changes.Banner patching potentially breaks ODS objects.[e.g.] Banner object changes are replicated automatically.New streams propagation and apply processes. [e.g.] New processes means new methods of support and training.Still requires a schedule for changes to load.[e.g.] Data replicated by streams still requires a job to move to ODS tables and materialized views.Streams error logs to review: data left unapplied. [e.g.] Requires intervention by IT to review, consult, apply.

Description Contains data about all awards sought and/or earned by a student. 

Details include program of academic study, majors, 

degree GPA, total credits earned etc. 

 GOVERNMENT_ACADEMIC_OUTCOME reporting view

Key & Frequency One row per person per outcome number.PERSON_UID,OUTCOME_NUMBERSTATUS,STUDENT_LEVELACADEMIC_PERIOD_GRADUATIONPROGRAM,MAJOR,PERSON_UIDCOLLEGE,DEPARTMENT,PERSON_UID

Target Column Business Definition Database Data Type Source Name Source Column

ACADEMIC_PERIOD Academic period or time frame associated with this degree record. VARCHAR2(63) SHRDGMR  SHRDGMR_TERM_CODE_STUREC 

ACADEMIC_PERIOD_DESC Academic period description. VARCHAR2(255) FUNCTION  GOKODSF.F_GET_DESC 

ACADEMIC_PERIOD_GRADUATION Academic period when graduation requirements will be completed. VARCHAR2(63) SHRDGMR  SHRDGMR_TERM_CODE_GRAD 

ACADEMIC_PERIOD_GRAD_DESC Academic period description. VARCHAR2(255) STVTERM  STVTERM_DESC 

ACADEMIC_YEAR_GRADUATION Academic year associated with this graduation date. VARCHAR2(63) SHRDGMR  SHRDGMR_ACYR_CODE 

ACADEMIC_YEAR_GRADUATION_DESC Academic year description. VARCHAR2(255) STVACYR  STVACYR_DESC 

ADMINISTRATIVE_GROUP Organization-oriented identifier for the record used to determine access authorization to this record.

VARCHAR2(255) NA  NA 

APPLIED_FOR_OUTCOME_IND Indicates whether the person has applied to officially receive this specific degree.

VARCHAR2(1) FUNCTION  F_APPLIED_FOR_DEGREE 

Recommended Search Columns 

SGHE Banner Operational Data Store

Academic Outcome: Graduation - ODS

Evaluate your database configurationRun the SGHE script: SGHE_BPRA_Check_Streams_Config.sql to review the DB parameters

AQ_TM_PROCESSES = 1

shared_pool_sizemin. 256

streams_pool_sizemin. 256

global_names= true

Compatible 10.2 or higher

job_queue_processes>= 4

_job_queue_interval= 1

10g _CMPKEY_ONLY = ‘N’

11g COMPARE_KEY_ONLY = ‘N’

PARALLELISM = 0 (review defect correction 1‐H4R3OC)If you are planning to use RAC configuration Streams propagation must be queue_to_queue

Preparing for Implementation

FAQ 1‐EN5VG9 : How should STREAMS_POOL_SIZE be set for Banner/BPRA environment streams configuration

FAQ 1‐GBP75K: ODS 8.2 ORA‐04031 unable to allocate bytes of shared memory (increasing SHARED_POOL_SIZE and STREAMS_POOL_SIZE)

FAQ 1‐AXRVD8: Steps required to change passwords in ODS 8.2/EDW 8.1.x after initial install

FAQ 1‐7UYF83 : ODS Creating an ODS 8.2 Database environment on Red Hat Linux 4.0 64‐bit

Preparing for Implementation

FAQ 1‐GBP75K: ODS 8.2 ORA‐04031 unable to allocate bytes of shared memory (in Banner)

FAQ 1‐IEH1Z4: ODS 8.2 Oracle Streams Spilled messages and BPRA_BANNER$CAP queue

FAQ 1‐IEH1Z4: ODS 8.2 Oracle Streams Spilled messages and BPRA_BANNER$CAP queue

RAC ODS 8.2: How to fix the error ORA‐25315 shown in the dba_propagator the alert.log

Preparing for Implementation

ODS ‐ Apply Errors ORA‐01435: user does not exist ORA‐06512  at SYS.LCR$_DDL_RECORD

Fixed in patch P1‐BXVYD5_ODS8020113

BannerDo not change the ODSSTG password in Banner –It is used by the db_link from ODS to Banner

Patching and Upgrading

Patch 1‐hlgaku_ods8020111 ODS8.2 Streams ORA‐01403 error caused by applying transaction with matching on non‐key columns.

Defect 1‐H4R3OC ODS 8.2.x ‐Oracle Streams apply parameter "parallel" set higher than 1 generates deadlock  (This is fixed in 8.3)

Defect 1‐HT9VLBStreams ORA‐25315: unsupported configuration for propagation of buffered messages (RAC)

Patching and Upgrading

Apply Errors: ORA‐1403 no data found (LCR’s match on non‐key data ) 

Apply Patch 1‐hlgaku_ods8020111 { …   11.2.0.2 and above –sets init parameter COMPARE_KEY_ONLY = ‘Y’ 

Previous Oracle versions sets init parameter _CMPKEY_ONLY = ‘Y’  …  }

FAQ 1‐E5P4VJ: after the Grant Fund Conversion program (FRRGRNL, FGRGRBD) 

FAQ CMS‐12719 after Budget Availability Rebuild processes (FGBBAVL, FGRBAVL) 

Streams Apply processes

Follow SunGard Higher Education’s recommended procedure for cloning both Banner and ODS.

FAQ 1‐B2VYHB How to backup and clone environments for ODS and streams using RMAN

FAQ 1‐B2UMX7 Post clone steps of an ODS and Banner environment with ODS streams

Cloning with Streams turned on

Cloning: both ODS and Banner environments at the same time

Cloning: the ODS target only

Cloning: the Banner source only

FAQ#: 1‐BM5JZA ODS 8.2: Steps to remove ODS stream objects from a clone banner environment

Remember to re‐point the db_link to the correct database and also complete a password rotation for your ODS and OWB component.

Cloning with Streams turned on

Streams Administration: EDW 8.2

EDW: Star Schema Design

EDW Dimensional Model for Enrollment

The star schema is the simplest data warehouse schema. It is called a star schema because the diagram resembles a star, with points radiating from a center. The center of the star consists of one or more fact tables and the points of the star are the dimension tables, as shown in the following discussion.

The most natural way to model a data warehouse is as a star schema, only one join establishes the relationship between the fact table and any one of the dimension tables.

A star schema optimizes performance by keeping queries simple and providing fast response time. All the information about each level is stored in one row.

EDW: Star Schema Design

EDW : Star Schema Design

EDW : Star Schema Design

Enrollment Star Report: Target Reports – Description

WDT_ACADEMIC_STUDY Academic Study Dimension Academic Study Dimension table provides information for the program of study for the individual. This may be the data on the admission application or the academic outcome record that identifies the major subject area and sought award or degree, etc.. It uses the cleansing rules in the EDW for the short and long descriptions.

WDT_DEMOGRAPHIC Demographic Dimension Demographic Dimension table provides information for the person that is consistent whether this is an employee, inquirer, applicant, student or graduate at the institution. This information includes gender, ethnicity, age range and family income range. It uses the cleansing rules in the EDW for the short and long descriptions as well as the definition of the ranges in this dimension.

EDW : Star Schema Design

WDT_ENROLLMENT_STATUS Enrollment Status Dimension Enrollment Status Dimension table provides information for the person that includes the time frame status of enrollment. This information includes whether they intended to study full or part time, whether or not they are currently enrolled and their current time status based on the institution defined rules. It uses the cleansing rules in the EDW for the short and long descriptions.

WDT_STUDENT Student Dimension Student Dimension table provides information for the person as the student for the time frame. This information includes student population (New first time, continuing, re‐admit, etc.), year of study or student class, residency (in district or out) and campus affiliation. It uses the cleansing rules in the EDW for the short and long descriptions.

EDW: Star Schema Design

WDT_TIME Time Dimension Time Dimension table provides information on the time frame for which the rest of the associated dimension and fact table are storing the information in the star. The time dimension may be based either on a calendar, academic, financial aid or fiscal year, The time frame will go down at least one and sometimes two layers to periods and or sub periods of time. It uses the cleansing rules in the EDW for the short and long descriptions.

WFT_ENROLLMENT Enrollment Fact Table Enrollment Fact table provides measures that may be compared and displayed based on the dimensions in this star schema. This set of measures is stored per person per enrollment academic period

Q&A ?

Questions

Greg TurmelSr. Database AdministratorTennessee Board of Regents1415 Murfreesboro Rd. #358Nashville, TN. 37217615.366.4467 (Office)http://itinfo.tbr.edu (IT website)http://twitter.com/datahaulr http://www.linkedin.com/in/gturmel

Pamela ClippardSr. Data ArchitectTennessee Board of Regents1415 Murfreesboro Rd. #358Nashville, TN. 37217615.366.3915 (Office)http://itinfo.tbr.edu (IT website)http://twitter.com/pwclippard http://www.linkedin.com/in/pwclippard

Contact Information

Master Note for Streams Recommended Configuration [ID 418755.1]

Streams Secure Queues & Using DBMS_STREAMS_ADM.SET_UP_QUEUE to Setup the Secure Queue [ID 230902.1]

Streams Support for Compression [ID 763997.1]

How To Exclude A Table From Schema Capture And Replication When Using Schema Level Streams Replication [ID 239623.1]

How To Configure Streams Real‐Time Downstream Environment [ID 753158.1]

How To Setup Schema Level Streams Replication with a Downstream Capture Process with Implicit Log Assignment [ID 733691.1]

How To Access Streams Or Advanced Queuing Information From The 10.2 Dbconsole [ID 336061.1]

Banner Operational Data Store 8.2 Handbook / Architecture 

Reference Materials

Reference Materials

11gR2 Streams Concepts and Administration ‐ e17069.pdf

11gR2 Streams Replication Administrators guide ‐ e10705.pdf

11gR2 Streams Advanced Queuing users guide ‐ e11013.pdf

11gR2 Oracle Database Warehouse Builder ‐ Concepts ‐ e10581.pdf

11gR2 Oracle Database Warehouse Builder Release Notes ‐ e10585.pdf

11gR2 Oracle Warehouse Builder Data Modeling ‐ ETL ‐ Data Quality guide ‐ e10935.pdf

BPRA Upgrades ‐ Oracle 11g/OWB 11g, PM App Dependencies, Jim Carter, Brian Large: http://www.edu1world.org/CommonsBI/wiki/document/4754

Post‐Installation Steps for Oracle Warehouse Builder 11g Release 2 (11.2.0.2) http://download.oracle.com/docs/cd/E11882_01/relnotes.112/e10585/toc.htm

ODS 8.2 and Oracle Streams – One Year Later, Karen Funston, presented at the SunGardHE Summit 2011, March 22, 2011