Oracle9i Warehouse Builder User's Guide Release 2 (v9.0.2) Part Number A95949-01 |
|
Metadata reporting allows you to examine the data stored in your Warehouse Builder repository. The Warehouse Builder Browser integrates with Oracle Portal and makes it possible to add metadata reporting functionality to Oracle Portal. There are five Warehouse Builder portlets that you can add to customize your Warehouse Builder Browser. 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. You can access these reports from the Warehouse Builder client which opens Oracle Portal, or directly through Oracle Portal without using the client.
This chapter includes the following topics:
You can view a report on an item using the Warehouse Builder clients if Oracle Portal is installed. You must set up your Warehouse Builder Preferences to view reports.
To set Browser Preferences:
To open reports from within the Warehouse Builder client:
The Oracle Portal site you defined in the Preferences window opens and displays the logon.
The Warehouse Builder Browser displays a list of reports available for the selected object.
The report displays in a separate window. Figure 12-3 shows an example of the Table Summary Report.
To print the report, select the print operation from the browser menu or toolbar.
You can view reports from the Warehouse Builder Browser without installing Warehouse Builder. You start by launching Oracle Portal. From there you can select the Warehouse Builder Browser portlets.
To start the Warehouse Builder Browser:
The Warehouse Builder Browser uses a standard Oracle Portal login screen. This is not specific to the Warehouse Builder Browser.
Your default Oracle Portal page displays. To display the Warehouse Builder Browser portlets, you must add them to your Oracle Portal home page. Use the Edit Page option to add portlets.
Warehouse Builder Browser portlets can be added to Oracle Portal after they have been installed on the machine running Oracle Portal. For more information about these portlets, see "Available Warehouse Builder Browser Portlets" . To install and configure the Warehouse Builder Browser Portlets, see the Warehouse Builder Installation and Configuration Guides. For information about customizing Oracle Portal pages, see the Oracle Portal documentation.
To add a portlet to a page:
The Edit Page displays the contents of the Oracle Portal page.
The Add Portlets page displays a list of available portlets on the left side, and a list of selected portlets on the right side. See "Available Warehouse Builder Browser Portlets" for more details.
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.
The Edit Page displays the Warehouse Builder portlets that you added.
You can add the following Warehouse Builder portlets to Oracle Portal. You can also 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 Launcher Portlet provides access to the following actions from your Oracle Portal home page:
The Administration Portlet provides access to the following Administration features from your Oracle Portal home page:
The Favorites Portlet provides access to your favorite Navigator pages and Reports from your Oracle Portal home page. This portlet does not contain any default favorites. You select Navigator pages and Reports as favorites using the Add to My Favorites links on the Warehouse Builder Navigator. You can also click the Full Page link to display Favorites in full page mode.
To view your favorite Navigator pages, select an item name under the column Name. You can also use the action links to view the properties, contents, related items, reports, or links associated with this item.
To view your favorite Reports, select a report name under the Report column. If you click on the item name under the column Name, the Navigator page for that item displays.
The Navigator Portlet enables you to browse repositories accessible to the current user. The Navigator Portlet has the same functionality as the main Navigator page, except it displays within the area of the portlet. You can click the Full Page link to display the Navigator in full page mode.
The Reports Portlet displays a Warehouse Builder Report on your Oracle Portal home page. This portlet does not contain a default Report. In order to display a Report, add a report to your favorites and use the Customize link on this portlet to add it.
To view Warehouse Builder Reports:
The Repository list contains repositories that have been previously set up by your Warehouse Administrator using the Warehouse Builder Browser Administration pages.
The role determines the available reports. Roles are set up by your Warehouse Administrator in the Warehouse Builder Browser Administration pages. For more information, see "Understanding Roles".
This displays the Warehouse Builder Navigation Pages. You can then navigate around your repository and find the item to report on.
When you first install or upgrade Warehouse Builder, you can use the Warehouse Builder Browser Assistant to set up your Warehouse Builder Browser. You can either run this wizard during your installation process, or defer it to a later time. See the Warehouse Builder Installation Guide for instructions on how to use this wizard.
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.
Warehouse Builder provides a set of standard metadata reports. Use these pre-built reports to examine your metadata. The following types of reports are available:
Summary Reports are run against a module. Each Summary Report presents a list of items contained within the module. The type of items displayed is determined by the Summary Report you select. For example, a Table Summary Report lists all tables in the module. A Materialized View Summary Report lists all Materialized Views in the module. Header information that identifies the module also displays. Selecting the name of an item displays the Detailed Report for that item.
Available Summary Reports for a Module include:
Figure 12-15 show a section from a Dimension Summary Report.
Detailed reports provide comprehensive information about an item. For example, a Detailed Table Report will list information about the table columns, keys, foreign keys and physical configuration parameters. Table 12-1 list the Detailed Reports and their contents.
Figure 12-16 shows a section from a Detailed Fact report.
Implementation Reports can be run on Dimensions and Facts. They provide information on how physical objects are used to implement logical objects. Table 12-2 lists the available Implementation Reports.
Lineage and Impact Analysis Reports are available for Facts, Dimensions, Materialized Views, Tables, Views, and Records. These reports should not be confused with the Lineage and Impact Analysis Diagrams.
Impact Analysis Reports list all items belonging to the subject of the report that are used as a source of a mapping. The name of the mapping and the name of the item that it is mapped to is also displayed. The report gives a one step impact analysis for all items related to the selected item.
For example, if you list all the columns in a table that is used as a source in any maps, use this report.
Lineage Reports are similar to Impact Analysis Reports. They list items that are the targets of a map.
Figure 12-17 shows a section from an Impact Analysis report.
A Lineage Diagram graphically displays all the objects and transformations that are used to make up the subject of the Diagram. Lineage can be performed at either the object level or the item level. At the Object Level, the diagram can contain Tables, Views, Materialized Views, Dimensions, Facts, Records, and Operators. At the item level the diagram can contain Columns, Measures, Fields, Operator Parameters, and Level Attributes.
The Lineage Diagram is displayed with the subject on the right side of the screen.
An Impact Analysis Diagram is identical except it shows all objects and transformations that might be affected by a change to the subject. The subject is displayed on the left side of the screen.
Figure 12-18 shows an example of an Impact Analysis Diagram.
Lineage and Impact Analysis diagrams are created based on a Dependency Index. In order for the data displayed in the diagram to be current, the index must be refreshed. For more information, see "Managing the Dependency Index".
Use Warehouse Builder Browser to browse metadata reports and search for specific information by using the various functions of the pages including:
You can navigate the Warehouse Builder Browser in many ways. The main type of page is the Navigator page that includes the following parts:
The top of every Warehouse Builder Browser page defines the type of page you are looking at and has links to other common pages.
The header contains the following:
The center of the Navigator contains the current item definition and provides information to identify the item and its type.
The current item information section contains the following:
The Properties tab displays property name-value pairs for the current item. The property names are different depending on the type of the current item.
Column Name | Value |
---|---|
Property Name |
The name of the property as defined in the Warehouse Builder Public Views. For more details on the Public views, see "Creating Other Custom Reports". |
Property Value |
The value of the property from the Warehouse Builder repository for the current item. |
The Contents tab lists the items that the current item contains. For example, if the current item is a table, then the Contents tab lists the columns, keys, and foreign keys. Use this tab when you drill down into the Warehouse Builder repository. In Figure 12-23, the current item is the Warehouse Builder repository and the list of items that the repository contains. Table 12-4 lists the columns in the Contents tab and brief descriptions of their values.
The Related tab allows you to view relationships between items. For example, if the current item is a table, then you can identify tables related by foreign keys. You can use this tab to drill down into items in the repository. When you make one of these items the current item, the path switches to represent the path of the new item.
Figure 12-24 shows the foreign key associations with a table. Table 12-5 lists the columns in the Related tab and brief descriptions of their values.
The Reports tab shows you the available reports for the current item. Each role has its own set of reports available for each item type. A Warehouse User sees a different set of reports than a Warehouse Engineer. Table 12-6 lists the columns in the Reports tab with brief descriptions of their values.
Column Name | Value |
---|---|
Name |
The name of the report. |
Actions |
View: Displays the report. |
The Links tab allows you to associate other sources of information with an object or type of object. You can link to anything that can be described with a URL, such as documents stored in Oracle Portal content areas or other web pages.
Use the Customize link on the Links tab to:
To edit a link:
The Customize Warehouse Builder Links page displays the Existing Links.
To delete a link:
The Customize Warehouse Builder Links page displays the Existing Links.
The link is deleted and the Customize Warehouse Builder Links page displays without the deleted link in the list of existing links.
To add a link:
The Customize Warehouse Builder Links page displays the Existing Links.
The Add Link page displays.
Selecting the Browse My Favorites link goes to your Warehouse Builder Favorites page. Every navigation page and every report has an Add to My Favorites link on the top right corner. When you select this link, a link to the current report or Navigation page is added to your Favorites list. You can also use the features on the Favorites pages to delete items from the list and to change the formatting of the list.
The Favorites Page consists of the following:
The top of every Warehouse Builder Browser page has a header that defines the type of page you are looking at, and has links to your default home page, your Favorites customization page, and log out.
If you want to customize your Favorites page, select the Customize link. For details about customizing your page, see "Customizing Your Favorites Page".
You can narrow down the content of the two Favorite tables by selecting a particular Repository, Role, or Item Type. Each of these Items has an All option.
This list contains items added from the Navigation pages. The table contains these columns:
This list contains items added from the Report pages. The table contains these columns:
To customize your favorites page, click the Customize link in the Warehouse Builder Favorites header. Table 12-6 lists the available actions and brief descriptions.
Selecting the Options link on the Favorites Customize page goes to the Customize Display Options page. Use this page to change the layout of your favorites on the Favorites page.
This page contains the following options:
You can create custom reports on your metadata using the Warehouse Builder Public Views. 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 reports.
To create a custom report that can be viewed in the Warehouse Builder Browser, follow the steps in these sections:
To create a custom report:
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 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:
To register a custom report with the Warehouse Builder Browser:
The report appears in the resource list of the administration page.
To add a custom report to a Warehouse Builder Browser role:
To view a custom report from the Warehouse Builder Browser:
The report displays.
Use the tables below to determine which Public Views to report from. Table 12-9 lists repository object types and their associated public views. Table 12-10 lists runtime object types and their associated views. For detailed information on each view, including what objects they contain, see Appendix G, "OWB Public View Tables Management".
Object Type | Views |
---|---|
ALL_IV_PROCESS_RUN |
|
ALL_IV_RUN_TARGET |
|
ALL_IV_RUN_ERROR |
From the Oracle Portal Home page, select the Administer Warehouse Builder Browser link to access the Warehouse Builder Administration pages.
The Warehouse Builder Administration pages can only be accessed if your Oracle Portal user name has full administrator privileges.
The Administration page contains the following administration actions:
Before you can report on metadata in a repository, that repository must be registered with the Warehouse Builder Browser. Registrations creates the link to the repository.
Note: When you register a Warehouse Builder repository, the database link to that repository must already exist. If it does not, you must create one. Skip to Administering Database Links to create a database link before continuing. |
To register a Warehouse Builder Repository:
Table 12-11 Warehouse Builder Repository Properties
The repository displays in the Warehouse Builder Administration home page.
You can create, view, edit, or drop database links to Warehouse Builder repositories from the Administer Database Links page. This section describes each of these actions.
To create a database link:
The new link displays on the Administer Database Links page.
To view a database link:
The View Database Link page displays with a detailed report on the database link you selected.
To edit a database link:
The Edit Database Link page displays.
To drop a database link:
.
The database link is dropped and the browser returns to the Administer Database Links page.
To unregister a Repository:
The Warehouse Builder Administration page displays with the registered repositories listed in the table at the bottom of the page.
The repository is unregistered and no longer appears in the list of registered repositories. You can no longer browse it using the Browser.
A custom report is an application component created using 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:
Table 12-12 Custom Report Properties
If you add users through the Warehouse Builder Browser and remove them through Oracle Portal, the user information remains in the Access pages of the Warehouse Builder Browser. Use the Purge Stale User Information link to remove these users and groups.
The Resource Management page contains a table listing all resources that have been registered in the Warehouse Builder Browser. The following sections describe how to modify these resources.
Table 12-13 lists the types of resources.
Resource Type | Description |
---|---|
Portlet |
A portlet provided by the browser system. The Launcher portlet provides access to repository navigation, reporting, favorites, and administration. |
Repository |
Warehouse Builder Repository that can be browsed using the Warehouse Builder Browser. |
Role |
A view of the data in a Warehouse Builder Repository. For more information, see "Understanding Roles". |
Custom Report |
A custom report on a Warehouse Builder repository object. |
Use the Portlet Access pages to grant and revoke access to Warehouse Builder portlets. The users and groups that have access are listed under Change Access.
From the Portlet Access page, you can:
The Resource Management page lists all registered repositories. The actions listed next to the repository are described in Table 12-14.
The Resource Management page lists all registered custom reports. The actions listed next to the repository are described in Table 12-15.
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.
In order to browse the Warehouse Builder Repository, choose one of the pre-defined roles:
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 or register repositories, 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.
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:
To save preferences to a file:
The preferences display in a separate window in text format
This file can be loaded into Warehouse Builder Browser using a tool such as SQL* Plus.
To load preferences from an existing 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.
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.
To specify the dependency refresh option:
The Manage Dependency Index page displays the available repositories and refresh options.
Table 12-16 Dependency Index Options
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:
The Contents tab that displays lists the available repositories.
The Refresh Dependency Index page displays with a log of previous refreshes at the bottom of the page. The elapsed time helps you determine how long the operation will take.
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.
|
![]() Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|