COGNOS GOVT.

1
COGNOS GOVT. HE USERS GROUP

• Operational reporting using Cognos and Oracles
  Logical database technologies
• Presenter Angela Hooper, Colorado Community
  College
• September, 18th, 2009

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

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

4
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

5
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

6
CCCS Business Need

• Current reporting set up
• Home grown student data mart on 9hr 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
• Dont buy anything new ?

7
Business Solution Good Practices

• Dont 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
• Dont self promote code

8
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

9
Technical Solution for CCCS
10
Oracle LSB-DB

• Stand by database 10gR2
• 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

11
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_bene
fits.htm
12
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

13
Ref-Cursor Example?
procedure PROC_SALARY_VERIFICATION (p_cursor out
cursorType,
p_vpdi in varchar2,
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_empl_status ltgt 'T' and
nbrjobs_status ltgt 'T' and nbrjobs_effective_dat
e trunc(p_effect_date) and
(pebempl_vpdi_code p_vpdi or
pebempl_vpdi_code like p_vpdi) END
PROC_SALARY_VERIFICATION
14
Ref-Cursor More Info

• Devshed http//www.devshed.com/c/a/Oracle/Working
  -with-REF-CURSOR-in-PL-SQL/
• Burelson http//www.fast-track.cc/t_easyoracle_pl
  _sql_ref_cursors.htm

15
Returning ref-cursors to report writer tool

• Stored procedure output - just like a table
• Access Example
• Pass thru query (ODBC)
• Crystal Example
• Enable data connection to packages or procs
• Cognos Example
• FWM example

16
Cognos FWM 1 of 4

• Steps for set up
• Compile proc in database
• Create directory connection object to LSB in
  cognos connection
• Create new FWM model with LSB
• Set up name spaces for data and business areas

17
Cognos FWM 2 of 4

1. Create new query subject in database layer

18
Cognos FWM 3 of 4

1. Create business layer QS

19
Cognos FWM 4 of 4

• Create package. Include only business layer query
  subjects
• Publish package
• Grant security
• Build report front end

20
Technical Solution for CCCS
21
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.

22
Take Home Architecture

• Try setting up LSB
• Create Views with Ref cursors to protect Ad-hoc
  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.

23
Questions and Answers
24
Presenter Information

• Angela H.Hooper
• Colorado Community College System
• Manager of Business Intelligence and Data
  Warehousing
• 1059 Alton Way
• Denver, CO 80230
• [phone]
• angela.hooper_at_cccs.edu
• http//angela.h00per.com/setadallas2008