5 Maintaining the Repository and Data Mart

This chapter contains the following topics:

5.1 Maintaining the Oracle Argus Analytics Repository

Each release of Oracle Argus Analytics contains a Repository (RPD) file. The Repository is the data store for the Oracle BI Server. It maintains the mapping of the physical tables comprising the data mart to the Presentation Layer, which holds the columns and tables available for use in OBIEE Requests. As shipped, the RPD corresponds to the Oracle Argus Analytics data mart, and can be used without any modification.

However, you might find it desirable to modify the Oracle-supplied Oracle Argus Analytics Repository file (RPD), for any of the following reasons:

  • You want to add a column or table to the data mart, and propagate that addition into the layers of the repository.

  • You want to add a calculated column in the Presentation Layer as a function of some set of physical layer columns.

  • You want to modify a repository variable value, or add a new repository variable, for use in some Presentation Catalog calculation.

  • You want to modify a group, an account, or a privilege maintained through the repository.

This section describes the procedures you must follow to carry out these types of modifications.

You should be aware that, once you have modified the Oracle-supplied Repository, it is your responsibility to merge these modifications into Repositories supplied by Oracle in patches and releases of Oracle Argus Analytics. Details on how to re-apply your modifications are provided below.

Caution:

Changes to the Repository should be made with care.

Privileges to make changes in the Oracle Argus Analytics Repository should be granted only to a limited set of users who need to make such changes and also know how to make them correctly.

Changes should be tested on a side copy of the Repository before being released for production use.

5.1.1 Modifying the Repository

The Oracle Argus Analytics Repository is maintained as a versioned object in a version controlling software. A copy of that Repository is deployed to the application server file system. This deployed Repository is the one that the Oracle BI Server uses. All changes to the Repository, however, must be made through a two-step process:

  • Modify the versioned Repository object.

  • Deploy the latest version of the Repository object.

Therefore, Oracle requires that you do not modify the deployed Oracle Argus Analytics Repository directly.

If you do need to modify the Repository, perform the following tasks:

  1. Open RPD using the Administration tool and make the desired modifications.

  2. Launch the Oracle BI Presentation Server to verify the changes.

5.2 Maintaining the Argus Analytics Data Mart

You may need to modify the Oracle Argus Analytics data mart, typically for one of the following reasons:

  • Derivation: Calculation of a new measure as a function of some supplied measures.

  • Extension: Adding data that was not delivered with Oracle Argus Analytics.

  • Substitution: Swapping data from a different source for a column that was delivered with Oracle Argus Analytics.

Caution:

Exercise caution when you modify the data mart. Please conform to the recommendations mentioned in the subsequent sections.

5.2.1 Derivations

A derivation is a calculation of a new measure as a function of some supplied measures. Oracle Argus Analytics displays all derivations as a column in Answers. You can use any of the following approaches to calculate derivations:

  • Calculate the derivation as part of the creation of a request.

    In this approach, only the Web Catalog is modified. However, you must specify the calculation for each request, and the calculation is executed every time the request is executed.

  • Calculate the derivation in the physical or business layer of the RPD file; it is propagated to the presentation layer. This makes the derivation you created appear in Answers as a column.

    Using this approach, you can specify the calculation once and use it for multiple requests. The derived value looks the same as any other Answers column.

  • Calculate the derivation in the data mart.

    The calculation is run at ETL execution time and not at query time. The derived value looks the same as any other Answers column. In this approach, you must add the result column to the staging and target tables, modify the ETL procedures (both Source Dependent Extract (SDE) and Source Independent Load (SIL)), and then add the column to all the layers of the RPD.

5.2.2 Substitutions

A substitution occurs if you have a preferred alternative source of data for a column that Oracle Argus Analytics populates from Oracle Argus Safety. For example, you may want to load the W_RXI_STUDY_D.STUDY_SPONSOR other than what Oracle Argus Analytics is loading. You will have to perform the following tasks:

  1. Create a table at database containing the locally-sourced values of the column, and also add whatever keys are needed to join to the Oracle-supplied view.

  2. Create a program that joins the two tables and creates a new table, in which the locally-sourced values replace the Oracle-supplied values for the column of interest. Call this the Substitution Table.

  3. Modify the SDE to read from the Substitution Table, rather than the Oracle-supplied table or view.

If you make changes to a source table, you must propagate that change forward as far as necessary. Some of the scenarios and the related necessary adjustments are described in the Table 4-1:

Table 5-1 Scenarios Requiring Necessary Adjustments

Scenario Adjustments Required

New table has the same layout as the old table, but is passed through from a different source

Change the SDE that reads the old table to instead read the new table.

Modified table has modified layout

  1. Modify the SDE to read the modified layout.

  2. Modify the staging table populated by the SDE to include the modified layout.

  3. Modify the SIL to read the modified layout.

  4. Modify the target table to include the modified layout.

  5. Modify the RPD to accept the changed data mart table.

New table

  1. Add a staging table to accept the new input.

  2. Add an SDE to read from the new table and write to the staging table.

  3. Add a data mart table to make the new data available to the BI Server.

  4. Add an SIL to populate the new data mart table from the new staging table.

  5. Modify the RPD to accept the new data mart table.

5.2.3 Modifying Data Mart Tables

Depending on what changes are required to the data mart, it is necessary to modify the source, staging, and target tables.

Managing Indexes

Oracle Argus Analytics is delivered with a set of indexes. If you wish, you can create additional indexes to meet your query requirements.

Note:

Oracle recommends that you Drop and re-create all indexes only for full load. This should be disabled for incremental load.

It is useful to drop all indexes on the data mart tables before loading large volumes of data, and to recreate them afterward. ODI can automate this process for you.

To drop indexes per table, use the procedure call opvautilcommon.p_Drop_Indexes( pTableName in varchar2).

To create indexes per table, use the procedure call opvautilcommon.p_Create_Indexes( pTableName in varchar2).

Note:

The P_DROP_INDEXES procedure stores the index metadata information in the table W_PVA_INDEX_S OR PVA_INDEX_S. This information is later used by the procedure P_CREATE_INDEXES to re-create the dropped indexes on the given table.