18
Managing Warehouse Builder Browser

Warehouse Builder Browser integrates with Oracle Portal to enable you to add and customize Warehouse Builder portlets. The Warehouse Builder Browser uses Warehouse Builder Public Views to create pre-built reports on all repository objects and relationships between objects. You can also use the Public Views to create your own custom reports.

This chapter contains the following information:

• Warehouse Builder Browser Overview

• Adding Portlets

• Administering Warehouse Builder Browser

• Creating Custom Reports

Warehouse Builder Browser Overview

Warehouse Builder Browser is a web based application that you can use to extend, access, and run reports on your repository metadata. To view the metadata and access these reports, you must have access to Oracle9iAS Portal.

When you first install or upgrade Warehouse Builder, you can use the Warehouse Builder Browser Assistant available on the start menu to install the Warehouse Builder Browser. The Browser Assistant enables you to set up an Oracle9iAS Portal from which you can access and create metadata reports. You can run the Browser Assistant during your installation process or defer it to a later time.

After Warehouse Builder Browser is installed, you can add the Warehouse Builder portlets to your Portal home page. You can run Metadata reports using the Browser from either the Warehouse Builder client or Oracle Portal.

About Oracle9iAS Portal

Oracle9iAS Portal enables you to create and view database objects using an HTML-based interface. It provides tools for creating HTML-based interfaces. Portal provides you with a centralized and personalized view of relevant applications and data in a single web site.

The fundamental building blocks of an Oracle9iAS Portal site are called portlets. A portlet is a re-usable information component that summarizes or provides access to an information source. Portlets can stand alone in a Portal site, link to other portlets or portal sites, or they can be nested within one another.

Warehouse Builder Browser integrates with Oracle9iAS Portal to obtain metadata reporting portlets.

Portlets for Managing Warehouse Builder Browser

The Warehouse Builder Browser consists of portlets that you can add to customize your portal page. You can add more than one of each portlet. For example, you can add two Reports portlets with each running a report on a different repository.

The portlets used for managing Warehouse Builder Browser include the following:

• Launcher Portlet

• Administration Portlet

• Reports Portlet

To access any portlet, you must first configure the Warehouse Builder Browser to access a Warehouse Builder Design Repository.

You can also add custom portlets to suit your business intelligence needs. For more information, see "Adding Portlets".

Launcher Portlet

The Launcher Portlet provides access to all the available functions, as shown in Figure 18-1. Other portlets contain subsets of all available functions.

• Browse a Repository: Select a role and click Browse to browse the repositories available to the current user. The main Warehouse Builder Browser page displays in full page mode. You can select from a list of repositories, and use the Navigator to view the detail of that repository. 

• Browse My Favorites: Select this link to view your Warehouse Builder Favorites in full page mode.

• Administer Warehouse Builder Browser: Select this link to view the Warehouse Builder Administration in full page mode. Use these pages to configure your Browser.

Figure 18-1 Launcher Portlet

Text description of the illustration laun_poa.gif

Administration Portlet

The Administration Portlet provides access to the following Administration features from your Oracle Portal home page, as shown in Figure 18-2:

• Register an OWB Repository: Register Warehouse Builder repositories and maintain database links.

• Register a Custom Report: Register custom reports.

• Purge Stale User Information: Purge obsolete Warehouse Builder Browser settings.

• Resource Management: Manage access privileges to Warehouse Builder Browser resources.

• Manage Preferences: Save and load preference settings from files or an existing schema.

• Manage Dependency Index: Improve performance of Impact Analysis by specifying how often to refresh the Dependency Index.

Figure 18-2 Administration Portlet

Text description of the illustration addport.gif

Reports Portlet

The Reports Portlet displays a Warehouse Builder Report on your Oracle Portal home page, as shown in Figure 18-3. The Reports Portlet displays one of your favorite reports. When you first add this portlet to a page, it does not contain a default report. You must first use the customize option to select and add a report from you favorites list.

When you have a Reports portlet on your Oracle Portal home page, it refreshes each time you reload the home page. This can delay the home page display.

Figure 18-3 Reports Portlet

Text description of the illustration 13_5a.gif

Adding Portlets

Warehouse Builder Browser portlets can be added to Oracle Portal after they have been installed on the machine running Oracle Portal.

For information about customizing Oracle Portal pages, see the Oracle Portal documentation.

To add a portlet to a page:

1. From the Oracle Portal page, select the Edit Page link from the upper-right corner of the page.

   The Edit Page displays the contents of the Oracle Portal page, as shown in Figure 18-4.

Figure 18-4 Oracle Portal Edit Page

Text description of the illustration 14_6.gif

2. Select the Add Portlets icon.

   The Add Portlets page displays a list of available portlets on the left side, and a list of selected portlets on the right side, as shown in Figure 18-5.

Figure 18-5 Add Portlets Page

Text description of the illustration addport.gif

3. Select the portlets to add to the Oracle Portal home page.

   The portlets you have added display in the right column. You can organize them by using the arrow buttons and delete them by using the X button.

4. Click OK when you are done.

   The Edit Page displays the Warehouse Builder portlets that you added, as shown in Figure 18-6.

Figure 18-6 Edit Page

Text description of the illustration 14_8.gif

Administering Warehouse Builder Browser

The Warehouse Builder Administration pages can only be accessed by Oracle Portal users with full administrator privileges.

From the Oracle Portal Home page, select the Administer Warehouse Builder Browser link to access the Warehouse Builder Administration pages.

Figure 18-7 shows the Administration Portlet in Warehouse Builder Browser.

Figure 18-7 Administration Portlet in Warehouse Builder Browser

Text description of the illustration admn_por.gif

Figure 18-8 shows the launcher portlet in Warehouse Builder Browser.

Figure 18-8 Launcher Portlet in Warehouse Builder Browser

Text description of the illustration laun_poa.gif

The Administration page contains links for the following administration actions:

• Register a Warehouse Builder Repository

• Register a Custom Report

• Resource Management

• Managing Preferences

• Managing the Dependency Index

Register a Warehouse Builder Repository

Before you can access repository metadata reports, register the Warehouse Builder repository with the Warehouse Builder Browser.

To register a Warehouse Builder Design Repository:

1. Click Register an OWB Repository on the Warehouse Builder Administration home page. The Register Repository page displays as shown in Figure 18-9.

Figure 18-9 Register Repository Page

Text description of the illustration 14_11.gif

2. Specify the Warehouse Builder repository properties. Table 18-1 lists the properties.

   Table 18-1 Warehouse Builder Repository Properties  

   Field	Description
   Name	The user-defined name to identify the repository in the browser system. This name is displayed in the navigation pages.
   Database Link	The name of the database link used to access the repository. The link must already be created using the Administer Database Links page. This field must be specified even if the repository is in the same database as the browser system.
   Description	The user-defined descriptive text. This appears in the navigation pages for the repository.

3. Click Apply to register the repository.

4. Click OK.

The repository displays in the Warehouse Builder Administration home page.

Managing Repositories

The Resource Management page lists all registered repositories. The actions listed next to the repository are described in Table 18-2.

Table 18-2 Repository Management  

Actions	Description
Access	Use this to grant or revoke repository access privileges to the users.
Edit	Use this to edit repository properties.
Unregister	Use this to unregister the repository. After unregistering the repository, it can no longer be browsed using the Browser system. You must re-register the repository if you want to browse it again.

Creating Database Links

Use the Administer Database Links page, as shown in Figure 18-10, to connect to Warehouse Builder repositories from the Browser.

Figure 18-10 Administer Database Links Page

Text description of the illustration 14_13.gif

To create a database link:

1. Click Register an OWB Repository on the Warehouse Builder Administration home page. The Register Repository page displays.

2. Click the link for Administer Database Links.

3. Select Create Database Link from the Administer Database Links page.

   Figure 18-11 shows the Create Database Links page.

Figure 18-11 Create Database Links Page

Text description of the illustration 14_14.gif

4. Specify the database link name.

5. Specify the Warehouse Builder Design repository user name and password.

6. Specify the remote database information.

   Provide the host address, service name, protocol, and host port number.

7. Click Apply to connect the link.

8. Click OK.

   The new link displays on the Administer Database Links page.

Viewing a Database Link

To view a database link:

1. Select the name of the database link from the Administer Database Links page.

   The View Database Link page displays with a detailed report on the database link you selected.

2. Click OK.

   The browser returns to the Administer Database Links page.

Editing a Database Link

To edit a database link:

1. From the Administer Database Links page, select edit for the database link you want to alter. The edit link is located under the Actions column.

   The Edit Database Link page displays as shown in Figure 18-12.

Figure 18-12 Edit Database Link Page

Text description of the illustration 14_15.gif

2. Edit the database link and click Apply.

3. Click OK.

Dropping a Database Link

Dropping a database link deletes it permanently. You must create a new link to use it again.

To drop a database link:

1. If the database link has been used to register Warehouse Builder repositories, unregister the Warehouse Builder repositories.

2. From the Administer Database Links page, select drop for the database link you want to drop. The drop link is under the Actions column.

   The database link is dropped and the browser returns to the Administer Database Links page.

Unregistering a Repository

To unregister a Repository:

1. Select the Administer Warehouse Builder Browser link from the Browser page.

2. Select Manage Resources.

   The Warehouse Builder Administration page displays as shown in Figure 18-13. The table at the bottom of the page lists the registered repositories listed.

Figure 18-13 Registered Repositories and Roles

Text description of the illustration unreg.gif

3. Select the repository to unregister and click on the unregister link.

   The repository is unregistered and no longer appears in the list of registered repositories. You can no longer browse it using the Browser.

Register a Custom Report

A custom report is an application component created in a tool such as the Oracle Portal facilities. Registration of the report provides the browser system with the information required to invoke the report. For more information, see "Creating Custom Reports".

To register a custom report:

1. From the launcher portlet, click the Administer Warehouse Builder Browser link, and then select the Register a Custom Report link.

   The Register a Custom Report page displays as shown in Figure 18-14.

Figure 18-14 Register Custom Report Page

Text description of the illustration 14_16.gif

2. Enter a display name for the report.

3. Select the type and repository from the drop-down lists. Table 18-3 lists the custom report properties.

   Table 18-3 Custom Report Properties  

   Field	Description
   Display Name	Name of the report. This name is displayed on the Reports List page.
   Type Name	Name of the data type reported on by this report.
   Package	The full name of the PL/SQL package which implements this report.
   Repository	Name of the repository containing target objects for this report.

4. Enter the qualified package name for the report in the format <schema>.<package>. The package name is displayed on the Develop page for the report.

5. Click Apply or OK to complete the registration.

The report appears in the resource list of the administration page.

Adding a Custom Report to a Role

To add a custom report to a Warehouse Builder Browser role:

1. Click the roles action link from the resource list entry for the custom report.

2. Click the add action link for each role that you want to access the report.

Resource Management

Resource Management allows you to access rights to many aspects of the Warehouse Builder Browser as well as editing, registering, and unregistering repositories and reports.

The Resource Management page contains a table listing all resources that have been registered in the Warehouse Builder Browser, as shown in Figure 18-15. The following sections describe how to modify these resources.

Figure 18-15 Resource Management

Text description of the illustration rscrc_mn.gif

Adding User Accounts

To add a user account for Warehouse Builder Browser:

1. From the Resource Management page, select the Access action listed next to the resource entitled Launcher Portlet, as listed first in Figure 18-15.

   Warehouse Builder Browser displays the Portlet Access page as shown in Figure 18-16.

Figure 18-16 Portlet Access

Text description of the illustration port_acc.gif

From the Portlet Access page, you can perform the following tasks:

• Grant an access right to a Single Sign On user by selecting the name of the user from the drop-down list and clicking Grant User.

• Grant an access right to an Oracle Portal group by selecting the name of the group from the drop-down list and clicking Grant Group.

• Revoke an access right by clicking revoke in the appropriate table row.

To use the Warehouse Builder Browser, your single sign on user or a group associated with that user must have rights to one of these Warehouse Builder pre-defined roles:

• Warehouse Developer: A user who uses Warehouse Builder to create the warehouse.

• QA User: A user who tests the quality of the warehouse before it is deployed.

• Warehouse User: A user who uses the deployed warehouse to understand the underlying metadata.

The Administrator can assign these roles to users and groups. All of the pre-defined Reports and Navigation pages are available to all roles. When you add custom reports, you can assign them to different roles.

For the QA User role, objects that fail validation display an error icon in the Contents tab of the Navigation page, as shown in Figure 18-17. This error icon is not displayed in reports for the other roles.

Figure 18-17 Validation Error

Text description of the illustration qavalida.gif

Managing Custom Reports

The Resource Management page lists all registered custom reports. The actions listed next to the repository are described in Table 18-4.

Table 18-4 Custom Report Management  

Actions	Description
Role	Use this to assign a report to one or more roles. When the report is assigned to a role, it appears in the appropriate report list for that role.
Edit	Use this to edit custom report properties.
Unregister	Use this to unregister the report. After unregistering the report, it can no longer be browsed using the browser system.

Assigning a custom report to a role adds the report to the appropriate report list page for that role. The name and subject type of the report are indicated at the top left corner of the page. A list of the available roles is provided in the table at the bottom of the page.

Managing Preferences

Use the Manage Preferences page to save and load Warehouse Builder Browser preferences. This lets you retain your preferences when you upgrade to a new version of Warehouse Builder Browser. You can copy schema preferences across schemas.

The preferences you can save include:

• Favorites.

• Registered custom reports.

• Registered Warehouse Builder Repositories.

• Access rights associated with roles, repositories, custom reports, and the Launcher Portlet.

• External Links.

Figure 18-18 shows the Manage Preferences page.

Figure 18-18 Manage Preferences Page

Text description of the illustration mng_pref.gif

Saving Preferences

To save preferences to a file:

1. Select Save Preferences from the Manage Preferences page.

   The preferences display in a separate window in text format

2. From the browser menu bar, select File, and then Save As to save the file.

   This file can be loaded into Warehouse Builder Browser using a tool such as SQL* Plus.

Loading Preferences

To load preferences from an existing schema:

1. Select Load Preferences from the Manage Preferences page.

   Figure 18-19 shows the Load Preferences page.

Figure 18-19 Load Preferences Page

Text description of the illustration load_pre.gif

2. Specify the following information from an existing Warehouse Builder Browser schema: Schema Name, Schema Password, Hostname, Host Port Number, Host Service Name.

3. Click OK to load the preferences into the current Warehouse Builder schema.

   A status page displays the preferences that were loaded and any errors that occurred. All errors must be resolved to load the preferences. Errors due to missing database links provide links to the Create Database Links page.

   Database links are not automatically created. If the preferences you are loading contain references to repositories, the database links to those repositories must be created before the load can be successful.

Managing the Dependency Index

Use the Manage Dependency Index page to specify the refresh frequency options for the dependency index for each repository. The dependency index is used to increase performance when running lineage and impact analysis diagrams. You can refresh the dependency index at any time from the Repository page of the Warehouse Builder Navigator.

Setting the Refresh Options

To specify the dependency refresh option:

1. Open the Warehouse Builder Browser, and select Manage Dependency Index from the Administration page or portlet.

   The Manage Dependency Index page displays the available repositories and refresh options, as shown in Figure 18-20.

Figure 18-20 Setting Refresh Options

Text description of the illustration mng_di.gif

2. Choose one of the options from the drop-down list and click OK. Table 18-5 describes each option.

   Table 18-5 Dependency Index Options  

   Option	Description
   Refresh on demand	You must activate the refresh dependency index link to refresh the index. This link is located on Navigator page that lists all accessible repositories. The dependency index is only refreshed when this action link is activated. This is the best option when using a repository that changes infrequently.
   Refresh on first diagram request of the session	Refreshes the dependency index when the first Lineage or Impact Analysis diagram for a repository is run during a session. This is the best option if you want current information, but are not concerned with repository updates that occur during the session.
   Refresh on every diagram request	Refreshes the dependency index every time a Lineage or Impact Analysis diagram is requested. This is the best option if you want to display your diagrams and reports with the latest information in the repository.

Refreshing the Dependency Index on Demand

You can refresh the dependency index at any time. If you run a Lineage or Impact Analysis diagram that has never been refreshed, an automatic refresh occurs prior to displaying the diagram.

To refresh the dependency index:

1. Open the Warehouse Builder Browser from the Launcher portlet.

   The Contents tab lists the available repositories, as shown in Figure 18-21.

Figure 18-21 Refreshing the Dependency Index

Text description of the illustration rfrsh_di.gif

2. Select refresh dependency index.

   The Refresh Dependency Index page displays with a log of previous refreshes at the bottom of the page, as shown in Figure 18-22. The elapsed time helps you determine how long the operation will take.

Figure 18-22 Dependency Index Refresh Log

Text description of the illustration rfrsh_da.gif

3. Select Refresh Dependency Index to refresh the dependency index based on the latest data in the repository.

   After the refresh is complete, the log displays the user name, date, and elapsed time of the refresh. You can purge the log by selecting Purge Log. This purges the log of the refreshes except for the last refresh.

Other Administration Tasks

To refresh the Portlet Repository:

1. From the Oracle Portal Home Page, click the Administer tab.

2. Scroll down to locate the Portlet named Portlet Repository.

3. Click Refresh Portlet Repository.

4. Click Refresh.

Configuring the Warehouse Builder Client

The Warehouse Builder Console includes a Preferences dialog containing tabs that you use to configure the Warehouse Builder environment. Use the Browser tab to set the network and IP connection information for Oracle Portal. This enables you to view metadata reports using the Warehouse Builder Browser.

To access the preferences dialog:

1. From the Project menu, click Preferences.

2. From the Preferences dialog, select the Browser tab as shown in Figure 18-23.

Figure 18-23 Browser Tab

Text description of the illustration 14_21.gif

3. Specify the following information:

   Oracle Portal Host Name

   Oracle Portal Port Number

   Oracle Portal DAD

   Warehouse Builder Browser Schema Name

4. Click OK.

Creating Custom Reports

You can create custom reports on your metadata using the Warehouse Builder Public Views and Standard Query Language (SQL). These views provide access to your metadata repository tables and report on your data definitions, transformations, and deployment areas. You can use the Warehouse Builder Browser or another reporting tool to view the public views.

For a complete list of available Public Views and the objects they contain, see Appendix D, "Warehouse Builder Public Views".

Creating a Custom Report in Oracle Portal

To create a custom report using Oracle Portal:

1. Log on to Oracle Portal and select the Database Objects tab from the Navigator page.

2. Select the Applications tab, and click the Create New Application link.

3. Create a new application and click OK.

4. Select the application you just created and select the Create New Report link.

5. Select the Report from SQL Query link from the page that displays next.

6. Type the report name and display name and click Next.

7. Type the SQL query to define the report and click Finish.

Click Next to continue to pages where you can customize the appearance of the report. To customize the report at a later time, select the Edit action.

Your SQL queries must reference a database link to the Warehouse Builder repository. You can use the default_owb_link created during the Warehouse Builder Browser installation. The SQL query for a report can only call PUBLIC database link or links within the application schema where a report resides.

Although a reports can reside in a schema other than Warehouse Builder Browser schema, the report must be executable by the Warehouse Builder Browser schema. To grant execution privilege for a portal report, go to Oracle Portal Home Page > Database Objects > Database Schemas > Report Schema > Report Package > Grant Access.

The following query provides a simple project report that lists the information systems it contains:

select * from all_iv_information_systems@default_owb_link where project_id = :id

When run from the Warehouse Builder Browser Navigation pages, the marker :id is automatically substituted with the appropriate value.

Verify that the report can be run in the following environments:

• SQL*Plus: Log on as the user who owns the report. The owner is displayed on the Develop page for the report. In SQL* Plus, replace the marker :id with a valid project_id.

• Oracle Portal: From the Develop page, select the Customize link, enter a valid project_id in the edit box labelled Id, and click Run Report.