|
Oracle® Business Intelligence Discoverer Administration Guide
10g Release 2 (10.1.2.1) B13916-04 |
|
 Previous |
 Next |
|
Oracle® Business Intelligence Discoverer Administration Guide
10g Release 2 (10.1.2.1) B13916-04 |
|
|
Previous |
Next |
This chapter introduces you to administering Oracle Business Intelligence Discoverer with a relational datasource, and includes the following topics:
"What is a relational datasource, an OLTP system, and a data warehouse?"
"What is Oracle Business Intelligence Discoverer Administrator?"
"What are the fundamental concepts behind a Discoverer system when using a relational data source?"
"What is your role as Discoverer manager when using a relational datasource?"
"What are the steps to a successful Discoverer implementation with a relational data source?"
"What is involved in maintaining a Discoverer system with a relational data source?"
"What are the prerequisites for using Oracle Business Intelligence Discoverer Administrator?"
"About Discoverer Administrator documentation and online help"
A relational datasource is a database that 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 Business Intelligence Discoverer provides business users with data analysis capabilities, regardless of whether the RDBMS was designed for an OLTP system or as a data warehouse.
Oracle Business Intelligence Discoverer Administrator is one of the components of Oracle Business Intelligence Discoverer.
Figure 3-1 Oracle Business Intelligence Discoverer components
Discoverer Administrator is a tool to hide the complexity of a datasource from business users, so they can answer business questions quickly and accurately using Oracle Business Intelligence Discoverer.
Discoverer Administrator's wizard-style interfaces enable you to:
set up and maintain the 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. For more information, see "Introducing the End User Layer".
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.
Discoverer Administrator is shipped as part of Oracle Business Intelligence Tools, which is a collection of business intelligence tools.
Before you design and implement a Discoverer system when using a relational data source, you need to become familiar with some fundamental Discoverer concepts.
These fundamental concepts are described briefly in the sections below:
Each of these sections is only a brief description, but includes a cross-reference to other chapters in this manual where you can find more information.
The End User Layer (EUL) insulates Discoverer end users from the complexity and physical structure of the database. The EUL provides an intuitive, business-focused view of the database that you can tailor 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.
For more information about the EUL, see Chapter 4, "Creating and maintaining End User Layers".
Typically, no single user (or group of users) 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, you create one or more business areas as containers of related information.
Having created a business area, you load the database tables containing the related information into that business area.
For more information about business areas, see Chapter 5, "Creating and maintaining business areas".
The tables and views you load 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, you can make the names of folders and items more helpful 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. You can create complex folders that contain items based on columns from multiple tables or views. You can also create custom folders based on SQL statements you write yourself.
Similarly, the items in a business area do not have to be based directly on columns. You can create calculated items that perform calculations on several columns, or that make use of the analytic functions available within the Oracle database.
For more information about folders and items, see:
Oracle Business Intelligence 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.
In some cases, you will want to restrict Discoverer end users to analyzing 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 enables you to decide which end users can create their own workbooks, and which end users can only use workbooks that have been created for them.
For more information about workbooks and worksheets, see Oracle Business Intelligence Discoverer Plus User's Guide.
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 you load tables into a business area, Discoverer automatically creates default date hierarchies for date items. Often you will want to create your own hierarchies for other items as well.
For more information about hierarchies, see Chapter 13, "Creating and maintaining hierarchies".
Summary folders are a representation of queried data that has been saved for reuse.
You create summary folders 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. You can also direct Discoverer 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.
For more information about summary folders, see:
As a Discoverer manager, you are responsible for ...[]::
the initial implementation of the Discoverer system
the ongoing administration and maintenance of the Discoverer system
There are essentially six steps to the implementation of a Discoverer system with a relational data source, as shown in the flowchart below:
Figure 3-2 Discoverer implementation flowchart (with a relational data source)
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 you like to see?
how would you like the information presented?
As a starting point, why not review the reports and information sources that users are currently using? You will quickly see 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.
Remember that users' requirements typically change over time. Often the biggest changes are requested by users when a system has been rolled out. When users see what Discoverer can do for them, they soon have suggestions for other areas where it could be useful.
Try and anticipate changing requirements. After all, a successful system often starts by meeting a subset of requirements and is then modified over time based on user feedback.
Create an EUL (mandatory if one does not exist already).
An EUL must exist before you can create a business area. If an EUL does not already exist, you must create one.
Create a business area and load data into it (mandatory).
Having identified users' requirements, you will 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, you group information with a common business purpose into a business area. Having created a business area, you must specify which database tables and views hold that information. You do this 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 you with a number of features to enhance the default analysis capabilities.
Specifically, you can:
create optional and mandatory conditions to restrict the number of rows returned in a folder (for more information, see Chapter 12, "Creating and maintaining conditions")
create calculated items to provide users with ready-made computations (for more information, see Chapter 11, "Creating and maintaining calculated items")
create joins to combine folders that were not automatically joined when tables were loaded from the database (for more information, see Chapter 10, "Creating and maintaining joins")
combine folders into complex folders to completely hide joins and relational structures from users (for more information, see Chapter 6, "Creating and maintaining folders")
create custom folders, to represent a result set (returned by a SQL statement that you have entered) as a folder with items (for more information, see Chapter 6, "Creating and maintaining folders")
edit item names, descriptions, and other formatting information to make data easier to understand (for more information, see Chapter 9, "Maintaining items and item classes")
create item classes to support lists of values, alternative sorts, and drill to detail (for more information, see Chapter 9, "Maintaining items and item classes")
create hierarchies to simplify drilling operations (for more information, see Chapter 13, "Creating and maintaining hierarchies")
create summary tables, let Discoverer automate summary management, or register existing summary tables to maximize query performance (for more information, see Chapter 14, "Managing summary folders")
Grant business area access to users or roles (mandatory).
Having identified users' requirements, you will 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, you 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.
Deploy Discoverer
Users' requirements will determine which of the Discoverer components you decide to make available in your company.
When identifying their requirements, you will probably have realized 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.
Use the table below as a guide to decide which Discoverer components to deploy in your organization.
| User requirement | Plus | Viewer | Desktop |
|---|---|---|---|
| Install and run Discoverer on a PC running Windows | No | No | Yes |
| Run Discoverer using a Web browser | Yes | Yes | No |
| Build new worksheets | Yes | No | Yes |
| Save workbooks to the file system | Yes | No | Yes |
| Customize Discoverer user interface | No | Yes | No |
Other factors will probably also influence your decision, including network performance and security issues.
Having decided which Discoverer components to deploy, refer to the appropriate documentation for specific installation or configuration instructions. For more information, see "Related Documents".
Having implemented a Discoverer system with a relational data source, you will probably find that a small amount of ongoing maintenance is required to make sure that Discoverer continues to meet users' requirements.
Typically, you will continue to refine business areas by:
adding new item classes, to support new lists of values, alternative sorts, and drill to detail (for more information, see Chapter 9, "Maintaining items and item classes")
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 (for more information, see Chapter 10, "Creating and maintaining joins")
adding new optional and mandatory conditions, to restrict the number of rows returned in a folder (for more information, see Chapter 12, "Creating and maintaining conditions")
adding new calculated items, to provide users with ready-made computations that were not initially required (for more information, see Chapter 11, "Creating and maintaining calculated items")
adding new complex folders, to simplify query creation (for more information, see Chapter 6, "Creating and maintaining folders")
adding new custom folders, to meet users' requirements that cannot be met using conventional folders (for more information, see Chapter 6, "Creating and maintaining folders")
adding new hierarchies, to enable users to analyze data in new ways (for more information, see Chapter 13, "Creating and maintaining hierarchies")
adding new summaries to resolve performance issues with particular queries (for more information see Chapter 14, "Managing summary folders")
In addition to the above, you will probably have to 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, you will have to grant them access to the business areas they need to do their job
when an existing user changes jobs or departments, you might have to grant them access to new business areas, or revoke their access from previous business areas
For more information, see Chapter 7, "Controlling access to information".
This section describes the system and data access prerequisites for using Oracle Business Intelligence Discoverer Administrator and contains the following topics:
Before you can use Discoverer Administrator:
A suitable database must be installed and available. An Oracle Enterprise Edition database will support the use of materialized views to improve the performance of summary folders.
Discoverer Administrator must have been installed on a PC, typically as part of a full Oracle Developer Suite installation.
Before end users can use Discoverer, either one or both of the following must have been installed:
Discoverer Plus and/or Discoverer Viewer must have been installed on an application server machine and configured correctly as part of an Oracle Business Intelligence installation (for more information, see the Oracle Business Intelligence Discoverer Configuration Guide)
Discoverer Desktop must have been installed on the end users' PCs.
To create and maintain a Discoverer system using Discoverer Administrator, you will require certain Discoverer privileges and database privileges:
to create an EUL you must have the privileges described in:
to manage an EUL you must have:
the Discoverer Administration privilege on the EUL
the Allow Administration privilege on business areas you want to modify
the SELECT database privilege on any tables you want to add to a business area
to install the sample data, see Chapter 27, "Installing the Discoverer sample data EUL, data, and workbook".
to take advantage of the following Discoverer features, you need specific privileges:
Automated Summary Management (for more information, see "What are the prerequisites for creating summary folders with ASM?")
manual summary folder creation (for more information, see "What are the prerequisites for creating summary folders manually in Discoverer?"
To use a Discoverer system, end users will require certain Discoverer and database privileges:
access to at least one EUL
access to at least one business area in the EUL
the SELECT database privilege (granted either directly to database users or via a database role) on the tables on which folders in a business area are based
to take advantage of the following Discoverer features, users need specific privileges
scheduled workbooks (for more information, see "What are the prerequisites for scheduling workbooks?")
user PL/SQL functions (for more information, see "What are custom PL/SQL functions?"
Discoverer uses a number of database parameters to take advantage of functionality that is available in the database. Database parameters need to be set to recommended values to achieve the expected outcome. You can specify database parameter values in the initialization file of the Oracle DBMS, the INIT<SID>.ORA file. For example, Discoverer's summary management feature, and workbook scheduling feature both use the Oracle database's native scheduling capability. The value you specify for job_queue_processes will affect both features.
The following table lists the database parameters referenced in this guide, that are used by Discoverer.
| Possible result if value is not set | Database parameter | Description | Recommended value |
|---|---|---|---|
| No scheduling or summary folders. | job_queue_processes | Related to workbook scheduling and summary processing. For more information, see "What Oracle database features support workbook scheduling?" | greater than 1 |
| No query prediction given. | timed_statistics | Related to enabling query prediction. For more information, see "How to verify and change the timed_statistics parameter for query prediction". | TRUE |
| No query prediction given. | optimizer_mode | Related to enabling query prediction. For more information, see "How to verify and change the optimizer_mode parameter for query prediction". | CHOOSE |
| Slower queries. | optimizer_index_cost_adj | Related to tuning the way the Cost-Based Optimizer uses indexes. For more information, see the Oracle10g documentation. | For more information, see your database administrator. |
| Slower queries. | optimizer_index_caching | Related to tuning the way the Cost-Based Optimizer uses indexes. For more information, see the Oracle10g documentation. | For more information, see your database administrator. |
| Invalid connect strings. | global_names | Related to setting up generic connectivity. For more information, see "How to set up generic connectivity for Discoverer using Oracle Application Server Control". | FALSE |
| Database processes not relocated to alternative backup component on database failure. | failover_mode | Related to enabling support for Transparent Application Failover. For more information, see "How to enable Discoverer support for Transparent Application Failover". | FAILOVER_MODE=<type><retries> |
| Database processes not relocated to alternative backup component on database failure. | type | Sub-parameter of the failover_mode parameter. For more information, see "How to enable Discoverer support for Transparent Application Failover". | (TYPE=SELECT) |
| Database processes not relocated to alternative backup component on database failure. | retries | Sub-parameter of the failover_mode parameter. For more information, see "How to enable Discoverer support for Transparent Application Failover". | (RETRIES=5) |
To start Discoverer Administrator:
From the Windows Start menu, choose Programs | Oracle Business Intelligence Tools - <BI_TOOLS_HOME_NAME> | Discoverer Administrator to display the Connect to Oracle Business Intelligence Discoverer Administrator dialog.
Figure 3-3 Connect to Oracle Business Intelligence Discoverer Administrator dialog
Enter the username of the database user with which you want to start Discoverer Administrator in the Username field
Enter the password of the database user with which you want to start Discoverer Administrator in the Password field.
Specify the database to connect to in the Connect field, using the following guidelines:
if you are logging onto your default Oracle database, do not enter anything in the Connect field
if you are logging onto a different Oracle database, specify the name of the database (if you are not sure which name to use, contact your database administrator)
Click the Connect button to start Discoverer Administrator and connect to the database.
What you see next depends on your Discoverer system:
if you have access to one or more EULs, you are prompted to create a new business area or open an existing business area in your default EUL (for more information, see Chapter 5, "Creating and maintaining business areas")
if you do not have access to any EULs (e.g. if you are the first person to use Discoverer Administrator in your organization, or if you have not been given Administration privilege on any existing EULs), you are prompted to create a new EUL (for more information, see "How to create an End User Layer for an existing database user")
Notes
The Connect to Oracle Business Intelligence Discoverer Administrator dialog might contain the Oracle Applications User check box. Select this check box if you want to use Discoverer Administrator to manage an EUL for use with Oracle Applications. For more information about using Discoverer with Oracle Applications, see Chapter 17, "Using Discoverer with Oracle Applications".
You always start Discoverer Administrator in your default EUL. Your default EUL is the one specified on the "Options dialog: Connection tab". If you want to change the EUL you are working in, you must change your default EUL and then reconnect to Discoverer Administrator (for more information, see "How to view or change the default End User Layer").
When you use Discoverer Administrator, you should make sure that no more than one current connection exists against a particular EUL. When you have a single connection against an EUL, you avoid the risk of unknowingly making conflicting changes to EUL objects.
The Workarea is your view into the End User Layer. The Workarea is where you maintain the EUL by creating and editing:
business areas and folders and items
hierarchies
item classes
summary folders
The Workarea window is displayed within the Discoverer Administrator main window. You can open more than one Workarea window at a time, which is useful when you want to copy objects between business areas. Note however that all Workarea windows contain the same business areas.
The Workarea window contains four tabs:
The Data tab displays the structure and content of each business area. The Data tab enables you to:
create calculated items
create complex and custom folders
create joins
create conditions
create new business areas, folders, and items
modify object properties
For more information about the Data tab and the icons displayed on it, see "Workarea: Data tab"
The Hierarchies tab displays the hierarchies within each business area. The Hierarchies tab enables you to:
create new hierarchies
review the content and organization of existing hierarchies
view the hierarchy templates supplied with Discoverer Administrator
The Show button on the Hierarchies tab enables you to specify the hierarchies that are displayed.
Figure 3-5 Workarea window: Hierarchies tab
For more information about the Hierarchies tab and the icons displayed on it, see "Workarea: Data tab".
The Item Classes tab displays the item classes within each business area. The Item Classes tab enables you to:
create new item classes
view the list of values associated with an item class (if there is one)
view items that use each item class
identify the item classes that have drill to detail and alternative sort attributes, and whether those options are active
The Show button on the Item Classes tab enables you to specify the item classes that are displayed.
Figure 3-6 Workarea window: Item Classes tab
For more information about the Item Classes tab and the icons displayed on it, see "Workarea: Data tab".
The Summaries tab displays the summary folders within each business area. The Summaries tab enables you to:
create new summary folders
review the organization and definition of summary folders
refresh summary folders
Figure 3-7 Workarea window: Summaries tab
For more information about the Summaries tab and the icons displayed on it, see "Workarea: Data tab".
If you click the right-mouse button when working with Discoverer Administrator, a popup menu is displayed. In the Workarea window, the contents of this popup menu are the commands most frequently used with the currently selected object.
If no object is currently selected, the popup menu displays commands for working with a business area in general and commands appropriate to the current tab.
When you first start Discoverer Administrator, the Administration Tasklist is displayed on top of the main Discoverer Administrator window.
Use the Administration Tasklist in two ways:
as a reminder of the basic steps involved in preparing a business area
as a shortcut method of displaying the dialogs associated with the listed tasks
Discoverer Administrator Version 10.1.2 contains the following new and improved features:
Discoverer Administrator UI enhancements to handle large numbers of users - Discoverer Administrator has improved its ability to search, select and display large numbers of users. For more information, see "Select User/Role dialog".
Complex folder reach through - Increases the availability of items from complex folders and their base folders to end users. For more information, see "What is complex folder reach through?"
Auto Generate naming - Automatically updates the names of EUL items when the names of the EUL items from which they are derived are changed. For more information, see "About generating and updating EUL item names automatically".
Transparent Application Failover - Transparently recovers from database failover. For more information, see "About Transparent Application Failover".
Advanced Security Option (ASO) Encryption Certification - Discoverer is certified to work against the ASO option in the Oracle database. For more information, see "About Discoverer and security".
Discoverer Administrator is supplied with online Help and documentation.
The Discoverer Administrator Help System gives you context sensitive access to reference information from the Administration Guide in HTML format.
To start the Help System either click Help in a Discoverer dialog or choose Help | Help Topics.
the Help button on Discoverer Administrator dialogs displays detailed context sensitive help on the fields in the dialog
the Help | Help topics menu option displays the contents of the Oracle Business Intelligence Discoverer Administrator help system, including the context sensitive dialog help
the Help | Manuals menu option displays a list of available Discoverer manuals (including the Oracle Business Intelligence Discoverer Administrator error messages file)
To find a topic in the Help System:
click the Contents icon at the top of each help page or choose Help | Help Topics (to see a list of the topics in the help system)
click the Index icon at the top of every help page to see a list of index entries
To view (and print) the Administration Guide in PDF format, use the Oracle Developer Suite documentation CD.
Hint: To search for words or phrases, use the Administration Guide in PDF format.
Additional information about Discoverer (e.g. whitepapers, best practices, tutorials) is available on the Oracle Technology Network at www.oracle.com/technology.
