1 Maintaining the OBIEE Repository and Warehouse

This chapter contains the following topics:

Maintaining the Oracle Health Sciences Clinical Development Analytics OBIEE Repository
Maintaining the Oracle Health Sciences Clinical Development Analytics Data Warehouse
Derivations

1.1 Maintaining the Oracle Health Sciences Clinical Development Analytics OBIEE Repository

Each release of Oracle Health Sciences Clinical Development Analytics (OHSCDA) contains an OBIEE 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 Analyses. As shipped, the RPD corresponds to the OHSCDA data warehouse, and can be used without any modification.

Note:

OHSCDA ships with more than one Repository (DAC and Informatica, as well as OBIEE, have Repositories). This section, however, is concerned only with the OBIEE Repository that ships with OHSCDA. Therefore, throughout this section, the terms Repository and RPD should be taken to mean the OBIEE Repository shipped with OHSCDA.

However, you might find it desirable to modify the Oracle-supplied OHSCDA 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 OHSCDA must refresh this variable, refer to the Note in step 13 of the Executing the ETL Programs section.

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 subsequent patches and releases of OHSCDA. 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 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.

1.1.1 Modifying the Repository

In OHSCDA Standard configuration, maintaining versions of the Repository is the responsibility of the administrator. Oracle recommends that you:

Make a backup copy of each state of the repository before you modify it
Attempt all modifications on a side copy of the Repository before putting it into production use
Verify all changes to the Repository by running requests from the Presentation Service against the modified Repository

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

If you need to modify the Repository, use the OBIEE Administration Tool.

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

1.1.2 Merging Changes into a New Oracle-supplied Repository

Each OHSCDA release, and some patches, includes a copy of the OHSCDA Repository. If you do modify your copy of the OHSCDA Repository, you must merge your changes into the Oracle-supplied Repository each time you receive a release or patch of OHSCDA that includes a repository. At upgrade time, use the OBIEE Merge Repository Wizard (accessed through File > Merge) in the Repository Administration Tool to merge your modified RPD with the Oracle-supplied RPD.

For information on comparing and merging Repositories, refer to Chapter 15, Managing Oracle BI Repository Files, in Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1.

1.2 Maintaining the Oracle Health Sciences Clinical Development Analytics Data Warehouse

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

Caution:

Exercise caution when you modify the data warehouse. Please conform to the recommendations mentioned in Section 1.2.3, "Modifying Data Warehouse Tables" .

1.2.1 Extensions

An extension is a new column added to the data warehouse for data not extracted from the transactional sources by the SDE supplied with OHSCDA.

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. The column in that table holding the Study Manager name is STUDY_MANAGER_NAME.
This table has a foreign key to the primary key in Oracle Clinical table OCL_STUDIES.

Note:
When you add extension columns to the OHSCDA data model, the column name must start with "X_". This avoids collisions with names of columns added by Oracle in later releases of OHSCDA.

You can add the column either to the target table, or to an extension table. 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 name to the study dimension for each study:

Modify staging table W_RXI_STUDY_DS, adding the X_STUDY_MANAGER_NAME 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 OHSCDA 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, as indicates in the following steps:
- Copy the SDE into another folder (to ensure that the changes are not overwritten in the next OHSCDA 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. STUDY_MANAGER_NAME to W_RXI_STUDY_DS.X_STUDY_MANAGER_NAME.
- To modify the SDE, perform the following tasks in DAC:
  
  Navigate to Design > Tasks subtab.
  Navigate to SDE of Study Dimension. Right click and select Synchronize.
If deduplication is required for the dimension, then modify the existing De-Dup SDEs to include the new source table definitions in a similar way as explained in Step 2. After the mapping is modified, perform the following tasks in DAC to modify the table definitions
1. Navigate to corresponding De-Dup Container and then navigate to the Design > Tables subtab.
2. Add the new column under W_RXI_STUDY_DS table.
3. Click Save.
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 the extension table W_RXI_STDY_DX. To add a table, perform the following tasks in DAC:
1. Duplicate the container in DAC (to ensure that the changes are not overwritten in the next OHSCDA upgrade) or use an existing customized table definition.
2. Navigate to Tables subtab Under Design Tab and add the new table W_RXI_STDY_DX 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_NAME into W_RXI_STUDY_DX.STUDY_MANAGER_NAME 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 OHSCDA upgrade) or use an existing customized SIL.
2. Add the new column and save.
Modify the repository:

Important:
Create a backup of the repository before modifying it. Retain a log of changes you make to the Repository so you can ensure that the Merge Wizard correctly re-applies them to the next repository that Oracle ships.
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.
If deduplication is required for the dimension, then modify the existing De-Dup SDEs to include the new source table definitions in a similar way as explained in Step 2. After the mapping is modified, perform the following tasks in DAC to modify the table definitions
1. Navigate to corresponding De-Dup Container and then navigate to the Design > Tables subtab.
2. Add the new column under W_RXI_STUDY_DS table.
3. Click Save.

1.2.2 Substitutions

A substitution occurs if you have a preferred alternative source of data for a column that OHSCDA 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 OHSCDA 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 extracts the data to a staging table already exists. You will have to perform the following tasks:

Create a view that joins the Oracle Clinical table and the table containing your mandatory DCI information, in which your values replace the Oracle-supplied values for the column of interest. Call this the Substitution View.
Modify the SDE to read from the Substitution View, 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 OHSCDA 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 1-1:

Table 1-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.

1.2.3 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

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

Note:
If you opt for deduplication choose CDA - Complete Warehouse De Dup.
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.

1.3 Derivations

A derivation is a calculation of a new measure as a function of some supplied measures. You can use any of the following approaches to calculate derivations:

Calculate the derivation as part of the creation of an analysis.

In this approach, only the Presentation Catalog is modified. However, you must specify the calculation for each analysis, and the calculation is executed every time the analysis is executed.
Calculate the derivation in the physical or business layer of the RPD and propagate it 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 analyses. Once it has been propagated to the Presentation Layer, the derived value looks the same as any other Answers column to an end user.
Calculate the derivation in the data warehouse.

In this approach, you add an extension column to the warehouse to hold the derived values. To do so:
1. Add the result column to the staging and target table of the fact or dimension in which the derivation is being created. It is an extension column, so its name must begin with "X_".
2. Modify the SDE to calculate the derived value during extract of records from the sources.
3. Modify the SIL to transfer the derived value from the Staging to the Target table.
4. Create and execute a script to provide values for the derived column in rows already present in the Target.
5. Add the column to all layers of the RPD.

In carrying out these steps, follow the rules described above for changes to the RPD and warehouse.