4 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 Oracle LSH. 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:

Check out the Business Area (BA) to a different domain in which you will customize the Repository, or use an existing customized Business Area.

Important:
Use the Copy definition to the local Application Area and check out option to check out the program to a different domain. This preserves the changes to the definitions in a different domain, and ensures that the changes are not overwritten automatically in the next upgrade of OCDA.
If the reason you are modifying the RPD is that you have modified the data warehouse:
1. Verify the mappings of the tables in the BA.
2. Remap table instances and table descriptors, if necessary.
  
  If you made any changes to the BA, reinstall the Work Area and check out the Business Area again.
  
  Note:
  In this checkout, do not use the Copy definition to the local Application Area option to check out the program.
Click Launch IDE on the Business Area's Properties screen.

This downloads the versioned RPD object from the Business Area, and opens the Oracle BI Administration tool. Make the desired modifications to the downloaded RPD. Refer to Details for Selected Modifications for instructions on applying selected modifications.
Save the changes; exit the Oracle BI Administration tool.
In Oracle LSH, upload the modified RPD back into the Oracle LSH Business Area.

The modified RPD can be found in a location that has been defined for your LSH configuration.
Ensure that the OBIEE DP Server is up and running. Otherwise, the next step will indicate success, but the RPD will not be deployed.
Install the WorkArea that contains the BA that contains the modified Repository.
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:

Create a pass-through view of the STUDY_MANAGERS table so that the table is visible in Oracle LSH.
Modify staging table W_RXI_STUDY_DS, adding the STUDY_MANAGER column. To modify the staging table, perform the following tasks in Oracle LSH:
1. Check out the table definition into another domain (to ensure that the changes are not overwritten in the next OCDA upgrade) or use an existing customized table definition.
2. Add the new column and reinstall.
  
  Important:
  Before you reinstall, ensure that the Informatica DP Server is up and running.
Modify the SDE that populates W_RXI_STUDY_DS, in two ways:
- Add the STUDIES_MANAGERS table as a source of the 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 Oracle LSH:
1. Check out the SDE into another domain (to ensure that the changes are not overwritten in the next OCDA upgrade) or use an existing customized SDE.
2. Add the new column and reinstall.
  
  Important:
  Before you reinstall, ensure that the Informatica DP Server is up and running.
If it does not already exist to support some other extension, create extension table W_RXI_STDY_DX, containing one column [STDY_WID] to function as a foreign key that joins to the primary key in W_RXI_STDY_D. This table is populated with one row for each row in W_RXI_STDY_D when the Study SIL executes.
Add column STUDY_MANAGER to W_RXI_STDY_D to hold the name of the study manager. To add a column, perform the following tasks in Oracle LSH:
1. Check out the table into another domain (to ensure that the changes are not overwritten in the next OCDA upgrade) or use an existing customized table definition.
2. Add the new column and reinstall.
  
  Important:
  Before you reinstall, ensure that the Informatica DP Server is up and running.
Modify the SIL that populates W_RXI_STDY_D. Add instructions to create a record in W_RXI_STDY_DX for each record in W_RXI_STDY_D, and to copy W_RXI_STUDY_DS. STUDY_MANAGER into W_RXI_STDY_DX.STUDY_MANAGER for each record.

To modify the SIL, perform the following tasks in Oracle LSH:
1. Check out the SIL into another domain (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 reinstall.
  
  Important:
  Before you reinstall, ensure that the Informatica DP Server is up and running.
Modify the repository:

Important:
Before you modify the repository, ensure that you check out the Business Area containing the repository (OCDA_OBIEE_WA) to a different domain. This preserves the changes to the definitions in a different domain, and ensures that the changes are not overwritten automatically in the next upgrade of OCDA. Alternatively, you can use an existing customized Business Area.
1. Import the definition of the extension table, W_RXI_STDY_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 or pass-through view in Oracle LSH 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 Oracle LSH:
1. Check out the SDE into another domain (to ensure that the changes are not overwritten in the next OCDA upgrade) or use an existing customized SDE.
2. Modify the definitions and reinstall.
  
  Important:
  Before you reinstall, ensure that the Informatica DP Server is up and running.

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 4-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 Oracle LSH, or the source, staging, and target tables. In either case, use Oracle LSH capabilities to modify the definition of the relevant tables, or to create new tables.

Managing Indexes

OCDA is delivered with a set of indexes. If you wish, you can add appropriate indexes to meet your query requirements. Use Oracle LSH for this purpose.

If all indexes must be dropped and recreated, perform the following tasks in Oracle LSH:

Navigate to the Submit Execution Setup screen of the program instance.
In the Submission Parameters tabbed page, set the value of Drop and Recreate Index to Yes.

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

If you do not want to drop and recreate indexes for all Table Descriptors, you can call the Oracle LSH API to drop and recreate specific indexes. For more information about selective index management, refer to the Oracle Life Sciences Data Hub Developer's Guide, (Selective Index Management).