Oracle apps Discoverer reports interview questions and answers

Q1.what is Discoverer?
Ans : Discoverer is a Tools of oracle for Business Intelligence . Discoverer is an
intuitive ad-hoc query , reporting analysis and web publishing toolset that gives
business users immediate access to information of database.
Discoverer provides a business view to hide the complexity of the
underlying data structures, enabling u to focus on solving business problems.

Q2.what are difference between Discoverer Desktop and Administrator ?
Ans : Discoverer Administrator :- It is a tool to hide the complexity of the database
from business user, so they can answer business questions quickly and
accurately.

The component of Discoverer Administrator are :-
a) EUL (End User Layer) : It is a set of database tables that contain
information (metadata) about the other tables and views in the database.
Which enable the end user to focus on business issue instead of data access
issue. Whatever the discoverer manager or end user does with discoverer, it
affects only the metadata in the EUL and not in the data base. EUL is a
collection of approximately 50 tables in the database.

b) Business Area : It is a containers of related information, While creating the
business area we can load the database table containing the related
information.

c) Folders: The tables and views we load into the business area are called
folders.

Database : Directly from oracle database tables and views.
Complex : Folder which contain the items based on columns from multiple
tables and views.
Custom : Contain the items based on columns from SQL statement we write.

d) Items : The columns within the tables and views we load into the business
area are called items.
We can create calculated items that perform calculations on several
columns. We can use available functions within database.

Discoverer desktop :- it is a data access tool. You use it to view the information
in your company database. The whole purpose of Discoverer desktop to help
you – the business professional – view the data from data base , analyse it to
support your business decision and create report to keep track of thing.

The main character tics are Discoverer Desktop are :
· see data displayed quickly without waiting for computer to spend a long time
searching through the entire database.
· View data in a familiar format that is easy to read and understand .
· Share data with others, and in other application just like (excel).
· Analyse data using a wide array of techniques using include drill up and down
through the data details.

Q3.what are features of Discoverer and how to works Discoverer?
Ans : - Features of Discoverer :-
a) Enhanced parameter handling : you can select parameter using
parameter index values and parameter is marked as mandatory or optional
you can also used the reserved word into a parameter field.

b) Conditional formatting : you can used stoplight formats created in
discoverer plus.
Discoverer works cycle : -
Users request the information from the database in the form of a worksheets.
When we create or open the worksheet , Discoverer

1. Convert the worksheet into corresponding SQL Stmt (i.e. Converting
folder name and item name into table name and column name
respectively.)

2. Send the SQL Stmt to the database .

3. Display the result set that is return from the database.

Q4. what is a Business area?
Ans : Business area is a logical grouping of database and views that apply to your
specific data requirement and exact combinations of tables and views for each
departments is usually unique.
Example : the accounting departments wants data about budgets and finance.

Query to find all Oracle Discoverer Reports/Workbooks

Run the following sequence of queries in APPS schema to find all Discoverer Reports/Workbooks that exist in the system.

Query1: Run the following query to know the name of table

SELECT owner,table_name
  FROM all_tables
 WHERE owner = 'EUL4_US'
   AND table_name LIKE '%DOCUMENT%';

Generally the above query returns table_name as 'EUL4_DOCUMENTS'
Meaning, all the discoverer report/Workbook names are stored in this table

Query 2: Run the following query to see all reports

SELECT DOC_ID,
       DOC_NAME,
       DOC_DEVELOPER_KEY,
       DOC_CREATED_BY
  FROM EUL4_US.EUL4_DOCUMENTS;

Note:
If the First Query is not returning any data then try the following query and manually find the table name.

   SELECT owner,table_name
     FROM all_tables
    WHERE table_name LIKE '%EUL%DOCUMENT%';

You have to modify the 2nd query in this case with the new OWNER.TABLE_NAME

Oracle Discoverer EUL Tables & Description

EUL Tables Information:

EUL4_BAS
Table gives list of Business Areas

EUL4_OBJS
Table gives all folders in the EUL

EUL4_KEY_CONS
Table gives all Folder Joins details.
- Key_Obj_ID points to parent folder,
- FK_Obj_ID_Remote points to child folder

EUL4_EXPRESSIONS
Table gives all Items that are in the EUL
- IT_Obj_ID is not null AND join to a row in EUL4_Objs you have a Folder Item.
They are listed as Exp_Type of 'CO' or 'CI'.
COs are database object items.
CIs are created items (like date hierarchy items, complex folder items, etc).

EUL4_OBJS
Table gives details about the types of objects
- Obj_Type tells you Standard (SOBJ) from Complex (COBJ) folders.

EUL_USERS
Table gives user details.

EUL4_ACCESS_PRIVS
Table to use to get the list of Discoverer users that was granted access to the User Edition.
Basically, when a user has been granted privileges one row per privilege is inserted into this table.
- The column called AP_EU_ID contains the ID of the user.
- The column GP_APP_ID is the one that tells you what privilege a user has.

How to make VIEW ONLY user in Oracle Discoverer

Background:
The idea here is to create view only user. This viewer only user can only run pre-built reports, but neither edit them nor create a new report.

How to do it?
Discoverer Administrator -> Tools -> Privileges

Uncheck all privileges. Check Desktop and Plus Privilege only. (Here i am doing it for user 'LOCT') 

Select a business area -> Right click -> Security

Give access on the business area to the user

Create report using Discoverer Desktop or Discoverer Plus and share the report to the user
File -> Manage Workbooks -> Sharing

The user only can run pre-built reports, but cannot edit them or create a new report !!