6/9

1 Maintaining the OBIEE Repository and Warehouse

This chapter contains the following topics:

• Section 1.1, "Maintaining the Oracle Health Sciences Clinical Development Analytics OBIEE Repository"

• Section 1.2, "Maintaining the Oracle Health Sciences Clinical Development Analytics Data Warehouse"

• Section 1.3, "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 (Oracle Data Integrator (ODI) and 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.

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, maintaining versions of the Repository is the responsibility of the administrator. Oracle recommends that you:

• Back up 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 recommends 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:

1. In Oracle BI Administration Tool, click Manage > Variables.

2. Expand Repository and click Initialization Block > ETL_Refresh_Ranges.

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

See Also:

• Oracle® Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 for more information about modifying the RPD.

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

Note:

For each user-defined extension tables, its error table E$_<user-defined extension tables name> must be created. Also, provide the select, insert, update, and delete grants to the ODI warehouse work schema.

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

Important:

Create a backup of the Repository and Views before modifying it. Retain a log of changes you make to the Repository to ensure that the Merge Wizard correctly reapplies them to the next repository that Oracle ships.

1. Modify staging table W_RXI_STUDY_DS, adding the X_STUDY_MANAGER_NAME column. To modify the staging table, perform the following tasks in ODI:

   1. Copy the warehouse datastore W_RXI_STUDY_DS into another model.

      This is to ensure that the changes are not overwritten in the next OHSCDA upgrade.

   2. Add a new column X_STUDY_MANAGER_NAME and save it.

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

2. Modify the SDE that populates W_RXI_STUDY_DS, as indicated in the following steps:

   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. Duplicate the SRO view (VW_SDE_STUDY_DIM), used for this SDE as VW_SDE_STUDY_DIM_X.

   3. Add the STUDIES_MANAGERS table in the SQL to select STUDY_MANAGER_NAME.

   4. Grant select on SRO view (VW_SDE_STUDY_DIM_X) to the ODI source work schema.

   5. Reverse-engineer the new customized SRO schema view into another model.

   6. Open the copied SDE mapping in ODI.

   7. Click the Logical tab.

   8. Select the source to view its properties.

   9. Under the General section, change the Datastore to point to the newly created customized SRO view (VW_SDE_STUDY_DIM_X).

   10. Select the mapping target to view its properties.

   11. Under the General section, change the Datastore to point to the customized stage table.

   12. Map the new column STUDY_MANAGER_NAME from source to target.

   13. Validate and save the mapping.

   14. Generate scenario and schedule it in the load plan.

3. If W_RXI_STUDY_D does not already exist to support other extension, create the 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 the extension table W_RXI_STUDY_DX in ODI. To add a table, perform the following task in ODI:

   1. Reverse-engineer W_RXI_STUDY_DX into a new model (to ensure that the changes are not overwritten in the next OHSCDA upgrade) or use an existing customized table definition, if already exists.

5. Modify the SIL view and mapping that populate W_RXI_STUDY_D.

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

7. Generate scenario and schedule it in the load plan.

8. Using W_RXI_DISCREPANCY_FX as an example, propagate the extension table and its content to the Business and Presentation layers.

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. Perform the following tasks:

1. 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. Also, provide the select grant on Substitution view to the source work schema.

2. Modify the SDE to read from the Substitution view, rather than the Oracle-supplied view.

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

   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. Reverse-engineer the Substitution view into another model.

   3. Select the source to view its properties.

   4. Under the General section, change the Datastore so that the mapping reads from the Substitution view.

3. Validate and save the mapping.

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 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 view that reads the old table to instead read the new table.
Modified table has modified layout	Modify the SDE view to read the modified layout. Modify the staging table populated by the SDE to include the modified layout. Modify the SIL view and mapping 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 new staging table and its error table to accept the new input. Add a new SDE view in the SRO schema. Add a new SDE mapping to read from the new view and write to the new staging table. Add a new warehouse table and its error table to make the new data available to the BI Server. Add a new SIL view in the warehouse. Add an SIL mapping to populate the new data warehouse table from the new view. 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 SRO and warehouse views either in SRO and/or warehouse schema and in ODI, or in the source, staging, and target tables.

Managing Indexes

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

1. Execute the CDA - Complete Warehouse with Dedup load plan.

2. Set the variable VAR_OCDA_DWH_LOAD_FLAG value as 1 while running the load plan.

   Note:

   Setting the variable VAR_OCDA_DWH_LOAD_FLAG value as 1, while running load plan, executes for full load, truncates all warehouse tables, drops indexes, loads data, and recreates indexes.

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:

Note:

For more information on the existing data model, see Oracle Health Sciences Clinical Development Analytics Electronic Technical Reference Manual.

• 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 view and mapping to calculate the derived value during extract of records from the sources.

  3. Modify the SIL view and mapping 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.