4 Set up data transformations

This section describes how to map at the following levels:

• Clinical data model
• Table
• Column

It also describes how to cascade blinding and masking, install a transformation, and run transformations and view history. You can also learn how to set automatic triggering of transformations and validation checks, upgrade transformations to synchronize with models, and view validation status and lifecycle stages.

The following diagram shows consecutive clinical data models Source, All Sites, Review, and Analysis. Subjects' weight is collected at each visit and mapped from the source model to each subsequent model, merging data from all sites in a union to the All Sites model, and converting the units from pounds to kilos in the Review model. Subjects' height is collected during the initial visit. To calculate the BMI in the Analysis model, Height is mapped from the source model and Weight is mapped from the Review model. The calculation is performed as an expression on the target column.

Figure 4-1 Transformation Example: BMI Calculation

• Map at the clinical data model level
  
• Use side transformations
  
• Map at the table level
  
• Map at the column level
  
• Cascade blinding and masking
  
• Table transformation types
  
• Use a custom program
  
• Install a transformation
  
• Upgrade transformations to synchronize with models
  
• Run transformations and view history
  
• FAQs
  

Map at the clinical data model level

For details on mapping at the clinical data model level, see this video:

1. Click the Study Configuration icon from the navigation bar. Then click the Transformations tab.

2. In the Data Models pane on the left, select the clinical data model the transformation will write data into.

   The system displays its tables in both the Target Tables and Source Tables panes so that you can use the target tables as a source for side models.

3. Click the Add or Remove Source Model icon in the Source Tables pane.

   1. Select one or more models to feed data into the transformation's target model.

      Tip:

      Reference models are listed after the study models. See Set up reference data.
   2. For each, select the Can Trigger checkbox if you want the completion of a job updating data in the source model to trigger the execution of this transformation.
   3. Click Save.

   Tip:

   If many people are working on the same transformation, Use side transformations.

   Next: Map at the table level.

   Note:

   A tilde (~) displayed next to an input model means the model was deleted while the transformation was checked in. When you check out the transformation, the deleted model is no longer displayed at all.

Use side transformations

To allow many people to work on a transformation at the same time, each person can create a side model with one target table to work on in a corresponding side transformation.

• Create a side transformation
  
• Merge a side transformation with the main transformation
  

Create a side transformation

Work on one side transformation at a time, then merge it into the main transformation, and delete the side model.

1. Check out the transformation if it is not already checked out.
2. In the Target Tables pane of the Transformation tab, select the target table(s) you want to work on. Use Ctrl+click or Shift+click to select multiple tables.

   Tips:

   A target table can be in only one side model at a time. An icon next to the table name indicates a table already in a side model. Hover over the icon to see who is working on it.

   You can select a table marked Not Used. It is marked Used when you merge back to the main transformation.

3. Click the Create Side Model icon. You may need to click the >> icon to see it. It is not enabled until you select a table.
4. Enter a name for the side model. Do not use the string $TEMP.
5. Click OK.
6. Select one or more source tables, then select the target table, and then click the Map icon in the target table's row.

   You can Use Automap before or after mapping manually.

7. Select a Transformation Type: Direct, Join, Union, Pivot, Unpivot, or Custom.

   Tip:

   Even if you are using a custom program, select the actual type of transformation your program performs: Direct, Join, Union, Pivot, or Unpivot. Use Custom only if your program cannot support data lineage tracing.
8. Map the target table and columns; see Map at the table level and Map at the column level.
9. (Optional) Cascade blinding or define tables and columns as Not Used. When the side transformation is merged back into the main transformation, the blinding and Not Used attributes are applied to the tables in the main transformation.

   Any validation checks or custom listings dependent on tables or columns marked as Not Used are disabled when the side transformation is merged with the main transformation.

10. Check In the side model.

    Tip:

    You can now run the side transformation from the Home page without affecting the main transformation. You can see data in the target tables in the Listings page.

Next: Merge a side transformation with the main transformation.

Merge a side transformation with the main transformation

When you merge the side model with the main model, any blinding and Not Used values are merged with the target model.

1. Go to the main transformation for the target model.
2. Check out the main transformation.

   Tip:

   If another user has the main transformation checked out, you cannot merge the side model into it. Either wait until you can check out the main transformation or ask the person who has checked it out to merge your side model.

3. Select Copy from Side Model from the Actions drop-down list.
4. Select the side model and click Next.

   If any tables have been modified since you created the side model, the system displays a message and you can choose to include the tables in the copy or not.

   • If you continue, the side model tables overwrite the main model tables.

   • If you cancel the copy operation, you can upgrade the side model to reflect changes made in the main model.

5. Select the side model and click the Delete Side Model icon. You get a warning if it has not been merged with the main transformation.

Map at the table level

For details on mapping at the table level, see this video:

You must specify how to handle every table in the target model: either mark it Not Used or map it to one or more source tables.

You can work in many ways:

• Copy table transformations
  
• Use Automap
  
• Map tables manually
  
• Mark target tables and columns as Not Used
  
• Create a self-join
  
• Authorize target table data for nonprivileged users
  
• Add expressions on mapped sources
  
• View source code
  
• Validate mappings
  
• Unmap tables
  

Copy table transformations

If a transformation between the same or very similar source and target tables exists in a different study, you can copy it and do most of the mapping automatically.

1. Select target tables that are the same or similar to the target table mappings you plan to copy.
2. Select Copy from Another Transformation from the Actions drop-down list.
3. Specify the transformation to copy: Select a project (or other study grouping) from the drop-down list, then select a study, then the model that is the target of the transformation to be copied.

   Tip:

   When a transformation is copied from an external transformation or as part of applying a study template, authorized tables are always copied into the new transformation as Not Authorized. If any of the source tables are blinded, the target table must be either blinded or re-authorized if it does not contain blinded data.

4. Click Next. The system validates the Copy and displays details including any errors or warnings detected by the validation:

   • For pivot and unpivot transformations, if metadata differences exist, the system displays an error. If the differences are on the source side, they must be resolved manually. If the differences are on the target side, you can run synchronization to resolve the differences and then copy. See I noticed the tables in the target model changed. Why?.

   • For union and join transformations, if some of the source tables or columns in the union do not exist in the copied-to study, those mappings are not included and a warning is displayed. If only one table exists in the copied-to study, the union or join map is converted to a direct map and a warning is displayed.

   • For custom transformations, if metadata differences exist, the system displays an error. If the differences are on the source side, they must be resolved manually. If the differences are on the target side, you can run synchronization to resolve the differences and then copy. See I noticed the tables in the target model changed. Why?.

   • For column mappings that use expressions, if all the column references do not exist in the current source tables, the missing column references are removed from the expression.

   You can't copy transformations with errors, but you can copy those with warnings.

5. Select mappings to copy, and click Accept Selected Mappings.

   Tip:

   If the source and target tables in the copied transformation have more columns than the tables in the current model, mark those columns Not Used.

Use Automap

The Automap process searches for tables in source models to map to tables in the target model. It creates only direct 1:1 mappings. It does not change existing manual mappings. After running it, you select the mappings you want to use.

For details on the automap process, see this video:

and this:

1. After you select a study, click the Study Configuration icon from the navigation bar to open the Clinical Data Model tab.
2. Click the Transformation tab to view the source and target tables.
3. Choose a target data model from the Data Model list in the left panel.
4. Do one of the following:
   • To map all the tables and columns in the data model, click Map > Automap from the Transformation tab title bar.
   • To map tables and columns you select, select one or more target tables and click Map > Automap Selected Tables from the option in Target Tables.

     Tip:

     For the best performance, select 10 tables or less.
5. Wait for the mapping process to complete on the data model. (If you need to work on other data models while the mapping runs on the selected model, you can.) During the mapping process, you see a red status message in the Transformation tab title bar (next to the Check In button). For example, you see an "Automapping in progress" or "Automapping is complete" message in red.
   You also see a notification message open from the notification bubble in the title bar. When done, you see this message: "Automapping suggestions require review and approval."
6. To review and accept the maps, see Accept or reject suggested mappings.

For more details on the Automap feature, see FAQ How does Automap work?

• Accept or reject suggested mappings
  

Accept or reject suggested mappings

Once the automap process completes, a notification opens with: "Automapping suggestions require review and approval." You can accept or reject the suggested mappings. (For more details selecting tables to automap, see Use Automap.)

For details on the automap process, see this video:

1. Review all table and column mappings.

   The Type column shows the type of logic used to find the source column: Name Match, Alias Match, Datatype Match, or Partial Name/Alias Match.

2. Clear any maps you do not want.

3. To accept the maps, click Map > Review and Accept maps from the Transformation tab title bar.

Map tables manually

First, Map at the clinical data model level.

To map at the table level:

1. Select one or more source tables.
2. Select one target table.
3. Click the Map icon in the target table's row.
4. If you are using a custom program, select the program.
5. Select a Transformation Type: Direct, Join, Union, Pivot, Unpivot, or Custom.

   Tip:

   Even if you are using a custom program, select the type of transformation your program performs. Use Custom only if your program cannot support data lineage tracing.

6. If you specify a Join, Pivot, or Unpivot, the system displays an icon. Click the icon and supply details.

   Tip:

   Keep the primary key intact in the target table. Include SubjID in the primary key.

7. Authorize: When one or more source tables contains blinded data, by default the target table is completely blinded and only users with Blind Break privileges can view any data in the table.

   • To allow users without Blind Break privileges to see some of the table's data but keep other data hidden, modify the target table to mask values in the appropriate columns, rows, or cells. Do this in the Clinical Data Model page; see Set up data blinding in tables for details.

   • If you are certain that the target table will contain only nonblinded data, select the checkbox in the Authorize column to allow users without Blind Break privileges to view all data in the target table. This authorization is audited.

8. Save. You must save before mapping columns.

Next: Map at the column level.

Mark target tables and columns as Not Used

See What happens when I mark tables and columns as Not Used? for more information.

• Explicitly mark as Not Used
  
• Cascade Not Used from source to target
  
• Mark mapping as Complete
  

Explicitly mark as Not Used

1. After you select a study, click the Study Configuration icon from the navigation bar to open the Clinical Data Model tab.

2. Click the Transformation tab to view the source and target tables.

3. Select a single table or column.

4. Select Mark as Not Used from the Mark drop-down in the table or column pane.

Cascade Not Used from source to target

If the source model contains columns and tables not used, you can extend the Not Used status in the source through to the target tables so they do not appear in the transformation.

1. After you select a study, click the Study Configuration icon from the navigation bar to open the Clinical Data Model tab.

2. Click the Transformation tab to view the source and target tables.
3. Check if the source has tables or columns not used.
4. Select Cascade Not Used from the Actions menu in the title bar of the Transformation tab.

Mark mapping as Complete

To mark all remaining unmapped columns in a single table as Not Used:

1. Go to the Column Mapping page for a single table.

2. Select Mark as Complete from the Mark drop-down.

To mark all remaining unmapped tables as Not Used:

1. Go to the main transformation page.

2. Select Mark as Complete from the Actions drop-down in the title bar of the tab.

Create a self-join

1. Select the table in the Source Tables pane and the Target Tables pane and click the Map icon.
2. Enter an alias for each occurrence and save.
3. Select a transformation type of Join for the target table.
4. Click the Join icon and define the join. See Join.
5. Click the Map Column icon for the target table. See Map at the column level.

Authorize target table data for nonprivileged users

If you are certain that one or all target tables do not contain any unmasked blinded data, so users without special blinding privileges should be able to view all data in the table, authorize the table(s).

Tip:

Before authorizing, modify all target tables that do contain data that should be hidden by masking values in the appropriate columns, rows, or cells. Do this in the Clinical Data Model page. See Set up data blinding in tables.

To authorize a single table:

1. Select the table.

2. Select its Authorize checkbox.

3. Save the transformation.

To authorize all target tables:

1. Select Authorize Target Tables from the Actions drop-down.

2. Save the transformation.

   Tip:

   You cannot undo authorization for all tables at once.

To undo authorization:

1. Select the table.
2. Deselect its Authorize checkbox.
3. Save the transformation.

For more information, see Data blinding and authorization.

Add expressions on mapped sources

To generate a WHERE clause to filter by column value on the source table(s), restricting the set of source data that participates in the transformation:

1. Select the target table.
2. If needed, add an alias for the source table.

   Note:

   The Logical Name field shows alternate names for the table defined in the data model. These values are used in automapping.

3. In the Mapped Sources tab, click the icon in the Source Filters column and write an expression, either as free text or using the Expression Builder. See Use the Expression Builder for details.

   Important:

   • If you reference a static package or function in free text, you must select it in the Selected Packages tab.

   • In free text, use just the column name, not the table.column format, unless you need to use an alias, as in a self-join. In that case the alias.column format is required.

4. If you reference a static package in free text, you must select it in the Selected Packages tab.

View source code

To view generated PL/SQL source code for a table mapping:

1. Select the table.
2. From the Actions drop-down, select View Source.

Validate mappings

To validate selected mappings:

1. Select one or more target tables.

2. From the Map drop-down in the Target Tables pane, select Validate Mappings.

To validate all mappings in the transformation:

1. From the Map drop-down at the top of the page, select Validate Mappings.

To see error messages, hover over the table or column's mapping status icon. See What does this transformation validation error message mean? for more information.

Unmap tables

1. Select one or more target tables.
2. From the Target Tables Map drop-down, select Unmap.

Map at the column level

For details on mapping at the column level, see this video:

Note:

Make sure you mapped at the table level before you map at the column level, Map at the table level.

1. In the Target Tables pane, select the target table, then click the Map Column icon.
2. (Optional) Select one or more target columns, then select Automap Selected Columns from the Map drop-down. The system suggests mappings to matching source columns, if any.
3. Select one or more source columns to map to a single target column, select the target column, and click the Map icon in the target column's row.
4. To write an expression operating on the target column data as part of the Select clause, do one of the following:

   • Enter free text either in the Expressions field, using normal SQL syntax (without curly brackets).

     Important:

     • If you reference a static package or function in free text, you must select it in the Selected Packages tab.

     • In free text, use just the column name, not the table.column format, unless you need to use an alias, as in a self-join. In that case the alias.column format is required.

     • Create table aliases in the Mapped Sources tab to use in free text expressions.

   • Click the Add or Modify Expression icon to Use the Expression Builder that generates code using curly brackets.

     Important:

     Only one syntax style can be supported in a single table mapping.

5. If you create a Many-to-1 column mapping, you must select one of the source columns as the preferred path in the Mapped Sources tab.
6. Continue until all columns are either mapped or marked as Not Used.
7. Save.
8. From the Install drop-down, select either Install or Full Install. See Install a transformation.

To run the transformation, go to the Home page.

Cascade blinding and masking

You can apply the blinding settings of source tables and columns to the target tables and columns they are mapped to using the Cascade Blinding feature for tables and the Cascade Masking feature for columns.

Use the View drop-down to display columns in the Source Tables or Columns pane with blinding-related information:

You must complete all the mappings first.

• Cascade blinding to downstream tables
  
• Cascade masking to downstream columns
  

Cascade blinding to downstream tables

1. In the Table Mapping pane, click Cascade Blinding from the Actions drop-down list.

   The Cascade Blinded Tables window displays all blinded source tables, the target table they are mapped to, and the type of blinding.

   • If a source table has column-level blinding, you can select or deselect individual columns for cascade blinding.

   • If a source table has row-level blinding, you must enter blinding criteria for the target table in the Clinical Data Model page. If you don't, the whole target table is blinded.

2. Accept the cascade operation. Either:

   • Select a target table and click Accept.

   • Check Select All at the top of the window and click Accept.

Cascade masking to downstream columns

Masking substitutes a dummy value for real, sensitive, data.

1. In the Column Mapping pane, click Cascade Masking.

   The Cascade Blinded Columns window displays all blinded source columns, the target column they are mapped to, and the type of blinding.

   If a source column has cell-level masking, the target column will have column-level masking until you specify masking criteria in the Clinical Data Models page.

2. Accept the cascade operation. Either:

   • Select a target table and click Accept.

   • Check Select All at the top of the window and click Accept.

Table transformation types

The system generates program code based on your specifications for the following transformation types (except Custom):

Note:

For more information on joins, unions, pivots, and unpivots, see Oracle® Database SQL Language Reference 11g Release 2 (11.2).

• Direct
  
• Join
  
• Union
  
• Pivot
  
• Unpivot
  
• Custom
  

Direct

One or more source tables feed data to a single target table. Column maps in a direct table relation may have a 1-to-1 or Many-to-1 relation.

1. Select the source and target tables, then select a Transformation Type of Direct.
2. Click the Map Column icon.
3. For each target column either:

   • Select the source column(s) and click the Map icon.

   • Click the Mark as Not Used icon.

4. Save.

Join

For details on creating a join, see this video:

Two or more source tables feed data to a single target table in a join relationship with a join condition. Column maps in a join relation may have a 1-to-1 or Many-to-1 relation.

1. Select the source tables and target table and click the Map icon, then select a Transformation Type of Join.
2. Click the Join icon.
3. Click the Add icon.
4. Select Table 1 (left) and Table 2 (right, also known as the foreign key table) to be joined.
5. Specify if it is to be an outer join on either the left or right side. Leave the checkbox unselected to create an inner join.
6. To join more than two tables, click the Add icon again and define the next join. Also follow the next steps for each pair of tables.
7. Click the Add icon in the Join Details pane.
8. Create the join condition for the Where clause by selecting a column from each table and the operator required.

   To specify additional Join conditions on other columns, click the Add icon in the Join Details pane again as many times as required.

9. Click OK.
10. Click the Map Column icon.
11. For each unmapped target column either:

    • Select the source column(s) and click the Map icon.

    • Click the Mark as Not Used icon.

12. Save.

• Join Types
  

Join Types

An inner join (sometimes called a simple join) returns only those rows that satisfy the join condition.

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition:

• A left outer join returns returns all rows from Table 1 and only rows meeting the join condition from Table 2. Rows in Table 1 that do not have a corresponding row in Table 2 have null values in the columns from Table 2.

• A right outer join returns returns all rows from Table 2 and only rows meeting the join condition from Table 1. Rows in Table 2 that do not have a corresponding row in Table 1 have null values in the columns from Table 1.

• A full outer join returns all rows from both or all tables. Rows in either table that do not have a corresponding row in the other table have null values in the columns from the other table.

Union

For details on creating a union, see this video:

All the data in two or more tables is fed into a single target table that is a superset of all columns in the sources. The source tables should have some overlapping content, such as two versions of a Vital Signs form or two lab vendors providing results for the same subject visit.

1. Select the source tables and target table and click the Map icon
2. Select Union from the Type drop-down.
3. Click the Map Column icon.
4. Select each target column in turn and do one of the following:

   • Select the source column(s) and click Map.

     Note:

     If only one source column is mapped to a target column, the system adds a null in the code for the other table(s). For example:

     insert into tgt_table1 
     (col1, col2, col3)
     select col1, col2, col3 
     from table1
     union all
     select col1, col2, null
     from table2; 

   • Click the Mark as Not Used icon.

5. Save.

Pivot

For details on creating a pivot, see this video:

A pivot converts a source table with a tall, skinny structure of few columns and many rows, such as lab data or ODM, into a target table that represents the same data in a more horizontal (short, fat) structure with more columns and fewer rows, based on the value in a pivot column, which must be associated with a codelist.

Tip:

For pivots to work correctly the source table must have a primary key that includes the pivot column plus the minimum number of columns required to ensure that each record is unique.

If there are more columns in the key than that, the resulting table may have too many rows with target column values sparsely populated among them rather than having fewer rows with a value in each column.

1. Select the source and target tables and click the Map icon, then select a Transformation Type of Pivot.
2. Click the Pivot icon.
3. Query for and select the pivot column. It must be associated with a codelist.
4. Click OK.
5. Click the Map Column icon.
6. In the View drop-down, select Columns and then select Filter Value.
7. Scroll over to the Filter Value column. For each pivoted column in the target table, select the pivot column value to identify the row in the source table from which to get the value for the target column.
8. Save.

Pivots and InForm Repeating Itemsets: In InForm, the primary key includes an internal index column called itemsetindex. In DMW, this internal column must be removed and replaced with the pivot column, which is associated with a codelist. To do this, create an intermediate direct transformation to remove itemsetindex and any other unneeded internal columns and add the pivot column to the primary key. Use the resulting table as the source table for the pivot transformation.

Pivot Example: Lab results are shipped one per row, but the review data model requires one row containing all lab results for each patient at the same visit.

Table 4-1 Source table in pivot example

SubjID	Date	Visit	Test	Unit	Value
972	03262112	5	IG	mh/dl	853
972	03262112	5	Lith	null	neg
972	03262112	5	PTH	pg/mL	285
989	03312112	3	IG	mh/dl	824
989	03312112	3	Lith	null	pos
989	03312112	3	PTH	pg/mL	290

The Test column, which contains the lab test name in the source table, is the pivot column. It is associated with a codelist whose values are IG, Lith, and PTH. The source columns Unit and Value are also pivoted. The columns SubjID, Date, and Visit are not pivoted.

Table 4-2 Target Table in Pivot Example

SubjID	Date	Visit	IG	IG_Unit	Lith	Lith_Unit	PTH	PTH_Unit
972	03262112	5	853	mh/dl	neg	null	285	pg/mL
989	03312112	3	824	mh/dl	pos	null	290	pg/mL

Unpivot

For details on creating an unpivot, see this video:

An unpivot converts a source table with a short, fat structure of many columns and few rows into a target table that represents the same data in a tall, skinny structure with more rows and fewer columns, based on the value in an unpivot column, which must be associated with a codelist. Unpivot transformations are used for tables where multiple columns collect the same data, such as the same assessment repeated in each section of a CRF.

To create an Unpivot transformation:

1. Select the source and target tables and click the Map icon, then select a Transformation Type of Unpivot.
2. Click the Unpivot icon.
3. Query for and select the unpivot column from the target table columns. It must be associated with a codelist.
4. Click OK.
5. Click the Map Column icon.
6. For each pivoted column, select the target column and all the source columns that will feed data to it, and click Map. When appropriate, you can copy a mapping with its filter value. See Step 8 for more information.

   You can use the following pseudo-expressions without quotes:

   • To populate a target column with codelist values, add the following pseudo-expression to the column mapping expression:

     $UNPIVOT$CODEVALUE
     

     Note:

     A column that uses the pseudo-expression $UNPIVOT$CODEVALUE cannot be used as a pivot column.

   • By default, rows with null values for all target columns are suppressed. To include them in the target table, add the following pseudo-expression to the unpivot column mapping expression:

     $UNPIVOT$INCLUDENULLS
     

7. In the Mapped Sources tab, Filter Value field, for each pivoted column in the source table, select the pivot column value to identify the row in the target table into which to put the source column value.
8. If you have multiple target columns that map to the same source columns and have the same filter value, you can map one of them, then select it and click the Copy Map To icon. Select the target column and click OK.
9. Save.

Unpivot example: Multiple observations are collected in an InForm CRF using sections rather than itemsets. A flat form is created with three sections, one section for each time point in that visit for a blood draw. In InForm this is one CRF instance and one record but the standard review data model in use requires that these be three separate records.

Metadata values, the section name in this case, should be inserted as data in the corresponding row for that section.

Certain values in the flat section—the subject ID, visit, and date—should repeat on each row. These are the nonpivoted columns and the source column must be mapped to the target column.

The Section (Sect) column in the source table is the pivot column. It is associated with a codelist containing the values 0hr, 1hr, and 2hr.

Multiple columns in the source table—for example Sect1_Test, Sect2_Test, and Sect3_Test, map to a single column in the target table: Test.

Table 4-3 Source table columns in unpivot example (short, fat table)

SubjID	Date	Visit	Sect	Sect1_Test	Sect1_Unit	Sect1_Value	Sect	Sect2_Test	Sect2_Unit	Sect2_ Value	Sect	Sect3_Test	Sect3_Unit	Sect3_Value
509	01082112	1	0hr	Hb	gl	8	1hr	Hb	gl	8	2hr	Hb	gl	9
598	02092112	1	0hr	Hb	gl	8	1hr	Hb	gl	9	2hr	Hb	gl	10
613	02112112	1	0hr	Hb	gl	9	1hr	Hb	gl	10	2hr	Hb	gl	10

The system generates a row in the target table for each value in the codelist per set of nonpivoted values (SubjID, Date, and Visit) and populates the Section column in the target table with the codelist values as shown in Table 4-4.

Table 4-4 Target table columns in unpivot example (tall, skinny table)

SubjID	Date	Visit	Section	Test	Unit	Value
509	01082112	1	0hr	Hb	gl	8
509	01082112	1	1hr	Hb	gl	8
509	01082112	1	2hr	Hb	gl	9
598	02092112	1	0hr	Hb	gl	8
598	02092112	1	1hr	Hb	gl	9
598	02092112	1	2hr	Hb	gl	9
613	02112112	1	0hr	Hb	gl	9
613	02112112	1	1hr	Hb	gl	10
613	02112112	1	2hr	Hb	gl	10

Custom

Select a Transformation Type of Custom only if you need a custom program AND it performs operations on data in such a way that it is not possible to track all data items in the source model that contributed to each data item in the target model.

Otherwise, even if you use a custom program, select the type of transformation it actually performs: join, union, pivot, or unpivot. The system then generates the code required for data lineage tracing. See How the system tracks data lineage.

Use a custom program

For details on creating a transformation using a custom program, see this video:

If the logic you need for a table mapping is too complex to do in the user interface for generated transformations, use a custom program. For example:

• To perform data aggregation, case statements, or complex calculations.

• To use 1-to-many or many-to-many column mappings.

• To call an API to set a flag on records that meet specified criteria; see the Oracle Health Sciences Life Sciences Warehouse Application Programming Interface Guide for information on APIs for Oracle LSH and DMW, and see "Sample Program that Calls the API to Set a Flag" on page 6‐9.

Tip:

If you use multiple custom programs with the same name for different target tables in the transformation, the programs must all be in the same location. In other words, they must be the same program. These conflicts can arise if the custom program is part of a template and therefore part of the study created from the template, for example.

If you reference two custom programs with the same name in different locations, installation ends with a warning and the conflicting packages are listed in the installation log file. The validation process, which must be run before installation, marks all mappings with conflicting package names as Invalid in the UI, and gives the full path for each. You must resolve the conflicts and reinstall the transformation before execution can succeed.

You must create custom programs in LSH. See Create custom programs.

1. Select the source and target tables and click the Map icon.
2. In the Type column, select the type of transformation that the custom program actually performs, if possible: Direct, Join, Union, Pivot, or Unpivot.

   Select Custom only if the code does not do one of these. Data lineage tracing does not work if you select Custom.

3. Click the Select Custom Program icon. Search for the program you want. Use the Query By Example fields above the columns. If they are not displayed, click the Query By Example icon.
4. Select a program and click OK.

   Tip:

   You can open the Static Packages tab and use the drop-down to display either all packages, only those that are already selected in the transformation, or all except those already selected.

Install a transformation

After creating or modifying a transformation, you must install it to make it usable.

Note:

Before installing a transformation for the first time, check if another user has checked out the target model and if so, ask that user or an administrator to check it in. The installation process checks out target tables to add auxiliary columns if they have not already been added, and cannot do so if the model is checked out by a user different from the user who is installing the transformation. The transformation installation fails.

1. In the Study Configuration page, navigate to the transformation, select it, and select one of the following from the Install drop-down:

   • Install upgrades all tables and programs without deleting any data.
   • Full Install drops and replaces all tables and programs, deleting all data. Full installation is not available in the Production lifecycle.

     Tip:

     The options are active only if the transformation is installable:

     • The Version and the Installable Version must be the same.
     • See Transformation installation requirements.

2. To see the updated job status in the Install Status field, click the Refresh icon.

3. To see the log file:

   1. Go to the Home page, Transformations tab.

   2. From the Model drop-down list, select the source clinical data model.

   3. In the Target Model pane, select the model.

   4. Click the icon in the Install Job Log column in the same row.

See What happens during installation? for more details.

Upgrade transformations to synchronize with models

If there have been metadata changes in a clinical data model—for example, an increase of column length—that affect a transformation or validation check, the system sets the transformation or validation check to Upgrade Required. You must run the upgrade job to synchronize the transformation or validation check with the model.

• If the main transformation is checked in, check it out. This automatically upgrades the main transformation and in some cases side transformations too.

• If either the main transformation or a side transformation is checked out and its Upgrade Required flag is set to Yes, run the Upgrade job. The Upgrade job for the main transformation may automatically upgrade the side transformation or it may set the side transformation's Upgrade Required flag to Yes, in which case you must run the Upgrade job for the side transformation.

• If columns or tables have been removed, mappings may be broken. You must fix these manually.

• After upgrade, install the transformation. You can make other changes before installing.

Run transformations and view history

• Run a transformation

• View run history

• Run a transformation
  
• View run history
  

Run a transformation

To run or schedule a transformation:

1. Go to the Home page, Transformations tab.
2. Select a transformation and click the Submit Job icon.

   Tip:

   The Submit Job icon is not active if the selected transformation has not been installed. Check the Install Status column.

   If the installation status of the transformation is Warning, you may still be able to run the batch. Check the installation log file.

3. Enter values:

   • Submission Mode: Select one:

     • Full mode includes data deletion. Use Full mode only if you are confident that you are reloading all current data.

     • Incremental is faster and does not include data deletion.

     If you are submitting a transformation for a single table and the table is defined with Unit of Work processing, select:

     • Full UOW includes data deletion. Use Full UOW only if you are confident that you are reloading all current data for each subject or subject visit that has any data included.

     • Incremental UOW is faster and does not include data deletion.

       Tip:

       Set up regular Incremental loads at frequent intervals and do Full loads at longer intervals.

   • Force Execution: Select if you want to run the job even though the source data currency, parameter values, and the version number of the program(s) have not changed since the last run. The system uses Full mode regardless of the Submission Mode setting. Full mode includes data deletion.

     If not selected and all the conditions are the same as the last run, the system does not execute the job and returns a status of Success.

   • Submission Type:

     • Immediate Run the job once, as soon as possible.

     • Scheduled Set up a regular schedule.

     • Deferred Run the job once, at a future time.

   • Trigger Downstream Transformations and Validation Checks: Select to make this job trigger validation checks on the target model and transformations from the target model to all others that come after it, in sequence. This can happen only if the source models are set up to trigger downstream processes.

View run history

1. Go to the Home page, Transformations tab.
2. To view all jobs, click the View Full Job History icon.

   To view only recent jobs again, click the View Recent Jobs Only icon.

Transformations are displayed by the name of their target clinical data model.

• To view table transformations, click a transformation's node.

• To view run history and pending jobs, select a transformation in the upper pane.

• To view log files, click the icon in the column for the type of job:

  • Log: The most recent manually submitted job.

  • Triggered Job Log: The most recent triggered job.

  • Install Job Log: The most recent installation of the transformation.

FAQs

• Why can't I check out my transformation?

• Why can't I merge my side transformation?

• Why does it say Upgrade Required when I know the data model hasn't changed?

• I noticed the tables in the target model changed. Why?

• What happens when I mark tables and columns as Not Used?

• Can I display mappings in a spreadsheet?

• How does Automap work?

• Where are custom programs stored?

• Can I use a side model like any other model?

• Why doesn't a data load automatically trigger this transformation?

• Why can't I trigger other transformations when I run this one?

• What does this transformation validation error message mean?

• Why can't I check out my transformation?
  
• Why can't I merge my side transformation?
  
• Why does it say Upgrade Required when I know the data model hasn't changed?
  
• I noticed the tables in the target model changed. Why?
  
• What happens when I mark tables and columns as Not Used?
  
• Can I display mappings in a spreadsheet?
  
• How does Automap work?
  
• Where are custom programs stored?
  
• Can I use a side model like any other model?
  
• Why doesn't a data load automatically trigger this transformation?
  
• Why can't I trigger other transformations when I run this one?
  
• What does this transformation validation error message mean?
  

Why can't I check out my transformation?

It may be because its target clinical data model is checked out by a different user.

Why can't I merge my side transformation?

It may be because you haven't checked it in. More likely it's because someone else checked out the model-level transformation and only that person can merge side models/transformations.

Why does it say Upgrade Required when I know the data model hasn't changed?

When a transformation is checked out, its target clinical data model is also checked out. But if the transformation checkout is undone, the model checkout is not automatically undone. To undo the model checkout, go to the model and undo the checkout there. Otherwise the transformation is set to Upgrade Required, and the new model version is no different from the old.

When the transformation is checked in, the target model is not automatically checked in, but the transformation is set to Upgrade Required.

Checking out the model with the transformation enables the Cascade Blinding and Cascade Masking features because blinding and masking are properties of the model and the cascade operation is done from the transformation.

I noticed the tables in the target model changed. Why?

When copying transformations from another study or model, the synchronization job modifies the tables in the current model so that they match the table metadata being copied, including adding, modifying, and removing columns.

What happens when I mark tables and columns as Not Used?

• Any mappings to the table or column are deleted.

• The transformation's status can be Complete without mapping the table or column. Completeness is required for validation and installation.

  Note:

  Columns populated by TMS are never included in calculating completeness even if they are marked Used.

• Validation checks and custom listings that are dependent on a table or column marked Not Used are disabled. If you later mark the table or column as Used, you must manually reenable the validation checks and custom listings.

• The table or column is not visible in the Listings pages.

Can I display mappings in a spreadsheet?

Yes.

• For table mappings, select Export All to Excel from the Actions drop-down in the Target Tables pane.

• For column mappings, click the Export All to Excel icon in the Target Columns pane.

How does Automap work?

Automap uses the following matches to suggest mappings:

• Name

• Alias

• Data type

• Partial name

• Partial alias

If you select a target table that is already mapped, the existing mapping remains.

Where are custom programs stored?

All custom programs for both validation checks and transformations are stored in the DMW_UTILS domain/namespace in the database. There may be subdomains to organize the programs by therapeutic area or other logical grouping.

Can I use a side model like any other model?

Side models are intended for temporary use only and have limitations:

• Side models cannot be used as source models for other transformations.

• If a validation check raises discrepancies on data in a side model, lineage tracing is not enabled and the discrepancies do not appear in the source model.

• Side models are not visible in the Study Configuration page.

• Side models and transformations cannot have a higher validation status than Development.

Why doesn't a data load automatically trigger this transformation?

Set up the source model(s) to trigger:

1. In the main Transformation page, click the Add or Remove Source Model icon.
2. Select Can Trigger for each, and Save.

Why can't I trigger other transformations when I run this one?

Set up triggering in the target model:

1. Navigate to the target model.
2. In the main Transformation page, click the Add or Remove Source Model icon.
3. Select Can Trigger for each, and Save.

What does this transformation validation error message mean?

The validation error messages you may see are, in alphabetical order:

• DME_XFMVAL_AUTH_YES_ERR: One or more source tables is blinded and the target table is not blinded. You must either set the target table's Authorize flag to Yes if you know that it will not contain any of the sensitive, blinded data from the source tables, or go back to the clinical data model and set the target table's blinding attributes appropriately. Special privileges are required for either action.

• DME_XFMVAL_COL_DATATYPE_ERR: The data type of the source and target columns must be the same.

• DME_XFMVAL_COL_LEN_TRUNC_ERR: To prevent data loss, the target column must have a length equal to or greater than the source columns.

• DME_XFMVAL_COL_NOSRC_ERR: Mapping required. Add one or more source columns or specify a constant value or mark as Not Used.

• DME_XFMVAL_COL_NOTRGT_ERR: Each column mapping must have one target column.

• DME_XFMVAL_COL_SRC_TRGT_SAME: A column cannot be mapped to itself.

• DME_XFMVAL_ERROR_NOT_FOUND: All mappings are valid.

• DME_XFMVAL_MOD_CYLIC_TAB_ERR: This model has circular table mappings.

• DME_XFMVAL_MOD_SRC_NOTFOUND: A model mapping must have at least one source model.

• DME_XFMVAL_MOD_SRC_TRGT_SAME : A model cannot be mapped to itself.

• DME_XFMVAL_MOD_TAB_INVALID: A model mapping must have valid table mappings.

• DME_XFMVAL_MOD_TAB_NOTFOUND: A model mapping must have at least one table mapping.

• DME_XFMVAL_MOD_TRGT_NOT_ONE: A model mapping must have one target model.

• DME_XFMVAL_NOTNULLCOL_HARDCOD: Not null columns in the target table must be mapped to either a source column or a constant value.

• DME_XFMVAL_NOTNULLCOL_NOMAPERR: Mapping required. Not null columns in the target table cannot be left unmapped.

• DME_XFMVAL_PRMCOLS_HARDCOD: All primary key columns in the target table cannot be mapped to a constant value.

• DME_XFMVAL_PRMCOLS_UNMAPPED: Mapping Required. All primary key columns in the target table are unmapped.

• DME_XFMVAL_PRMCOL_HARDCOD_ERR: Primary key column in the target table cannot be mapped both to source column or to a constant value simultaneously.

• DME_XFMVAL_PRMCOL_MAP_ERR: Primary key column in the target table must be either mapped to source column or to a constant value.

• DME_XFMVAL_PRMCOL_NOMAPERR: Mapping Required. Primary key column in the target table cannot be left unmapped.

• DME_XFMVAL_TAB_DIR_MULTSRC_ER: Table mappings of type Direct can have only one source table.

• DME_XFMVAL_TAB_JOIN_ONESRC_ERR: Table mappings of type Join must have at least two source tables.

• DME_XFMVAL_TAB_NOCOLMAP_ERR: Table mappings must have at least one column mapping.

• DME_XFMVAL_TAB_NOSRC_ERR: Each table mapping must have at least one source table.

• DME_XFMVAL_TAB_NOTRGT_ERR: Each table mapping must have one target table.

• DME_XFMVAL_TAB_SRC_TRGT_SAME: A table cannot be mapped to itself.

• DME_XFMVAL_TAB_UNION_ONESRC_ER: Table mappings of type Union must have at least two source tables.

• DME_XFMVAL_XFORM_TYPE_ERR : Incorrect Table Map Type. It can be Direct, Join, Union, Pivot or Unpivot.