5 Maintaining the Repository and Warehouse

This chapter contains the following topics:

Maintaining the Oracle Clinical Development Analytics Repository
Maintaining the Oracle Clinical Development Analytics Data Warehouse

Maintaining the Oracle Clinical Development Analytics Repository

Each release of Oracle Clinical Development Analytics (OCDA) 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 OCDA data warehouse, and can be used without any modification.

However, you might find it desirable to modify the Oracle-supplied OCDA 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. For instance, you may want to modify the frequency with which the value of the dynamic repository variable CURRENT_DAY is refreshed. For more information about why OCDA must refresh this variable, refer to the Note in step 13 of the Executing the ETL Programs section.
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 OCDA. 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 OCDA 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.

Modifying the Repository

The OCDA 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 OCDA Repository directly.

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

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

Make the desired modifications to the downloaded RPD. Refer to Details for Selected Modifications for instructions on applying selected modifications.
Launch the Oracle BI Presentation Server to verify the changes.

Details for Selected Modifications

This section contains details on how to perform certain modifications to the RPD.

To modify the frequency with which CURRENT_DAY is refreshed:

In Oracle BI Administration Tool, click Manage > Variables.
Expand Repository and click Initialization Block > ETL_Refresh_Ranges.
In the Repository Variable Init Block - ETL_Refresh_Ranges screen, modify the value of Refresh interval.

Refresh interval indicates how often you want to refresh the value of CURRENT_DAY dynamic repository variable. By default, this value is set to 5 minutes. That is, the CURRENT_DAY dynamic repository variable is refreshed every five minutes. Modify Refresh interval to a suitable value.

Merging Changes Into a New Oracle-supplied Repository

OCDA releases include a copy of the Repository. The installation process for each release deploys that Repository. If you do modify your copy of the OCDA Repository, you must merge your changes into the Oracle-supplied Repository each time you receive a release or patch of OCDA that includes a repository. At upgrade time, use the OBIEE utility File > Merge in the Repository Administrator to merge your modified RPD with the Oracle-supplied RPD.

Maintaining the Oracle Clinical Development Analytics Data Warehouse

You may need to modify the OCDA 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 OCDA.
Substitution: Swapping data from a different source for a column that was delivered with OCDA.

Caution:

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

Derivations

A derivation is a calculation of a new measure as a function of some supplied measures. OCDA 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.

Extensions

An extension is a new column added to the data warehouse for data not available in Oracle Clinical or Oracle's Siebel Clinical.

Example: Adding the study manager's name as an attribute of the study dimension for each study. The following are the assumptions:

This information is available in a non-Oracle Clinical database, in a table named STUDY_MANAGERS.
This table has a foreign key to the primary key in Oracle Clinical table OCL_STUDIES.

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:

Modify staging table W_RXI_STUDY_DS, adding the STUDY_MANAGER 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 OCDA 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.
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 OCDA upgrade) or use an existing customized SDE.
- Add the STUDIES_MANAGERS table as a source in the Informatica program.
- Add a mapping of column STUDY_MANAGER from STUDY_MANAGER 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.
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.
Add column STUDY_MANAGER to W_RXI_STUDY_D to hold the name of the study manager. 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 OCDA 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.
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. STUDY_MANAGER into W_RXI_STUDY_DX.STUDY_MANAGER 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 OCDA upgrade) or use an existing customized SIL.
2. Add the new column and save.
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 OCDA. Alternatively, you can use an existing customized repository.
1. Import the definition of the extension table, W_RXI_STUDY_DX, into the Repository.
2. Using W_RXI_DISCREPANCY_FX as an example, propagate the extension table and its contents to the Business and Presentation layers.

Subsequent Releases

If a subsequent OCDA release requires an update of the tables or ETL that you modified in a different domain, OCDA does not overwrite such modifications. You can choose to manually upgrade to the new OCDA releases by pointing your definitions to Oracle-supplied definitions.
If a subsequent OCDA release requires an update to the Repository that you modified in a different domain, OCDA does not overwrite such modifications. You can use the OBIEE Repository merge utility equalizerpds.exe to merge your modified RPD with the Oracle-supplied RPD.
If a subsequent OCDA release requires an update to the Web Catalog, the OBIEE Web Catalog merge capability will preserve your changes to the catalog while applying Oracle's changes.

Substitutions

A substitution occurs if you have a preferred alternative source of data for a column that OCDA populates from Oracle Clinical or Siebel Clinical. For example, you have a system for defining what data collection instruments (DCIs) are mandatory for a given study, subject, or subject visit, and you prefer that over the OCDA calculation that is based on expected data collection modules (DCMs) and subject visit schedules. In this case, your column will be present in a table, and the SDE that propagates the data to a staging table already exists. You will have to perform the following tasks:

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.
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.
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 OCDA 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.

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

OCDA 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:

Navigate to the Execute > CDA-Complete Warehouse execution tab.
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.