Developing Sales History Business Area by Using OracleBI
Discoverer Administrator
This module describes how to create a sample Sales History
Business area by using OracleBI Discoverer Administrator. The module highlights
the main features of OracleBI Discoverer Administrator.
Approximately 2 hours
This tutorial covers the following topics:
Place the cursor over this icon to load and view all
the screenshots for this tutorial. (Caution: Because this action loads all screenshots
simultaneously, response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over
each individual icon in the following steps to load and view only the screenshot
associated with that step.
OracleBI Discoverer Administrator is used by Discoverer
managers to create, maintain, and administer the End User Layer, and to define
how users interact with the data. All administrative tasks for the EUL objects
(such as folders, business areas, and summaries) can be performed using Discoverer
Administrator. In this tutorial, you learn some of the major administrative tasks
related to creating the Sales History Business Area.
Back to Topic List
Before starting this tutorial, you should:
| 1. |
Install Oracle Business Intelligence Tools CD 10.1.2
|
| 2. |
Install OracleBI Sample workbooks. This lesson illustrates
key features of OracleBI Discoverer Administrator with the help of these
sample workbooks. If the sample workbooks are not installed, see instructions
in Installing
the Discoverer Sample.
|
Back to Topic List
| 1. |
To launch OracleBI Discoverer Administrator, select
Start > Programs > Oracle Business Intelligence
Tools - (BI Home Name) > Oracle Discoverer Administrator.
The Connect to Oracle Business Intelligence Discoverer Administrator dialog
box appears.
BI Home Name is the Oracle Home where you installed
the Oracle Business Intelligence Tools CD 10.1.2.
|
| 2. |
Enter system/system in the Username field and
the corresponding password in the password field. Also enter the appropriate
database connect string. In this example enter
orcl.
Database connect string is the connect alias for the database that is
defined in the tnsnames.ora file.
Click Connect.
|
Back to Topic List
Every user who connects to Discoverer Administrator
must be an EUL owner or must have administrative access to an EUL. If this is
not the case, Discoverer Administrator prompts you to create an EUL. You can
create a public or a private EUL. Public EULs are accessible to all users whereas
private EULs are accessible to the EUL owner only. You can grant access rights
on a private EUL to specific users. This is especially useful in situations
where two or more users perform the role of the Discoverer manager.
| 1. |
You log in to Discoverer Administrator as the system
user. Discoverer Administrator reports that this user does not have
access to any EUL and prompts you to create one.
Click Yes. The EUL Manager dialog box appears.
|
| 2. |
In the EUL Manager dialog box, click Create an EUL.
The Create EUL Wizard is launched.
|
| 3. |
In the Create EUL Wizard: Step 1 dialog box, select Selecting an existing
user and deselect Grant access to PUBLIC. Click the Select
button.
|
| 4. |
In the Select User dialog box, enter bi in the Search
for field and click the Go button. Select
bi_admin as the user. Click OK.
|
| 5. |
In the Create EUL Wizard: Step 1 dialog
box, enter the password for bi_admin as bi_admin.
(NOTE: For the other Discoverer OBEs, the EUL owner is bi_user. The
username bi_admin is used so that the bi_user is unaffected.) Click
Finish.
|
| 6. |
The EUL creation starts. The progress bars indicate that the tables and
views are being created and populated with default data.
|
| 7. |
Finally, the message indicates that the EUL has been successfully created.
Click OK.
|
| 8. |
OracleBI Discoverer is shipped with a sample
Video Stores tutorial. After the EUL has been successfully created,
Discoverer Administrator prompts you to install the tutorial. Click
No. The tutorial can be installed at any time later if required.
|
| 9. |
After you Install/Skip the tutorial installation, Discoverer Administrator
prompts you to connect as the user for whom you just created the EUL.
Click Yes.
The Load Wizard appears.
|
Back to Topic List
A business area is a logical grouping of information
about a common subject. For example, all the information related to a Human
Resources management system can be grouped together into a business area. Business
areas provide the end user a starting point for building a query. Moreover,
business areas are the units of access control in Discoverer.
In this topic, you will create the Sales History business
area using the Times, Channels, Countries, Customers, Products, Profits_tab,
and Sales tables from the SH schema. For creating the business area, bi_admin
user should have select access on these tables.
| 1. |
You create a business area by using the Load Wizard.
Immediately after creating an EUL, the Load Wizard appears. You can also
invoke the Load Wizard by selecting File > New. The Load Wizard is
currently open. Click Create a new business area.
|
| 2. |
Click Next.
|
| 3. |
In step 2 of the load wizard, you must select the schema that contains
all the tables that you want to include in the business area. Scroll
down and select SH.
|
| 4. |
Click Options. The On-Line Dictionary Options dialog box appears.
Here, you can select different options depending on what kind of tables
you want to see. For example, if you select Tables accessible
by user, all the tables in the user's schema along with the
tables in other schemas that the user has been granted privileges to
will be displayed
Click Cancel.
|
| 5. |
Click Next.
|
| 6. |
In step 3 of the load wizard, click the plus (+) symbol next to SH. All
the tables that belong to the SH schema are displayed.
|
| 7. |
Select the tables—Times, Channels,
Countries, Customers, Products, Profits_tab, and Sales—and move
them to the Selected list and click Next.
|
| 8. |
In step 4 of the load wizard, you can select what joins should be created,
whether or not to create additional EUL objects such as summaries, date
hierarchies, and default aggregates, and specify the data types for which
List of Values (LOV) should be created.
Clear Date Hierarchies and click Next.
|
| 9. |
In step 5, enter Sales History as the name of the business area.
You can also select options to control the way objects are named. For
example, if you select the option Replace all underscores with
spaces, the table names or column names that contain the symbol
underscore (_) will be replaced with a space. That is, a column called
Unit_Cost will be renamed Unit Cost.
|
| 10. |
Click Finish. The Sales History business area is created and is
displayed in the work area. (Note: The Administration Tasklist
appears by default. You can close the Administration Tasklist. You can
display the Administration Tasklist when required by selecting View
> Tasklist.)
|
Back to Topic List
A business area contains the following objects:
Using Discoverer Administrator, you can view and modify the
properties of each of these objects. For example, changing the display names
of objects, applying format masks to numeric values, hiding an object from the
end user, and applying or changing the default aggregate for items. You can
start by exploring the business area.
| 1. |
The Discoverer Administrator window displays four tabs—the
Data tab, the Hierarchies tab, the Item classes tab, and the Summaries
tab. In this topic, you observe the Data tab.
|
| 2. |
Make sure the data tab is selected. Click the plus sign (+) on the left
of Sales History. The business area expands to display all the folders
in it.
|
| 3. |
Click the plus sign (+) on the left of Products folder. The Products
folder expands to show the items in it.
|
| 4. |
Click Prod Name item to select it.
|
| 5. |
To view the item properties, right-click the item and select Properties.
Alternatively, you can select Edit > Properties from the menu or
you can click the  icon
on the toolbar.
|
| 6. |
The Item Properties dialog box is displayed. Scroll up or down to see
the various properties of the item.
|
| 7. |
You can now click any of the objects in the business area to display
its properties. Without closing the Item Properties dialog box, click
the Profits Tab folder. The dialog box now displays the properties
of the Profits Tab folder. Observe the Owner and Object properties. The
Object property indicates that the Profits Tab folder is based on the
PROFITS_TAB database object. The Owner property indicates that the Profits
Tab database object belongs to the SH schema.
To edit an item property, you click inside the Value column of the
corresponding attribute and select the new value. In some cases, you
need to type the new value—for example, the Description property.
You can also edit some properties for more than one item simultaneously.
To do this, select multiple items (by holding the Shift key) and then
invoke the property sheet.
|
| 8. |
Click inside the Value column corresponding to the Name attribute. Edit
the value to make it Profits.
|
| 9. |
Click OK. The folder is now renamed as Profits.
|
Back to Topic List
To enable end users to access the business area and perform
ad-hoc analysis using OracleBI Discoverer Plus you need to:
 |
grant appropriate privileges to each user
who will need to access the EUL |
|
grant access rights to the users
who will need to access the business areas |
|
grant select privileges on the base tables
to each of these users |
| 1. |
First, create a user bi_user1 for whom you can grant
privileges and access rights to the business area that you just created.
At the SQL* Plus prompt, connect as the system user and
enter the following SQL statements:
SQL> create
user bi_user1 identified by bi_user1 default tablespace users temporary
tablespace temp;
SQL> grant
connect,resource to bi_user1;
|
| 2. |
Now, grant select access to all the base tables to this user. To do this,
connect as SH/SH and enter the following SQL statements at the SQL prompt:
SQL> grant select on Channels
to bi_user1;
SQL> grant select on Countries
to bi_user1;
SQL> grant select on Customers
to bi_user1;
SQL> grant select on Products
to bi_user1;
SQL> grant select on Profits_tab
to bi_user1;
SQL> grant select on Sales
to bi_user1;
SQL> grant select on Times
to bi_user1;
|
| 3. |
In Discoverer Administrator, select Tools > Privileges.
The Privileges window appears.
|
| 4. |
In the Privileges window, click Select as shown in the screenshot.
|
| 5. |
The Select User/Role dialog box appears. Enter BI in the Search
for field and click Go.
|
| 6. |
A list of users whose names start with BI is displayed. Select BI_USER1
and click OK.
|
| 7. |
The list of privileges that can be granted for this user is displayed.
The privileges are of two types: Administrative privileges and Desktop
and Plus privileges. You grant the administrative privileges to a user
so that the user can perform administrative operations on the EUL. To
enable the user to access the EUL from Discoverer Plus, you grant the
privileges in the Desktop and Plus Privileges region.
|
| 8. |
Select Desktop and Plus Privilege and select all the privileges under
it. The privileges will be granted to BI_USER1.
|
| 9. |
Now, click Scheduled Workbooks tab. Click Select as shown.
|
| 10. |
Enter BI in the Search for field and click Go. Select
BI_USER1 from the list of usernames that appear. Click OK.
|
| 11. |
Select User is never required to schedule workbooks. Click OK.
|
| 9. |
Now select Tools > Security. The Security window appears.
|
| 10. |
Click Select as shown in the screenshot.
|
| 11. |
The Select User/Role dialog box appears. Enter BI in the Search
for field and click Go.
|
| 12. |
A list of users whose names start with BI is displayed. Select BI_USER1
and click OK.
|
| 13. |
Drag Sales History from the Available business areas list
to the Selected business areas list and click OK.
Alternatively, you can select the business area and click the move right
button.
|
| 14. |
Now, you have granted the user BI_USER1 privileges
on the EUL and also granted access rights to the Sales History business
area along with select access on the base tables. |
Back to Topic List
Complex folders simplify the user’s view of the data
model by combining data without the administrator having to create physical
views in the database. For example, creating a folder called Profits Analysis,
which combines items from other folders, enables a user to select from one folder
rather than two, and hides the actual relational joins from the user. The functionality
of a complex folder can be achieved by creating a database view. But creating
a complex folder helps when the Discoverer manager does not have database privileges
to create a view. Also, creating a complex folder avoids the need to create
database objects. Complex folders can be managed entirely by using OracleBI
Discoverer Administrator.
| 1. |
To create a complex folder, select the Sales History
business area and then select Insert > Folder > New.
|
| 2. |
An empty folder named NewFolder1 is created. Observe the folder icon;
it is different from the icon of other folders.
|
| 3. |
Right-click NewFolder1 and select Properties. The Folder
Properties dialog box appears.
|
| 4. |
Edit the value of the Name attribute to Profits Analysis and click
OK.
|
| 5. |
The folder is renamed.
|
| 6. |
Now, you need to add items to the folder. The Profits Analysis complex
folder should contain the following items:
Products.Prod Category
Products.Prod Subcategory
Products.Prod Name
Profits.Amount Sold
Profits.Quantity Sold
Profits.Total Cost
Profits.Profit
Times.Calendar Year
Times.Calendar Quarter Number
Customers.Cust State/Province
Customers.Cust City
Countries.Id
Countries.Region
Countries.Name
Channels.Class
Channels.Desc
To add items from different folders into the complex folder, a join
relationship should exist between the base folders. Check the joins
now. Expand the Countries Folder. You see that there is a join
between the Countries and Customers.
|
| 7. |
Select Window > New Window. This makes it easier to
drag items to the complex folder.
|
| 8. |
Expand Sales History business area in both the windows.
|
| 9. |
In one of the windows, expand the Products folder.
|
| 10. |
Now, drag Products.Prod Category, Products.Prod Subcategory,
and Products. Prod Name items from the Products folder to the
Profits Analysis complex folder. Hint: Hold Ctrl key and click multiple
items to select them at once.
|
| 11. |
Similarly, drag Profits.Amount Sold, Profits.Quantity Sold,
and Profits.Total Cost items from the Profits folder to the
Profits Analysis complex folder. Hint: Hold Ctrl key and click multiple
items to select them at once.
|
| 12. |
An error message is displayed that mentions that there is no join, which
will allow you to create a complex folder with the items from these folders.
So, you need to create a join between the Products and Profits folder.
Click OK.
|
| 13. |
To create a join between the Profits and Products folder, select the
Products folder and select Insert > Join.
|
| 14. |
The Join Wizard appears. Enter Products -> Profits in the Name
field. Select Products.Prod Id from Master Items drop-down list,
and Profits.Prod Id from the Detail Items drop-down list. The
equals (=) operator is selected by default. Click Next.
|
| 15. |
Leave all options in step 2 of the Join Wizard to the default value and
click Finish.
|
| 16. |
Expand the Products folder and verify whether the join is created.
|
| 17. |
Now repeat step 11. The items from the Profits folder are added to the
Profits Analysis complex folder.
Similarly, you can add the other items to the complex folder ensuring
that appropriate joins between the base folders are created.
You will have to create the following joins:
Join between Times and Profits folder based on Time Id.
Join between Customers and Profits folder based on Cust Id. There is
already a join between Customers and Countries, which was created based
on database constraints.
Join between Channels and Profits folder based on Channel Id.
|
| 18. |
With previous releases of Discoverer, end
users could select only the items that are added to the complex folder.
They could not select items from the base folders along with the items
from the complex folder. With this release of OracleBI Discoverer Administrator,
you can enable reach-through for complex folders, which enables end
users to select items from the base folders that are not included in
the complex folder.
Enable reach-through for the base folders of the Profits Analysis complex
folder. Right-click the Profits Analysis complex folder and select
Properties. The Folder Properties dialog box appears.
|
| 19. |
Select the Reach-Through tab.
|
| 20. |
Select Products and Profits to enable reach-through for
these folders. Click OK.
Now, end users can select items from the Profits Analysis complex folder
as well as the base folders Products and Profits.
|
Back to Topic List
A calculated item is used to create a new item when there
is no underlying database column that contains the data required. Calculations
can be simple, such as Sal*12, or they can be complex mathematical or statistical
expressions. The Discoverer manager can create commonly used calculated items,
which will appear as business area items. End users can then use these predefined
calculations in their queries. In this topic, you create a calculated item called
Profit in the Profits folder, which contains the formula Amount Sold - Total
Cost. Then, you create a calculated item called Quarter in the Times folder,
which uses the DECODE function to assign names to the quarter of the year based
on the value of the Calendar Quarter Number field in the Times table.
| 1. |
Select the Profits folder and select Insert
> Item. The New Item window appears.
|
| 2. |
Enter Profit in the Name field.
Expand the Profits folder, select Amount Sold item, and
click Paste. Now, click the minus (—) operator. Then, select
Total Cost item and click Paste.
Click OK.
|
| 3. |
The calculated item appears in the business area.
|
| 4. |
Now select the Times folder and select Insert > Item.
|
| 5. |
The New Item window appears. In the Name Field enter Quarter.
In the Calculation field, enter DECODE(Calendar Quarter Number,'1','Q1','2','Q2','3','Q3','Q4').
Click OK.
|
| 6. |
The calculated item appears in the business area.
End users can now use these calculated items in their queries.
|
Back to Topic List
The Discoverer manager creates hierarchies to enable end users
to drill down on data for more detail, drill up for summary data, and perform
associated data drills. You can also create a date hierarchy for drilling down
over time periods.
Back to Topic List
Creating Item Hierarchy
| 1. |
To create an item hierarchy, select Insert >
Hierarchy. The Hierarchy Wizard appears.
|
| 2. |
Select Item Hierarchy and click Next.
|
| 3. |
In step 2 of the hierarchy wizard, expand the Products folder and
drag Prod Category, Prod Subcategory, and Prod Name in that order
to the text box on the right, as shown.
Click Next.
|
| 4. |
In step 3 of the Hierarchy Wizard, enter Product Hierarchy in
the Hierarchy name field and click Finish.
|
| 5. |
To view the hierarchy, select the Hierarchies tab in the Discoverer Administrator
window.
|
Back to Topic
Creating Date Hierarchy
|
1.
|
To create a Date hierarchy, select Insert >
Hierarchy. The Hierarchy Wizard appears.
|
| 2. |
Select Date Hierarchy and click Next.
|
| 3. |
In step 2 of the hierarchy wizard, you must select the date formats for
the hierarchy. Expand Year and select YYYY. Move
YYYY to the list on the right.
|
| 4. |
Similarly, select "Q"Q as the format for Quarter and
Mon as the format for Month.
Click Next.
|
| 5. |
The date hierarchy that you are creating can be applied to any of the
items in the business area. In step 3 of the Hierarchy Wizard, click Next.
|
| 6. |
In step 4, enter Time Hierarchy in the Hierarchy name field and
click Finish.
|
Back to Topic List
Summary management is one of the most important
features of Discoverer. When used correctly, it can speed up query response
time significantly, resulting in queries that take seconds rather than hours
to execute. Managing summaries is the key to good performance with Discoverer
implementations. You use OracleBI Discoverer Administrator to define summary
folders. You can also use the automated summary management feature of Discoverer
Administrator for summary management.
Back to Topic List
Automated Summary Management
| 1. |
When using Automated Summary Management (ASM), Discoverer
performs all the summary administration for you and also finds out the
best summary combination strategies for the summary tables. ASM simplifies
the process of summary creation and maintenance by offering you a fully
automated solution to summary management. When run over a period of time,
ASM can automatically refine the set of summaries that it creates by using
the query statistics gathered by Discoverer from the user queries.
To perform summary management, the EUL owner requires
the following database privileges:
|
Create table
|
|
Create view
|
|
Create procedure
|
|
Create session
|
|
Create sequence
|
Also, you must ensure that the DBMS_JOB package is
installed. To ensure that the DBMS_JOB package is installed, connect
as system by using SQL*Plus as system and run the following
SQL statement:
SELECT * FROM
all_objects WHERE object_name='DBMS_JOB' AND object_type='PACKAGE'
If this SQL statement returns rows, then the DBMS_JOB
package is installed.
For more details about setting up your database for
summary management, refer to the Oracle Business Intelligence Discoverer
Administration Guide 10g Release 2 (10.1.2.0.0).
To start ASM, select Insert > Summary.
The Summary Wizard appears.
|
| 2. |
Select Have Discoverer recommend and create the best summaries
and click Next.
|
| 3. |
To recommend the best summaries, Discoverer needs to analyze the base
tables. Click Start to begin table analysis.
|
| 4. |
When the message 'Analyzing completed. Press Next to continue'
appears, click Next.
You can also click Advanced Settings to specify different options to
control the analysis.
|
| 5. |
In step 3 of the Summary Wizard, you can specify the amount of space
that needs to be allocated in the database for summaries. The graph displayed
helps you decide on the space allocation by comparing it against the performance
gain.
Note: Immediately after EUL creation, ASM may not be able to recommed
perfect summary creation. As the EUL usage increases over a period of
time, the ASM results will become more appropriate.
|
| 6. |
Click Recommended Summaries. A list of summaries that Discoverer
recommends is displayed.
The screenshot shows only one recommended summary. As the usage of
your EUL increases, ASM recommends more appropriate summaries to fine
tune the EUL.
Click OK.
|
| 7. |
Click Finish. The recommended summaries are created. To verify
the summaries created, select the Summaries tab.
Note: If Discoverer does not recommed any summaries, you will
have to cancel the process.
|
Back to Topic List
Creating Summaries by Using
Items from the EUL
| 1. |
You can choose to build the summaries manually. If you
build the summaries manually, you also have to think about the best summary
combination strategies. Sometimes, this helps you create better summaries
because you know the data best. There are three ways you can build a summary
manually:
|
Using items from the EUL
|
|
Using query performance statistics
|
|
By registering an external summary table
|
In this topic, you build a summary by picking items from the EUL. To
create a summary, select Insert > Summary. The Summary
Wizard appears.
|
| 2. |
Select I want to specify the summaries myself. Click Next.
|
| 3. |
In step 2 of the Summary Wizard, select From items in the End User
Layer and click Next.
|
| 4. |
In step 3, you need to select the items that should form the summary.
You must select the items that are most commonly used in queries by
the end users. So you must be aware of t | |
Bookmark