Documenting EUL, Discoverer Workbook, Worksheet

Do you need to document EUL Schema, Oracle Discoverer Work-books,
worksheets?

Discoverer is a great ad-hoc and BI tool. People working and using this tool often
need to document the EUL (End User Layer) but there is no straight forward ways to
do it. I have seen clients run some scripts against the EUL meta data and then create
a word doc file for later referencing.

Lets focus on some of the key EUL tables which could be used to document the
different Discoverer components.

In the below examples, replace "disco" with the appropriate schema owner of EUL
tables. In my case it is the disco owner and hence the examples

List of Business Areas
select ba_name "Business Area", ba_created_by "Creator", ba_created_date
"Creation Date", ba_updated_by "Updated By ", ba_updated_date "Last Update
Date" , ba_id
from disco.eul4_bas
where ba_created_by like 'DISCO'

List of Folders
select b.ba_name, f.obj_name folder_name, f.obj_id, f.obj_ext_owner Owner
from disco.eul4_objs f,
disco.eul4_ba_obj_links l,
disco.eul4_bas b
where 1=1
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(b.ba_name) like upper('Video Store Tutorial')
and upper(f.obj_name) like upper('%')
order by b.ba_name,f.obj_name

List of Folder Items
select i.exp_name item_name, i.exp_id, i.it_ext_column, f.obj_name folder_name,
b.ba_name
from disco.eul4_expressions i,
disco.eul4_objs f,
disco.eul4_ba_obj_links l,
disco.eul4_bas b
where f.obj_id= i.it_obj_id
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(i.exp_name) like upper('%')
and upper(b.ba_name) like upper('Video Store Tutorial')
and upper(f.obj_name) like upper('Products')
order by b.ba_name,
f.obj_name,
i.exp_name

List of Folder Joins
select key_description
from disco.eul4_key_cons
where fk_obj_id_remote=100027 or key_obj_id=100027
(substitute the numeric id from the Folders obj_id (see the List of Folder query)

List of Workbooks
select doc_name "Document",doc_developer_key, doc_description "Description" from
disco.eul4_documents

View Discoverer Reports through Oracle Apps
Self Service
In this article we will discuss how to enable Discoverer reports to be viewed through
Oracle Applications Menu structure.

We will take an example report say "AR - Invoice Aging Buckets" with developer key
"AR_INV_AGING_REPORT"

Let us assume there is a seperate responsibility to view AR reports such as "Accounts
Receivables Reports". We also assume that there is a menu (top level) called
"ALL_AR_REPORTS" which is attached to this responsibility using the Define
Responsibility form.

Using the Discoverer User tool we share the "AR - Invoice Aging Buckets" to
"Accounts Recievables Reports" responsibility.

Now we will begin creating the Function and menus for this workbook.
· Navigate > System Administrator > Application > Function
· Go to the Description TAB
· Enter Function Name = AR_INV_AGING_REPORT_DWB
· Enter User Function Name = AR - Invoice Aging Buckets
· Enter Description = AR - Invoice Aging Buckets

We appended a suffix of "_DWB" at the end of the function name to indicate this is a
Discoverer Workbook function. You may choose any other name for your function but
it is a good idea to keep the function name same as the developer key for easy
identification and tracking.

· Now Go to the Properties TAB
· Select Type = SSWA plsql function that opens a new window (Kiosk Mode)
· Maintenance Mode Support = NoneContext Dependence = Responsibility
Selecting the Kiosk Mode opens the Discoverer workbook in a new window.
· Now Go to Form TAB

· Enter Parameters => “workbook=AR_INV_AGING_REPORT&viewer=Y”
In the above we entered the workbook developer key and forcing the report to be
opened using Discoverer Viewer. (If your ICX profile is set to use Discoverer Viewer
then you may omit the part from "&" )
· Now Go to Web HTML TAB
· Enter HTML Call = OracleOASIS.RunDiscoverer and then Click on the Save
Button

Now that we have created our Form Function we will create a new menu to hold this
function and then attach this new menu to the "ALL_AR_REPORTS" menu.

Navigate > System Administrator > Application > Menus
Create a new menu "Aging reports"


Then attach the function to this menu


Now query the main menu "ALL_AR_REPORTS"


and add submenu to this main menu


Now we have finally linked all the pieces together. Wait a minute, just ensure that
the user who needs to access this report is given the "Accounts Receivables Reports"
responsibility. So when user "ARUSER" logs onto Self service signon, the user will
see the "Accounts Receivables Reports" responsibility as part of the menu list.
Clicking on this responsibility, the user will see the "Aging Reports" as one of the
sub-menu and underneath there will be the link to the discoverer workbook. If "all is
fine", clicking on the report link will launch a seperate window where user can enter
parameter values and execute the report.

NOTE: If the menu changes does not appear as expected then ask your DBA's to
bounce the Apache middle tier. Also, make sure all the Discoverer profile values are
set properly.

The eul_date_trunc function information from a date. The idea is that it
will "truncate" out all information EXCEPT for the element(s) that you specify
in the format argument. For example if you specify eul_date_trunc(date,'mon')
then you can consider the item to ONLY contain month information. Thhis is
logically equivalent to saying to_char(date,'mon'). However eul_date_trunc
goes one step further than to_char in that it keeps the return type as date.

This has several major advantages:
It allows the user to use Oracle formatting & localization.

It automatically gives you sensible ordering (i.e. The months will
be ordered Jan, Feb, Mar... instead of Apr, Aug... that to_char would
give you).

The one disadvantage of this is that as Oracle date routines insist
on certain parts of a date always being set ( Year, Month & Day )
then we have to provide defaults for these elements if they are not
present in the format asked for. These defaults are 1900, Jan & 1
respectively. Contrary to popular belief, these were not chosen at
random but because 1900 was a century date that started on a Monday.

Discoverer Reports

• Limitations of oracle tools like
reports,sql*plus,Isql,Ora Graphic
– Can’t change parameters
– Can’t change view
– Matrix rep is complex
– Do not support direct feature of oracle security

• Discoverer is tool using that we can overcome
these limitations.

• It is ad-hoc query tool.

• Reporting and analysis and web-publishing tool

• A business professional can use this tool vary
easily

• Oracle application security based on responsibility
Discoverer lets the business professional find the
data he/she needs,analyze it easily, and get
answers to support business decisions.

• Oracle Discoverer is the tool that enables business
users to make this transformation and gain
strategic insight into their business and operations
via a familiar and intuitive tool,the web browser
interface.

Discoverer components
Discoverer components operate in both
client/server and web browser environment
– Discoverer Plus
– Discoverer 4i Plus
– Discoverer 4i Viewer
– The Discoverer administration Edition
Discoverer plus is designed for business
professionals who do not have computer
programming or database background and is an
easy-to-use read-only data access tool.

• Discoverer 4i plus is the internet version of the
award-winning windows product.

• Discoverer 4i Viewer is designed for viewing
workbooks created by users of the windows

• The Discoverer Administration edition is used to
hide the complexity of the underlying data
structure from the business user

End User Layer (EUL)
Its design determines how users access and
view data.This is where administrators
define the business areas used by
Discoverer Plus

• It insulates end users from database
complexity and constant change.

Data PageThe data page enables you to:
– Create Calculated items.
– Create Complex Folders
– Create Joins
– Create Conditions
– Create New business Areas,Folders & items
and modify object properties

Business Area
• The business area icon (a filling cabinet)
represents the logical grouping of related
objects within the database that users need
to issue queries.

Oracle Discoverer Administrator

ORACLE DISCOVERER ADMINISTRATOR

WHAT IS DISCOVERER :

v Oracle Discoverer (like Oracle Reports) is part of Oracle's Business Intelligence set of tools.

WHY DISCOVERER :

v Oracle Discoverer is intended to provide end users easy access to data and allow them to do data analysis and set-up ad-hoc queries. .

v Oracle Discoverer provides business users with data analysis capabilities, regardless of whether the RDBMS was designed for an OLTP system or as a data warehouse.

v Discoverer is an ad hoc reporting tool It allows end users to quickly create reports without the need for knowledge of SQL.

Oracle Discoverer is a business intelligence toolset that comprises:

v Oracle Discoverer Administrator- Its design determines how users access and view data.

v Oracle Discoverer Desktop- It provides logical and intuitive access to information from your organization’s relational databases for ad hoc query, analysis, and reporting.

v Oracle Discoverer Plus- Same as above on web.

v Oracle Discoverer Viewer- for viewing workbooks created by users of the Windows or Web releases of the Discoverer Plus.

Discoverer Components

What features does Discoverer support for Oracle Applications users?

• Ad-hoc query access to Oracle Applications databases using Oracle Applications user names, passwords and responsibilities

• Oracle Applications mode EULs

• An Oracle Applications mode EUL is a Discoverer End User

Layer based on an Oracle Applications schema (containing

the Oracle Applications FND (Foundation) tables and

views).

• Oracle Applications multiple reporting currencies

• Oracle Applications multiple organizations

Connect dialog in Oracle Applications mode

Connectivity details.

Connect to standard EULs	The Oracle Applications User check box is not displayed in the Connect dialog and Discoverer expects standard database users.
Connect to applications EULs	The Oracle Applications User check box is not displayed in the Connect dialog but Discoverer expects users to connect using an Applications user id/password and Oracle Applications database connect string.
Connect to both standard and applications EULs	The Oracle Applications User check box is displayed in the Connect dialog and (depending on whether the check box is cleared or selected) you can connect to either standard or Oracle Applications database EULs.

v Specify the type of EUL to connect to by selecting one of the following option:

v Gateway User ID (GWYUID)/Password

v You can enter your Gateway User ID and Password here (the default value used is 'applsyspub/pub' if you enter nothing here).

v Foundation Name (FNDNAM) -- FND NAME

v You can enter the Foundation Name (the default value used is 'apps' if you enter nothing here).

• Before you connect to Oracle Discoverer as an Oracle Applications user, you must configure the Connect dialog to default to Oracle Applications users.

• Select Tools > Options to display the "Options dialog: Connection tab":

• After connecting to Oracle Discoverer as an Oracle Applications user, you must find an check box as shown in the fig.

What is Oracle Discoverer Administrator?

• Oracle9i Discoverer Administrator is one of the components of Oracle Discoverer.

• 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:

(i) set up and maintain the End User Layer (EUL)

(ii) control access to information

(iii) create conditions and calculations for Discoverer

end users to include in their worksheets

• Users of Discoverer Administrator are called Discoverer managers.

Sample Report to be Developed

‘Order Details’ report from ‘Order Management’ has been identified to be developed in this training

The report has two tables.

v OE_ORDER_HEADERS_ALL

v OE_ORDER_LINES_ALL

Create an EUL (mandatory if one does not exist already).

v EUL resides between the database dictionary/table definitions and Discoverer.

v EUL insulates Discoverer end users from the complexity and physical structure of the database.

v Provides an intuitive, business-focused view of the database

v Enables Discoverer end users to focus on business issues instead of data access issues.

v The metalayer structure of the EUL preserves the data integrity of the database.

v Discoverer provides read-only access to the application database.

v A database user can only own one EUL.

v EUL Access (Public-> all users, Public -> only owner)

v EUL user should have certain privileges on the database (CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE)

Discoverer Administrator> Tools> EUL Manager

Use the EUL Manager to create and maintain EULs for different users (userids) in the database

Discoverer Administrator> Tools> EUL Manager> Create EUL

The Create EUL Wizard walks you through the steps of creating a new EUL.

The first step is to choose the user who will own the new EUL.

‘APPS’ database user has access to all the schema used for Oracle Apps

Discoverer Administrator> Tools> EUL Manager> Create EUL

The user creating EUL for a schema should know the password of schema’s database user id

Discoverer Administrator> Tools> EUL Manager> Create EUL

Select the default and temporary tablespace for the new user by clicking your choice in the list.

These settings are database settings

Default table space must be at least 3mb

How to know the default End User Layer (EUL)

Discoverer Administrator> Tools> Options

Create a business area and load data into it (mandatory).

v Business areas are conceptual groupings of tables and/or views

v Discoverer Administrator displays a business area as a file cabinet on the Data tab of the Workarea.

v You create a business area in Discoverer Administrator using the Load Wizard (User-friendly Interface)

v Identify the data source and have a clear understanding of its design.

v Identify which tables, views, and columns are required. Identify those that are likely to be included in multiple business areas.

v Map out the necessary joins and determine whether they exist in the database or will have to be created by you using Discoverer Administrator.

v Identify security issues and access privileges.

Discoverer Administrator> File> New

The Load Wizard is where you open existing business areas or create a new one.

It is the first step to working in the Administration Edition.

Discoverer Administrator> File> New

Discoverer allows you to load metadata that is present in the database, but in non-Oracle format.

Discoverer Administrator> File> New

Define the objects that will be loaded into the new business area.

You choices depend on where you chose to get your meta data.

‘ONT’ schema is the owner of two tables in the report

Discoverer Administrator> File> New

Select the specific objects to load into the business area.

Each table and view that you select will be a folder in the business area.

Two tables to be used in the report are selected

Discoverer Administrator> File> New

These options let you make your business area and its folders easy to find and access

Discoverer Administrator> File> New

Finally, you must name your new business area.

Features provided for Business Area

v Opening an Existing Business Area (File> Open)

v Exporting a Business Area to a File (File> Export)

v Importing EUL elements from a File (eg. Business Area, Folders, Functions etc) (File> Export)

v Editing Business Area Properties (Double click on Business Area Icon)

v Deleting a Business Area (Right Click > Delete Business Area)

v Synchronizing the Business Area with the Database (File> Refresh)

v Data migration issues (Analytic Functions)

v Use Discoverer access permissions to control who can see and use the data in business areas

v Use Discoverer task privileges to control the tasks each user is allowed to perform

v You can grant Discoverer access permissions and task privileges to database roles as well as to database users.

Granting Access Permission for Business Area

Tools> Security

This section describes how to grant (or deny) access permission for business areas to specific users or roles.

Apps and Scott Users will be able to access the business area.

Granting Task Privileges

Tools> Privileges

v Use this tab to grant privileges to a specific user or role/responsibility*.

v Oracle Applications users will see the term Responsibility displayed here instead of Role.

v Grant the head privilege (Administration, User Edition) before you grant the privileges under it.

v If you wish to grant (or deny) Administration privileges to a user or role, you must also grant (or deny) that user Administration access to the business area.

Administration Edition Tasks

v Format Business Area

v Create/Edit Business Area

v Create Summaries

v Set Privilege

v Manage Scheduled Workbooks

Discoverer Plus Tasks

v Create/Edit Query

v Collect Query Statistics

v Item Drill

v Drill Out

v Grant Workbook

v Schedule Workbooks

v Save Workbooks to database

Query Retrieval Limits

Tools> Privileges> Query Governor

Use this tab to view and edit the query retrieval limits for a specified user or responsibility.

Specifying Schedule Workbench Limits

Tools> Privileges> Scheduled Workbooks

To set the parameters for scheduling workbooks to a user or role that has that privilege.

Folders

Folder: analogous to a directory in Windows where folders are the containers and items are the files held in the folders.

v Folders can include items, calculated items, joins, conditions, item classes, and hierarchies.

v You can assign a folder to one or more business areas.

v A folder has a single definition, regardless of the number of business areas to which you assign it.

Three Type of Folders:

v Simple Folders : which contain items based on columns in a single database table or view

v Complex Folders : which can contain items based on columns from multiple database tables or views. This is analogous to a view in the database.

v Custom Folders : which are based on SQL statements

Adding Simple Folders from Database

This section describes how to add Simple folders from the database to an existing business area.

Follow the same process you use when creating a new business area.

Insert> Folder> From Database> On-line Dictionary

Creating Custom Folder

creating New custom folder

Enter the SQL Query to this custom folder

Creating Custom Folder -- Contd

Insert> Folder> Custom

This section describes how to create a custom folder

v TIP: You can add comments to your SQL statements by beginning the comment line with --.

v Like other folders, custom folders require joins in order for its data to relate to other data in the business area.

Complex Folder

v Complex folders consist of items from one or more other folders.

v Complex folders enable you to create a combined view of data from multiple folders.

v This can simplify the business area without creating a new database view.

v Complex folder’s result set could also be produced by a database view. But:

Ø Complex folders can be created without the database privileges required to create a database view.

Ø Security is handled through the Folder’s Business Area.

Ø Complex Folders has no effect on the physical schema, thus they are very safe to use.

Ø Views can be complicated to maintain, whereas Complex Folders are managed entirely within Discoverer Administration Edition.

Creating Complex Folder

1. On the Data page of the work area, select the business area to which, you want to add a Complex folder.

2. Choose Insert | Folder | New. This creates a new complex folder.

3. Drag items from any folder (in any open business area) to your new folder.

v Each item that is added in the complex folder must belong to a folder, that is joined to the folder of at least one other item in the complex folder

Item dragged and droapped in complex folder references the original, source item. Therefore any change made in the original item will reflect on the item inside the complex folder

Manage Folders

Items

v An Item, is a representation of a database table's column, in the EUL

v Administrator can do following on items (Columns):

Ø Formatting Change

Ø Name Change

Ø Other changes to enable user to clearly read the data

v Items are stored in folders and can be:

Ø Created

Ø Deleted

Ø Moved among different folders

Editing Item Properties

This section shows you how to enhance the user’s view of the data by editing item properties.

Item>Right Click> Properties

Joins

v A join relates two folders using one or more common items.

v End users cannot create joins

v Administrator has to create joins for end users to create reports that combine information from multiple folders.

v A join between two folders enables you to include items from both folders when creating (Worksheet, Complex Folder, Hierarchy)

v Single item joins relate two folders using an item that is common to both folders.

v Multi-item joins relate two folders using more than one join condition.

v You cannot directly include functions or literals (e.g. text strings, numbers, dates) in the join conditions.

v Items in joins can be hidden later, allowing end users to benefit from the use of joined folders without having to see the join details.

v While Creating Joins, You do not need to enter a name for the join, Discoverer Administrator automatically creates a default name for the join when you click OK.

Create Joins

v To create a join, select

Ø the Master folder

Ø the operator

Ø the detail folder

v Use ‘Multi-Item’ for joining multiple Master and detail folders

v For defining join types (outer join, Foreign key with null value, one to one relationship) use ‘OPTIONS’

Item>right click> New Join

Edit Join Properties

v You can inspect and edit more than one Join item at a time.

v Select a join to view its properties.

v If you select more than one Join Property and make a change to a property, the changes apply to all selected properties.

Join Name> Right Click> Properties

The screen details are same as ‘Create Join’ screen

Join Name> Right Click> Edit Join

Create Calculation Item

v Calculation Items behave much like any other Item in a Folder

v Calculated Item can be used in Conditions, Summaries, Lists of Values, Joins, and other Calculation Items.

v 3 Calculations in Discoverer

v Derived calculations

v Aggregate calculations

v Aggregate Derived calculations

Add new item to show difference of order qty and invoice qty in sample report

Folder Name> Right Click> New Item

Editing Calculation Properties

Item Name> Right Click> Properties

Editing Item Calculation

Item Name> Right Click> Edit Item

Create Conditions

Item (on which condn to apply)> Right Click> New Condition

v End users can use Conditions to restrict the results of their query to the areas they are interested

v This can result in faster queries.

No condition is applied in the sample report

Hierarchies

v Hierarchy is a logical linking that you define between Items that enables Discoverer Plus users to:

Ø drill up (to a greater level of aggregation) and

Ø drill down (to a greater level of detail).

v Hierarchical relationships are not defined in the database; you create them in the Business Area.

v Discoverer Hierarchies mimic data relationships from the end user’s perspective instead of from the database perspective.

v There are two types of Hierarchy in Discoverer Administration Edition:

v Item Hierarchies

v Date Hierarchies

Creating Item Hierarchy

Hierarchies Tab> Business Area> Right Click> New Hierarchy> Item Hierarchy

Creating Date Hierarchy

Example:

Year> Month > Week

v User looks at total sales for each year in their records, they can drill down (using the Date Hierarchy)

v you implement this Date Hierarchy in a Business Area, and an end user has a report in Discoverer Plus

v You can use Discoverer Administration Edition’s existing Date Hierarchy templates to define many common Date Hierarchies, or you can create your own customized Date Hierarchies.

Hierarchies Tab> Business Area> Right Click> New Hierarchy> Date Hierarchy

Default date hierarchy is used in sample report

Creating Date Hierarchy- Contd..

You can choose the date items in your Business Area that will use the new date hierarchy.

This step is optional.

Hierarchies Tab> Business Area> Right Click> New Hierarchy> Date Hierarchy

Creating Date Hierarchy- Contd..

Like other items in your Business Area, you can name your hierarchy.

Choose names that describe the hierarchy so they are easy to find and use later.

Hierarchies Tab> Business Area> Right Click> New Hierarchy> Date Hierarchy

Item Class

An item class is a group of items that share similar attributes.

The administrator creates an item class to enable the following features:

v Lists of values

v Alternative sorts

v Drill-to-detail links (hyperdrills)

These features help users build queries more quickly and easily.

The item class can be created to support these features individually, or in combination

The only exception is that an alternative sort must relate to a list of values

List of Values

A list of values is the item’s set of unique values

The values the item class references correspond to those found in a database column.

Lists of values are used by end users to refer to values in the database and to apply conditions and parameter values.

Lists of values are often generated automatically when the business area is first created (in Load Wizard: Step 4).

The Item Class Wizard provides a way to extend a list of values to other items.

Create List of Values

Select the items that use this item class.

If you do not select the items using the item class, you will need to apply the item class to specific items later.

Item Classes Tab> New Item Class> List of Values

Alternative Sorts

Data elements to be sorted in an alternative order other than Ascending or Descending Order

For example, a series of sales regions would be sorted alphabetically by default, such as

East,

North,

South, and

West.

But the end user may need them sorted in this order:

North,

South,

v East, and

v West.

No Alternate sort is used in Sample Report

Create Alternative Sorts

Select the item that generates list of values.

You may choose from any Business Area in the End User Layer.

Selecting a Business Area displays its folders.

Click the plus (+) symbol to open folders and view the items contained in them.

Item Classes Tab> New Item Class> Alternative sort

Create Alternative Sorts- Contd..

Select the item containing the alternative sort sequence

Note that the alternative sort item must be in the same folder as the list of values.

Item Classes Tab> New Item Class> Alternative sort

Select the items that use this item class.

If you do not select the items using the item class, you will need to apply the item class to specific items later

Item Classes Tab> New Item Class> Alternative sort

Summary Folders

What are summary folders?

v Summary folders are a representation of queried data that has been saved for reuse.

v Created to improve query response time for end users.

v Query is improved because the query accesses pre-aggregated and pre-joined data rather than accessing the database tables.

v The data is stored in the database in one of the following

-- materialized views

-- tables

Why summary folders?

v This can improve query performance in Discoverer Plus and Discoverer Viewer.