2 Using Discoverer Plus with a relational data source
This chapter introduces Discoverer Plus Relational and contains the following topics:
2.1 What is Discoverer Plus Relational?
Discoverer Plus Relational is the Discoverer component that enables you to create and analyze worksheets. Using Discoverer Plus Relational, you can access and analyze data from your company's database, without having to understand complex database concepts. Using wizards and menus, Discoverer Plus Relational guides you through the steps to retrieve and analyze data that supports your business decisions.
Figure 2-1 Oracle BI Discoverer components
Description of "Figure 2-1 Oracle BI Discoverer components"
Other Discoverer users can open the worksheets you share with them, using Discoverer Plus Relational, Discoverer Viewer, Discoverer portals, Discoverer Desktop, and Discoverer Web Services.
2.2 What is a relational data source?
A relational data source is a database in which information is stored in several database tables. Each database table comprises several columns, and one or more rows. The different tables in a database can be related. Having data in separate but related tables is an efficient way to store and retrieve information.
The example above shows three database tables:
-
the STORE table contains three columns:
-
Store_key
-
City
-
Region
-
-
the PRODUCT table contains three columns:
-
Product key
-
Description
-
Brand
-
-
the SALES_FACT table contains five columns:
-
Store_key
-
Product_key
-
Sales
-
Cost
-
Profit
-
Relational databases are a widely used data source. However, relational databases can become complicated when they contain hundreds of related tables, with thousands of columns, and millions of rows. Finding information in such a database can be a real challenge.
The Discoverer manager uses the Discoverer End User Layer to hide the complexity of the database from you. By defining business areas, folders, and items, the Discoverer manager enables you to use Discoverer Plus Relational to find just the data you want to analyze.
2.3 What are business areas?
A business area is a collection of related information in the database.
The Discoverer manager works with the different departments in your organization to identify the information that each department requires from the database. The Discoverer manager locates the information in the database and groups it into business areas. Within each business area, the Discoverer manager organizes information into folders.
For example, the key areas of a company's business might be sales, production, and human resources. So the Discoverer manager might create three corresponding business areas.
Figure 2-3 Three Discoverer business areas
Description of "Figure 2-3 Three Discoverer business areas"
The Discoverer manager also decides which users can access which business areas.
The figure below shows how business areas are displayed in Discoverer Plus Relational. The business areas in this example are called Video Store Tutorial, and NewBusinessArea1.
2.4 What are folders?
A folder is a collection of closely related information. For example, information about products that your company produces might be in a folder called Products.
If you are familiar with relational databases, a folder is similar to a database table or view. Indeed, a folder can be based directly on a database table or view.
The different folders in a business area might contain related information. For example, a business area might contain two folders:
-
a Products folder containing information about each product (for example, product ID, product description, product brand)
-
a Sales folder containing information about the sales of each product (for example, the store where a particular product was sold, the price for which the product was sold, the cost of the product, the profit made on each sale, and the product reference number)
Figure 2-5 A business area containing two folders
Description of "Figure 2-5 A business area containing two folders"
By querying the Sales folder, you can see information about a particular sales transaction. But to see the description of the product that was sold (rather than simply the product reference number) you must also query the Products folder.
The Discoverer manager might combine information from several folders into one folder to make it easy to find all the information you need. For example, the Discoverer manager might create a third folder called Product Sales Info, containing the description of each product sold (from the Products folder) and the price that was paid for it (from the Sales folder).
Figure 2-6 A Discoverer folder containing information from a Products folder and a Sales folder
Description of "Figure 2-6 A Discoverer folder containing information from a Products folder and a Sales folder"
Figure 2-7 shows how folders (for example, Video Analysis Information, Stores and Sales Details, Monthly Sales Analysis) are displayed in Discoverer Plus Relational's item navigator tab.
2.5 What are items?
Items are different types of information within a folder.
If you are familiar with relational databases, an item is similar to a column in a database table. Indeed, an item might be based on a column in a database table.
For example, each product your company produces might have a reference number, a description, and a brand. The Products folder containing information about these products would have three items (a Product Reference Number item, a Description item, and a Brand item).
Figure 2-8 A Discoverer folder containing three items
Description of "Figure 2-8 A Discoverer folder containing three items"
Each item contains individual pieces of information. For example, the reference number item might contain a list of reference numbers.
The Discoverer manager decides which items are included in folders based on the information you want to analyze.
Figure 2-9 shows how items (for example, Department, Region, City) are displayed in Discoverer Plus Relational's item navigator tab.
2.6 What is the Discoverer Plus Relational sample database?
Discoverer is supplied with a sample database containing data about a fictitious company called Video Stores. Information in the Video Stores sample database includes:
-
sales region
-
year
-
department
-
sum of profit
-
size of store (in square metres)
-
type of store design (for example, compact, modern, or traditional)
-
store name
Discoverer is also supplied with a sample workbook called Video Tutorial Workbook that you can use to analyze the example data. The Video Tutorial Workbook contains several worksheets, including:
-
a table worksheet called Tabular Layout
-
a crosstab worksheet called Crosstab Layout
Note: The Discoverer manager might have given the sample workbook a different name from Video Tutorial Workbook.
Both worksheets enable you to analyze the sales and profitability of Video Stores (see Figure 2-10).
Figure 2-10 Sample Discoverer worksheets in the Video Tutorial Workbook
Description of "Figure 2-10 Sample Discoverer worksheets in the Video Tutorial Workbook"
Key to figure:
a. The Tabular Layout worksheet, showing profit figures for departments across regions for 2000.
b. The Crosstab Layout worksheet, showing a comparison of profit figures for each department in quarter one (Q1) and quarter two (Q2) across regions for 2000.
2.7 What are the new features in Discoverer Plus 11.1.1?
Discoverer Plus has been enhanced with Oracle BI Discoverer Web Services, which is an Application Programming Interface (API) that enables a client to obtain Discoverer connections, workbooks, and worksheets; to execute worksheet queries; and to return worksheet content using the SOAP protocol (version 1.1 with RPC/Encoded messaging format). This API enables you to use Discoverer Plus features programmatically and through the user interface.
For information see the Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Web Services API.
2.8 Where can I find out more about Discoverer Plus Relational?
To find out more about Discoverer Plus Relational, use:
-
the Discoverer Plus Relational help system
The Discoverer Plus Relational help system gives you context sensitive access to reference information from the Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Plus.
Click the Help button or press F1 in any Discoverer Plus Relational dialog to display context sensitive help, or choose Help | Help Topics to see a list of the topics in the help system.
To find a topic in the help system:
-
use the Contents tab to see a structured list of the topics in the help system
-
use the Index tab to search the help system using pre-defined keywords and phrases
-
use the Search tab to search the help system for keywords and phrase s that you define
-
-
the Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Plus
The Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Plus is available in html and PDF format on:
-
the Oracle Application Server Documentation CD
-
the Oracle Technology Network at www.oracle.com/technology
-
-
Oracle Technology Network
Other information about Discoverer Plus (for example, white papers, best practices) is available from Oracle Technology Network at www.oracle.com/technology.