Operational Data Store(ODS) Functional Training

1. Operational Data Store(ODS)Functional Training Youngstown State University August 8, 2006 – August 10, 2006 R. Joanne Keys SCT Enterprise Information Management

2. The Objective The objective of this training is to provide the participants with an understanding of Finance and related information within the ODS, and enough introductory experience using it so that they are able to successfully support ad hoc operational reporting needs at Youngstown State University as appropriate from the ODS.

3. 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

4. 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: • Confirm the associated issues and requirements • Identify the information needed, including reorganizing or manipulating current data • Identify requirements for presentation • Create it…. • Repeat Step 2 • Independent Work

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

6. 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 – The people who have a broad knowledge of the university and the ability to make or escalate key decisions regarding reporting on campus • IT Representative

7. 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

8. 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

9. 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?

10. Why Reporting? It’s asking questions. - Operations - Management - Research and PlanningIt’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.

11. 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?

12. 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

13. Evaluate success and plan! Detail organized and stored for processes and multiple events I need to compare generally and quickly! Summary information for multiple events Tell them,and compare! Detailed information organized and stored for multiple events Don’t move the target! “Current” snapshot Transaction system: Current, detailed, transaction data What’s going on? Tell them! Answering Questions

14. Delivering the Answers Detail organized serially and longitudinally Strategic Enrollment Management Summary information organized serially Enterprise Data Warehouse Detailed information organized serially Operational Data Store “Current” snapshot Transaction system: Current, detailed, transaction data Baseline reports Object:Access Ad hoc reports

15. Maturing the Maturity Model • Operational Data Store • End User Reporting Enterprise Data Warehouse Institutional Performance Management • Banner Reporting • Standard Reports • OA: Views • Real Time Reports • Batch updates • Nested Reports • Operational Data Store • End User & • IT Reporting • Banner Reporting • Standard Reports • OA: Views • ODBC Reporting Tools • Institutional • Performance • Management • Process Improvement • Dashboard/Scorecard • Analytical Applications • Analytical • Reporting • Historical Trends • Forecasting • Self-Service • Reporting • Ad-Hoc Time Custom Reports BaselineReports Level 1 Level 2 Level 3 Level 4 Level 5 Institution Adoption

16. SCT Institutional Performance Management ABC Dashboards KPI’s Performance Dashboards Balanced Scorecard Analytical Applications Reporting Tools Self Service Data Marts Meta Data Current Business Intelligence Architecture Future Business Intelligence Architecture Enterprise Data Warehouse Operational Data Store Legacy Enterprise Data Warehouse ETL ETL Operational Data Store Banner Other OLAP Tool

17. 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

18. 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

19. 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.

20. 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.

21. Related Documentation • Banner to Operational Data Store User Guide June 2006 • Operational Data Store Release Guide June 2006 • Operational Data Store Handbook June 2006 • GTVSDAX Handbook

22. ODS Online Help

24. 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

25. ODS Load Process

26. 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.

27. ODS Incremental Refresh

28. 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.

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

30. ODS Components Review • Banner tables as they exist today. • Not all tables used. PERSON TRIGGER CHANGE TABLE ID TRIGGER PERSON COMPOSITE VIEW Banner

31. ODS Components Review • 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 PERSON TRIGGER CHANGE TABLE ID TRIGGER PERSON COMPOSITE VIEW Banner

32. ODS Components Review • Maintains information on what records/tables have been changed, inserted or deleted. • One change table per logical key group/module. • Emptied after every refresh. PERSON TRIGGER CHANGE TABLE ID TRIGGER PERSON COMPOSITE VIEW Banner

33. ODS Components Review • 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 PERSON TRIGGER CHANGE TABLE ID TRIGGER PERSON COMPOSITE VIEW Banner

34. ODS Components Review PERSON COMPOSITE TABLE PERSON BASE VIEW • 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 PL/SQL ETL PRESEN- TATION VIEWS O:A VIEWS PL/SQL DELETE ODS

35. ODS Components Review PERSON COMPOSITE TABLE PERSON BASE VIEW • 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 PL/SQL ETL PRESEN- TATION VIEWS O:A VIEWS PL/SQL DELETE ODS

36. ODS Components Review PERSON COMPOSITE TABLE PERSON BASE VIEW • View of the corresponding composite table. • Elements match PL/SQL ETL PRESEN- TATION VIEWS O:A VIEWS PL/SQL DELETE ODS

37. ODS Components Review PERSON COMPOSITE TABLE PERSON BASE VIEW • Primary views used for reporting from the ODS • The O:A views match the O:A views as delivered with Banner. PL/SQL ETL REPORTING VIEWS O:A VIEWS PL/SQL DELETE ODS

38. PERSON COMPOSITE TABLE PERSON BASE VIEW • Primary views used for reporting from the ODS • The O:A views match the O:A views as delivered with Banner and version 1 of the datamart. PERSON TRIGGER CHANGE TABLE PL/SQL ETL ID PRESEN- TATION VIEWS O:A VIEWS TRIGGER PERSON COMPOSITE VIEW PL/SQL DELETE Banner ODS 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 Web Administration

39. 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

40. 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

41. 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.

42. 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

43. 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

44. 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

45. Operational Data Store Administration • Place where you set up and maintain the ODS • Primarily a technical responsibility BUT users be aware of

47. 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.