Transcript Operational reporting using Cognos and Oracle's Logical

COGNOS GOVT. & HE
USERS GROUP
Operational reporting using Cognos and
Oracle’s Logical database technologies
Presenter: Angela Hooper, Colorado Community College
September, 18th, 2009
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 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 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 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 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
 Don’t buy anything new 

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 and procs
Cognos framework model – one query subject per
report or view
Technical Solution for CCCS
Operational Reporting Database
PKG_HR
REPORTS Schema
owned package for
HR related reports
and functions
PROD
PROD_LSB
PKG_STU
REPORTS Schema
owned package for
Student related reports
and functions
FWM
COGNOS
PKG_FIN
REPORTS Schema
owned package for
Finance related reports
and functions
Report Requests
DataGuard
ODSMGR.VIEWS
ODSMGR schema
objects from PROD
`
USER
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
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 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 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 <> 'T'
and nbrjobs_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-withREF-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
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
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 3 of 4
6.
Create business layer QS
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
Operational Reporting Database
PKG_HR
REPORTS Schema
owned package for
HR related reports
and functions
PROD
PROD_LSB
PKG_STU
REPORTS Schema
owned package for
Student related reports
and functions
FWM
COGNOS
PKG_FIN
REPORTS Schema
owned package for
Finance related reports
and functions
Report Requests
DataGuard
ODSMGR.VIEWS
ODSMGR schema
objects from PROD
`
USER
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 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
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
[email protected]
http://angela.h00per.com/setadallas2008