Best Implementation Practices for Discoverer

1
Best Implementation Practices for Discoverer

• April Sims, Senior DBA, OCP 8i 9i
• Southern Utah University
• Wednesday, September 10,2003
• 830am

2
"The degree of normality in a database is
inversely proportional to that of its DBA" --
unknown
3
Introduction

• This session will provide opportunity to see how
  Oracle Discoverer has been implemented as an
  ad-hoc querying tool.
• Pros and Cons will be discussed as to its
  implementation and use across different clients
  and similar ad-hoc query tools.

4
Topics of Discussion

• Why use Discoverer?
• Implementation
• Management
• Security
• Usability
• Performance

5
MSAccess is already there

• ODBC connection, ODBC drivers
• Limited ability for remote access
  offsite/offhours.
• DBA must proactively monitor for security
  breaches, cartesian products, slow queries.

6
Why Discoverer?

• Common functionality, centralized management and
  deployment.
• Very secure- end user can only query with this
  tool.
• Only administrator can make joins
• Easy to install and deploy.
• Access data/saved reports remotely.

7
ImplementationDesktop, Network, Web

• Oracle Discoverer Administration Edition for
  Windows (including Discoverer Plus, SQLPlus and
  SQLNet)
• Oracle Discoverer Plus for Windows (including
  Discoverer Plus and SQLNet to connect the client
  software to a database)
• Oracle Discoverer 4i /9i Plus (a version of
  Discoverer Plus written in Java for building and
  running reports on the web)
• Oracle Discoverer 4i/9i Viewer (an HTML tool for
  viewing reports created using Discoverer Plus and
  Discoverer 4i/9i Plus)

8
Web Deployment- Separate Server

• Oracle 9iAS v 1.0.2.2.0 or 9iAS R2 9.0.2/9.0.3
• Discoverer 9i certified with 8.1.7 DB Versions
• Currently deploying Discoverer Plus using a Sun
  E250 on Solaris 8 using 6-18GB drives with 2GB of
  Memory on 9iAS 1.0.2.2.2
• Approximately 100-200 active users.
• 3 tier delivery (database, client, services)

9
Oracle 9iAS R2

• Migrating to Oracle Portal utilizing SSO along
  with Oracle Forms/Reports.
• Requires a OID Infrastructure install
  (recommended on a separate server)
• Use OEM for Connection Management using private
  and/ or public connections. http//servername1810
  
• Discoverer Workbooks/Worksheets can be deployed
  as portlets

10
Web Deployment-contd

• Still requires Discoverer Admin license to create
  the workbooks.
• Performance, Use and Stability of Discoverer Plus
  has been outstanding.
• If using firewall, you must implement the use of
  a gatekeeper to do Network Address Translation.

11
Web Deployment- contd

12
Management

• Business Areas can be exported and imported
  between databases and/or EULs.
• Can be deployed using a centralized model with
  control of the administrator application strictly
  in the IT department vs the decentralized model
  where certain departmental designees are given
  access to the administrator application to
  develop workbooks for their department.

13
(No Transcript)
14
(No Transcript)
15
Security

• Oracle Discoverer can only do select statements.
  Other reporting tools such as MSAccess, ODBC and
  SQLPLUS have the inherent ability for an enduser
  to have direct access to tables for update,
  deletes, etc.
• Easily managed via the use of Oracle Roles.
• Security is regulated at the database level and
  the application level.
• Different modules/campuses or organizations can
  be functionally separated using the EUL (End User
  Layers) and/or flexibility in granting access to
  the different Business Areas.

16
(No Transcript)
17
(No Transcript)
18
http//technet.oracle.com/products/discoverer/cont
ent.html

• See this website for some on-line views of the
  application and functionality. When it says that
  there is no setup required for Discoverer.NOT
  really truethey are telling you that the
  application itself doesnt have to be modified
  (it works straight out of the box) but access and
  security still has to be configured.
• EUL schema install, Oracle role definition and
  assignment, Granting application, workbook access
  to administrators and endusers.

19
Usability

• 4 to 8 hours of training will prepare most people
  to be able to modify, save their own queries.
• The level of expertise with MS Excel typically
  predicts the level of success with Discoverer.
• Our end users love it because it gives them
  power- they can manipulate parameters, drill
  down, modify the look of the report without
  knowing SQL.

20
Discoverer Administrators

• Can be functional end-users who are somewhat
  technical or IT staff who understand the
  functional areas.in other words a FUNKYTECH!!!
• Someone with no prior programming experience will
  take from 3 to 6 months (with some training) to
  become completely comfortable.
• Start with a person who is already an expert at
  MSExcel.

21
Discoverer Admin Security

• Recommendations
• Maintain EUL (schema username/password) in the IT
  department under DBA control.
• Discoverer allows you to limit who has access as
  an Administrator vs an enduser.

22

• Pros and Cons in the following realms
• Implementation
• Management
• Security
• Useability
• Performance

23
(No Transcript)
24

• Pros and Cons in the following realms
• Implementation
• Management
• Security
• Useability
• Performance

25

26
(No Transcript)
27
Performance

• It takes longer for the same query to run in
  Discoverer via Oracle Reports because it is
  collecting statistics about all queries run.
• Changing the database to Cost-based optimizing
  would help performance.
• Possibility of producing Cartesian products that
  fill up temp Tablespace. The discoverer
  administrator creates the joinsnot the enduser.

28
How do I improve performance?

• Use a STANDBY database as the reporting database
  instead of your OLTP.
• Database Version 8.1.7- Physical standby only.
• Applied archive redo at night, brought database
  up in read-only mode during the day. (see notes
  for how to set this up.)
• Limited to yesterdays data.
• See notes .

29
Logical Standby

• Database Version 9.2.0.2 (required level)
• Logical standby (can create a combination data
  warehouse and OLTP available for failover)
• The data is transported using SQL apply with SQL
  statements only.
• Can use DataGuard, OMS or manually install.
• Reporting database has REAL-TIME data.
• Metalink Docs
• 234631.1 , 215020.1 , 186150.1 , 233261.1

30
Upgrades

• Required to install 2nd EUL before removing
  first. (Possible space issue)
• Each EUL needs to be upgraded separately.
• Earlier version is still accessible during
  transition until you run script to delete.
• The same queries work between versions and
  between EULs if the Business Area has not been
  modified or the underlying tables/views.
• Table modified, then do a refreshqueries still
  WORK!

31
Questions and Answers

• If you would like more information please visit
• http//technet.oracle.com/products/discoverer/cont
  ent.html
• http//metalink.oracle.com

32
Thank You!

• April Sims
• sims_at_suu.edu
• Please fill out the Evaluation Form