Скачать презентацию COGNOS GOVT HE USERS GROUP Operational reporting Скачать презентацию COGNOS GOVT HE USERS GROUP Operational reporting

7d37a32248fa7748b2152fbc1bb01236.ppt

  • Количество слайдов: 24

COGNOS GOVT. & HE USERS GROUP Operational reporting using Cognos and Oracle’s Logical database COGNOS GOVT. & HE USERS GROUP Operational reporting using Cognos and Oracle’s Logical database technologies Presenter: Angela Hooper, Colorado Community College September, 18 th, 2009

Description This session addresses the gap in real-time operational reporting in Banner. CCCS has Description This session addresses the gap in real-time operational reporting in Banner. CCCS has leveraged Cognos, Oracle's Logical Standby database, ODS views and custom pl/sql procedures to provide an efficient and secure operational reporting environment. Discussion to include technical set up information, and ways that schools without high end report writing tools can use the core of this solution.

Session Rules of Etiquette Please turn off your cell phone/beeper If you must leave Session Rules of Etiquette Please turn off your cell phone/beeper If you must leave the session early, please do so as discretely as possible Please avoid side conversation during the presentation Thank you for your cooperation!

Introduction Angela Hooper – Manager of Business Intelligence and Data Warehousing Colorado Community College Introduction Angela Hooper – Manager of Business Intelligence and Data Warehousing Colorado Community College System (CCCS), Denver 13 colleges, 107, 000 annual enrollment 1 centralized banner 7. 4 installation VPD

Topics of Discussion CCCS Business need Business solution Technical answer to CCCS’ need Oracle Topics of Discussion CCCS Business need Business solution Technical answer to CCCS’ need Oracle LSB-DB Ref-Cursors in PL/SQL Cognos FWM setup Take home architecture points

CCCS Business Need Current reporting set up Home grown student data mart on 9 CCCS Business Need Current reporting set up Home grown student data mart on 9 hr refresh Custom job subs – strain on PROD Cognos front end Future ODS client (Dec 2008) Tasks Reduce reporting off of PROD Maintain real time data (HR and FIN) Control and audit security Don’t buy anything new

Business Solution – “Good” Practices Don’t report off of Production if you can help Business Solution – “Good” Practices Don’t report off of Production if you can help it Do a requirements document for ALL report requests. (example) Separate business logic from report writing tool Don’t self promote code

Technical Solution for CCCS Logical stand by reporting database New LSB schema for views Technical Solution for CCCS Logical stand by reporting database New LSB schema for views and procs Cognos framework model – one query subject per report or view

Technical Solution for CCCS Technical Solution for CCCS

Oracle LSB-DB Stand by database 10 g. R 2 Uses Oracle Data Guard with Oracle LSB-DB Stand by database 10 g. R 2 Uses Oracle Data Guard with SQL Apply The main advantage of logical database is that the data structure of the replicated may be different than the master database. April Simms SUU – Seta Dallas 2007

Ref-Cursor – What is it? The ref cursor is a Ref-Cursor – What is it? The ref cursor is a "pointer" data type Allow you to quickly reference any cursor result (usually an internal PL/SQL table array) Data values are kept in RAM Can be used multiple time in one Proc http: //www. dba-oracle. com/t_pl_sql_ref_cursor_benefits. htm

Ref-Cursor – How it helps? Returns a large dataset – for reporting Return Specific Ref-Cursor – How it helps? Returns a large dataset – for reporting Return Specific SQL to users Control joins in DB not report writer PIN procedures with ref cursor output in memory

Ref-Cursor – Example? procedure PROC_SALARY_VERIFICATION (p_cursor out cursor. Type, p_vpdi in varchar 2, p_effect_date Ref-Cursor – Example? procedure PROC_SALARY_VERIFICATION (p_cursor out cursor. Type, p_vpdi in varchar 2, p_effect_date in date) is BEGIN open p_cursor for select spriden_id … from spriden, nbrjobs, where spriden_pidm = nbrjobs_pidm and spriden_change_ind is null … and pebempl_status <> 'T' and nbrjobs_effective_date = trunc(p_effect_date) and (pebempl_vpdi_code = p_vpdi or pebempl_vpdi_code like p_vpdi); END PROC_SALARY_VERIFICATION;

Ref-Cursor – More Info Devshed: http: //www. devshed. com/c/a/Oracle/Working-with. REF-CURSOR-in-PL-SQL/ Burelson: http: //www. fasttrack. Ref-Cursor – More Info Devshed: http: //www. devshed. com/c/a/Oracle/Working-with. REF-CURSOR-in-PL-SQL/ Burelson: http: //www. fasttrack. cc/t_easyoracle_pl_sql_ref_cursors. htm

Returning ref-cursors to report writer tool Stored procedure output - just like a table Returning ref-cursors to report writer tool Stored procedure output - just like a table Access Example Crystal Example Pass thru query (ODBC) Enable data connection to packages or procs Cognos Example FWM example

Cognos FWM 1 of 4 Steps for set up 1. Compile proc in database Cognos FWM 1 of 4 Steps for set up 1. Compile proc in database 2. Create directory connection object to LSB in cognos connection 3. Create new FWM model with LSB 4. Set up name spaces for data and business areas

Cognos FWM 2 of 4 5. Create new query subject in database layer Cognos FWM 2 of 4 5. Create new query subject in database layer

Cognos FWM 3 of 4 6. Create business layer QS Cognos FWM 3 of 4 6. Create business layer QS

Cognos FWM 4 of 4 7. 8. 9. 10. Create package. Include only business Cognos FWM 4 of 4 7. 8. 9. 10. Create package. Include only business layer query subjects Publish package Grant security Build report front end

Technical Solution for CCCS Technical Solution for CCCS

Added Value – – Portability, & disaster recovery for reporting Quicker drop down parameters Added Value – – Portability, & disaster recovery for reporting Quicker drop down parameters in Cognos Security modifications allowed Mgr to promote developer code PL/SQL skills spread out. Cognos skills kept light.

Take Home Architecture – – Try setting up LSB Create “Views” with Ref cursors Take Home Architecture – – Try setting up LSB Create “Views” with Ref cursors to protect Adhoc users Create Procs with Ref Cursors for frequently requested SQL – doubles as code repository Invest on learning the tables and data, NOT on a report writer tool expert.

Questions and Answers Questions and Answers

Presenter Information Angela H. Hooper Colorado Community College System Manager of Business Intelligence and Presenter Information Angela H. Hooper Colorado Community College System Manager of Business Intelligence and Data Warehousing 1059 Alton Way Denver, CO 80230 (720) 858 -2710 angela. [email protected] edu http: //angela. h 00 per. com/setadallas 2008