Topic outline

• Creating a Data Transformation

  Creating a Data Transformation

  Content Type
  Exercise

  Duration
  20m

  Level
  Introductory

  Learn how to create and test a data transformation in InterSystems IRIS® data platform using the data transformation language (DTL) editor. The DTL editor's graphical user interface allows nontechnical users to create data transformations without writing code. Transformations can be used to change the format and content of incoming data from one system to meet the requirements of a downstream system. With InterSystems IRIS, data transformations are simple to create, test, and maintain.

  Common transformations include:

  • Copying values from the source message to the target message.
  • Performing calculations based on values of the source message and copying the results to the target message.
  • Assigning new literal values to the target message.
  • Rearranging the order of data.

  In this exercise, you will build a data transformation that converts a student's final grade in a class into a different format to be used by another application.

  
  

  Learning Objectives

  • Use the DTL editor to create and modify a data transformation.
  • Apply transformation logic to copy data, modify property values, and use functions.
  • Test and validate the data transformation within a production to ensure correct formatting and processing.

  Prerequisites

  • None
• Start the Exercise

  Start the Exercise

  Data about a student’s final grade in a class must be converted so that a different application can use the data. You will use the DTL editor in InterSystems IRIS to create a data transformation, and add actions to the transformation which change the source data into the necessary target format. 

  • Launch the virtual machine (VM) below to begin working on the exercise. For responses to common questions on using VM-based Learning Labs, visit FAQ: Using Learning Services Virtual Machines.

    Your login information for the VM is the following:

    • Username: student
    • Password: LearningServices7
  • You must be logged in to launch an InterSystems IRIS instance.

    • Log in with your existing account.
    • Don't have an InterSystems account? Create a new account.
  • Create a new data transformation

    InterSystems IRIS provides a Data Transformation Wizard to speed up and simplify the process of creating a new data transformation. For this exercise, you are creating a data transformation that connects two record maps related to a student’s grade in a class. To create the data transformation:

    1. Open Google Chrome on your virtual machine and navigate to the IRIS bookmark.
    2. Log in to the Management Portal using username tech and password demo7.
    3. From the Management Portal home page, select Interoperability > List > Data Transformations.
    4. Ensure you are in the INTEROP namespace, then click New. The Data Transformation Wizard opens.
    5. Select Demo from the Package drop-down list.
    6. In the Name field, enter a unique name such as DTLDemoTransform.
    7. Click the magnifying glass next to the Source Class field.
    8. Click Message Classes > Demo > Complex Map > StudentWCD > Record > Go.
    9. Click the magnifying glass next to the Target Class field.
    10. Click Message Classes > Demo > Complex Map > StudentPFFixed > Record > Go.
    11. Click OK. The new data transformation opens in the DTL editor.

    

    Note:

    In this exercise, All Messages was left selected for the Source Type and Target Type. The DTL editor also supports X12 and EDIFACT standards. Selecting one of these options as the message type when creating a new data transformation allows you to select a document type that populates the DTL editor with the right schema.
  • Now that you have created the data transformation, you will use the DTL editor to:

    • Copy data from the source to the target, rearranging the order of the data as needed.
    • Add a college-specific code to the beginning of the course number.
    • Round off the final grade to a whole number.
    • Use the final grade to determine whether the student passed or failed, and indicate this result in the target file.
    • Ignore data from the source that is not required in the target.
  • Copy data

    One of the most powerful and time-saving features of the DTL editor is the ability to drag and drop values from the source to a corresponding property of the target—simply click and hold the circle on the source’s property and drag it to a property of the target message.

    Try copying values from the source to the target:

    1. Click and hold the circle of the ClassID property of the source (on the left-hand side of the editor).
       
    2. Drag your cursor to the ClassID property of the target until it is highlighted, then release the mouse button.
       
       The value of the source’s ClassID will be copied to the target’s ClassID property.
    3. Following this procedure, connect the following source properties to the corresponding target properties:
       • StudentID
       • Grade
       • LastName
       • FirstName
       • MiddleName
         After connecting all of these properties, you will see a green arrow pointing from the source to the target for each property, with a circle in the center. You can click a circle to select one of these transformations.
         

    Notice that you have changed the order in which the data will be stored simply by connecting the property pairs.

  • Modify the value of a property

    In some cases, you want to change the value of the source’s property before copying it to the corresponding target property. In this exercise, the target system accepts a ClassID that starts with a two-character identifier that indicates the source’s college. The source’s message does not include this identifier, so the data transformation must modify the value of ClassID before copying it to the target message.

    1. Click the circle on the line that connects the source’s ClassID property and the target’s ClassID property.
       
       
       Notice that the Action tab on the right-hand side of the editor now shows information about the Assign action that was created when you connected the two ClassID properties. The Property field is set to target.ClassID, and the Value field is set to source.ClassID.
       
    
    
    2. Change the Value field of the Action tab to: "UC."_source.ClassID.
       The college identifier, UC, is added to the beginning of the ClassID using the underscore, which is the concatenate operator in the InterSystems ObjectScript programming language. This gives you an idea of how you can include ObjectScript code in your transformation.
  • Use functions

    You can use built-in functions when setting the value of a property. In this exercise, you are rounding the final grade to a whole number using the Round( ) function.

    1. Select the connector between the source’s Grade property and the target’s Grade property.
    2. Click the magnifying glass next to the Value field of the Action tab.
    3. Select Round ( ) from the Function drop-down list.
    4. Click OK.

    The Value field of the Action tab is now set to ..Round(source.Grade).

  • Define a conditional set of actions

    Notice that as you mapped source properties to target properties, the DTL editor added Set actions to the table below the diagram.
    

    While the graphical portion of the DTL editor makes it easy to visualize and perform actions quickly, sometimes it is better to use the table below the diagram to further define the transformation. Rather than select the connector, you can select actions from the table below the diagram.

    In transforming data, you can use conditional statements to perform actions only if certain conditions are true. In this exercise, you define a transformation to indicate that the student passed the class if the final grade is greater than or equal to 65. To add an If statement to accomplish this:

    1. Make sure the last item is the table is highlighted. The If statement is placed right after the highlighted action.
    2. Select If from the Add Action drop-down list.
       
       Notice that an If block is added to the table below the diagram.
    3. With the If statement selected in the table, define the Condition field on the Action tab as target.Grade>=65. The actions that you put inside the If block are executed only if the value of the target’s Grade property is greater than or equal to 65.
    4. Select Set from the Add Action drop-down list.
    5. On the Action tab, define the Property field as target.Pass.
    6. Define the Value field as 1. This is the boolean value indicating that the student passed the class.
    7. Select the else action in the table.
    8. Select Set from the Add Action drop-down list.
    9. On the Action tab, define the Property field as target.Pass.
    10. On the Action tab, define the Value field as 0. This is the boolean value indicating that the student did not pass the class.

    The if block should now include a set action under the if line and a second set action under the else line.

    

  • Notice that the Email, Phone, and Phone1 properties were not connected to the target message. No further action is needed to prevent this data in the source message from being copied to the target message.

  • Perform preliminary testing

    The DTL editor gives you the ability to quickly test the data transformation without having to run a message through the entire production. You simply input the test message, and the DTL editor displays what the output message will look like. At this point in this exercise, you are pasting an XML representation of the record map data into the Test Transform tool. Later, you will test the transformation by running a text file through the production.

    1. Click Compile to save and compile your data transformation.
    2. Click the Tools tab on the right-hand side of the editor.
    3. Click Test.
    4. Copy and paste the following data into the Input Message text box. This data is also available on your desktop in the Test-XML file for easier copy/pasting. Begin code:

       <test>
       <Record>
       <ClassID>CS241</ClassID>
       <StudentID>930698</StudentID>
       <Grade>77.8</Grade>
       <LastName>Sutherland</LastName>
       <FirstName>David</FirstName>
       <MiddleName>Timothy</MiddleName>
       <Email>david.sutherland@mail.com</Email>
       <Phone>978-343-3940</Phone>
       <Phone1>978-343-0951</Phone1>
       </Record>
       </test>

       End code.
    5. Click Test.
       The Output Message text box should now look like this: Begin code:

       <Record>
         <StudentID>930698</StudentID>
         <ClassID>UC.CS241</ClassID>
         <Grade>78</Grade>
         <Pass>true</Pass>
         <FirstName>David</FirstName>
         <MiddleName>Timothy</MiddleName>
         <LastName>Sutherland</LastName>
       </Record>

       End code.

    Tip:

    When working with HL7 V2 or other EDI messages, you can copy and paste raw text from a sample message to test the transformation. You do not need an XML representation of the message.
  • Add a transformation to the production

    Now that you have created and compiled a data transformation, you must add it to your production so that source messages flowing through the production get transformed into the message that gets sent to the downstream application. In this exercise, you are adding the transformation to a business rule associated with the business process DTLDemoRouter.

    1. From the Management Portal home page, go to Interoperability > List > Productions.
    2. Select Demo.ComplexMap.SemesterProduction and click Open.
    3. Select DTLDemoRouter under Processes.
    4. On the Settings tab, click the magnifying glass next to the Business Rule Name field.
    5. Double-click the send block located in the business rule.
       
    6. In the Transform dropdown, select the data transformation that you created for this exercise.
    7. Click OK.
    8. In the Business Rule Editor, click Save and then Compile.
  • Test the data transformation

    1. Start the production containing the demo data transformation:
       1. From the Management Portal home page, go to Interoperability > List > Productions.
       2. Select Demo.ComplexMap.SemesterProduction and click Open.
       3. Click Start.
    2. Open the Data folder on the desktop of your virtual machine and copy input.csv to the In folder.
       The production is defined to accept a .CSV file containing the student information from the Data/In folder. As long as the production is running, this file should disappear from the directory within a few seconds, indicating that the production has started to process the file successfully.
       
       Next you will verify data in the output file. The demo production takes the .CSV file you copied into the production’s input directory and uses the data transformation to convert the data into a different format with new content. This outgoing file is copied into the production’s output directory, Data/Out, with a timestamp as the filename.
    
    
    3. Open input.csv and the new output file in the Out folder and compare the two files.
       You should see that the order of the properties has changed, and the grade and class ID were updated. Additionally, notice that the output file does not contain the email or phone numbers from the input file; it also has a new binary property representing whether or not the student passed.
  • In this exercise, you built a simple data transformation and implemented it in an interoperability production.
    

• What's Next?

  What's Next?

  Continue learning about data transformations with the following resources:
  

  • Learn more about developing DTL transformations (documentation).
  • Learn how to use for each statements in a data transformation to iterate through a collection (documentation, 4m). 
  • See how to use subtransformations to modularize your data transformations (documentation, 2m). 
  • Practice creating an advanced DTL data transformation (online course, 45m).
  • Use the BPL and DTL reference while you develop transformations (documentation).
  • Read more about developing business rules (documentation).

  • Share Your Feedback!