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.
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.
- Oracle Discoverer Administrator
- Oracle Discoverer Plus
- Oracle Discoverer Desktop Edition (Viewer)
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
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
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:
- Item Hierarchies
- Date Hierarchies
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.
============
Database Query for Discoverer
All discoverer folders and workbooks that you create get stored in database. You can use the below queries to find the details.
Find your Discoverer Workbook and Folder Details from Database:
01 | select fu.user_name owner, |
02 | doc.doc_id "Document Id", |
03 | doc.doc_name "Document Name", |
04 | doc.doc_developer_key, |
05 | doc.doc_folder_id, |
06 | doc.doc_created_by, |
07 | doc.doc_created_date, |
08 | doc.doc_updated_by, |
09 | doc.doc_updated_date, |
10 | min(qs.qs_created_date) first_acc, |
11 | max(qs.qs_created_date) Last_Acc |
12 | from disco_apps.eul5_documents doc, |
13 | apps.fnd_user fu, |
14 | disco_apps.eul5_qpp_stats qs |
15 | where '#'||fu.user_id = doc.doc_created_by |
16 | and qs.qs_doc_name=doc.doc_name |
17 | and qs.qs_doc_owner=fu.user_name |
18 | and doc.doc_created_date<qs.qs_created_date |
19 | group by fu.user_name, |
20 | doc.doc_id, |
21 | doc.doc_name, |
22 | doc.doc_developer_key, |
23 | doc.doc_folder_id, |
24 | doc.doc_created_by, |
25 | doc.doc_created_date, |
26 | doc.doc_updated_by, |
27 | doc.doc_updated_date |
28 | order by doc.doc_updated_date desc; |
Find your Discoverer Workbook performance for different Business Areas:
01 | select ba.ba "Business Area", |
02 | usr.user_name, |
03 | doc.doc_name "Work Book", |
04 | min(qs_act_elap_time) "Fastest", |
05 | max(qs_act_elap_time) "Slowest", |
06 | round(avg(qs_act_elap_time),2) "AVG (s)", |
07 | round(avg(qs_act_elap_time)/60,2) "AVG (m)", |
08 | count(*) "Often", |
09 | doc.doc_created_date, |
10 | min(acc.qs_created_date) "First", |
11 | max(acc.qs_created_date) "Last Access" |
12 | from disco_apps.eul5_documents doc, |
13 | apps.fnd_user usr, |
14 | disco_apps.eul5_qpp_stats acc, |
15 | (select distinct gd_doc_id from disco_apps.eul5_access_privs ) privs, |
16 | ( |
17 | select distinct doc.doc_id,ba.ba_name ba |
18 | from disco_apps.eul5_documents doc |
19 | ,disco_apps.eul5_elem_xrefs eex |
20 | ,disco_apps.eul5_ba_obj_links bol |
21 | ,disco_apps.eul5_objs obj |
22 | ,disco_apps.eul5_bas ba |
23 | WHERE doc.doc_id = eex.ex_from_id |
24 | AND eex.ex_to_par_name = obj.obj_name |
25 | AND obj.obj_id = bol.bol_obj_id |
26 | AND bol.bol_ba_id = ba.ba_id |
27 | )ba |
28 | where '#'||usr.user_id=doc.doc_created_by |
29 | And doc.doc_name=acc.qs_doc_name |
30 | And privs.gd_doc_id = doc.doc_id |
31 | And usr.user_name = upper(acc.QS_DOC_OWNER) |
32 | And doc.doc_created_date<acc.qs_created_date |
33 | And doc.doc_id=BA.doc_id |
34 | group by ba.ba, |
35 | usr.user_name, |
36 | doc.doc_name, |
37 | doc.doc_created_date; |
====================
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: 4960210To 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]
How to run Discoverer Reports in Multi-Org Environment?
How to run Discoverer Reports in Multi-Org Environment?
When you are working in R12 MOAC environment, sometimes discoverer reports which are based on some context based view will not give data. Then do the following setup to make the discoverer reports work in MOAC environment.1) Create a Security Profile in HR
1) Run concurrent program Security List Maintenance
1) Tag this profile to MO: Security Profile option at responsibility level.
1) Update profile option “Initialization SQL Statement – Custom” with following pl/sql block at responsibility level.
begin if (fnd_profile.value(‘XLA_MO_SECURITY_PROFILE_LEVEL’)= 0) then mo_global.init(‘S’); else GL_SECURITY_PKG.init(); mo_global.init(‘M’); end if; end;
When multiple organizations are included in Security Profile: TEST SECURITY PROFILE, This profile is attached to resp Discoverer EUL Management.
=====================
==
LOVs in Discoverer Reports
We can create list of values (LOV) for any parameter in discoverer reports through Item Classes. Here suppose in Discoverer Report, we have a parameter called Period Name. It has a small LOV icon in the right side.
When clicked on the LOV icon, the below window with all the Period Names will appear where you can choose one or many period names.
Now the question is how to do this. You just need to do the below steps to accomplish this task.
1] Create a custom folder named ‘Time Periods’ in a business area with the below query.
01 | select distinct |
02 | period_name, |
03 | period_num, |
04 | period_year, |
05 | decode(period_num,1, 'January', |
06 | 2, 'February', |
07 | 3, 'March', |
08 | 4, 'April', |
09 | 5, 'May', |
10 | 6, 'June', |
11 | 7, 'July', |
12 | 8, 'August', |
13 | 9, 'September', |
14 | 10, 'October', |
15 | 11, 'November', |
16 | 12, 'December', null) month_name |
17 | from gl_periods; |
3] Select the LOV Item class attribute.
4] Select the above created folder (Time Periods) and choose the Period Name column.
5] Here you can select the various items of other folders that can use this item class. You can skip this stage and later you can manually assign this item class to other items.
6] Choose the defaults and click next.
7] Give a suitable name to the Item Class
8] Now go to the Item of the folder on which you created the Parameter (Period Name) and then go to Item Properties. Here you assign the item class that you have created just now.
9] If you already created the parameter in your workbook and your discover plus or desktop is open then close it and reopen. Then you can view the LOV attached to the parameter.
10] If you haven’t already created the parameter, then create the parameter and run the report. You can view the LOV attached to the parameter.
===================
Working with Complex Folders in Discoverer
Complex folders contain items from one or more base folders. It is same like a view in database. It enables you to create a combined view of data from multiple folders.
A base folder can be any of the following types of folder:
- a simple folder, containing items based on columns in a single database table or view
- a custom folder, based on SQL statements
- a complex folder, containing items from one or more base folders
- When we use complex folders, the sql queries are automatically optimized by Discoverer. If we use view, we cannot get that improved performance.
- We can create complex folders without database privileges, but for view creation we require that.
1] Logon to Discoverer Administrator.
2] Select the business area in which you want to create a complex folder.
3] Choose Insert > Folder > New to create a new complex folder.
4] Click the new folder’s icon on the Data tab and choose Edit | Properties. We can give a more descriptive name, Description and Identifier Name for the new folder.
5] On the “Workarea: Data tab”, Drag an item from any folder in any open business area to the new folder. Or you can simply do copy and paste.
Tip: You might find it easier to drag items between folders if you have two Workareas open. To open a second Workarea, choose Window | New Window (Shift W).
Note: When you add an item to a complex folder, the folders that it comes from must be joined to the folder of at least one other item already in the complex folder. If this is not the case, Discoverer Administrator will display an error dialog.
Note: If you select items from two folders that are joined using more than one join, Discoverer displays the Choose Join dialog. Here you can select one or more joins and click OK.
Note: If you select an item from a simple folder that has a join that conflicts with existing items, Discoverer will display an error and you will not be allowed to add the item.
If you want to see all the joins, conditions and the workbooks that are based on your complex folder, go to Folder Properties | Dependents Tab.
What is complex folder reach through?
Complex folder reach through is a mechanism that enables Discoverer Plus and Discoverer Desktop users to add items to their worksheets in addition to those provided in a selected complex folder.
In Discoverer Administrator you can define one or more base folders within a complex folder as ’reach through enabled’. When a Discoverer Plus or Discoverer Desktop user selects an item from the complex folder, the associated reach through enabled base folders become available for selection in a worksheet.
=====
What is Fan Trap in Discoverer and how it handles them?
Fan Trap is a situation while running discoverer reports that return unexpected results due to a group of joined database tables. The most common manifestation of a fan trap occurs when a master table is joined to two or more detail tables independently.
If you use a straightforward SQL statement to aggregate data points here, you may get incorrect results due to fan trap. Now, if you enable fan trap detection in Discoverer and if you use Discoverer to aggregate the data points, Discoverer will never return incorrect results.
Example of Fan Trap:
Consider an example fan trap schema that includes a master folder (ACCOUNT) and two detail folders (SALES and BUDGET), as shown below:
Now let’s say we need to answer the question, “What is the total sales and total budget by account?
Straightforward SQL statement approach:
01 | SELECT Account.Name, |
02 | SUM(sales), |
03 | SUM(budget) |
04 | FROM |
05 | Account, |
06 | Sales, |
07 | Budget |
08 | Where |
09 | Account.id=Sales.accid |
10 | AND Account.id=Budget.accid |
11 | GROUP BY Account.Name; |
Account 1 800 1200
Account 2 130 200
Account 3 600 750
Account 4 600 600
The above results are incorrect, because they are based on a single query in which the tables are first joined together in a temporary table, and then the aggregation is performed. However, this approach causes the aggregates to be summed (incorrectly) multiple times.
Discoverer Approach:
If we run the query in Discoverer interrogates the query, detects a fan trap, and rewrites the query to ensure the aggregation is done at the correct level. Discoverer rewrites the query using inline views, one for each master-detail aggregation, and then combines the results of the outer query.
Here are the results from discoverer which is correct:
Account Sales Budget
Account 1 400 400
Account 2 130 100
Account 3 200 750
Account 4 300 200
How to enable fan trap in discoverer?
By default, fan trap detection is always enabled for you. If you want to disable it (however not recommended), you can logon to Discoverer Plus, go to Tools > Options >Advanced Tab and click on ‘Disable fan trap detection’.
If a fan trap is detected, Discoverer can usually rewrite the query using inline views to ensure the aggregation is done at the correct level. Discoverer creates an inline view for each master-detail aggregation, and then combines the results of the outer query.
In some circumstances, Discoverer will detect a query that involves an unresolvable fan trap schema, as follows:
- If the detail folders use different keys from the master for the join
- If there is a direct join relationship between the detail folders (thereby creating an ambiguous circular relationship)
- If non-aggregated values are chosen from more than one of the detail folders
- If more than one detail folder has a separate join relationship to a different master folder
Registering Custom PLSQL Functions in Discoverer
Although Discoverer provides many functions for calculation in reports, sometime we require to use custom PL/SQL functions to meet additional Discoverer end user requirements (for example, to provide a complicated calculation). For this we first need to create the functions in database through Toad or other PL/SQL editors.To access custom PL/SQL functions using Discoverer, you must register the functions in the EUL. When you have registered a custom PL/SQL function, it appears in the list of database functions in the “Edit Calculation dialog” and can be used in the same way as the standard Oracle functions.
Note: To register a PL/SQL function you must have EXECUTE privilege on that function.
You can register custom PL/SQL functions in two ways:
- Import automatically, by importing the functions (recommended)
- Manually
How to register custom PL/SQL functions automatically:
To register PL/SQL functions automatically you must import them in the following way:1. Choose Tools | Register PL/SQL Functions to display the “PL/SQL Functions dialog: Functions tab”.
2. Click
Import to display the “Import PL/SQL Functions dialog”. This dialog
enables you to select the PL/SQL functions that you want to import.
3. Select the functions that you want to import. You can select more than one function at a time by holding down the Ctrl key and clicking another function.
Discoverer imports the selected functions and displays the function details in the “PL/SQL Functions dialog: Functions tab”. Information about the selected functions is imported automatically. In other words, you do not have to manually enter information or validate the information.
The PL/SQL function is now registered for use in Discoverer.
How to register custom PL/SQL functions manually:
To manually register a PL/SQL function for use in Discoverer:1. Choose Tools | Register PL/SQL Functions to display the “PL/SQL Functions dialog: Functions tab”.
2. Click New and specify the function attributes.
4. If the function is invalid, correct the attributes and click Validate again.
5. (Optional) if the function accepts arguments:
a. Display the “PL/SQL Functions dialog: Arguments tab”.
b. On the Arguments tab, click New and specify the argument attributes.
The custom PL/SQL function is now registered for use in Discoverer.
It is always recommended to register PL/SQL functions by importing automatically (especially if you have many functions to register), because it is easy to make mistakes when manually entering information about functions. When you import functions, all of the information about each function (for example, names, database links, return types, lists of arguments) is imported.
I have created a function and registered it in discoverer admin,but i am not able to get that function in discoverer desktop. how to do that ?
ReplyDelete