Oracle Discoverer is a business intelligence toolset that comprises:
· Oracle9i Discoverer Administrator
· Oracle9iAS Discoverer Viewer
Figure 1-1 The Discoverer components
All of the Discoverer tools rely on the Discoverer End User Layer (EUL). The EUL is a set of database tables that contain information (or 'metadata') about the other tables and views in the database.
Oracle9i 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 us to:
· set up and maintain the End User Layer (EUL)
· control access to information
· create conditions and calculations for Discoverer end users to include in their worksheets
Users of Discoverer Administrator are called Discoverer managers.
Role of a Discoverer manager
Discoverer manager is responsible for:
· the initial implementation of the Discoverer system
· the ongoing administration and maintenance of the Discoverer system
Discoverer manager needs to understand how to design business areas that support company's decision-makers. On the database side, knowledge of data in the database, their location, how it is stored, and how it relates to other data is essential. On the business side, understanding of data the decision-makers require, what kinds of analysis is necessary, and how the final results should be presented for easy comprehension is desired.
Basic concepts for Discoverer managers
· Business intelligence
Business intelligence is the ability to analyze data to answer business questions and predict future trends.
Oracle Discoverer is a great business intelligence tool because it enables users to analyze data in an ad hoc way. Discoverer users can choose the data to analyze and can continue manipulating results until they have the necessary information to take business decisions. Oracle Discoverer also enables users to share the results of their data analysis with their colleagues in different formats (including charts and Excel spreadsheets).
· Relational databases, OLTP systems, and data warehouses
A relational database stores data in tables that are composed of rows and columns that contain data values. The overall structure of a relational database management system (RDBMS) can be set up in any number of ways, depending on how the system will be used.
A typical RDBMS is designed for online transaction processing (OLTP), with the main objective of storing vast quantities of transaction data as efficiently as possible. OLTP system design is primarily concerned with getting data into an RDBMS. An OLTP system contains the information that a business uses on a day-to-day basis. The information in an RDBMS designed for an OLTP system is typically process-oriented, current, and subject to change.
A data warehouse is an RDBMS with a structure designed to facilitate data analysis, rather than simply efficient storage of information. Data warehouse design is primarily concerned with getting data out of an RDBMS. The information in a data warehouse is typically subject-oriented, historical, and static.
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.
· End user Layer (EUL)
The End User Layer (EUL) insulates Discoverer end users from the complexity and physical structure of the database. The EUL provides a business-focused view of the database that can be tailored to suit each Discoverer end user or user group. The EUL enables Discoverer end users to focus on business issues instead of data access issues. It helps Discoverer end users produce queries by generating SQL and provides a rich set of default settings to aid report building.
The metalayer structure of the EUL preserves the data integrity of the database. Whatever the Discoverer manager or the Discoverer end user does with Discoverer, it affects only the metadata in the EUL and not the database.
The EUL is a collection of approximately 50 tables in the database. These are the only tables that can be modified through Discoverer Administrator. Business areas are defined in Discoverer Administrator using the EUL database tables. Discoverer provides read-only access to the application database.
· Business Area
No user is interested in all the information in the database. The users are much more likely to be interested in a subset of the information that is connected in some way to the job that they do. Using Discoverer Administrator, we can create one or more business areas as containers of related information.
Having created a business area, we can load the database tables containing the related information into that business area.
· Folders and items
The tables and views loaded into a business area are presented to Discoverer end users as folders. The columns within a table or view are presented as items.
Often the database tables and columns have names that users will not find meaningful. Using Discoverer Administrator, the names of folders and items can be made meaningful than the names of the tables and columns on which they are based.
The folders in a business area do not have to be based directly on database tables or views. Complex folders can be created that contain items based on columns from multiple tables or views. Also, custom folders based on SQL statements which a user can write can be created.
Similarly, the items in a business area do not have to be based directly on columns. Calculated items that perform calculations on several columns, or that make use of the analytic functions available within the Oracle database can also be created.
Oracle Discoverer end users analyze information by including items in worksheets and using Discoverer's data analysis and charting wizards to find the information they are interested in. Discoverer worksheets are grouped into workbooks. A workbook can be stored on the file system or in the database.
End users can be restricted to analyze information in worksheets that have been created for them. In other situations, it will be more appropriate to allow end users to create their own worksheets. Discoverer Administrator can be used to decide which end users can create their own workbooks, and which end users can only use workbooks that have been created for them.
· Hierarchies and drills
Hierarchies are logical relationships between items that enable users to drill up and down to view information in more or less detail.
To analyze information effectively, Discoverer end users will want to drill down to see more detail about a particular piece of information (e.g. if the sales total for a specific region is disappointing, an end user will typically want to drill into the region's sales total figure to see which cities within that region have under-performed) drill up to see how the detail data contributes to information at a higher level (e.g. when looking at the sales figure for a particular city, an end user will typically want to drill up to see the total sales figure for the region)
When tables are loaded into a business area, Discoverer automatically creates default date hierarchies for date items. Users can create their own hierarchies for other items as well.
Summary folders are a representation of queried data that has been saved for reuse.
Summary folders can be created with Discoverer Administrator to improve query response time for end users. The response time of a query is improved because the query accesses pre-aggregated and pre-joined data rather than accessing the database tables. Discoverer can also be directed to use summary folders based on tables containing summary data that have been created by another application. These tables are known as external summary tables.
How does Discoverer Work?
Users' requests for information from the database are in the form of worksheets. 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 and displays the result set that is returned from the database
In the case of Discoverer Plus and Discoverer Viewer, the SQL statements are routed to the database via Discoverer processes running on an application server machine.
Steps for a successful Discoverer implementation
There are essentially six steps to the implementation of a Discoverer system, as shown in the flowchart below:
Figure 1-2 Discoverer implementation flowchart
These six steps are described in more detail below.
· Identify users' requirements
For a Discoverer implementation to be successful, it must meet users' requirements. To find out what those requirements are, conduct interviews with key users and ask them questions like:
Ø What information do you use now?
Ø What information would they like to see?
Ø How would they like the information presented?
As a starting point, review the reports and information sources that users are currently using?
Quickly we can understand how using Discoverer will give users both access to the information they currently use and the ability to analyze that information in new and powerful ways.
· Create an EUL (mandatory if one does not exist already).
An EUL must exist before creating a business area. If an EUL does not already exist, must create one.
· Create a business area and load data into it (mandatory).
Having identified users' requirements, we can have a good idea of the information that users need to access. For example, one group of users might want to access sales information, another group might want to access manufacturing information, and so on.
In Discoverer, we can group information with a common business purpose into a business area. Having created a business area, we must specify which database tables and views hold that information. This can be done by 'loading' the tables and views into the business area.
· Refine the structure of the business area so that users can view data in the most flexible and understandable way.
The default settings and contents of a business area are sufficient to enable users to access and analyze data. However, Discoverer Administrator provides with a number of features to enhance the default analysis capabilities.
Ø create optional and mandatory conditions to restrict the number of rows returned in a folder
Ø create calculated items to relieve users of the task of creating complex calculations by providing them with ready-made computations
Ø create joins to combine folders that were not automatically joined when tables were loaded from the database
Ø combine folders into complex folders to completely hide joins and relational structures from users
Ø create custom folders, to represent a result set returned by a SQL statement that you have entered as a folder with items
Ø edit item names, descriptions, and other formatting information to make data easier to understood
Ø Create item classes to support lists of values, alternative sorts, and drill to detail
Ø create hierarchies to simplify drill-down operations
Ø create summary tables (or let Discoverer automate summary management, or register existing summary tables) to maximize query performance
· Grant business area access to users or roles (mandatory)
Having identified users' requirements, we can have a good idea of which users (and groups of users) need access to which information. In some cases, different users will want access to the same information.
For example, information about an employee might be required by the employee's manager, payroll staff, and users in the Human Resources department.
In other cases, it is appropriate for only one group of users to have access to the information. For example, information about an engineering project is invaluable for a project manager but of no interest to payroll staff.
Keeping users' information requirements in mind, can grant users access to the business area.
Note that Discoverer users (whether end users or managers) never compromise the security of the underlying database. Users cannot see information in Discoverer to which they do not already have sufficient database privileges to access. In other words, all Discoverer security and privileges are additional to the database security mechanisms.
· Discoverer Deployment
Users' requirements will determine which of the Discoverer components need to be made available to make available in your company.
When identifying their requirements, we will probably realize that some users want the ability to create their own worksheets, while other users simply want to use worksheets that have been created for them. In addition, some users will want to run Discoverer using a Web browser, using either a Java applet user interface or an HTML user interface.
Following table illustrates how to decide which Discoverer components to deploy:
Other factors will probably also influence the decision, including network performance and security issues.
· Maintaining a Discoverer system
Small amount of ongoing maintenance is required to make sure that Discoverer continues to meet users' requirements.
Typically, will continue to refine business areas by:
Ø adding new item classes, to support new lists of values, alternative sorts, and drill to detail
Ø adding new joins, to combine folders that were not automatically joined when tables were loaded from the database and which users now need access to in the same worksheet
Ø adding new optional and mandatory conditions, to restrict the number of rows returned in a folder
Ø adding new calculated items, to provide users with ready-made computations that were not initially required
Ø adding new complex folders, to simplify query creation
Ø adding new custom folders, to meet users' requirements that cannot be met using conventional folders
Ø adding new hierarchies, to enable users to analyze data in new ways
Ø adding new summaries to resolve performance issues with particular queries
In addition to the above, we can change which users have access to which business areas and the operations that individual users can perform in those business areas. For example:
Ø when a new user joins, grant them access to the business areas they need to do their job
Ø when an existing user changes jobs or departments, we might have to grant them access to new business areas, or revoke their access from previous business areas
· New features in Discoverer Administrator Version 9.0.2
Discoverer Administrator Version 9.0.2 contains the following new and improved features:
Ø Enhanced export - Export End User Layer (EUL) objects to a file using the Discoverer Administrator user interface. The EUL objects that you can export to a file include business areas, folders, item hierarchies, date hierarchies, item classes, workbook definitions (created in Discoverer Desktop and Discoverer Plus), PL/SQL function registration information, summary folders and the automated summary management (ASM) policy.
Ø Alphabetical sorting - Sort folders and items when you load a business area. You can also sort folders in a business area and sort the items in a folder from within the Workarea.
Ø EUL cross references - View the impact that deleting EUL objects will have on Discoverer workbooks. For example, Discoverer Administrator can tell you whether a folder you are deleting is referenced by a specific workbook and whether the workbook will be affected by deleting the folder
Ø Improved list of values - Specify in Discoverer Administrator how Discoverer Plus users select and display LOV options. You can determine the maximum number of rows to display to an end user (for each array fetch of data from the database), when a list of values is expanded in Discoverer Plus. You can also specify to hide duplicate values, cache the LOV once it has first been displayed and force end users to enter search criteria for long LOVs.
Configuring the Connect dialog for Administration Edition and Discoverer plus (for Windows)
Before connecting to Oracle Discoverer as an Oracle Applications User, configure the Oracle Discoverer Connect dialog to expect Oracle Applications users, as follows:
Select Tools | Options from the main menu to display the following dialog:
Select one of the following radio buttons:
Connect to standard EULs
Oracle Applications User check box is not displayed in the Connect dialog and Discoverer expects standard database users.
Connect to applications EULs
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
Oracle Applications User checks 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.
Entering Details into the fields GWYUID/Password and FNDNAM
If either the Connect to applications EULs or Connect to both standard and applications EULs radio button is selected, further details can be entered into the following fields:
Gateway User ID (GWYUID)/Password
This field enables to record the Gateway User ID and Password (the default value ‘applsyspub/pub' will be used if nothing is entered here).
This field enables to enter the Foundation Name (Default value `apps' will be used if nothing is entered here).
How to use Discoverer Administration Edition in Applications Mode
Create an Applications mode EUL
In order to run Discoverer in Applications mode, start Discoverer Administration Edition in Applications mode then create an Applications mode EUL. This EUL has special features that provide support for use with Oracle Applications.
The only native-Oracle user that can connect to an Applications Mode EUL is the EUL owner.
· Start Discoverer Administration Edition
· Connect as the dba entering the username/password and database connect string in order to create an Oracle Applications user.
The EUL owner must be a database user and not an Oracle Applications user.
If the Oracle Applications User check box is displayed beneath the Connect dialog make sure to select the Oracle Applications User option. Click Connect to display the following dialog:
Click Yes to display the EUL Manager dialog:
Click Create an EUL to start the Create EUL Wizard where you create a new database schema/user and Oracle Applications EUL:
Select the Create a new user radio button. This enables to create a new Oracle Applications EUL user/schema.
(If there is an existing user/schema select the Select an existing user radio button you can choose that user as the owner of the new Oracle Applications EUL).
Select the Grant access to PUBLIC check box. It is recommended to select this check box, however if you wish to explicitly give access to your EUL then do not check this box. But you will need to grant access to your EUL tables manually.
Select the New EUL is for Oracle Applications users ONLY check box.
This creates an Oracle Applications EUL in the user's Oracle schema (displayed in the User field).
Either enter a name and password for the new Oracle Applications user or Select the previously created user as the owner of the new Oracle Applications EUL.
Click Next to display the Create EUL Wizard Step 2 where you select the Oracle Applications schema and enter the schema password:
Use the drop down list to select the Oracle Applications schema containing the Oracle Applications FND tables.
Enter the password for the Oracle Applications schema.
Click Next to display Step 3 of the wizard where you select the Default and Temporary Tablespaces for the new database user/schema:
Highlight the required Default and Temporary Tablespaces you want to use for the new Oracle Applications user. Click Finish
This creates the tables and views for the new Oracle Applications EUL and populates them with default data. The following message is displayed:
Click OK to display the following dialog:
Click No unless you want to install the tutorial data
The following dialog is displayed:
Click Yes to connect as the owner of the EUL you have just created
Or click No to remain connected as the dba using the current database connection.
If you clicked Yes at the previous step, you are now connected to the Oracle Applications EUL just created (as the EUL owner).
You can now grant Task Privileges to an Oracle Applications user so that they can now administer this Oracle Applications EUL.
Granting Task Privileges
This section describes how to grant (or deny) the privilege to perform certain tasks as an Oracle Applications user.
The Privileges dialog box enables you to set task privileges. To open the Privileges dialog box, choose Tools | Privileges (or click the Privileges icon on the toolbar).
The Privileges page enables you to specify the task privileges granted to a responsibility or user.
The User/Responsibilities page enables you to grant task privileges to a user or responsibility.
Maintaining Assigned Privileges
If you want the list to include Oracle Applications users, select the Users check box (otherwise, clear it).
If you want the list to include Oracle Applications Responsibilities, select the Responsibility check box (otherwise, clear it).
The list is sorted alphabetically, with users at the top and responsibilities next.
Select the task privilege that you want to grant (or deny) to a set of users or responsibilities (from the drop-down list).
When you select a privilege from the drop-down list, a brief description of the privilege appears on the right-hand side of the dialog box.
Grant or deny the task privilege as required.
To grant a user or responsibility the task privilege, tick the relevant check box in the list.
To deny a user or responsibility the task privilege, clear the relevant check box in the list.
Preparing to Build a New Business Area
Load Wizard is used to build a New Business Area. The Load Wizard provides a user-friendly interface that enables to quickly:
· Name and describe the business area
· Load metadata into the business area
· Automatically create joins from existing relationships between tables
· Automatically create lists of values for items
Load Wizard: Step 1, Specifying the Metadata Source
The first page of the Load Wizard enables you to specify the source of the metadata that you want to populate the business area with.
Load Wizard: Step 1 gives the following choice:
Create a new business area
This option enables you to start the process for creating a new business area from scratch.
Open an existing business area
This option enables to open an existing business area.
.
Click Create a new business area.
Load Wizard: Step 1 adds the question, "Where do you want to load the metadata from?"
Specify the location of the metadata:
This option enables you to load tables and views from the standard Oracle dictionary.
Gateway
This option enables to choose the metadata source from the registered gateways.
Load Wizard: Step 2
Load Wizard: Step 2 will appear differently depending on whether On-line Dictionary or Gateway is selected on Load Wizard: Step 1.
Load Wizard: Step 2 (for On-line Dictionary)
Choose the database link from the Select a Database Link drop-down list.
By default, the database link is set to <Default Database>. This is the default database for the current user ID. The drop-down list only shows the databases that current user ID can connect to.
Select the users, whose objects to be loaded into the business area, from the Select the users you want to load list.
The users that appear in this list are those that have access to the database selected above.
Specify the pattern that user objects must match in order to be loaded in to the business area (in the Load user objects that match field).
By default the % symbol is specified. The % symbol is a wildcard that matches any character or string of characters. If you want to reduce the number of objects that can be loaded from the database, use the wildcard in combination with other characters as follows:
To load all objects, enter %.
To find all objects beginning with D, enter D%.
To find all objects ending with AND, enter %AND.
To find objects beginning with A and having a four letter name,
enter A_ _ _.
If you want to specify the type of tables to be loaded (for example, whether public or private, or whether owned by or accessible to the users you select) click Options and go to
By default, the Load Wizard will load only:
Tables owned by the users specified
This dialog box enables to specify the type of tables and views (from the user ID's database) that can be loaded.
Load Wizard: Step 3, Selecting Tables and Views
Load Wizard: Step 3 enables to select the specific tables and views (schema objects) that need to be loaded into the business area. The selections made in Load Wizard: Step 2 determine the tables and views that are available for selection in the Load Wizard: Step 3.
To change the selections made in Load Wizard: Step 2, click Back.
The left side of the wizard displays a hierarchical list of users (via the on-line dictionary) or schemas (via gateway) and the tables and views that are available to load into the business area.
Load Wizard: Step 4 enables to:
· control how the database column names are mapped to Discoverer item names
· specify how joins are created between items
· specify that Automated Summary Management (ASM) will recommend and create summaries after the load process, based on the folders that are created.
If you select this option Bulk Load may take longer to complete, but users should benefit from improved query performance. Do not select this option if you are going to run ASM later, or if you have limited free space available in your database.
· specify how date hierarchies are generated
· specify the default aggregate on data points
· specify which types of items you want Discoverer Administration Edition to generate lists of values for.
How Items are loaded (Axis Items or Data Point Items)
Items are loaded as data points if they are DECIMALS (that is, a NUMBER data type) and have a non-zero precision. Integer numbers, all keys, and all other data types are loaded as axis items, with a default position of "Top."
In Discoverer Plus, whether an item is an axis item or datapoint affects the items' default placement on cross tab worksheets, as follows:
Data points are shown with easy to pick aggregate functions and appear by default in the center of cross tabular reports, because they are usually numbers that users want to analyze. Another term for data points is measures.
Axis items can have visible lists of values; data points do not have visible lists of values. Axis items appear on the page, top, or side of cross tab reports by default. Another term for axis items is dimensions.
Load Wizard: Step 5 enables to name and describe the business area
· Specify a name for the Business Area in the Name field.
· Specify a description for the Business Area in the Description field. This step is optional.
Discoverer Administration Edition displays a progress indicator while it is generating new Business Area (and summaries, if appropriate). When it is finished, the progress indicator disappears and the new Business Area is displayed on the Data page of the work area.
Open an Existing Business Area
Select a business area or click Select All to select all the business areas.
The work area window opens with the Data tab selected, listing the business areas you selected.
Open an Existing Business Area
Exporting a Business Area to a File
Discoverer Administration Edition enables to export a business area to a file. This can be useful when copying business areas between EULs or when archiving data The following steps show how to export a business area to a file:
On the Data page of the work area, select the business area that you want to export.
Choose File | Export or click the right button of your mouse and
select Export from the Pop-up menu
this opens the Save as dialog box as shown below
Exporting the Business Area
The Save as type displays the file format the Business Area is saved in, which is Discoverer EUL(TM) Export File (*.eex)
Specify the location, filename and file format for the exported Business Area. If Discoverer EUL(TM) Export File format is chosen, give the file an EEX extension. For example, Export_file.eex. Click Save.
Importing EUL elements from a file
Discoverer Administration Edition enables you to import EUL elements (E.g. Business Areas, Folders, Functions etc.) from other EULs. To guide you through the process, Discoverer uses the
Import Wizard.
Identifiers are unique names used by Discoverer to identify unique EUL elements (and Workbook elements in Discoverer Plus).
When EUL elements are imported, Discoverer uses Identifiers to locate elements referring to the same business objects. This enables customized (or patched) elements to be preserved. For example, a folder named 'Sales' in EUL `A' may refer to the same folder named 'Sales Figures' in EUL `B'. Both folders have the same Identifier and can therefore be recognized as referring to the same element.
Identifiers are visible in the Discoverer Administration Edition but are hidden from Discoverer Plus users. Supported File Formats are:
· Discoverer 4.1 supports the following import EUL formats:
· Discoverer Export Files (*.EEX) exported using Discoverer 3.1 and earlier.
· Discoverer Export Files created using Discoverer release 4.1 and later, in XML format, (which also have an *.EEX file extension).
The Import Wizard provides a user-friendly interface. Select the files to be imported.
Import Wizard can be started at any time during a Discoverer Administration Edition session.
Choose File | Import.
This opens
the Import Wizard:
Step 1, as shown below:
Import Wizard: Step 1 enables to choose which EUL files to import.
· Click Add to display the file locate dialog.
· Locate and select one or more import files - selected files appear in the list.
Import Wizard: Step 2 enables to choose how to process Conceptually Identical Objects from another EUL.
Choosing match options
Specify what
action should occur when objects match:
The term `object' also applies to `element'
(E.g. Folders, Items, Functions etc.)
Rename the
imported object
Renames imported elements to differentiate them from your existing elements. For example, if you are importing a matching Folder named `Sales', selecting this option would rename the imported Folder to `Sales1' leaving you with two Folders, your existing one named `Sales' plus the imported Folder named `Sales1'.
Rename the existing object
Renames your existing elements to
differentiate them from imported elements.
For example, if you are importing a matching
Folder named `Sales', selecting this option would rename your existing
Folder to `Sales1' leaving you with two Folders, your existing one
renamed `Sales1' plus the imported Folder unchanged as `Sales'.
Do not import
the matching object
Will not import elements that match your existing elements. For example, if you are importing a matching Folder named `Sales', selecting this option would not import the matching Folder `Sales' leaving you with your existing Folder unchanged as `Sales'
Refresh the
object
Refreshes any matching elements. For example, if you are importing a matching Folder named `Sales', selecting this option would update your existing Folder `Sales' leaving you with one Folder named `Sales'. Specify whether to preserve display related properties (only available if the Refresh the object radio button is selected)
Preserve display related properties
The following item properties will not be
refreshed if this check box is selected:
- Default position
Specify how objects to be matched:
Match objects using element Identifiers. To ensure that Conceptually Identical Objects are matched correctly, use this option instead of the Display Name option.
By Display Name
Match objects using the element Display
Name.
This option is included here for backwards compatibility – It is recommended to match By Identifier.
Specify whether the current user should take ownership of the imported workbooks
Always take ownership of imported
workbooks
If this radio button is set then any imported workbooks will become owned by the current user.
Only take ownership if original owner
cannot be found
If this radio button is set then any imported workbooks will have their owner changed to the current user only if the original owner cannot be found in the current database.
Import Wizard: Step 3 enables to start the import and monitor its status as each EUL element is processed.
Choose Start to start the import.
The status bar at the top of the wizard shows the percentage of the import that has completed. The Log window displays status messages about the import process.
These messages provide information about imported elements.
These messages are warnings about potential problems - use the Cancel option to abort the import if required.
Save log...
After a completed import, this option
enables to save the
status information to a text file. Click `Save log... and choose a file name and location.
Cancel
This option aborts the Import - you may
want to use this option if there are warning messages in the Log window.
Choose Finish.
If you perform a complete Import, your Discoverer Data window is updated to reflect the elements imported, according to the matching options that you have chosen
Copying Business Areas between EULs
It might be needed to copy a business area from one EUL to another, for example, from a test system to a production system. The following steps show how to do this:
· Open the business area(s) you want to move.
· On the Data page of the work area, select the business area(s) you want to move.
· Choose File | Connect. Connect to the EUL that you want to move the business area into.
Editing Business Area Properties
The following steps show how to edit a business area's properties:
· Open the Business Area Properties dialog box
There are four ways of doing this:
Double-click
Double-click the relevant business area
icon on the Data page.
Popup Menu
Right-click
the relevant business area icon on the Data page
and choose Properties on the popup menu.
Toolbar Icon
Click the relevant business area on the Data page and click the Properties
tool bar icon ()
Menu
Click the relevant business area icon on
the Data page and choose Edit | Properties.
Figure 7-14
Business Area Properties with General Tab Selected
Set the business area properties as required.
For more information on the fields on this dialog box, click Help.
Deleting a Business Area
Open the Confirm Business Area Delete
There are two ways of doing this:
Popup Menu
Right-click the relevant business area icon
on the Data page and choose Delete Business Area on the popup menu.
Menu
Click the relevant business area icon on the Data page and choose Edit | Delete.
Specify the scope of deletion that you require:
Delete this Business Area
This option removes the Business Area itself, but does not delete the contents of the Business Area. The folders that are in the Business Area remain in the EUL.
Folders that exist in the EUL but do not belong to a Business Area are known as Orphan Folders.
Delete this Business Area and its Folders
This option removes the business area and all of the folders contained in that business area. It does not remove folders that are part of any other business area. This is the default option and is usually recommended.
Click
Impact (optional).
This
displays the Impact dialog box which shows the other objects
that may be affected by deleting this business area.
Click Yes to delete the selected business area based on the choices you have made.
Click No to close the Confirm Business Area Delete dialog box without deleting the business area.
Managing Folders & Items:
Folders represent a result set of data, much like a database view. It is useful to think of folders as a SQL statement that returns the result set. In fact the SQL is actually stored in the End User Layer, and is used in SQL generation.
The assignment of a folder to a business area should be considered temporary and changeable. A folder can be used in multiple business areas. In these cases, the folder's definition is still unique: it is simply assigned to multiple business areas. Folders can be removed from all business areas, but sustained in the EUL. Such folders are known as orphan folders.
Folder Types
There are three types of folder:
Whether a folder is simple, custom, or complex is only important to the administrator. There is no difference to the end user. Even in Discoverer Administration Edition, there is very little difference in the behaviors of these different types of folders. All folders can include calculations, joins, conditions, summaries, item classes, and hierarchies.
Simple Folders
Simple folders are created by loading a folder from the database or Oracle Designer. They map directly onto a single table or view. Items in simple folders represent columns or calculations on other items in the folder.
Complex Folders
Complex folders consist of items from one or more other folders. Complex folders enable you to create a combined view of data from multiple folders.
This can simplify the business area without creating a new database view. For example, we can create a Complex folder called Dept-Emp which has columns from both the DEPT and EMP tables. The user then only has to select from one folder, not two. All concepts of relational joins are hidden to the user.
For two Items from different Folders to belong to the same Complex Folder, a Join condition must exist between the two Folders.
Refresh
Custom Folders are refreshed by editing and
validating the existing SQL. Simple folders are refreshed when the Business Area is
refreshed.
Items
Items generated in a Custom folder do not have an Item Formula property where you can change the SQL expression for the item. Therefore, you cannot edit the formula of an item in a Custom folder, except by editing the SQL for the whole folder.
Properties
Properties for Custom folders do not
include database, owner, and table name, nor do they have component
source folders. The Properties dialog box for a Custom folder, displays a
field named
Custom SQL, which contains the custom SQL statement used to generate the custom folder.
This section describes how to create a custom folder:
On the Data page of the work area, select a business area (or any object within a business area).
Choose
Insert | Folder |
Custom.
This opens the Custom Folder dialog box
Specify the SQL
statement.
Specify the name of the folder in the Name field.
Click Validate SQL to ensure you have entered valid SQL.
This validates the SQL statement and saves the custom folder.
Note that Discoverer Administration Edition allows save the custom
folder even if the SQL is invalid. This enables to insert the SQL before
the actual database objects are created or made available. However, end
users will not be able to query the object until the SQL is valid.
Custom Folder Examples
This section consists of the following examples:
Example 2: Set Operator in a Folder Definition
Example 3: ODBC-specific SQL Syntax
Example 4: Subquery in a Folder Definition
Example 8: Speeding Up Lists of Values
Example 1: Synonyms
SELECT ENAME, JOB, SAL FROM EMP@ORCL
where EMP is a synonym that points to the EMP table on another database.
Example 2: Set Operator in a Folder Definition
SELECT "COMPANY1" COMPANY, ENAME, SAL FROM EMP@HQ
UNION
SELECT "COMPANY2", ENAME, SAL FROM EMP@REGIONA
where HQ and REGIONA are database links for remote databases. The result set is the union of all employees with a column named COMPANY1 to show which company they are from.
Example 3: ODBC-specific SQL Syntax
{EMP LEFT OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO}
This example uses the ODBC outer join syntax.
Editing Folder Properties
Folder properties are accessible through Folder Properties dialog boxes. This section shows how to enhance the user's view of the data by editing folder properties.
Folders can be assigned to and removed from any business area, and can be included in multiple business areas. However, for each folder, there is only one definition, shared across all the business areas that include it
When using the Folder Properties dialog box, can have changes saved as soon as you enter them by ticking Automatically save changes after each edit. With this option ticked, you don't have to click OK or Apply after each edit.
You can change folder names at any time without affecting its logical structure, because Discoverer uses an internal way of identifying folders which does not depend on the folder name. Names only affect the appearance of the information in the business area. However, Folder names must be unique within the EUL and Item names must be unique within a particular Folder.
Items:
An Item, is a representation of a database table's column, in the EUL. By presenting columns as Items, Discoverer enables the administrator to make formatting changes, name changes and other similar changes enabling the user to clearly read the data. Items are stored in folders and can be created, deleted, and moved among different folders.
Joins:
In Discoverer, a Join relates two folders using one or more common items. This is similar to a Join in the database which relates two tables using common columns.
The Joins created in Discoverer Administration Edition affect the combinations of items that can be selected during the following operations:
· Creating a worksheet in Discoverer Plus.
· Creating a Complex Folder in Discoverer Administration Edition.
Joins are defined with a master and detail end. The master end is the folder that has one row, for which there are several detail rows. For example, the relationship of a master row in the Department folder to the many detail rows in the Employee folder.
It is important to make sure you define the Join with the correct folders at the master and detail ends. If you set up this relationship incorrectly, it adversely affects what combinations of folders a user can combine in a single query, which in cases of queries with three or more folders can, in some circumstances, lead to misleading or incorrect results. It can also affect whether summary tables can be used to speed up queries.
Usually joins are one-to-many, where one row in the master folder is joined to multiple rows in the detail folder.
Occasionally there are one-to-one and many-to-many joins. Many-to-many joins are not supported directly in Discoverer, or in any relational system, although they can always be worked to be transformed to multiple many-to-one joins.
Users of Discoverer Plus cannot set up their own join conditions. However, they can decide which join path to use if more than one join exists.
Creating Joins
This section describes how to create Joins.
Creating a Join starts with either selecting the Item that will be the Master Item, or the Folder it belongs to. If you choose Insert | Join before selecting an Item or Folder, a selection dialog box opens for selecting the Item that will be the Master Item.
On the Data page of the work area, select the Item that you want to be the Master Item.
If you did not select the Master Item in step 1, the first New Join dialog box. Select the Folder that contains the Item that you want to be the Master Item and click OK.
This opens the main New Join dialog box. The Master Item is displayed in the Master Folder column.
Specify the Join type using the Operator field.
Operator-- use the drop-down list to select an operator for the type of join you want to create. Operators include:
equijoin, combining rows that have equivalent values for specified items | |
--select the folder that contains the detail item from the drop-down list. The detail item can be in a folder in either the same business area as the master item, or in a folder in a different business area. The syntax for the value in Detail Items is folder name.item name.
Name--name the join you are creating.
Description--text field for describing the join you are creating.
Multi-item--replaces the New Join dialog with a New Multi-item Join dialog with room for multiple rows of join criteria, which can be added and deleted using the Add and Delete buttons
Options--displays a dialog box for defining outer join conditions
This dialog presents the following options:
Outer join on detail--creates an outer join. Returns all master rows that have no corresponding detail items, plus all matching master and detail rows.
Outer join on master--creates an outer join. Returns all detail rows that have no corresponding master, plus all matching detail and master rows.
Calculations:
Typical business calculations include values such as profit margins, average revenues per month, expected sales, and percent of profit by product type. You can represent these business calculations in Discoverer by creating Calculation Items. Once created, Calculation Items behave much like any other Item in a Folder and can be used in Conditions, Summaries, Lists of Values, Joins, and other Calculation Items.
Calculation Items are created using expressions that can contain:
There are three types of calculations:
Derived items and aggregate calculations appear with different symbols to differentiate them.
A derived item is a non-aggregate expression that appears and acts exactly like any other item in the folder. Derived items can be axis items or data points and can be used anywhere one would use an ordinary item. They are static, in that their value depends only on the value of the other items in the same row, and when computed will be the same regardless of what other items are selected in the user's query.
Examples of Derived Items:
Aggregate Calculations
If the formula of a new item contains an aggregate or group function such as SUM, AVG, MAX, MIN, or COUNT, and the items it aggregates are in the current folder, the item is created as an aggregate calculation.
Examples of Aggregate Calculations:
Aggregate calculations are dynamic, in that their value depends on the other items selected in the Discoverer Plus worksheet in which they are used, because this affects the axis items that are grouped together and hence the number of rows that are aggregated. This is particularly important in the case of calculations that are ratios of two aggregates.
For example to calculate Margin, one would use the calculation SUM(Profit)/SUM(Sales) rather than Profit/Sales. Used in a query, the latter would result in SUM(Profit/Sales), which produces a different result from SUM(Profit)/SUM(Sales). Data points should always be summed before a ratio is computed.
Condition
A condition can be used to selectively filter out data. End users can use Conditions to restrict the results of their query to the areas they are interested in. This can result in faster queries.
There are two types of Condition:
A mandatory Condition is always applied to a query that contains one or more items from the folder that contains the Condition. Users of Discoverer Plus are not notified of mandatory Conditions and are unable to turn them off.
For example, you may want to assign a mandatory Condition to sales data for regional sales managers, limiting their view of sales to the region for which each manager is responsible.
· Optional
An optional Condition can be applied (or not), as required, to their worksheets by users of Discoverer Plus. They can also view the Condition's formula, but they cannot edit it.
For example, a Vice President responsible for all sales regions should be able to see all of the sales data, and also be able to apply Conditions to see sales data pertaining to specific sales regions.
You create mandatory and optional conditions in the same way, and although Discoverer Administration Edition enables you to change a condition from optional to mandatory and vice versa, there are some subtle differences between the two types of condition
Creating Conditions
On the Data page of the work area, either
select the Folder that you want to contain your new Condition, or
select the Item that you want to form part of your Condition.
Open the New Condition dialog box.
There are three ways of doing this:
Popup
Menu
Right-click
the Folder or Item on the Data page and choose New Condition... on the popup menu.
Toolbar Icon
Click the New condition toolbar icon ()
Menu
Choose Insert | Condition....
Specify the Description for your new Condition.
Set Type to either Required or Optional.
Software requirement:
· Oracle Applications 11.5.9
· Oracle 9iDS (for Discoverer Administrator & Desktop)
· Windows XP/2K or any MS client
DBA activities before using Discoverer:
1. Create a tablespace DISC for Discoverer users.
2. Create a user DISC with default tablespace DISC and sufficient quota on this tablespace
3. grant DBA to user DISC
How to Start Oracle Discoverer Administrator:
Follow the navigation below, on your Desktop to start the Discoverer Administrator. Administrator is used to define the basic objects to be used by end users to create online reports.
Start ->
All Programs ->
Oracle9i Developer Suite ->
Discoverer Administrator ->
Oracle 9i Discoverer Administrator
Login as user DISC:
Once the login screen appears, login as the user with DBA privilege granted at database level.
How to Create EUL:
First time when we login to the Discoverer Administrator, it will prompt for the creation of EUL. Follow the steps below to create an EUL.
Click YES to create EUL:
Click on the Tab “Create an EUL”…..
Check “New EUL is for use by Oracle Applications users ONLY”. This is essential to use the tables and objects of Oracle Applications Database.
Check “Grant access to PUBLIC” if all the Apps users need to access this EUL
Enter the APPS user password as this is the user who owns FND tables
Enter the Default tablespace as DISC (created in the step ONE) and the temporary tablespace TEMP. All the EUL objects will be stored in this tablespace.
The tables and views for this EUL are being created and later data will be populated in these tables.
Once EUL is created, above message will be displayed.
To create the tutorial data in EUL, the above screen will come, Click no as we need not to install tutorial data as this is used for demo and tutorial purposes only.
To login as the owner of just created EUL, click YES else continue to be as user DISC with current database connection. Click YES to login as the owner of the EUL.
How to create Business Area:
To work within Discoverer, we need to define a Business area which is a place to store all the objects to be used for report development.
Click on the “Create a new business area” and “On-line dictionary” to load metadata from option.
In the Database Link nothing to be selected and Default database DEV will be picked up automatically for the database usage.
For the training purpose, let us select user INV for the objects to be used.
We can choose the types of tables and views we want to see by selecting the options. By default it is Tables owned by the user and Private Tables.
Under INV user, let us select two tables for training purpose.
Do not create Joins as of now, this will be created later. Check the Date Hierarchies checkbox so that the Discoverer automatically creates the Date hierarchies.
List of values checkbox selection will create LOV for the types selected. This option automatically creates lists of values for each axis item of each type specified, except character items longer than 40 characters. The values are derived from the values in the database column
Name the business area and select the options how to see the object names. Also by checking the last two checkbox, tables and columns in the tables will be sorted.
Wait till the objects are loaded into the business area.
How to manage Privileges & Security:
Under Menu Tools -> Privileges, for the user TRAINER grant the Privileges as shown below and then apply. This will allow the user TRAINER to administer the Business area, set privileges, create and Edit business area.
Under Menu Tools -> Security, for the Business Area COMPANY_INV_TRAINING, check the box “Allow Administration” for user TRAINER and then Apply. This will enable the user TRAINER to administer this business area.
How to create joins:
From the Menu Select Insert -> Join. If multi-item join to be defined, select in the option. Joins on multiple columns can be created using this Wizard.
How to start Discoverer Desktop:
Start -> Oracle 9i Developer Suite -> Discoverer Desktop -> Oracle 9i Discoverer Desktop
Login to Oracle Applications User:
Login to Discoverer as Oracle Application User. This will provide the access to objects of Oracle Applications database.
All the Responsibilites attach to the user will be displayed in the LOV. Select the responsibility you want to login with. Choose
Create a New Workbook:
Select the Display as TABLE TYPE to create a tabular report. Other options Page-Detail Table, Crosstab and Page-Detail Crosstab can also be selected.
Business area on which the user has access privilege will be populated in the LOV. Select the Business area you want to work in.
Tables within the business area are called folders and columns of the folders are items. Select the items from the available folders which are to be used in the report.
The default layout of the report will be as displayed above. The positions of the items in the worksheet can be changed by click and drag.
Conditions can be defined to view only those data which match to the user’s requirement.
Above screen shows the condition for the item Segment1.
Additional Conditions on other items can be defined as well in the above screen by clicking New.
Reports can be sorted on the Items as shown above. Multiple sorting can be done on different items .
Calculations based on Item values or other functions can also be included in the report. In the above Wizard, Calculation column is being created based on the values in two Items.
Additional calculation items can be created as required by clicking on New button.
Output of the report for the sample run is shown above. This workbook can be saved either to database or on the local Desktop.
The above saved workbook in the database can be opened later. All the definition will be stored and when the definition is run later, report will be generated with current data.
Comments
Thank you so much for such a wonderful work.
Asha
th ank you
RSS feed for comments to this post