Slide 1

Transcript Slide 1

Operational Data Store(ODS) Functional Training

Youngstown State University August 8, 2006 – August 10, 2006 R. Joanne Keys

SCT Enterprise Information Management

www.sungardhe.com

The Objective

provide the participants with an related information within the ODS, and enough introductory are able to successfully support ad hoc operational reporting the ODS.

www.sungardhe.com

Agenda – For Today

          Introductions Roles & Responsibilities Developing a Reporting Strategy What is the Operational Data Store Why use the Operational Data Store ODS Architecture ODS Administration Data Models Report Templates Questions and Concerns www.sungardhe.com

Agenda: The Rest of the Week

The ‘Just Getting It Done’ Workshop Identify some reports we want or need Choose a report for group study, and: a.

Confirm the associated issues and requirements Identify the information needed, including reorganizing or manipulating current data c.

Identify requirements for presentation Create it….

Repeat Step 2 Independent Work www.sungardhe.com

Introductions

   

Name Responsibility at University Future Involvement with the Operational Data Store What you hope to get out of the class

www.sungardhe.com

Your Campus Reporting Group



Do you have a plan for Reporting



Do you have a Core Reporting Group



Who belongs in the group:

 ODS Administrator     Reporting Tool Administrator Key Report Writers Average Users Key Administrators on campus – The people who have a broad knowledge of the university and the ability to make or escalate key decisions regarding reporting  IT Representative www.sungardhe.com

Roles & Responsibilities



Core Reporting Group

   Responsible for managing expectations of reporting community Makes recommendations on access Assist IT group with decision making involving end-users and executives •

ODS Administrator

    Responsible for managing the datamart Reviews logs and checks for errors May be responsible for freezing views Is usually part of the IT group www.sungardhe.com

Roles & Responsibilities



Organizational Experts

 Who has responsibility  Who to call to assist with designing new reports 

Department Training

 Who will be responsible  When will training occur www.sungardhe.com

Developing a Reporting Strategy

    

Who are the users?

   Power users – ad hoc Business users - managed Casual users – directed  IT – omnipotent

Why do we need this report?

What is the value it adds to the organization?

Where is the data for the report coming from?

What tools do they need?

www.sungardhe.com

Why Reporting?

It’s asking questions.

- Operations - Management - Research and Planning It’s getting the information ready for the questions.

And then answering them.

It’s putting the right ‘stuff’ together at the right time and for the right purpose.

www.sungardhe.com

Questions in Search of Answers



What is the average gift by reunion class or constituent type?



How many constituents are donors?



Tell the right people the right things. (Giving, Active/Inactive, Scholarship Awards, Activities.)



How many constituents do I have in a particular geographic region?



What trends do we see in giving?

www.sungardhe.com

Basics for Effective Reporting



Questions focused on meaningful business issues yield the most useful answers



Answers are more easily developed when the supporting information is directly related to the business question: Content, focus, organization, presentation.



The devil, and dividend, are in the details that support consistent, easily replicable results from appropriate information

www.sungardhe.com

Answering Questions

Evaluate success and plan!

I need to compare generally and quickly!

Tell them, and compare!

Don’t move the target!

What’s going on?

Tell them!

Detail organized and stored for processes and multiple events Summary information for multiple events Detailed information organized and stored for multiple events “Current” snapshot Transaction system: Current, detailed, transaction data www.sungardhe.com

Delivering the Answers

Detail organized serially and longitudinally Summary information organized serially Detailed information organized serially “Current” snapshot Transaction system: Current, detailed, transaction data

Strategic Enrollment Management Enterprise Data Warehouse Operational Data Store Baseline reports Object:Access Ad hoc reports

www.sungardhe.com

Maturing the Maturity Model

Banner Reporting

• •

Standard Reports

• •

OA: Views

• •

Real Time Reports

•

Batch updates Store Data Store

•End User & •IT Reporting •

Nested Reports Enterprise Data Warehouse Institutional Performance Management Self-Service Reporting

•

Ad-Hoc Analytical Reporting

•

Historical Trends

•

Forecasting Institutional Performance Management

•

Process Improvement

•

Dashboard/Scorecard

•

Analytical Applications Custom Reports Baseline Reports Level 1 Level 2 Level 3 Level 4 Institution Adoption

Legacy Banner Other

Operational Data Store

Self Service ETL Meta Data Data Marts Operational Data Store Reporting Tools

Enterprise Data Warehouse

ABC Dashboards

SCT Institutional Performance Management

KPI’s ETL Enterprise Data Warehouse OLAP Tool Performance Dashboards Balanced Scorecard Analytical Applications www.sungardhe.com

SCT Operational Data Store Functions



Simplifies information access



Provides timely information to support all levels of management



Improves information access performance



Provides access to historical and summarized information



Data refresh occurs at your specified interval

www.sungardhe.com

SCT Operational Data Store Functions

    

Ensures consistent reporting results by providing a common data source and common business concepts



Data models and reports can be tailored to department-specific needs Uses Human Resources, Finance, and Student product security Allows use of web-based reporting tools with graphical capabilities The ODS Administration component is web based Allows you to share solutions in an open environment

www.sungardhe.com

What is the Operational Data Store

     

ODS tables are constructed specifically for reporting ODS resides on a separate reporting Server ODS is populated from the source system (s) using composite views ODS has denormalized tables called composite tables.

ODS reporting views provide access to the data

 Security   Display rules Used to create operational and ad hoc reports

ODS tables and reporting views were constructed with the business needs of higher education administration in mind.

www.sungardhe.com

Why use an Operational Data Store



Ability to produce reports without the overhead of a transactional system.



Built to address reporting queries not for efficiency of data capture.



Provides for the freeze of data to accommodate point in time reporting.



Most upgrades to the administrative system do not affect the ODS.

www.sungardhe.com

ODS Online Help

www.sungardhe.com

Operational Data Store Architecture



ODS Initial Load Process – Load all rows of the relevant data into the ODS



Incremental Refresh – Refresh the ODS with only the information that has changed since the last ODS refresh

www.sungardhe.com

ODS Load Process

www.sungardhe.com

ODS Load Process Flow



Load job submitted to execute LOAD scripts for all processes identified in the Job Submission Menu



PL/SQL script reads views via DBLINK which accesses the ODSMGR schema in Banner



Data is written to ODS composite tables on a separate database



When all jobs have completed, the log file is verified for successful load and completion of all jobs.

www.sungardhe.com

ODS Incremental Refresh

www.sungardhe.com

ODS Incremental Refresh Process Flow



Refresh job submitted to execute DELETE and UPDATE scripts for all identified processes.



Reads and/or updates of Banner database are performed via DBLINLK which access Banner ODSMGR user schema



DELETE processes run before UPDATES to set the update flag on records in Banner Change Tables



Data is written to, or deleted from, the ODS composite tables on separate database.



When all jobs have completed, the log file is verified for successful load and completion of all jobs.

www.sungardhe.com

ODS Architecture

Banner Operational Data Store Object Access View Object Access Views Person View Gift View Pledge View Constituent View Degree View Annual Giving Composite Views OWB Person Gift Pledge Constituent Degree Annual Giving Security and Display Rules Degree Table Person Table Gift Table Annual Giving Table Pledge Table Constituent Table Composite Tables www.sungardhe.com

ODS Components Review

PERSON TRIGGER  Banner tables as they exist today.

 Not all tables used .

CHANGE TABLE TRIGGER ID PERSON COMPOSITE VIEW

Banner

www.sungardhe.com

ODS Components Review

PERSON TRIGGER ID TRIGGER CHANGE TABLE PERSON COMPOSITE VIEW  New Create/Update/Delete triggers placed on the Banner tables  One trigger per table used in the ODS  Updates/Inserts records into the change table(s)  Delivered with Banner releases starting 01/31/2004

Banner

www.sungardhe.com

ODS Components Review

PERSON TRIGGER CHANGE TABLE  Maintains information on what records/tables have been changed, inserted or deleted.

 One change table per logical key group/module.

 Emptied after every refresh.

TRIGGER ID PERSON COMPOSITE VIEW

Banner

www.sungardhe.com

ODS Components Review

PERSON TRIGGER ID TRIGGER CHANGE TABLE PERSON COMPOSITE VIEW  View of one to many Banner tables  Built for incremental refresh but can be used for reporting.

 Joined with the change tables when processing the incremental refresh.

 Matches the similar table on the ODS side

Banner

www.sungardhe.com

ODS Components Review

 PL/SQL scripts built and maintained by Oracle Warehouse Builder (OWB)  Two or Three per target table (Load, Delete and Update)  Ran during the initial load and incremental refresh PERSON COMPOSITE TABLE PERSON BASE VIEW PL/SQL ETL PL/SQL DELETE

ODS

PRESEN TATION VIEWS O:A VIEWS www.sungardhe.com

ODS Components Review

 Physical tables located in the ODS.

 Elements match those of the corresponding Composite Views on the Banner side.

 Built during the initial install of the ODS PERSON COMPOSITE TABLE PERSON BASE VIEW PL/SQL ETL PL/SQL DELETE

ODS

PRESEN TATION VIEWS O:A VIEWS www.sungardhe.com

ODS Components Review

PERSON COMPOSITE TABLE  View of the corresponding composite table.

 Elements match PERSON BASE VIEW PL/SQL ETL PL/SQL DELETE

ODS

PRESEN TATION VIEWS O:A VIEWS www.sungardhe.com

ODS Components Review

 Primary views used for reporting from the ODS  The O:A views match the O:A views as delivered with Banner.

PERSON COMPOSITE TABLE PERSON BASE VIEW PL/SQL ETL REPORTING VIEWS O:A VIEWS PL/SQL DELETE

ODS

www.sungardhe.com

ODS Components Review

 View the ODS metadata  Schedule the incremental refresh, reload  Schedule a table freeze  Review logs from the incremental refresh/load  Update user security for fine grained access  Primary views used for reporting from the ODS  PERSON The O:A views match the O:A views as delivered with Banner and version 1 of the datamart.

ID TRIGGER

Banner

CHANGE TABLE PERSON COMPOSITE VIEW PERSON COMPOSITE TABLE PL/SQL ETL PL/SQL DELETE

ODS

PERSON BASE VIEW PRESEN TATION VIEWS O:A VIEWS Web Administration www.sungardhe.com

ODS Components Review - Administration



Administration

 Web based administrative interface using SCT’s Web Tailor.

 Used for execution and monitoring of ETL processes  Preferences and Security  Information Access Options   Information Access Meta Data New Web Tailor Administration 

Some end users need to use this

www.sungardhe.com

SCT Operational Data Store Design



Oracle Warehouse Builder

 Allows you to design a complete logical model of your warehouse  Helps to plan how to EXTRACT data from the source, TRANSFORM the data, and configure the data for LOADING into the data warehouse.  That’s what we call ETL – Extract, Transform, Load  Let the IT people worry with this www.sungardhe.com

SCT Operational Data Store Design



Report Templates

 ODS 2.0 provides new report templates using Oracle Discoverer reporting tool written specifically against the ODS data model and reporting views within the ODS, rather than the Object:Access structures 

Enterprise business area within Oracle Discoverer with join conditions established.

 

Cognos ReportNet Business views added in the 2.2 release Included the Object:Access data structures as part of the ODS to allow for data structure compatibility with the previous releases of the solution.

www.sungardhe.com

SCT Operational Data Store Design



Tools with “Value Added”

 Cognos Impromptu Version 7/ Cognos ReportNet 1.1

    Brio Query Designer 6.6

Information Builders WebFOCUS Version 5.2.3 and higher Microsoft Access 2000 Oracle Discoverer Version 9.0.39.02

www.sungardhe.com

ODS Definitions

       

OWB – Oracle Warehouse Builder ODS – Operational Data Store (formerly EDM) EDW – Enterprise Data Warehouse EDM – Enterprise Datamart (now ODS) OLTP – Online Transaction Processing ETL – Extract, Transform and Load Source – Where the data is coming from Target – Where the data is going to

www.sungardhe.com

ODS Naming Conventions



SCT Banner

  Composite view – Ax_name Object:Access view – Ax_name 

SCT Operational Data Store

    Database tables – MxT_name Reporting views – English name reports Subset of Reporting views – English name_SLOT Object:Access view – Ax_name www.sungardhe.com

Operational Data Store Administration



Place where you set up and maintain the ODS

 Primarily a technical responsibility BUT users be aware of www.sungardhe.com

www.sungardhe.com

User Accounts and Security



Two types of users

 Administrative Users – to set up and maintain the ODS  Oracle Users – require an Oracle user account so that they can use a reporting tool to access the ODS and build reports.

www.sungardhe.com

Setting Up Data Display Rules

   

Control which data values to retrieve and how to display various types of data Example – test scores or address types.

Set up Display rules at the “Institution” level Stored in the MGRSDAX table which has predefined delivered values



Matches external (user-defined) codes with internal (SCT-defined) codes



Need to review all delivered Display Rules and edit them to reflect your institution’s specific code values used by your ODS reporting users.

www.sungardhe.com

MGRSDAX fields

   

Internal Group – don’t change Internal Code 1 – don’t change External Code – You need to change Internal Code Sequence Number

www.sungardhe.com

GTVSDAX in Banner



MGRSDAX table in the ODS is a copy of the Crosswalk Validation Table (GTVSDAX) in Banner.

 

GTVSDAX controls values a report will retrieve from Banner.

MGRSDAX specifies Display Rules that define which code values will be loaded into the ODS



Each Display Rule in the ODS matches a defined concept (or set of values) in GTVSDAX.



ODS rules and GTVSDAX concepts use the same Internal Group and Internal Code values.

www.sungardhe.com

Setting Up Security Rules for Oracle Users



Determine Data Security Requirements



Set up and maintain security rules

 Full Access to all data in the ODS  Full Access to all data at the level of the Organizational Dimension   Full Access to all data at an element level Restricted access to data at the element level based on a list or range of values for a specific data element www.sungardhe.com

Security Rule



Organization Dimension

 Organization where rule belongs  Academic, Course and Academic, Financial, Workforce 

Table – Where you will secure the data



Rule Type – LIST or RANGE of values



Column – define one or two columns to secure data

www.sungardhe.com

Loading the ODS



Makes use of OWB mappings to associate source elements to corresponding element in ODS.

  LOAD - Load REFRESH – Update or Delete 

Schedule a process –

 Run a job  Calls the related mappings and loads or updates data define by them www.sungardhe.com

Why is my Data out of Sync



Currency of data in the ODS

 Timing of a query and when ODS refreshed 

Display Rule Definitions

 Rules may differ between two systems 

Security Rule Definitions

 Rules may differ between two systems www.sungardhe.com

When to Load and Update Data



Initial Load



Refresh data in ODS on a regular basis

 Nightly, Weekly ?



Update specific area as needed when there is a data change in source system.

www.sungardhe.com

Composite Tables and Slotted Tables



Composite Table

 Include the main data that is extracted from source system and stored in ODS 

Slotted Table

 Store data values for a specific code related to a base table  Optimizes the speed of queries 

Need to keep these synchronized

www.sungardhe.com

Updating or Freezing ODS data on a Recurring Basis



Specify a job to be run on a recurring basis



Interval

 Execution of job follows the previous run by a specific time interval  Job executes on specific dates and times.



See Handbook (Chapter 2) for list of Interval values

www.sungardhe.com

Freezing ODS Data



Allows the saving of snapshots of data tables and/or views at a point-in-time



Need to come up with a naming convention for frozen tables and views



Freeze multiple related tables/views at once

 Create list of tables/views  Schedule a process to freeze them all 

Freeze single table or view

www.sungardhe.com

Freezing ODS Data



Freeze Data Maintenance – Allows for creation of lists of related tables and views for freezing.

   Specify the tables/views Define a history table name for each Then schedule a process to freeze the tables www.sungardhe.com

Viewing Control Reports



When process runs a control report is created to show details of status of the process



Make sure someone is viewing these immediately each day



View Messages

 View individual error messages   Select lines of report using Filter option Save report to a CSV file www.sungardhe.com

Set Up Process Parameters



LET IT WORRY WITH THIS

www.sungardhe.com

Meta Data Object Types



Target View – ODS views that join related information. Used for reporting.



Source Table – Database tables from administrative system



Source Function – Functions that use data from administrative system source table to create new data that will be stored in the ODS.

www.sungardhe.com

Data Models



Shows the transition of each data element from its origin in the database through its location in the ODS composite tables and views.



Close to 300 reporting views containing data across seven subject areas applicable to higher education: Accounts Receivable, Advancement, Common, Finance, Financial Aid, Human Resourses and Student.

www.sungardhe.com

Data Models



Chapter 3 of ODS Administration Guide

 Entity Relationship Diagrams (ERD)   A diagram that represents the data model Shows the logical relationships between the reporting views within an ODS business concept.

www.sungardhe.com

ERD Relationship Legend

www.sungardhe.com

 

Data Models

Common – 51 Reporting and Slotted Views Entity Relationship Diagrams (ERD)

  Event Institution  Organization Entity     Person Demographic Person Role Person Supplemental Relationship www.sungardhe.com

Common Views

Activity Address_By_Rule Address_Preferred Certification_Slot Communication (Mailing) Contact Cross_Reference_Slot Employment_History Geographic_Region Hold Address Address_Current Certification Combined_Academic_Outcome Communication_Slot Contact_Slot Current_Employment Event Geographic_Region_Institution Hold_Slot

www.sungardhe.com

Common Views

Institution Interest_Slot Mail_Slot Medical_Information_Slot Organization_Entity_Address Person Person_Detail Person_Sensitive Previous_Education Interest Mail Medical_Information Organization_Entity Past_Employment_Slot Person_Address Person_International Person_Veteran Previous_Education_Slot

www.sungardhe.com

Common Views

Relationship Secondary_School_Subject Skill_Slot Telephone_Preferred Test_Slot Visa Year_Type_Definition Salutation Skill Telephone Test Validation Visa_Current

www.sungardhe.com

 

Data Models

Advancement – 47 Reporting and Slotted Views Erwin Diagram

 Advancement Prospect         Advancement Rating Annual Giving Campaign Giving History Constituent Designation Giving History Gift Organizational Constituent Pledge www.sungardhe.com

Advancement Views

Advancement_Rating Advancement_Rating_Slot Annual_Giving Campaign_Giving_History Constituent_Contact Constituent_Plan Degree Designation_Giving_History Donor_Category_Slot Exclusion_Slot Annual_Giving_Slot Constituent Constituent_Entity Constituent_Staff_Assign Degree_Slot Donor_Category Exclusion Funding_Interest

www.sungardhe.com

Advancement Views

Funding_Interest_Slot Gift_Associated_Entity Gift_Matching Gift_Memo Gift_Society Gift_Transaction Membership_Interest Organization_Contact Pledge Pledge_Matching Gift Gift_Auxiliary Gift_Matching_Transaction Gift_Multiple Gift_Society_Slot Membership Orgnaiztional_Constituent Organization_Contact_Slot Pledge_Installment Pledge_Transaction

www.sungardhe.com

100

Advancement Views

Prospect_Info Solicitation Special_Activity_Slot Special_Activity_Year_Slot Special_Purpose_Slot Prospect_Proposal Special_Activity Special_Activity_Year Special_Purpose_Group

www.sungardhe.com

101

www.sungardhe.com

102

www.sungardhe.com

103

www.sungardhe.com

104

www.sungardhe.com

105

www.sungardhe.com

106

www.sungardhe.com

107

www.sungardhe.com

108

www.sungardhe.com

109

www.sungardhe.com

110

Address Reporting Views



Address information is contained in the following views for a person:

  Person Person_Address  Address    Address_By_Rule Address_Current Address_Preferred www.sungardhe.com

111

Address Reporting Views



Person View

 Contains the Preferred Address of the person as set by the preferred address of the institution  Preferred Address of the institution is set in the GTVSDAX table PREFADDR www.sungardhe.com

112

Address Reporting Views



Person_Address View

 Used to support correspondence by providing a mailing address to a report or print a mailing label.

  Deceased people will not appear in this view.

Organizations and companies that are active will be selected for this view.

www.sungardhe.com

113

Address Reporting Views



Address View

 All the addresses in the administrative system   Start Date and Stop Date Address Status Indicator  Active or Inactive    Geographic Region Count Address Type and Address Number Preferred Address Indicator www.sungardhe.com

114

Address Reporting Views



Address Current View

 All active addresses in the administrative system    Geographic Region Count Address Type and Address Number Preferred Address Indicator www.sungardhe.com

115

Address Reporting Views



Address_By_Rule

 Addresses based on the display rule type      One per person based on the display rule Only current active addresses Geographic Region Count Address Type and Address Number Preferred Address Indicator www.sungardhe.com

116

Address Reporting Views



Address_Preferred

 Addresses based on the Preferred Address type of the entity     Only current active addresses Geographic Region Count Address Type and Address Number Preferred Address Indicator www.sungardhe.com

117

Report Templates



The Report Templates are just that “TEMPLATES”. Use them as a starting point.



Reports against the new Reporting views delivered with Oracle Discoverer.



Delivered the Business Area for the End User Layer (EUL) in Oracle Discoverer



Delivered the Data Model for Cognos ReportNet



Encourage clients to use common data model for operational and ad hoc reporting



Reports against the Object:Access views in 4 of the tools.



For complete list of reports by application refer to ODS Handbook Chapter 4.

www.sungardhe.com

118

Layout of Report Templates

   

Report No. and Name

 Ax01 – Name

Description – Short Description Parameters Reporting Area

www.sungardhe.com

119

Advancement Reports

www.sungardhe.com

120

GTVSDAX – MGRSDAX



Advancement

   Alumaddr – Address Alumxref – Cross Reference Alumexrs – External Source            Alumfund – Funding Alumdcst – Gift Society Alummail – Mail Alumproj – Project Alumrtgt – Rating Alumocon – Secondary Contact Alumprtp – Special Purpose Alumstft – Staff Assignment Alumphone – Telephone Alumvipc – Variable Purpose Alumexcl – Camp Exclusion, Desg Exclusion, Exclusion www.sungardhe.com

121

Helpful Datamart information



SCT Banner Composite View owner is BANINST1



OWB Repository owner is ODSREP



OWB User is RUNUSER



ODS Owner is ODSMGR



ODSEUL owns the report templates

www.sungardhe.com

122

Questions? Suggestions? Concerns?

www.sungardhe.com

123

Thank You!

R. Joanne Keys [email protected]

www.sungardhe.com

124

Slide 1

Transcript Slide 1

Operational Data Store(ODS) Functional Training

The Objective

Agenda – For Today

Agenda: The Rest of the Week

Introductions

Your Campus Reporting Group

Roles & Responsibilities

Roles & Responsibilities

Developing a Reporting Strategy

Why Reporting?

Questions in Search of Answers

Basics for Effective Reporting

Answering Questions

Delivering the Answers

Maturing the Maturity Model

SCT Operational Data Store Functions

SCT Operational Data Store Functions

What is the Operational Data Store

Why use an Operational Data Store

Related Documentation

ODS Online Help

Operational Data Store Architecture

ODS Load Process

ODS Load Process Flow

ODS Incremental Refresh

ODS Incremental Refresh Process Flow

ODS Architecture

ODS Components Review

ODS Components Review

ODS Components Review

ODS Components Review

ODS Components Review

ODS Components Review

ODS Components Review

ODS Components Review

ODS Components Review

ODS Components Review - Administration

SCT Operational Data Store Design

SCT Operational Data Store Design

SCT Operational Data Store Design

ODS Definitions

ODS Naming Conventions

Operational Data Store Administration

User Accounts and Security

Setting Up Data Display Rules

MGRSDAX fields

GTVSDAX in Banner

Setting Up Security Rules for Oracle Users

Security Rule

Loading the ODS

Why is my Data out of Sync

When to Load and Update Data

Composite Tables and Slotted Tables

Updating or Freezing ODS data on a Recurring Basis

Freezing ODS Data

Freezing ODS Data

Viewing Control Reports

Set Up Process Parameters

Meta Data Object Types

Data Models

Data Models

ERD Relationship Legend

Data Models

Common Views

Common Views

Common Views

Data Models

Advancement Views

Advancement Views

Advancement Views

Address Reporting Views

Address Reporting Views

Address Reporting Views

Address Reporting Views

Address Reporting Views

Address Reporting Views

Address Reporting Views

Report Templates