2 Cross-study setup outside DMW

DMW is installed with Oracle Life Sciences Data Hub (LSH) and shares the same database. LSH is installed on the Oracle E-Business Suite, and uses its profiles, lookups, and user management systems.

• Set up study and library groupings
  
• Set up custom program and function groupings
  
• Set lookup values for Reasons for Change and more
  
• Set profiles to determine system behavior
  

Set up study and library groupings

Add a set of study groups to organize studies in a logical way. Codelists and library clinical data models use the same organization. Users select a grouping when they create a study or library object, and when they search for a study template, clinical data model, transformation, or validation check to reuse.

For example, you can organize studies and library objects by project, drug, or therapeutic area. The user interface label for study groupings is Project, but you can change it by resetting a profile. See Change the study grouping user interface label.

When deciding how to organize your studies, consider:

• You can create only one flat list of values. So to organize studies by drug as well as therapeutic area you must create groupings like "Cardiology Drug A" and "Cardiology Drug B."
• If you have clinical data models (sets of tables) generic enough to be used in multiple groupings, create a "Generic" grouping.
• The grouping becomes the namespace for its studies and library objects. This has implications for user security privileges: if you assign a user group to the study grouping, the user group is assigned to all studies and library objects in the grouping. A study configurator can revoke the assignment and assign different user groups to the study.

Study groups are Oracle LSH domains. For information on creating them using APIs, see the Oracle Health Sciences Life Sciences Warehouse Application Programming Interface Guide.

When you create a study in the DMW user interface, the system creates it as a subdomain in the selected study group domain.

To create study groups in the LSH user interface:

1. Log in to Oracle LSH. Expand the Life Sciences Data Hub node on the left and click Applications.
2. In the Applications window, click the Search icon next to the Select Domain field.
3. Select Search By Domain Name, enter DMW_DOMAIN, then click Go.
4. Click the Quick Select icon for DMW_DOMAIN.
5. Click the Create Child icon for DMW_DOMAIN.
6. From the Select Child list, select Domain and click OK.
7. Enter a name exactly the way you want it to appear in the user interface. Do not use special characters (except _) or reserved words.
8. Click Apply.

Set up custom program and function groupings

To create a custom program or function for use in a transformation from one clinical data model to another or in a validation check, programmers must create a program in the Oracle DMW_UTILS domain in Oracle LSH.

You can create application area containers within the Oracle DMW_UTILS domain to help users find appropriate custom programs and functions and reuse them, promoting standardization.

The process is similar to creating domains to organize studies and libraries; see Set up study and library groupings.

Note:

For performance reasons, Oracle recommends creating application areas instead of domains inside DMW_UTILS to organize programs and other objects.

For information on creating program and function group application areas using APIs, see the Oracle Health Sciences Life Sciences Warehouse Application Programming Interface Guide.

To create grouping application areas in the LSH user interface:

1. Log in to Oracle LSH. Expand the Life Sciences Data Hub node on the left and click Applications.
2. In the Applications window, click the Search icon next to the Select Domain field.
3. Select Search By Domain Name, enter DMW_UTILS, then click Go.
4. Click the Quick Select icon for DMW_UTILS.
5. Click the Create Child icon for DMW_UTILS.
6. From the Select Child list, select Application Area and click OK.
7. Enter a name as you want it to appear in the user interface. Do not use special characters (except _) or reserved words.
8. Click Apply.

Set lookup values for Reasons for Change and more

1. Open your Oracle LSH URL.
2. Log on with the system administrator account. An E-Business Suite screen opens.
3. From the Navigator drop-down, select LSH Setup Admin, then Setups, then Lookups.
4. Press the F11 key. The window enters Query mode.
5. In the Type field, enter the name of the lookup you want to see:

   • DME_DSC_ACTION_REASON to create standard Reasons for Change.

   • DME_PART% to set the number of small, medium, and large studies in a database partition.

6. Press Ctrl+F11 to enter the query.
7. Enter values.

   Tip:

   The Meaning text appears in the UI exactly as entered.

   Leave the From and To columns blank to apply the values immediately and with no end date.

• Reasons for change
  
• Number of studies per partition
  

Reasons for change

To enable users to select from a list of predefined reasons for change to discrepancies, add values to the lookup DME_DSC_ACTION_REASON. The access level is User. For each value, add a code and a meaning. The meaning text appears in the user interface as the reason for change.

If you define meaning text that is the same as Reason for Change text in InForm, when an InForm user applies a Reason for Change that is then imported to Oracle DMW, Oracle DMW recognizes that the text is the same and stores the code as well as the meaning. Reasons for change are configurable in InForm as well as DMW.

Number of studies per partition

(DME_PARTITION_DEVQC and DME_PARTITION_PROD) The access level is User.

When users define a study in Oracle DMW they must specify whether they expect the amount of data collected in the study to be small, medium, or large relative to other studies.

These lookups set the maximum number of small, medium, and large studies' data in certain cross-study internal tables that can be stored in a single database partition. Development and QC data are stored in the same partition, while Production data is stored in a separate partition.

The default values are: 1 large study, 5 medium studies, and 20 small studies for both lifecycle partitions.

You can change the default values in the Meaning column. Each value must be different from the others in the same lookup.

See Database partitioning for more information.

Set profiles to determine system behavior

1. Open your Oracle LSH URL.
2. Log on with the system administrator account. An E-Business Suite screen opens.

   Note:

   This user interface requires Java 6 Update 7 (1.6.0.0.7). If you do not have it installed, you can download it from http://www.oracle.com/technetwork/java/javase/archive-139210.html. To use this feature, you must accept all warnings.
3. In the Main Menu pane, expand the System Administrator (not System Administration) node, then the Profile node, and then click System.

   A new browser screen opens with several windows open and the Find System Profile Values window on top.

   Note:

   If you lose the Find System Profiles window at any point, click the Search icon in the toolbar or click System Profiles in the Top Ten list.
4. In the Profile field, enter the name of the profile. See the following tasks for details.

• Use character semantics
  
• Increase the maximum number of nested domains
  
• Append username to discrepancy text
  
• Change the study grouping user interface label
  
• Register root folders for File Watcher watched folders
  
• Register root folders for File Watcher archive folders
  
• Set blinding behavior for InForm hidden items
  
• Make discrepancy categories mandatory
  
• Make discrepancy actions mandatory
  
• Date format for conversion to text in unpivot transformations
  
• Hosted environments
  
• Set the default number of days of job history to display
  
• Use custom listing name for the custom listing schema
  
• Set currency behavior for Staging with Audit tables
  
• Turn debugging on or off
  
• Enable UI changes for a clinical data system other than InForm
  
• Set login-related and other profile values
  
• Enlarge SAS buffer size
  

Use character semantics

On each computer where you install Oracle DMW, you must set the Oracle Applications profile LSH: Use Character Semantics for Workarea Installation to Yes, as instructed in the Oracle Life Sciences Data Hub Installation Guide.

1. Log in. See Set profiles to determine system behavior.
2. In the Profile field, enter LSH: Use Character Semantics for Workarea Installation.
3. Click Find.
4. In the Site column, select YES.
5. In the File menu, select Save and Proceed.

Increase the maximum number of nested domains

LSH: Domain Nesting Levels should be set to at least 6 because three levels of domains are predefined for Oracle DMW and you will need more to create categories such as Therapeutic Areas.

1. Log in. See Set profiles to determine system behavior.
2. In the Profile field, enter LSH: Domain Nesting Levels.
3. Click Find.
4. In the Site column, enter a number between 6 and 9. Oracle recommends 9.
5. In the File menu, select Save and Proceed.

Append username to discrepancy text

This profile determines the default setting for a checkbox in the Oracle DMW discrepancy creation user interface. Users can override the setting.

1. Log in. See Set profiles to determine system behavior.
2. In the Profile field, enter Oracle DMW: Append Username to Discrepancy Text
3. Click Find.
4. In the Site column, select Yes or No.

   • If set to No, the default setting is not to append the username of the person creating the discrepancy to the discrepancy text. Note that the system tracks the username in the database regardless of this setting.

   • If set to Yes, the default setting is to append the username of the person creating the discrepancy to the discrepancy text. When discrepancies are sent to InForm, this information is displayed in InForm.

5. In the File menu, select Save and Proceed.

Change the study grouping user interface label

This profile determines the label that appears in the user interface in several places for the category of a study or library. By default, the label is Project.

1. Log in. See Set profiles to determine system behavior.
2. In the Profile field, enter Oracle DMW_STUDY_CLASSIFICATION_UI_LABEL
3. Click Find.
4. In the Site column, enter the label exactly as you want it to appear in the user interface, including upper- and lowercase.
5. In the File menu, select Save and Proceed.

Register root folders for File Watcher watched folders

This set of profiles tells the system where to look for SAS and text data files to be loaded into Oracle DMW. There are nine profiles. You enter values for either:

• Three folders, one for each lifecycle. Both SAS and text files are placed in the same folder.
• Six folders, one for each lifecycle/file type combination.

You must create the root folders in the locations you specify here. All studies in this Oracle DMW instance must use the same three or six root folders for their input data files. The system creates a study-specific subfolder in each root folder using the name you specify; see Create study File Watchers. The study-specific subfolders become the watched locations for the study.

Note:

If you change these profile values after using them, you must stop and restart the File Watcher service; see Changing the root folder location for data files.

1. Log in. See Set profiles to determine system behavior.
2. In the Profile field, enter Oracle DMW:FWR_ROOT_FOLDER%
3. Click Find.
4. In the Site column, enter the full path to the subfolder for the profile's file type/lifecycle combination. Oracle recommends using a naming convention so that it is easy to tell which folder should hold which files. Enter values for either the first six profiles or the last three.

   If you want to use six file type-specific root folders, enter values for these profiles:

   • DME:FWR_ROOT_FOLDER_TEXT_DEV for text files in the Development lifecycle.

   • DME:FWR_ROOT_FOLDER_TEXT_QC for text files in the Quality Control lifecycle.

   • DME:FWR_ROOT_FOLDER_TEXT_PROD for text files in the Production lifecycle.

   • DME:FWR_ROOT_FOLDER_SAS_DEV for SAS files in the Development lifecycle.

   • DME:FWR_ROOT_FOLDER_SAS_QC for SAS files in the Quality Control lifecycle.

   • DME:FWR_ROOT_FOLDER_SAS_PROD for SAS files in the Production lifecycle.

   If you want to use three folders for both file types, enter values for these profiles:

   • Oracle DMW:FWR_ROOT_FOLDER_ALL_DEV for SAS and text files in the Development lifecycle.

   • Oracle DMW:FWR_ROOT_FOLDER_ALL_QC for SAS and text files in the Quality Control lifecycle.

   • Oracle DMW:FWR_ROOT_FOLDER_ALL_PROD for SAS and text files in the Production lifecycle.

5. In the File menu, select Save and Proceed.

Register root folders for File Watcher archive folders

To archive data files instead of deleting them after loading their data into Oracle DMW, you must create archive folders and register them using this set of profiles.

There are nine profiles, corresponding to the nine profiles that register root folders for watched folders. You must choose the same setup—three or six root folders—for archive folders that you did for watched folders. The system creates a study-specific folder in each root folder using the name you specify; see Create study File Watchers.

When the system moves a file to an archive folder, it appends the current timestamp to the file name to ensure uniqueness.

1. Log in. See Set profiles to determine system behavior.
2. In the Profile field, enter Oracle DMW:FWR_ARCHIVE%
3. Click Find.
4. In the Site column, enter the full path to the subfolder for the profile's file type/lifecycle combination. Oracle recommends using a naming convention so that it is easy to tell which folder should hold which files.

   If you want to use six file type-specific root folders, enter values for these profiles:

   • DME:FWR_ARCHIVE_TEXT_DEV for text files in the Development lifecycle.
   • DME:FWR_ARCHIVE_TEXT_QC for text files in the Quality Control lifecycle.
   • DME:FWR_ARCHIVE_TEXT_PROD for text files in the Production lifecycle.
   • DME:FWR_ARCHIVE_SAS_DEV for SAS files in the Development lifecycle.
   • DME:FWR_ARCHIVE_SAS_QC for SAS files in the Quality Control lifecycle.
   • DME:FWR_ARCHIVE_SAS_PROD for SAS files in the Production lifecycle.

   If you want to use three folders for both file types, enter values for these profiles:

   • Oracle DMW:FWR_ARCHIVE_ALL_DEV for all files in the Development lifecycle.
   • Oracle DMW:FWR_ARCHIVE_ALL_QC for all files in the Quality Control lifecycle.
   • Oracle DMW:FWR_ARCHIVE_ALL_PROD for all files in the Production lifecycle.

   In addition, you must set the following profile values:

   • Oracle DMW:FWR_ARCHIVE_ENABLE Set to Y to enable archiving. The default value is N.
   • Oracle DMW:FWR_ARCHIVE_SCHEDULE_DAYS Enter the number of days to elapse between loading the file and moving it to the archive folder. The default value is 7.

     Note:

     Be sure to set the file deletion time period for individual study File Watchers to a greater number of days.
5. In the File menu, select Save and Proceed.

Set blinding behavior for InForm hidden items

If this profile determines whether data marked as Hidden in InForm is imported to DMW as blinded or not.

1. Log in. See "Set profiles to determine system behavior".
2. In the Profile field, enter Oracle DMW:BLIND_INFORM_HIDDEN_ITEMS
3. Click Find.
4. In the Site column, enter either:
   • Y so that any tables that contain columns based on InForm hidden items are created with column-level blinding on the hidden items. Blinded columns have a default masking value.
   • N to create all tables with their blinding flag set to N. Users can manually define blinding for tables in the data model in Oracle DMW.
5. In the File menu, select Save and Proceed.

Make discrepancy categories mandatory

Set this profile to Yes to require a manually created discrepancy to have an assigned category. See Create categories for flags, discrepancies, and validation checks.

1. Log in. See Set profiles to determine system behavior.
2. In the Profile field, enter DMW_DSC_CATEGORY_MANDATORY.
3. Click Find.
4. In the Site column, enter:

   • Yes to require manually created discrepancies to have a category assigned.

   • No to make these categories optional.

5. In the File menu, select Save and Proceed.

Make discrepancy actions mandatory

Set this profile to Yes to require a manually created discrepancy to have an assigned action. The action sets the state and tag of the discrepancy.

1. Log in. See Set profiles to determine system behavior.
2. In the Profile field, enter DMW_DSC_ACTION_MANDATORY.
3. Click Find.
4. In the Site column, enter:

   • Yes to require manually created discrepancies to have an action assigned.

   • No to make these actions optional.

5. In the File menu, select Save and Proceed.

Date format for conversion to text in unpivot transformations

This profile determines the date format for use by an unpivot transformation when converting DATE datatypes to VARCHAR2.

1. Log in. See Set profiles to determine system behavior.
2. In the Profile field, enter DMW_XFM_UNPIVOT_DATE_FORMAT.
3. Click Find.
4. In the Site column, do one of the following:
   • Leave blank. The system uses the default date format in the database.
   • Enter a valid Oracle date format, for example, DD-MM-YYYY for 22-01-2018 or DD/MON/YYYY for 22/JAN/2018.
5. In the File menu, select Save and Proceed.

Hosted environments

This profile must be set to Y if the installation is in an environment hosted by Oracle, to support Single Sign-on functionality. The default value is N, which is the required setting for on-premise installations.

1. Log in. See Set profiles to determine system behavior.
2. In the Profile field, enter LSW_SSO_INTEGRATED
3. Click Find.
4. In the Site column, enter:
   • Y if the installation is in an environment hosted by Oracle, to support Single Sign-on functionality.
   • N if the installation is maintained by your company.
5. In the File menu, select Save and Proceed.

Set the default number of days of job history to display

In the Home page Data Loads, Transformations, and Validation Checks tabs, the system displays the run history for jobs for the number of days you specify in this profile. The user can toggle between this display and a display of the full history for data loads, transformations or validation check batches.

1. Log in. See Set profiles to determine system behavior.
2. In the Profile field, enter DMW:NUM_DAYS_PAST_RUN_HISTORY
3. Click Find.
4. In the Site column, enter the number of days for which you want to display job history by default. The default value is 7.
5. In the File menu, select Save and Proceed.

Use custom listing name for the custom listing schema

If you are using a visualization tool to view data through custom listing schemas, set this profile to Yes to generate the secondary schema name with the same name as the custom listing. Using the name makes it easier for users to pick the listing they want to view in the visualization tool. The visualization tool can also query for the custom listing name instead of the ID. For example:

select * from BA_INF_CL_DEV.CLTEST;

where BA_INF_CL_DEV is the custom listing schema and CLTEST is the custom listing name, instead of:

select * from BA_INF_CL_DEV.QB_3254235_V;

where QB_3254235_V is the generated ID.

If set to No, the secondary schema created for the custom listing is named with the map ID generated by the system and visible only in the LSH work area.

1. Log in. See Set profiles to determine system behavior.
2. In the Profile field, enter DMW : Synch Custom Listing BA synonyms with Primary Datamodel Business Area
3. Click Find.
4. In the Site column, enter:

   • Yes to create synonyms in the primary business area schema based on custom listing names.

   • No to name the schemas using a generated ID.

5. In the File menu, select Save and Proceed.

Set currency behavior for Staging with Audit tables

If a job using Staging with Audit processing does not load, modify, or delete any records, by default the currency is soft-deleted, making it appear that the job never ran, and the currency is not available for that table for downsteam programs. To change this, set this profile to Yes.

1. Log in. See Set profiles to determine system behavior.
2. In the Profile field, enter CDR_SAVE_CURR_STG_TAB: Record currency for Staging table on empty loads
3. Click Find.
4. In the Site column, enter:
   • Yes. The new currency is recorded for Staging with Audit tables even when a job makes no data changes. The currency is then available for that table for downstream programs.
   • No (or any other value). No currency is recorded when a job makes no data changes.
5. In the File menu, select Save and Proceed.

Turn debugging on or off

Enable or disable the recording of debugging statements for database processes. The setting applies to all users. The system reads the value of the profile once per user session, so you may need to restart the application tier for a changed setting to take effect.

1. Log in. See Set profiles to determine system behavior.
2. In the Profile field, enter LSH:ENABLE_DEBUGGING
3. Click Find.
4. In the Site column, enter:
   • Y to enable debugging.
   • N to disable debugging.
5. In the File menu, select Save and Proceed.

Enable UI changes for a clinical data system other than InForm

It is possible to integrate a clinical electronic data capture (EDC) system other than InForm with DMW. See Using the Generic Connector to Integrate DMW with a Clinical Data System, My Oracle Support article 2172786.1.

This profile makes changes in the UI required for the integration, including a tab for a new input clinical data model type called External_System Configuration, new actions for sending discrepancies to the external system, and a new web service location type.

1. Log in. See Set profiles to determine system behavior.
2. In the Profile field, enter DMW_GC_ON
3. Click Find.
4. In the Site column, set to OUI to make user interface changes related to the external system integration visible.
5. In the File menu, select Save and Proceed.

Set login-related and other profile values

To control various login and password-related requirements, as well as other system behavior, follow instructions in the Oracle Life Sciences Data Hub System Administrator's Guide.

See How to Log In to Oracle Applications Profile Forms

Enlarge SAS buffer size

By default, this profile sets the default SAS buffer size option for PROCSQL statements to 262144, which allows loading SAS data files with large "logical record" length. It should not be necessary to change the value of this profile.