Skip Headers

Oracle® Health Sciences Pharmacovigilance Operational Analytics User and Administrator Guide Release 1.0 E23554-01 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| PDF · Mobi · ePub

# 5 Maintaining the Repository and Warehouse

This chapter contains the following topics:

- Maintaining the Oracle Health Sciences Pharmacovigilance Operational Analytics Repository

- Maintaining the Oracle Health Sciences Pharmacovigilance Operational Analytics Data Warehouse

## 5.1 Maintaining the Oracle Health Sciences Pharmacovigilance Operational Analytics Repository

Each release of Oracle Health Sciences Pharmacovigilance Operational Analytics (OPVA) 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 warehouse to the Presentation Layer, which holds the columns and tables available for use in OBIEE Requests. As shipped, the RPD corresponds to the OPVA data warehouse, and can be used without any modification.

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

- You want to add a column or table to the data warehouse, 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 OPVA. 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 OPVA 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 OPVA 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 OPVA 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 Oracle Health Sciences Pharmacovigilance Operational Analytics Data Warehouse

You may need to modify the OPVA data warehouse, 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 OPVA.

- Substitution: Swapping data from a different source for a column that was delivered with OPVA.

Caution:

Exercise caution when you modify the data warehouse. 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. OPVA 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 warehouse.

  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 Extensions

An extension is a new column added to the data warehouse.

Example: Adding the Agency ID as an attribute of the study dimension for each study. The following is assumed:

- The information about agency is available in a table called LM_STUDY_SUSAR.

To minimize the level of effort required when implementing a release with a new repository, Oracle recommends that you add extensions to the warehouse through user-defined extension tables, rather than by adding new columns directly into the relevant staging and target tables.

Perform the following tasks to add the study manager to the study dimension for each study:

1. Modify staging table W_RXI_STUDY_DS, adding the AGENCY_ID column. To modify the staging table, perform the following tasks in Oracle Business Intelligence Data Warehouse Administration Console (DAC):

   1. Duplicate the container in DAC (to ensure that the changes are not overwritten in the next OPVA upgrade) or use an existing customized table definition.

   2. Navigate to the Design > Tables subtab and add the new column under W_RXI_STUDY_DS and save it.

2. Modify the SDE that populates W_RXI_STUDY_DS, in two steps:

   - Copy the SDE into another folder (to ensure that the changes are not overwritten in the next OPVA upgrade) or use an existing customized SDE.

   - Add the LM_STUDY_SUSAR table as a source in the Informatica program.

   - Add a mapping of column AGENCY_ID from LM_STUDY_SUSAR to W_RXI_STUDY_DS.

   To modify the SDE, perform the following tasks in DAC:

   1. Navigate to Design > Tasks subtab.

   2. Navigate to SDE of Study Dimension. Right-click and select Synchronize Tasks.

3. If it does not already exist to support some other extension, create extension table W_RXI_STUDY_DX, containing one column [STUDY_WID] to function as a foreign key that joins to the primary key in W_RXI_STUDY_D. This table is populated with one row for each row in W_RXI_STUDY_D when the Study SIL executes.

4. Add column AGENCY_ID to W_RXI_STUDY_D to hold the AGENCY_ID. To add a column, perform the following tasks in DAC:

   1. Duplicate the container in DAC (to ensure that the changes are not overwritten in the next OPVA upgrade) or use an existing customized table definition.

   2. Navigate to Tables subtab Under Design Tab and add the new column under W_RXI_STUDY_DS and save it.

5. Modify the SIL that populates W_RXI_STUDY_D. Add instructions to create a record in W_RXI_STUDY_DX for each record in W_RXI_STUDY_D, and to copy W_RXI_STUDY_DS. AGENCY_ID into W_RXI_STUDY_DX.AGENCY_ID for each record.

   To modify the SIL, perform the following tasks in DAC:

   1. Copy the SIL into another folder (to ensure that the changes are not overwritten in the next OPVA upgrade) or use an existing customized SIL.

   2. Add the new column and save.

6. Modify the repository:

   Important:

   Before you modify the repository, ensure that you have copied the repository to a different location. This ensures that the changes are not overwritten automatically in the next upgrade of OPVA. Alternatively, you can use an existing customized repository.

   1. Import the definition of the extension table, W_RXI_STUDY_DX, into the Repository.

### 5.2.3 Substitutions

A substitution occurs if you have a preferred alternative source of data for a column that OPVA populates from Oracle Argus Safety. For example, you may want to load the W_RXI_STUDY_D.STUDY_SPONSOR other than what OPVA 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.

   To modify the SDE, perform the following tasks in Informatica:

   1. Copy the SDE into another folder (to ensure that the changes are not overwritten in the next OPVA upgrade) or use an existing customized SDE.

   2. Modify the definitions and save.

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 | Modify the SDE to read the modified layout. Modify the staging table populated by the SDE to include the modified layout. Modify the SIL to read the modified layout. Modify the target table to include the modified layout. Modify the RPD to accept the changed data warehouse table.
New table | Add a staging table to accept the new input. Add an SDE to read from the new table and write to the staging table. Add a warehouse table to make the new data available to the BI Server. Add an SIL to populate the new data warehouse table from the new staging table. Modify the RPD to accept the new warehouse table.

### 5.2.4 Modifying Data Warehouse Tables

Depending on what changes are required to the data warehouse, it is necessary to modify either the source table in Informatica and DAC, or the source, staging, and target tables.

Managing Indexes

OPVA is delivered with a set of indexes. If you wish, you can create additional indexes to meet your query requirements. Use DAC for this purpose.

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 warehouse tables before loading large volumes of data, and to recreate them afterward. DAC can automate this process for you. To drop indexes before a load, and recreate them afterward, perform the following tasks in DAC:

1. Navigate to the Execute > OPVA Warehouse execution tab.

2. Select Drop and Re-create Index and save.

If set to Yes, Oracle DAC drops all indexes on all target tables before the Informatica Program is executed, and recreates them after execution.