1. Study Setup Guide
  2. Create custom programs, listings, and filters, and set up reference data
  3. Create custom programs
  4. Create a custom program for a transformation

Create a custom program for a transformation

You can create the custom program in Oracle LSH before or after creating the transformation.

  1. In the Study Configuration page, navigate to your target data model and check it out.

  2. Select the target table and click the Icon is a plus sign.Add icon in the Columns pane. Add one column for each source table that will feed data into it in the transformation. These columns must be of data type varchar2 and length 4000. There is no required naming convention for these columns, but see Naming restrictions.

    Note:

    This is to enable data lineage tracing. See Enable data lineage tracing in a custom program and Sample programs that populate auxiliary columns with the source surrogate key.

    If your program is performing an operation like aggregation that makes it impossible to support data lineage tracing, you do not need to create or populate these columns.

  3. Save and check in the data model. If you start to map to the target table in the user interface before creating the auxiliary columns, you will not see the auxiliary columns in the user interface. To display the columns in the UI, go to the model and add the columns, then go back to the transformation, check it in, and check it out again.

  4. Log in to Oracle LSH, select its Applications tab if it is not already selected, and navigate to the DMW_UTILS domain under DMW_DOMAIN.

    For details, see this video:

  5. In the DMW_UTILS domain, navigate to the application area your administrator has set up for storing custom programs of this type, and click Manage Definitions. Verify that you are in the right application area.

  6. From the Create drop-down, select Program and click Go. Enter a name and description and select the Program Type: PLSQL or SAS. Click Apply.

  7. For each source and target table: In the Table Descriptors subtab, click Add Target From: Library, then select Create a Table Descriptor from an existing Table definition and click the Icon is a magifying glass.Search icon for the Definition Source field.

  8. In the Search and Select window:

    1. In the Domain field, click the Icon is a magifying glass.Search icon and select DMW_DOMAIN, the appropriate study grouping, and your study.

    2. Select Display Table Definitions Under DataModel. Select the clinical data model and enter the table name if you know it, then click Go.

    3. Select the table. The system returns you to the Create Table Descriptor page with the selected table displayed in the Definition Source field. Click Apply, then click Return.

  9. For each source table: Click the table name, then click Update, change Is Target to No, click Apply, then click Return.

    Note:

    • Each source table's Oracle name cannot be longer than 25 characters. This is because the target table must contain a column for surrogate key information that contains the source table name.
    • You can change the table name in the source clinical data model.
    • Table descriptors allow you to reuse this program in a different study where the source or target tables may have different names and column names but the same structure, by mapping the tables in the Oracle DMW transformation.

  10. Write the program. If you have integrated SAS with Oracle LSH you can click the Launch IDE button from the program page. You can also upload a program into LSH.

    Your program must handle populating all target columns, including populating the new auxiliary columns with the value of the internal CDR$SKEY (surrogate key) column in each source table.

  11. In the Source Code subtab, click Add, then select Create a new Source Code definition and instance. Enter all required values. For a SAS program, the File Type should be Program, not Macro.

    Note:

    Source Code names:

    • must not include Oracle or PL/SQL reserved words or special characters; see Avoid special characters and reserved words.

    • must include a file extension—for example, .sas for SAS or .sql for PL/SQL.

    • The Oracle name must not be the same as the Oracle name of either a table descriptor or another source code in the same PL/SQL program.

    • If your code uses parameters, formally define them as Oracle LSH parameter objects in the Parameters subtab. However, using parameters makes sense only for functions called from an expression where you can provide input values. Oracle DMW transformation submission does not allow setting parameter values.
    • If required, you can call Oracle LSH or Oracle DMW public APIs from your code; see Use APIs.

  12. Upload the file containing your program and click Apply.

  13. Check in the program in Oracle LSH.

  14. In Oracle DMW, navigate to the transformation mapping for the target table.

    1. Select the source tables and target table.

    2. Click the icon in the Program column and select the Oracle LSH program.

    3. Specify the actual Transformation Type. In most cases do not select a Transformation Type of Custom. Select the actual transformation type that your code performs. The system uses the Transformation Type to ensure that data lineage tracing works correctly. Select a Transformation Type of Custom only if you are performing operations like aggregation that make it impossible to trace data lineage.

    4. Map the source and target columns, including mapping the CDR$SKEY column in each source table to the corresponding surrogate key column you created in the target table. (The CDR$SKEY columns are normally not visible in the Column Mapping pane, but this changes when the table Transformation Type is Custom.)

  15. Install the transformation.

Parent topic: Create custom programs