13 Auditing Deployments and Executions

About the Repository Browser

The Repository Browser is a browser-based tool that generates reports from data stored in Oracle Warehouse Builder repositories. Using the Repository Browser, you can view:

• Detailed information about the design of a workspace. Reports are generated from data stored in the workspaces.

• Reports that provide access to both high-level and detailed ETL run time information. This information includes the following:

  • Timings for each mapping and process flow

  • Details of activities for each process flow

  • Error details

  • Deployment information to manage separate target environments

As an alternative to using the Repository Browser, you can access the same information through the public views. Start a SQL*Plus session and query the public views. See Oracle Warehouse Builder API and Scripting Reference for a list of public views.

You can use Oracle Warehouse Builder to deploy data to non-Oracle databases such as Microsoft SQL Server and IBM DB2 UDB. However, in these cases, you are not able to view design-time object data and deployment audit data. You can only view reports that contain execution audit data.

For more information about reports that are generated for the Repository Browser in a heterogeneous environment, see "List of Heterogeneous Repository Browser Reports".

About the Heterogeneous Repository Browser (HRAB)

The Heterogeneous Repository Browser (HRAB) is a browser-based tool that generates reports about objects deployed to OC4J servers or heterogeneous databases such as DB2 and SQL Server. Heterogeneous Repository Browser enables you to access auditing information from systems that do not have Oracle Warehouse Builder installed.

Using the Heterogeneous Repository Browser, you can view reports that provide:

• Execution information about ETL objects deployed to heterogeneous databases such as SQL Server and DB2.

• Execution information for objects deployed to an OC4J server, such as Web services

Before you access auditing information from heterogeneous databases or OC4J servers, you must create data stores that represent the database or OC4J server. Your administrator creates the data store and assign it to the Repository Browser or OC4J server.

java -jar admin_client.jar deployer:oc4j:localhost oc4jadmin
  -addManagedDataSource -applicationName default -dataSourceName testDataSource 
  -jndiLocation jdbc/testDataSource -connectionPoolName scottConnectionPool

Types of Auditing

Auditing information displayed by the Repository Browser can be classified as follows:

• PL/SQL Run time Auditing

  This refers to the auditing information for the traditional Oracle Warehouse Builder run time auditing of objects deployed using PL/SQL scripts.

  Data objects, mappings, and process flows use PL/SQL deployments.

• Heterogeneous Run time Auditing

  This refers to auditing information about objects that are deployed to an OC4J server or to a heterogeneous database such as DB2 or SQL Server. Includes auditing from more than ne Control Center Agents (CCA).

  This includes information about code templates, Web services, and Code Template mappings.

Table 13-1 describes the types of auditing available when you deploy Oracle Warehouse Builder objects to different target locations.

List of Heterogeneous Repository Browser Reports

The content of the reports generated by Heterogeneous Repository Browser is based on the corresponding reports produced by the Repository Browser. The reports are a subset of the reports produced for an Oracle Database environment. The terms used in the Heterogeneous Repository Browser reports are related to heterogeneous databases. For example, the term Process is replaced by Task and Map by Step.

However, Heterogeneous Repository Browser reports contain lesser information than the Repository Browser reports for an Oracle Database. The following Repository Browser reports are available for the Heterogeneous Repository Browser:

• Execution Schedule Report

• Execution Summary Report

• Map Execution Report

• Map Start Report

• Map Run Execution Report

• Map Run Trace Report

• Map Run File Report

• Run Error Diagnostic Report

• Management Reports

The following functionality is available through Heterogeneous Repository Browser reports:

• Purge execution audit data for a job

• Purge error and trace data for a job

Viewing Audit Reports

Audit reports provide information about deployment and ETL jobs. Each time you deploy an object or start a job, the details of these activities are stored in the workspace. You can access this information from the following environments:

• Control Center Manager

• Repository Browser

The Repository Browser provides the information in the form of predefined reports. The reports are displayed in your default Web browser. The Repository Browser Listener must be running.

Managing the Repository Browser Listener

Before you can open the Repository Browser, you must start the Repository Browser Listener.

Starting the Repository Browser Listener in Windows

Open a command prompt window and run startOwbbInst.bat located in the OWB_HOME\owb\bin\win32 directory. You are prompted to set the password for the OC4J administrator. Enter a password and press the Enter key. When you are prompted to confirm the password, enter the same password again.

Stopping the Repository Browser Listener in Windows

Open a command prompt window and run stopOwbbInst.bat located in the OWB_HOME\owb\bin\win32 directory. You are prompted to provide the OC4J administrator password that you set while starting the Repository Browser listener. Type the password and press the Enter key.

Starting the Repository Browser Listener in UNIX

Run ./startOwbbInst.sh located in the OWB_HOME/owb/bin/unix directory. You are prompted to set the password for the OC4J administrator. Enter a password and press the Enter key. When you are prompted to confirm the password, enter the same password again.

Stopping the Repository Browser Listener in UNIX

Run ./stopOWBBInst.sh located in the OWB_HOME/owb/bin/unix directory. You are prompted to provide the OC4J administrator password that you set while starting the Repository Browser listener. Type the password and press the Enter key.

Accessing the Repository Browser

Once the Listener is running, you can start the Repository Browser in any one of the following ways:

• From the Start menu, select Programs, then the Warehouse Builder folder, and then Warehouse Builder, Repository Browser.

• From the Tools menu of the Design Center, select Repository Browser.

• From within any web browser, type the location of the Repository Connection page. For example, if the Repository Browser Listener is running on a computer named owb_server, then typing the following address start the Repository Browser:

  https://owb_server:8999/owbb/RABLogin.uix?mode=design

  or

  https://owb_server:8999/owbb/RABLogin.uix?mode=runtime

Regardless of which approach you take, when you start the Repository Browser, the browser opens the Repository Connection page from which you log in to the Repository Browser.

Logging in to a Workspace

To log in to a workspace, specify the connection information for the workspace that you would like to access. If you do not know the connection information, then contact your database administrator.

You can connect to the Control Center either as the workspace owner or as a workspace user. When you connect as the workspace owner, you can purge audit details, validate locations, unregister locations, change service node settings, view user data values, and start or stop execution jobs. When you connect as a workspace user with administrative privileges, you can purge audit details, unregister locations, change service node settings, view user data values related to your execution jobs, and start or stop your execution jobs.

Once you log in to the Repository Browser, you can view any of the following reports:

• Deployment

• Execution

• Management

Depending on the location of the auditing information, see the following sections:

• "Connecting to an Oracle Database"

• "Connecting to a Heterogeneous Database or OC4J Server"

If you have access to multiple workspace, the Repository Browser displays a list containing these workspaces. Select the workspace to which you want to connect.

Select Design Center to display the "Repository Navigator" that provides access to reports about object design. Select Control Center to display the "Control Center Reports" page that lists all the types of reports available through the Repository Browser.

Repository Navigator

Use the Repository Navigator page to search the metadata of a workspace and to access metadata main properties, lineage, impact analysis, and a list of related reports and Control Center reports for the workspace. The Repository Navigator contains the following sections: "Search", "All", and "Related Links".

Search

Search by the object type, or name, or both.

• To search by object type, select the type of object to search from the Search By Type list. The search result is a list of all objects of this type.

• To search by name, enter the name of the object in the Search field. You can search for just the first character of the name, in which case the search result is a list of objects whose names begin with that character.

Click Go to start your search.

The Repository Browser displays the results of the search in a new page called the Workspace Objects Search page. You can also search for new objects from this page.

All

Contains a navigator tree for the workspace.

Table 13-2 describes the use of the columns.

Related Links

Click Control Center: Reports to open the Control Center Reports page from which you can select a deployment, execution, or management report.

Refresh

Click Refresh to refresh your view of the workspace.

The tree collapses when you refresh the data. If you had navigated to or focused to a specific area before refreshing, then you can navigate to or focus on the desired node in the tree.

Object Properties

The Object Properties page displays the properties of the object that you selected in the "Repository Navigator". The properties include the object name, business name, validation status, creation timestamp, update timestamp, and the name of the user who created and updated the object.

From this page you can go to the "Object Reports", "Object Lineage", or "Object Impact" pages by clicking on the corresponding link on the left side of the page.

Object Reports

The Object Reports page provides access to the predefined Design Center reports for the object that you selected in the "Repository Navigator". Use these reports to examine your metadata.

Click a Report name to display a report.

The following types of reports are available:

• "Summary Reports"

• "Detailed Reports"

• "Implementation Reports"

• "Impact Analysis Reports"

Object Lineage

The Object Lineage page displays the lineage diagram for the object that you selected in "Repository Navigator". A Lineage Diagram graphically displays all the objects and transformations that are used to form 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, cubes, 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.

From the Object Lineage page you can go to the "Object Properties", "Object Reports", or "Object Impact" pages by clicking on the corresponding link on the left side of the page.

Object Impact

The Object Impact page displays the Impact Analysis diagram for the object that you selected in the "Repository Navigator". The Impact Analysis diagram is a graphical representation of the objects on which the definition of the selected object depends. It represents the potential impact of a change in the definition of the selected object. The subject is displayed on the left side of the screen.

From this page you can go to the "Object Reports", "Object Properties", or "Object Lineage" pages by clicking on the corresponding link on the left side of the page.

Lineage and Impact Analysis diagrams are created based on a Dependency Index. For the data displayed in the diagram to be current, the index must be refreshed.

When you click Impact Analysis for an object, the impact_objectID.jpeg file, where objectID represents the object ID of the selected object, is generated and displayed on the Browser page. Similarly, when you click Lineage for an object, the lineage_objectID.jpeg file is generated. For both lineage and impact analysis, in addition to the .jpeg file, an .svg file with the same name is generated. If you use these files, then they are available in the following locations:On UNIX, these files are stored in the folder OWB_HOME/owb/j2ee/applications/owbb/generated_images/.

On Windows, these files are stored in the folder OWB_HOME\owb\j2ee\applications\owbb\generated_images\

Deployment Reports

Top-level deployment reports are "Deployment Schedule Report", "Object Summary Report", and "Locations Report". From these top-level deployment reports, you can access "Deployment Error Detail Report"s and "Deployment Report"s that supply details about the deployment of a specific process, map, or data object.

Execution Reports

The top-level execution reports are "Execution Schedule Report"s and "Execution Summary Report"s.

From these top-level execution reports, you can access "Error Table Execution Report"s, "Job Error Diagnostic Report"s, "Trace Report"s, "Execution Job Report"s, "Job File Report"s, "Job Start Report"s, and "Execution Report"s.

Management Reports

The top-level management report is the "Service Node Report". From this report you can open a "Location Validation Report".

Identifying Recently-Run Processes

1. Open the "Execution Schedule Report" to see the latest Process runs.

2. Filter the displayed information by using the execution name, execution status, and date range, as required.

3. Note any Process runs that are reported as having errors or not having completed.

4. Expand the tree structure for any Process run identified in Step 3 to see details of its activities (that is, any of its subprocesses and maps).

Identifying Why a Process Run Failed

1. Open the "Execution Schedule Report" and note the Process run that is marked as having errors.

2. Click the Run Execution Report link, which opens a "Execution Report" that provides details of the Process run.

3. Note any Map runs that are reported as having errors or not having completed.

4. Click the Run Execution Report link, which opens an "Execution Report" that provides details of any Map run identified in Step 3.

5. For any process-level or map-level error messages, click the Run Error Diagnostic Report link, which opens a "Job Error Diagnostic Report" that displays more details of the error, including source data values.

Comparing Process Runs

1. Open the "Execution Summary Report" to see a list of all Processes.

2. Click the Process name to see its "Execution Report".

3. Compare the results of previous runs, using the hide/show feature to reveal further details as required.

Discovering Why a Map Run Gave Unexpected Results

1. Open the "Execution Schedule Report" and note the Process Run that contains the required Map Run.

2. Click the Run Execution Report link that opens an "Execution Report" that provides details of the Process Run.

3. Click the Run Execution Report link that opens an "Execution Report" that provides details of the Map Run.

4. If the Map Run had the Audit Level run time parameter set to Complete, select the Trace tab link to see its "Trace Report".

5. Filter trace lines by error and source or target as required, and note any unexpected source or target actions.

6. For error messages, click the Run Error Diagnostic Report link that opens a "Job Error Diagnostic Report" that displays more details of the error, including source data values.

7. Click Purge Error and Trace Lines to remove all details or errors and trace for this Map Run, if they are no longer required.

   If purging, a confirmation screen is shown, requesting that the action be confirmed.

Identifying Recently-Made Deployments

1. Open the "Deployment Schedule Report" to see the latest deployments.

2. Filter the displayed information by using the date range, as required.

   Note any deployments that are reported as having errors or not having completed.

3. Expand the tree structure for any deployment to see details of its components (that is, units and deployed objects).

4. For error messages, click the Deployment Error Detail Report link to display the related "Deployment Error Detail Report".

Identifying the Data Objects That Are Deployed to a Specific Location

1. Open the "Locations Report" to see the registered Locations.

2. Click the Location name link to see its "Object Summary Report".

3. Filter the displayed information by using object type and object status, as required.

4. Click the Name link for a Data Object to see its "Deployment Report".

   Details are shown for all deployments of this Data Object and its second-class Data Objects.

Identifying the Map Runs that Use a Specific Deployed Data Object

1. Open the "Object Summary Report" to see a list of all deployed objects.

2. Filter the information shown by using object type and object status as required.

3. In the Name column, click the link representing an object to view the "Deployment Report" for this object.

4. In the Available Reports section at the top right, click Execution to display the "Execution Report" for the object. This report contains a summary of how and when the object was used in a Map Run.

5. In the Execution Details section of the Execution report, select Execution Job Report to display the "Execution Job Report" that contains details of the mapping or process flow execution.

Discovering the Default Deployment Time Settings of a Deployed Process

1. Open the Object Summary Report to see all deployed Processes.

2. In the Name column, click a process flow to display its "Deployment Report".

3. In the Available Reports section, click Start to display the "Job Start Report" for the process.

   The execution parameters have the default deployment time settings.

4. In the Execution Parameters section, change any of the input parameter values, as required.

5. Click Start Execution to run the new process run.

Rerunning a Process

1. Open the "Execution Schedule Report" to see list of all Process Runs.

2. In the Execution Details section, click the mapping or process flow name in the Name column to display the "Execution Report" for the selected object.

3. In the Available Reports section, click Start to display the "Job Start Report" for the selected object.

   The execution parameters have the default deployment-time settings.

4. Change any of the input parameter values, as required.

5. Click Start Execution to run a new Process Run.

Monitoring a Process Run

1. Open the "Execution Schedule Report" to see the executing Process Runs.

2. If necessary, use the Execution Status filter to display only currently executing Process Runs.

3. Click Refresh as required, to follow the progress of the Process Runs.

4. In the Name column, click a process flow name to the display the "Execution Report" containing the details of a given Process Run.

5. Click Refresh as required, to follow the progress of the Process Run.

6. For Process Runs known to the Workflow system, click the Related information link to switch across to the Oracle Workflow Monitor and follow its progress in a graphical display. Use the browser's Back button to return to the current report page.

Terminating a Process Run

1. Open the "Execution Schedule Report" to see the executing Process Runs.

2. In the Name column, click a process flow name to display the "Execution Report" for the process run.

3. Click Stop to terminate the given Process Run.

4. Click Refresh as required, to follow the progress of the Process Run as its execution is terminated.

Removing the Execution Audit Details for a Process

1. Open the "Execution Schedule Report" to see the latest Process Runs.

2. Filter the displayed information by using an execution name.

3. Select all the executions that are to be removed, and click Purge Selected Audit Details.

   A confirmation screen is shown, requesting you to confirm the action.

Removing Old Deployment Audit details

1. Open the "Deployment Schedule Report" to see the latest deployments.

2. Filter the displayed information by using the date range.

3. Select all the deployments that are to be removed, and click Purge Selected Audit Details.

   A confirmation screen is shown, requesting you to confirm the action.

Viewing Error Tables Created as a Result of Data Auditor Execution

1. Grant privileges to the Repository Browser on the error table.

   See "Granting Privileges on Error Tables".

2. Open the "Execution Summary Report" or "Execution Schedule Report" to see a list of all the Processes.

3. Click the link in the Related Information column corresponding to the process flow that contained the data auditor to display the "Execution Job Report" for the process flow.

4. Click the link in the Related Information column corresponding to the data auditor activity to display the "Execution Job Report" for the activity.

5. Data that violated the data auditor constraints is listed in the Logical Errors table. Click the link in the Error Table column to display the Execution Report for the error table.

If you have sufficient privileges, you can use the Execution Report for an error table to purge data from that error table.

Unregistering a Location

1. Open the "Locations Report" to see the registered Locations.

2. Select the Location that is to be unregistered, and click Unregister Selected Locations.

   A confirmation screen is shown, requesting you to confirm the action.

Updating Location Connection Details for a Changed Database Environment

1. Open the "Locations Report" to see the Locations.

2. Select the location that is to be validated by clicking the Select column to the right of that location. Click the link in the Validation column corresponding to the selected location.

   The "Location Validation Report" is displayed, showing the connection details of the Location and the Control Center

3. Change the service description values, as necessary, and click Update Details.

4. In the Location Status section, click Get Status to validate the current connection settings for the Location.

   The results of Location connection tests are not maintained beyond the current session.

Updating Service Node Details in a Changing Oracle RAC Environment

1. Open the "Service Node Report" to see the settings that currently describe the Oracle RAC system.

2. Update details and usage of the Service Nodes, then click Update Node Details for the requested changes to be made.

3. Add or remove Service Nodes, as required.

4. Click Refresh to see the current settings of the Oracle RAC system.

5. Set the Net Service Name by which the Control Center may be accessed, as necessary.