1 / 47

ODS 8.4 Release Overview

ODS 8.4 Release Overview. Introduction. This presentation will provide an overview of the ODS 8.4 release. ODS 8.4 Agenda Slide. Objectives / Timelines Release Components System Requirements Overview of Materialized View Framework Update on the ODS 8.3m Controlled Release.

herman-monroe
Download Presentation

ODS 8.4 Release Overview

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ODS 8.4 Release Overview

  2. Introduction • This presentation will provide an overview of the ODS 8.4 release

  3. ODS 8.4 Agenda Slide • Objectives / Timelines • Release Components • System Requirements • Overview of Materialized View Framework • Update on the ODS 8.3m Controlled Release

  4. Objectives & Timelines of the ODS 8.4 Release

  5. Objectives and Timelines • Beta Clients for ODS 8.3m (End of Feb) • Upgrade from ODS 8.1 • Centennial College • Wayne State University • Upgrade from ODS 8.3 • Florida Keys CC • Texas A&M • Controlled Release for ODS 8.3m (Mid April) • Early Adopter • Wayne State • Texas A&M • Test Driver • St. Scholastica • UNC Greensborough • UNC Charlotte • Worchester Poly Institution • New Mexico State • Mohawk College • ODS 8.4 (August) • Alternative, simpler data integration option to existing Oracle Streams technology • Simplify installation, configuration, & ongoing maintenance • Staging area will remain; Only the mechanism to replicate/refresh the staging tables will change

  6. ODS 8.4 Release Components

  7. ODS 8.4 Release Components • ODS 8.4 Install • Fresh install of ODS 8.4 • Choice of using Oracle Streams or Materialized Views • Documentation • ODS 8.4 Upgrade • Upgrade from ODS 8.1 or 8.3 • Upgrade from 8.1 • All content from 8.2, 8.2.1, 8.3, 8.4 included • Choice of Oracle Streams or Materialized Views • Upgrade from 8.3 • Choice of Oracle Streams or Materialized Views • Remaining Posted Patches since Master Patch (21) • Documentation

  8. ODS 8.4 Release Components • Improved Trigger Performance • Enhanced Health Check for: • Triggers • Change table indexes • Defect fixes • All triggers revised to account for mview refresh behavior • No performance impact • Will fix existing issue in triggers when key values are updated

  9. ODS 8.4 System Requirements

  10. ODS 8.4 System Requirements • Oracle Software Requirements: • Database 11.2.0.2.5 (both source & target databases) • 11.2.0.3 also certified • Check out updated Wiki for more information on 11g • http://www.edu1world.org/CommonsBI/wiki/document/4754 • Oracle Warehouse Builder • Mega Patch V3 12874883 • One off patch 13533924 • One off patch 13009123 • Oracle Discoverer (if applicable) • Minimum 11.1.1.3 • IBM Cognos Software Requirements: (if applicable) • 10.1.0 or 10.1.1

  11. ODS 8.4 System Requirements • Banner Software Requirements • Minimum versions of Banner needed if licensed • Advancement 8.3 • Accounts Receivable 8.2 • Finance 8.4 • Financial Aid 8.13 • General 8.4 • Payroll 8.5 • Position Control 8.5 • Student 8.4 • Travel & Expense: 8.3

  12. ODS 8.4 System Requirements • Upgrading from ODS 8.1 • Disk Considerations • Expect approximately 150% of your Banner database • Delivered script to help estimate disk required to stage Banner tables: required_tablespaces_banner.sql

  13. ODS 8.4 System Requirements • Upgrading from ODS 8.3 • Disk Considerations • No additional • Must have installed ODS 8.3 Master Patch p1-vf5tht_ods8030021

  14. ODS 8.4 System Requirements • Be sure to review the Oracle Initialization parameters defined in the Oracle Streams Supplement & Materialized View Supplement • These supplements cover a lot of information about each of the replication mechanisms including: • Architecture • Set Up & Configuration • How to Administer staging in the Admin Interface • Cloning • Monitoring • Troubleshooting

  15. ODS 8.4 Master FAQ • FAQ 1-18571IL • Any install issues reported to the ActionLine subsequent to the posting of this release will be documented in the “Banner ODS 8.4 Master FAQ” solution/FAQ 1-18571IL and made available via the Customer Support Center (https://connect.sungardhe.com/customer_support). It is recommended that you check this document prior to beginning this install by querying under Solutions/FAQs for the FAQ 1-18571IL.

  16. Overview of Materialized View Framework

  17. Materialized View Framework • Materialized Views in the ODS • One for One Replication of Banner source tables in the ODS • Initial load of data when creating the Mview • Each Mview is setup using FAST REFRESH option • FAST REFRESH uses Primary Key if available, otherwise uses ROWID • Setup at creation of mviews • Materialized Views used as the source of the ODS ETL • Core ODS ETL stays the same (triggers, change tables, composite tables, reporting views) • Refresh of ODS now includes 2 types of refreshes • Mview refresh (new) • ODS Composite Table refresh (existing) • Mviews do not support LONG columns

  18. EDW SCHEMA BPRA Architecture – 8.1 Releases ODS/EDW (Target) BANNER (Source) PRODUCT SCHEMAS ODS SCHEMA COMPOSITE TABLE(S) BASE TABLES EDW ETL ODS ETL COMPOSITE VIEW REPORTING VIEW CHANGE TABLE TABLE TRIGGER REPORTING TOOL(S)

  19. BI Architecture –8.4 Releases using Mviews BANNER (Source) ODS/EDW (Target) PRODUCT SCHEMAS STAGING SCHEMAS ODS SCHEMA EDW SCHEMA BASE TABLES COMPOSITE TABLE(S) BASE TABLES MVIEWS ODS ETL EDW ETL COMPOSITE VIEW MVIEW LOGS REPORTING VIEW CHANGE TABLE TABLE TRIGGER REPORTING TOOL(S)

  20. BI Architecture –8.4 Releases using Mviews BANNER (Source) ODS/EDW (Target) PRODUCT SCHEMAS STAGING SCHEMAS ODS SCHEMA EDW SCHEMA BASE TABLES COMPOSITE TABLE(S) BASE TABLES MVIEWS ODS ETL EDW ETL COMPOSITE VIEW MVIEW LOGS REPORTING VIEW CHANGE TABLE TABLE TRIGGER REPORTING TOOL(S)

  21. Banner Database ODS Database SPRIDEN SATURN SATURN ODSSTG ODSSTG

  22. Materialized View Framework • Database Link Setup • Can be setup to create one PUBLIC DB link or multiple PRIVATE dblinks • Defined in login.sql as parameter STAGING_MV_LINK_TYPE using values: PRIVATE or PUBLIC • Public dblink between ODSSTG schemas in source and ODS databases • Private dblinks between product schemas in ODS database and ODSSTG schema in source database

  23. Banner Database ODS Database SPRIDEN SATURN SATURN Private DBLINK ODSSTG ODSSTG

  24. Banner Database ODS Database SPRIDEN SATURN SATURN Public DBLINK ODSSTG ODSSTG

  25. Materialized View Framework • Initial Creation of Materialized View (Source side) • Materialized View Log and Internal Oracle trigger placed on each Banner table in which an mview is being created for in ODS • Needed for FAST REFRESH • If there is an existing Mview Log on the Banner table, ODS will utilize that log • Have broken out creation of Banner side logs to minimize time Banner has to be placed in RESTRICTED mode • No performance impacts of these items on Banner

  26. Banner Database ODS Database SPRIDEN SATURN SATURN MLOG$_SPRIDEN Internal Trigger Public DBLINK ODSSTG ODSSTG

  27. Materialized View Framework • Initial Creation of Materialized View (ODS side) • Materialized View is created in the ODS in the same replicated product schema • New package in ODS called MGKMVEW to house all mview related procedures • Similar to MGKSTRM built for Oracle Streams • MGKMVEW.P_STAGE_MVIEW • Creates each mview structure • Loads the data • Specifies FAST REFRESH on PK or ROWID • Builds indexes based on the Banner source table • Once Mview is initially staged, the baseline ODS trigger is put on it to use for ODS Composite Table refresh

  28. Banner Database ODS Database SPRIDEN’ SPRIDEN Initial mview create & load SATURN SATURN MLOG$_SPRIDEN ST_SPRIDEN_INSERT_ODS_CHANGE Internal Trigger Public DBLINK ODSSTG ODSSTG

  29. Materialized View Framework • Materialized View FAST REFRESH • As data changes occur in Banner, those records are logged in the Mview logs for that Banner table • Logged with ROWID data or PK data • When Mview is FAST REFRESHed, only the records from the mview log in Banner are applied to the ODS mview to keep the data between the source and target in sync • Source and target are not updated automatically (like Streams). An explicit command to refresh the Mviews is required • Logs are cleared out once Mview refresh has been completed successfully (or after all mviews using that log have been refreshed)

  30. Banner Database ODS Database SPRIDEN’ SPRIDEN SATURN SATURN MLOG$_SPRIDEN ST_SPRIDEN_INSERT_ODS_CHANGE Internal Trigger Public DBLINK ODSSTG ODSSTG

  31. Banner Database ODS Database SPRIDEN’ SPRIDEN SATURN SATURN Fast Refresh MLOG$_SPRIDEN ST_SPRIDEN_INSERT_ODS_CHANGE Internal Trigger SPRPCHG Public DBLINK ODSSTG ODSSTG

  32. Materialized View Framework • Materialized View Refresh Groups • A Refresh Group is a grouping of materialized views to be refreshed at the same time. This ensures data is captured for each Mview refresh at a specific point in time • Delivered refresh group are broken into 2 groups per product schema • 1 group for Validation tables • 1 group for all other tables • Example: Refresh Groups for ODS General are: • ODS_REFGROUP_GENERAL • ODS_REFGROUP_GENERAL_VAL • Mviews in the delivered Refresh Groups can be reorganized using command line API calls from our delivered packages • An Mview can only exist in 1 Refresh Group

  33. Materialized View Framework • Materialized View Staging Collections • A Staging Collection allows you to group Refresh Groups together. • Staging Collections are stored as parameter records in MTVPARM under the INTERNAL_GROUP = STAGING REFRESH COLLECTION • Delivered Collections • 1 per product area • Each Collection contains the 2 delivered Refresh Groups per product • Example: Collection: REFRESH_GENERAL contains: • ODS_REFGROUP_GENERAL • ODS_REFGROUP_GENERAL_VAL

  34. Materialized View Framework • Materialized View Staging Collections • Since the refresh of the Mviews should happen prior to the regular ODS refresh, each Collection is delivered and set up to be run with the associated ODS ETL refresh of the Composite tables. • These 2 jobs are tied together in MTVPARM as shown below • Example: • Collection: REFRESH_GENERAL • ETL Refresh: REFRESH_GENERAL • When REFRESH_GENERAL is run, it will refresh the GENERAL Mviews and then refresh the GENERAL ODS Composite tables • REFRESH_ALL will refresh all of the MVIEWS first, then run the ODS Composite table refresh

  35. BI Architecture –8.4 Releases using Mviews BANNER (Source) ODS/EDW (Target) PRODUCT SCHEMAS STAGING SCHEMAS ODS SCHEMA EDW SCHEMA BASE TABLES COMPOSITE TABLE(S) BASE TABLES MVIEWS ODS ETL EDW ETL COMPOSITE VIEW MVIEW LOGS REPORTING VIEW CHANGE TABLE TABLE TRIGGER REPORTING TOOL(S)

  36. BI Architecture –8.4 Releases using Mviews BANNER (Source) ODS/EDW (Target) PRODUCT SCHEMAS STAGING SCHEMAS ODS SCHEMA EDW SCHEMA MVIEWs : INITIAL STAGING -- Creates Mview (w/ all data) and MV Log -- Run at install (from <product>_etl_installsql scripts -- Additional tables/schemas staged through Admin/UI as scheduled job (creates Control Report) -- Associates MV with Refresh Group REFRESH -- Run intermittently (as desired) to sync MVs with Banner changes -- Run through Admin/UI as scheduled job (generates CR) -- Linked to ODS Refresh ETL jobs so automatically run as initial step -- Can be run throughout the day to keep MVs in sync more often -- Uses REFRESH GROUPs (RGs) to streamline and sync data capture -- Two RGs per product delivered - can be restructured via OEM or APIs BASE TABLES COMPOSITE TABLE(S) BASE TABLES MVIEWS ODS ETL EDW ETL COMPOSITE VIEW MVIEW LOGS REPORTING VIEW CHANGE TABLE TABLE TRIGGER REPORTING TOOL(S)

  37. BI Architecture –8.4 Releases using Mviews BANNER (Source) ODS/EDW (Target) PRODUCT SCHEMAS STAGING SCHEMAS ODS SCHEMA EDW SCHEMA BASE TABLES COMPOSITE TABLE(S) BASE TABLES MVIEWS ODS ETL EDW ETL COMPOSITE VIEW MVIEW LOGS REPORTING VIEW CHANGE TABLE TABLE TRIGGER REPORTING TOOL(S)

  38. BI Architecture –8.4 Releases using Mviews BANNER (Source) ODS/EDW (Target) PRODUCT SCHEMAS STAGING SCHEMAS ODS SCHEMA EDW SCHEMA BASE TABLES COMPOSITE TABLE(S) BASE TABLES MVIEWS ODS ETL EDW ETL COMPOSITE VIEW ODS ETL: LOAD -- Completely loads ODS tables -- Run at Install time, or as needed to reload -- Run through Admin/UI as scheduled job -- Generates Control Report (tracking data changes) REFRESH -- Run intermittently (nightly) to sync ODS with Banner changes -- Run through Admin/UI as scheduled job -- Generates Control Report (tracking data changes) MVIEW LOGS REPORTING VIEW CHANGE TABLE TABLE TRIGGER REPORTING TOOL(S)

  39. BPRA 8.4 Overall Architecture Target Database = Staging Area, ODS, and EDW Source System Database Performance Management Applications Operational Staging Area* Operational Data Store Comp Tables Rep Views Legacy Legacy ETL (OWB) Recruiting & Admissions Performance Reporting Tools Banner Banner Replication using Oracle Streams or MViews ETL (OWB) Student Retention Performance D D Advance Advance F Advancement Performance D D OLAP Tools *Contains staging tables and all existing source objects for ODS (Change tables, triggers, Comp Views) Enterprise Data Warehouse

  40. BPRA 8.4 Overall Architecture Target Database = Staging Area, ODS, and EDW Source System Database Performance Management Applications Operational Staging Area* Operational Data Store Comp Tables Rep Views Legacy Legacy ETL (OWB) Recruiting & Admissions Performance Reporting Tools Banner Banner Replication using Oracle Streams or MViews ETL (OWB) Student Retention Performance D D Advance Advance F Advancement Performance D D OLAP Tools *Contains staging tables and all existing source objects for ODS (Change tables, triggers, Comp Views) Enterprise Data Warehouse

  41. Materialized View Framework • New tables and packages • MGBSTGE: • Houses all of the base tables to be staged using Materialized View or Oracle Streams. This table replaces existing table MGBSTRM in ODS 8.2/8.3. • MGKMVEW: • Contains the procedures that are used to stage tables as materialized views • MGKSSTG and MGKSTGU: • Provide the functionality to maintain and monitor the materialized views from the BPRA Administrative User Interface.

  42. ODS 8.3m Controlled Release Update

  43. 8.3m Materialized View Beta Testing • Testing Goals • Upgrade Testing • Upgrade from ODS 8.1 to ODS 8.3m • CDC Performance using Materialized Views • Gather metrics for mview staging performance • Gather metrics for mview refresh performance • Test to update 1,000 records in each Banner table • Test to update 10,000 records in each Banner table • Test to update 100,000 records in each Banner table • LOAD & REFRESH metrics 8.1 vs 8.3m/8.4m • Administrative Interface Functionality • Included adding/removing schemas & tables • Running various jobs including staging status report for mviews

  44. 8.3m Materialized View Beta Testing • Testing Goals • Data Validation • Running Reconciliation jobs for staging • Running Reconciliation jobs for ODS tables • Banner Upgrade Impacts • Performing a Banner upgrade to see impacts on mview refresh • Banner Production Impacts • Run major Banner processes (Payroll, etc) • Monitoring database impact • Cloning Banner & ODS • Materialized View Supplement Review

  45. 8.3m Materialized View Beta Testing • Materialized View Testing Results • Initial Staging

  46. 8.3m Materialized View Beta Testing • Materialized View Testing Results • Mview Refresh Testing with 10,000 updates per Banner table

  47. Thank You! Brian Large

More Related