Transcript Best Implementation Practices for Discoverer

Best Implementation
Practices for Discoverer
April Sims OCP 8i 9i
Topics of Discussion
•
•
•
•
•
•
Why use Discoverer?
Implementation
Management
Security
Usability
Performance
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.
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.
Implementation
Desktop, Network, Web
• Oracle Discoverer Administration Edition for
Windows (including Discoverer Plus, SQL*Plus and
SQL*Net)
• Oracle Discoverer Plus for Windows (including
Discoverer Plus and SQL*Net 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)
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)
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://servername:1810
• Discoverer Workbooks/Worksheets can be
deployed as “portlets”
Web Deployment-cont’d
• 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.
Management
• Business Areas can be exported and imported
between databases and/or EUL’s.
• 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.
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”.
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 true…they are telling you that the
application itself doesn’t 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.
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.
Discoverer Administrators
Can be functional end-users who are
somewhat technical or IT staff who
understand the functional area.
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.
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.
Pro’s and Cons in the following realms:
• Implementation
• Management
• Security
• Useability
• Performance
Pro’s and Cons in the following realms:
• Implementation
• Management
• Security
• Useability
• Performance
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
joins…not the enduser.
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 yesterday’s data.
See notes ….
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
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 EUL’s if the Business Area has
not been modified or the underlying
tables/views.
• Table modified, then do a refresh…queries
still WORK!
Questions and Answers
• If you would like more information please
visit:
http://technet.oracle.com/products/discoverer/co
ntent.html
http://metalink.oracle.com