6 Advanced administration topics

• Oracle Thesaurus Management System (TMS) integration
  
• Database partitioning
  
• Changing the root folder location for data files
  
• Updating web service location passwords in bulk
  

Oracle Thesaurus Management System (TMS) integration

• TMS processing
  
• Force rederivation
  
• DMW context information displayed in TMS
  

TMS processing

When DMW is integrated with TMS, transformation jobs have additional steps: they send new and updated source terms (data in specified columns) to TMS, run the TMS autoclassification job, and derive TMS data into DMW tables.

Terms that cannot be automatically coded must be manually coded in TMS. TMS then codes all future occurrences of the same term the same way within the same domain.

If a TMS user cannot classify the term because of a problem with the term, for example, it is really multiple terms, like nausea and headache, he or she can send a TMS action back to DMW as discrepancy text. The discrepancy is then sent to InForm.

• TMS autoclassification, synchronization, and manual processing
  
• New and updated data processed in TMS
  
• System interaction conflicts and discrepancy statuses
  

TMS autoclassification, synchronization, and manual processing

1. DMW TMS processing runs the TMS synchronization job, which checks for changes in the TMS dictionary(ies) associated with a study that impact DMW terms in TMS, either coded or not yet coded. It also checks for TMS data that meets the criteria in New and updated data processed in TMS.

2. TMS loops through all records that meet the criteria and calls autoclassification for each one.

   Autoclassification looks for exact matches to source terms in the coding dictionary level. If there is no exact match, it checks if a TMS user has previously classified the same term to a dictionary term (this is called a verbatim term assignment, or VTA), and classifies the current term the same way. It checks first in the current domain and then, if there is no VTA in the domain, globally.

   • For terms with a dictionary term or VTA match, TMS sends derived data back to DMW during the same transformation job.
   • For terms that do not have a dictionary term or VTA match, TMS creates a discrepancy in DMW and, if the lifecycle stage is Production, an omission in TMS. TMS sets the DMW discrepancy category to TMS in Progress as described in System interaction conflicts and discrepancy statuses.

3. For terms without a match, a TMS user does one of the following:

   • Classifies the term manually, creating a new VTA. During the next transformation, autoclassification finds the new VTA and TMS derives data to DMW and answers the discrepancy, setting the DMW discrepancy category to TMS DM Review as described in System interaction conflicts and discrepancy statuses.
   • Applies an action to the omission, requesting review or clarification of the term. This becomes the discrepancy text in DMW and the query text in InForm. TMS sets the DMW discrepancy category to TMS INV, sending the discrepancy to InForm for investigator review.

4. A DMW user can update a discrepancy as follows:

   • Add a comment. The user can choose to send the comment to TMS, which changes the category to TMS Evaluation, triggering autoclassification in TMS and making it impossible to make any other updates to the discrepancy until it is acted on in TMS.

   • Change the DMW discrepancy category directly to any of the TMS-related categories.

   • Send the discrepancy to the source system. After a discrepancy has been sent to InForm, any additional changes to the discrepancy in DMW or TMS are automatically sent to InForm.

   An InForm user can update a discrepancy as follows:

   • Answer the discrepancy by providing text in response to the discrepancy text.
   • Correct the underlying data item.

     In both cases, the category is updated to TMS Evaluation and TMS autoclassification is triggered.

     If the data change results in a successful classification, TMS sends derived data to DMW during the next transformation run. If a data change results in declassifying a term that was previously classified, previously derived data is deleted.

     If a source term is deleted in InForm, and then DMW, it is deleted in TMS as well.

Note:

TMS autoclassification, synchronization, derivation, actions, and DMW discrepancy management are the same in all three lifecycle stages. However, TMS omissions are created only in Production, so TMS users can do manual classification only in Production, and Force rederivation works only in Production.

New and updated data processed in TMS

TMS processing includes only target tables that have at least one column associated with a TMS Set primary column and meet at least one of the following conditions:

• New records in the source table.

• Updates to coded terms (data in the primary column) in the source table.

• Updates made in TMS that affect DMW data.

• Updates in DMW or the source system to TMS-originated discrepancies on data in the source tables.

System interaction conflicts and discrepancy statuses

The same data passes through three systems—InForm, DMW, and TMS—and conflicts may occur:

• An InForm user may update a TMS-originated discrepancy, changing its text or status, or the underlying data. Those changes are loaded as updates into the DMW discrepancy system, triggering TMS autoclassification and passing any new discrepancy text to TMS.

• State changes in InForm may conflict with the results of autoclassification in TMS. For example, an InForm user might close a discrepancy thinking that the term is a valid one while TMS does not recognize it. In this case, even though the Closed status is loaded into DMW, TMS opens a new discrepancy when autoclassification cannot find a match for the term.

• TMS classification might overwrite status changes made in InForm.

DMW uses four statuses to help coordinate the interaction of the three systems. The first two are set by the system:

• TMS Evaluation is set by DMW either when a DMW user adds a comment to a TMS-originated discrepancy (that is not marked as an internal comment) or when an InForm user answers a discrepancy (with or without a data change) and the update is loaded into DMW. The purpose of TMS EVALUATION is to trigger autoclassification on the data again during the next transformation. Without it, autoclassification would run only if there was a data change or if a TMS user had altered the record.

• TMS in Progress is set by TMS when autoclassification creates or updates an omission. TMS users can manually classify an omission only when its state is TMS IN PROGRESS.

  DMW users cannot update a discrepancy at status TMS IN PROGRESS. If the discrepancy has previously been sent to InForm, InForm users can update it. However, InForm users will not have new data from TMS until the TMS INV Review status is applied.

TMS users can apply the following statuses during omission management or when creating an action:

• TMS DM Review Prevents TMS users from updating. A DMW user may update with a comment, which sets the discrepancy and omission to TMS EVALUATION so that TMS runs autoclassification on the term during the next transformation.

• TMS INV Review Sends the discrepancy to InForm for Investigator review and prevents TMS users from updating.

Force rederivation

Normally, transformations process only new or changed data. Use the Force Rederivation job to process all data when you have made structural changes related to TMS in an ongoing study such as:

• Adding columns to target tables to hold derived data.

• Updating a dictionary to a new version with a different structure from the old one.

• Changing domain-related settings in the TMS reference codelist TMS_CONFIGURATION.

1. Go to the Home page.
2. Select the study.
3. Click the Modify icon at the top of the Studies pane.
4. In the Modify Study window, click TMS.
5. Click Force Rederivation. A confirmation message appears because running the job may take a long time. You can still work while it runs.
6. Click OK.

DMW context information displayed in TMS

For each discrepancy created by TMS, the equivalent TMS omission is displayed in several TMS windows with the following contextual information about the data point (source term) from DMW:

• Study grouping (such as Project or Therapeutic Area)

• Study name

• Clinical data model

• Table name

• Column name

• Subject ID

• Visit name

• DMW discrepancy ID

  Note:

  The subject ID and visit name can be displayed only if the Subject ID and Visit Name columns have the corresponding SDTM ID assigned in the DMW table that is mapped to a TMS set.

Database partitioning

When a user creates a study in Oracle DMW, he or she must specify a study size of either Small, Medium, or Large for the volume of data expected, relative to other studies. The system uses this value, together with lookup values that you can modify, to determine which database partition to use for storing certain types of data in internal cross-study tables.

• Partitioned tables
  
• Develop guidelines for setting study size
  
• Specify the number of similarly sized studies per partition
  
• Assignment algorithm
  

Partitioned tables

The tables that use partitioning do not store clinical patient data, but they do store data that is likely to be created in proportion to the volume of clinical data, especially the discrepancies table.

The system adds a column for the partition ID in all affected tables. The internal SYS_CONTEXT tracks the partition ID as well as the current study and lifecycle area. All internal queries to affected tables must include the partition ID and call an internal API to get the ID from the SYS_CONTEXT to run most efficiently.

The tables are:

• DME_CTXT_SKEYS_MAP: This table is used to trace the lineage between source and target tables used in transformations. It contains data that maps between specific source and target records. Each record in the target table of a transformation has at least one corresponding record in the DME_CTXT_SKEYS_MAP table, and for some transformations there are multiple records. For example, in a direct map there is one record per target table record; in a join of three tables, there are three record per target table record.

• DME_OPOBJ_CONTEXT_MAP: This table is used to highlight discrepancies on the Listings display. The table contains an entry for each primary discrepancy, in the model where it was created, and all of its secondary discrepancies on the same data item in upstream and downstream models. This table is also used to obtain the primary source data item for a discrepancy.

• DME_DISCREPANCIES: This table stores discrepancy-related information such as the table, column, model, study, and lifecycle of the record on which each discrepancy is created. This table also records discrepancy state, comments, and discrepancy ID.

• DME_FLAG_DATA: This table stores flag assignments to records that that users add and modify in the Listings pages.

• DME_DISC_ACTION_HISTORY: This table stores the history of actions performed on specific discrepancies.

• DME_DISC_CSV_FILES: This stable stores sets of discrepancies exported as CSV files.

Develop guidelines for setting study size

Oracle recommends that you develop guidelines to help study configurators categorize studies as small, medium, or large, and to help you in Specify the number of similarly sized studies per partition.

For example, base categories on the number of subjects and the number and size of CRFs in the protocol.

Specify the number of similarly sized studies per partition

The maximum number of small, medium, and large studies using a single partition is determined by lookups called DME_PARTITION_DEVQC and the DME_PARTITION_PROD. The default settings for both are:

• Small studies: 20

• Medium studies: 5

• Large studies: 1

You can change these values; see Number of studies per partition for details.

For example, if you have a relatively small amount of data in your Development and Quality Control lifecycle areas and a huge amount of data in Production, you might change to settings like:

• For the Development/QC partition:

  • Small studies: 20

  • Medium studies: 15

  • Large studies: 11

• For the Production partition:

  • Small studies: 10

  • Medium studies: 4

  • Large studies: 1

There is a limit to the number of partitions that can be created for a database table: 1024k-1. Each partition can hold any number of records for each study; that is, any number of discrepancies or flags for given study.

Assignment algorithm

Studies are assigned to partitions according to their defined size and in the order they are added.

For example, using the default value of 5 medium studies per partition, the system creates two partitions the first time a medium study is created, one for Development/QC and the other for Production, and assigns that study and the next 4 medium studies to those partitions. When the 6th medium study is created the system creates two new partitions (Development/QC and Production) and assigns the 6th through 10th medium studies to those partitions, and so on.

Changing the root folder location for data files

Changing the root folder location is disruptive to the flow of work in Oracle DMW and should only be done with caution. However, you can modify the location if necessary:

1. Create new root folders in the new location for both watched and archive folders.
2. Change the value of the LSH profiles to the new location. See Register root folders for File Watcher watched folders and Register root folders for File Watcher archive folders.
3. Restart the Distributed Processing Server that includes the File Watcher service. Instructions are in the Oracle Life Sciences Data Hub System Administrator's Guide.
4. Open each study File Watcher in Edit mode and click Regenerate to apply the new settings in the study; see Create study File Watchers.

The system then:

• Migrates all existing Study File Watchers to the new root folders.
• Processes any files already submitted for data load at the time of the change.

The system does not move any files. You must:

• Determine which files have not been loaded, if any, and move them to the new location.
• Determine which loaded files have not been archived or deleted, if any, and move or remove them.

Updating web service location passwords in bulk

When you update the password for one web service location, if there are other DMW web service locations that use the same SSO org (your company's tenant ID) and the same user name as the one you are updating, the password is updated on all of them. So there is no need to perform the same action on other similar web service locations.