COGNOS GOVT. - PowerPoint PPT Presentation

Loading...

PPT – COGNOS GOVT. PowerPoint presentation | free to view - id: 733557-NGMwN



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

COGNOS GOVT.

Description:

COGNOS GOVT. & HE USERS GROUP Operational reporting using Cognos and Oracle s Logical database technologies Presenter: Angela Hooper, Colorado Community College – PowerPoint PPT presentation

Number of Views:80
Avg rating:3.0/5.0
Slides: 25
Provided by: cole123
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: 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
  • (720) 858-2710
  • angela.hooper_at_cccs.edu
  • http//angela.h00per.com/setadallas2008
About PowerShow.com