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.

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.

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.

In the EUL Manager dialog box, click Create an EUL.

The Create EUL Wizard is launched.

In the Create EUL Wizard: Step 1 dialog box, select Selecting an existing user and deselect Grant access to PUBLIC. Click the Select button.

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.

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.

The EUL creation starts. The progress bars indicate that the tables and views are being created and populated with default data.

Finally, the message indicates that the EUL has been successfully created. Click OK.

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.

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.

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.

Click Next.

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.

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.

Click Next.

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.

Select the tables—Times, Channels, Countries, Customers, Products, Profits_tab, and Sales—and move them to the Selected list and click Next.

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.

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.

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.)

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.

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.

Click the plus sign (+) on the left of Products folder. The Products folder expands to show the items in it.

Click Prod Name item to select it.

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.

The Item Properties dialog box is displayed. Scroll up or down to see the various properties of the item.

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.

Click inside the Value column corresponding to the Name attribute. Edit the value to make it Profits.

Click OK. The folder is now renamed as Profits.

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;

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;

In Discoverer Administrator, select Tools > Privileges. The Privileges window appears.

In the Privileges window, click Select as shown in the screenshot.

The Select User/Role dialog box appears. Enter BI in the Search for field and click Go.

A list of users whose names start with BI is displayed. Select BI_USER1 and click OK.

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.

Select Desktop and Plus Privilege and select all the privileges under it. The privileges will be granted to BI_USER1.

Now, click Scheduled Workbooks tab. Click Select as shown.

Enter BI in the Search for field and click Go. Select BI_USER1 from the list of usernames that appear. Click OK.

Select User is never required to schedule workbooks. Click OK.

Now select Tools > Security. The Security window appears.

Click Select as shown in the screenshot.

The Select User/Role dialog box appears. Enter BI in the Search for field and click Go.

A list of users whose names start with BI is displayed. Select BI_USER1 and click OK.

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.

To create a complex folder, select the Sales History business area and then select Insert > Folder > New.

An empty folder named NewFolder1 is created. Observe the folder icon; it is different from the icon of other folders.

Right-click NewFolder1 and select Properties. The Folder Properties dialog box appears.

Edit the value of the Name attribute to Profits Analysis and click OK.

The folder is renamed.

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.

Select Window > New Window. This makes it easier to drag items to the complex folder.

Expand Sales History business area in both the windows.

In one of the windows, expand the Products folder.

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.

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.

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.

To create a join between the Profits and Products folder, select the Products folder and select Insert > Join.

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.

Leave all options in step 2 of the Join Wizard to the default value and click Finish.

Expand the Products folder and verify whether the join is created.

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.

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.

Select the Reach-Through tab.

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.

Select the Profits folder and select Insert > Item. The New Item window appears.

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.

The calculated item appears in the business area.

Now select the Times folder and select Insert > Item.

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.

The calculated item appears in the business area.

End users can now use these calculated items in their queries.

To create an item hierarchy, select Insert > Hierarchy. The Hierarchy Wizard appears.

Select Item Hierarchy and click Next.

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.

In step 3 of the Hierarchy Wizard, enter Product Hierarchy in the Hierarchy name field and click Finish.

To view the hierarchy, select the Hierarchies tab in the Discoverer Administrator window.

1.

To create a Date hierarchy, select Insert > Hierarchy. The Hierarchy Wizard appears.

Select Date Hierarchy and click Next.

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.

Similarly, select "Q"Q as the format for Quarter and Mon as the format for Month.

Click Next.

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.

In step 4, enter Time Hierarchy in the Hierarchy name field and click Finish.

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.

Select Have Discoverer recommend and create the best summaries and click Next.

To recommend the best summaries, Discoverer needs to analyze the base tables. Click Start to begin table analysis.

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.

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.

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.

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.

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.

Select I want to specify the summaries myself. Click Next.

In step 2 of the Summary Wizard, select From items in the End User Layer and click Next.

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 the kind of most common user queries and what items they consist of.

In this example, create a summary with all the items from the Profit Analysis complex folder. Expand the Profit Analysis folder and move all the items to the Selected Items list.

Click Next.

In step 4, you can specify different summary combinations. A summary table is created for each summary combination. Specifying more summary combinations ensures that a greater number of queries use summary data. Also this needs more space in the database. So select an optimum number of summary combinations.

In this example, use the summary combination that uses all the items in the summary. If a user creates a query with all these items, then that query will use summary data instead of base tables.

Click Next.

In step 5, you can specify the refresh frequency for the summaries. Summary can become stale if the data in the base table changes. You can specify a refresh schedule for the summaries taking this factor into account.

Click Next.

In step 6, you can specify a name for the summary and when to build it. Specify Profit Analysis Summary in the Name field, select Build immediately, and click Finish.

The Summaries tab displays the new summary created.

To connect to OracleBI Discoverer Plus, you need to specify connection information in the connections page. To launch the connections page enter the URL for the connections page in your Web browser. The URL is of the format:

http://<hostname>.<domain>:<port>/discoverer/plus

The Connect to OracleBI Discoverer page is displayed.

Note: Consult with the Discoverer manager for the correct URL.
An example URL might look like this: http://bi-server.mycompany.com:7778/discoverer/plus

Select OracleBI Discoverer from the Connect To drop-down list. Enter other values as follows:

User Name: bi_user1

Password: bi_user1

Database: orcl (This is your database connect string. Ask your Discoverer manager for the actual entry.)

End User Layer: BI_ADMIN (Note: The EUL name should be entered in uppercase letters.)

Click Go to connect.

OracleBI Discoverer Plus window appears with the Workbook Wizard active.

Ensure that Create a new workbook is selected, clear the Graph check box, and click Next.

In step 2 of the Workbook wizard, the Sales History business area is displayed. So, you can infer that granting bi_user1 access to the Sales History business area was successfull. Click the plus sign (+) on the left of the Profit Analysis complex folder to expand it.

The Profits Analysis complex folder contains all the items that you added while creating the complex folder. Drag all the items to the Selected list. Click Finish.

Hint: Hold the Shift key and click multiple items to select them at once.

The Query Progress indicator appears and the worksheet appears with the data.

Click Tools > Show SQL. The SQL Inspector window appears.

Click Plan to activate the Plan tab.

The Plan tab indicates that the query was redirected to a materialized view BI_ADMIN.EUL5_MV100311 instead of directly accessing data from the base tables. This materialized view was the result of creating the summary in a previous topic. So, you can conclude that the summary management is working as desired.

Note: The actual name of the materialized view might be different from the mentioned name.

Launch OracleBI Discoverer Administrator

Create an EUL for a new user

Create the Sales History business area by using the Load Wizard