Oracle Discoverer Basics


Oracle Discoverer Basics

About Discoverer:

Discoverer is the end user adhoc query, reporting and analysis tool, which provides quick development environment to develop Data Warehousing & Business intelligence reports.

What is business intelligence?

Business Intelligence is the ability to analyze data to answer business questions and predict futures.

Features and Benefits:

  • Automated Summary Management – improving query performance in Discoverer Plus is now easier with this new feature.
  • Oracle Applications support – This enables you to connect to Oracle Applications EULs as well as standard Discoverer EULs.
  • Identifiers – all objects in a Business Area are now uniquely defined within each EUL by an Identifier.
  • Change schema owner – you can now manually edit the schema Owner attribute (for a Folder) or leave it blank.
  • Support for Oracle Materialized Views.
  • Analytic Functions – perform complex mathematical analysis with this extended range of statistical functions.

How does it Works?

When a user creates or opens a worksheet, Discoverer:

  •  Converts the worksheet into the corresponding SQL statements (e.g. by converting folder names and item names to table names and column names respectively).
  •  Sends the SQL statements to the database.
  •  Displays the result set that is returned from the database.

Discoverer Components:

  •  Oracle Discoverer Administrator
  •  Oracle Discoverer Plus
  •  Oracle Discoverer Desktop Edition (Viewer)

Oracle Discoverer Administrator:

Discoverer Administrator is a tool to hide the complexity of the database from business users, so they can answer business questions quickly and accurately using Oracle Discoverer.

Discoverer Administrator’s wizard-style interfaces enable you to:

  • Set up and maintain the End User Layer (EUL)
  • Create Folders, Items and Item Classes
  • Control access to information
  • Create conditions and calculations for Discoverer end users to include in their worksheets

Oracle Discoverer Plus:

It is a web portal like Oracle Application front end through which you can log on with your oracle application username and password and create and run discoverer reports.

Oracle Discoverer Desktop Edition (Viewer):

It works same as Discoverer Plus, but it is a software that comes with Oracle BI Publisher setup. Here also you can create and run your discoverer reports.

End User Layer (EUL):

End User Layer is a Metadata repository for Business areas, folders and items on which Discoverer workbooks or reports based on. The EUL is a set of database tables that contain information (or ‘metadata’) about the other tables and views in the database. 

Business Area:

A Business Area is a set of folders containing related information with a common business purpose.

Business Areas:

  • Meet the specific data needs of the users
  • Usually contain data from several different tables or views
  • Have tables or views and their associated columns mapped to “folders” and “items” respectively
  • Can contain many simple folders and complex folders
  • May contain folders from one or more physical databases include conditions, joins, calculations, formatting,   hierarchy structures, and other custom features
  •  May be accessible to one or many user IDs or roles; also, a user ID or role may be granted access to many business areas
  •  Let users access data, without having to understand the database structure

Folders:

The tables and views loaded into a Business Area are called as folders. There are 3 types of folders that you can create – Simple, Complex and Custom.

Items:

The columns of the folders are named as items.

Item Classes:

Item classes are like LOVs in Oracle Application. We can create item classes based on any columns of a table and we can join that item classes to any parameters of the discoverer reports.

Joins:

A Join relates two folders using common items.

Conditions:

Conditions filter worksheet data, enabling Discoverer end users to analyze only the data they are interested. Conditions are applied to the folders or items.

Calculations & Hierarchies:

A calculated item is an item that uses a formula to derive data for the item.

Hierarchies are default drill paths between item that you define in Discoverer Administrator. There are two types of Hierarchy in Discoverer Administration Edition:

  1. Item Hierarchies
  2. Date Hierarchies

Summaries & Summary Folders:

A summary improves query performance in Discoverer Plus or Discoverer Viewer by using pre-aggregated data created through Discoverer Administration Edition.

A Summary Folder is how Discoverer represents the underlying structure of a summary table or MV. Each Summary Folder has one or more Items (i.e. columns from a summary table or MV).

ASM:

Automated Summary Management – It delivers fast performing queries while greatly reducing the amount of time spent on administration. ASM analyzes your tables, uses query statistics (when available) and default values (the summary policy) to determine how summaries are created for you. ASM is able to automatically create and maintain the best set of summaries based on this combination.

 

Advertisements

Discoverer 10g Installation steps


Discoverer 10g Installation steps

Oracle Business Intelligence 10g Release 2, a key component of Oracle Application Server 10g Release 2, is an integrated business intelligence solution supporting intuitive ad hoc query, reporting, analysis, and web publishing. Discoverer 10.1.2 is tightly integrated with Oracle E-Business Suite Release 12.  Release 12 users can use Discoverer to analyze data from selected business areas in Financials, Operations, and Human Resources etc.

The following are the Discoverer 10g Installation steps for Oracle R12 environment.

1] Install Oracle Business Intelligence Tools 10g (10.1.2.0.2) using below link

http://www.oracle.com/technology/software/products/ids/htdocs/101202winsoft.html


First right click on the zip file (as_windows_x86_bi_tools_101202). Extract all to a directory on your PC. Click on the Extracted folder which has the same name (as_windows_x86_bi_tools_101202). Click on the 10g set up icon and follow only the default settings. This installation creates a home “BIToolsHome_1” (If Default location is selected during install).

2] Apply the latest certified Discoverer Plus and Viewer Patches

To upgrade to Oracle Discoverer 10.1.2.2 – Apply the patch: 4960210

To upgrade to Oracle Discoverer 10.1.2.3 – Apply the patch: 5983622

Follow the installation instructions provided in the patch README to install the patch on your Discoverer 10g Server and to check supported operating systems.

3] Copy Database Connection file

Discoverer needs access to the Database Connection (dbc) file for the database you wish to connect to. A dbc file is a text file which stores all the information required to connect to a particular database.

First identify the Oracle Home for Discoverer. For example: If you could find dis51usr.exe under the directory D:\oracle\BIToolsHome_1\bin. Then the Oracle Home is D:\oracle\BIToolsHome_1

Create a folder named “secure” in the ORACLE_HOME directory (i.e. D:\oracle\BIToolsHome_1\Secure).

Then copy the dbc file from the $FND_SECURE directory of the E-Business Suite Release 12 instance you are setting up Discoverer with to the Discoverer 10.1.2 “ORACLE_HOME\secure” directory you just created.

Save the dbc file in lowercase.

4] Update tnsnames.ora file

On your standalone Oracle Business Intelligence Server 10g Release 2 node, update the file ORACLE_HOME/network/admin/tnsnames.ora and include the tnsnames entry to connect to your Oracle E-Business Suite Release 12 database. Use the same entry as exists in the tnsnames.ora file on your Oracle E-Business Suite Release 12 application tier server node.

5] Set the Environment

Discoverer requires Windows Environment variables to dynamically retrieve the location of the .dbc file on the PC.

Open Windows Control Panel and double click on the System icon. Select the Environment tab and create two new System Variables.

FND_SECURE = ORACLE_HOME\Secure
FND_TOP= ORACLE_HOME

Note: The Oracle Home is the one identified in step 3.

6] Restart the PC and you should be able to connect to Discoverer in an Oracle Applications mode.

Note: When you are connecting to Discoverer for the first time, then Goto Tools > Options. Check the proper EUL in the Connection Tab.

For More Detailed Information Please refer the below metalink note:

Using Discoverer 10.1.2 with Oracle E-Business Suite Release 12 [ID 373634.1]