{"id":10297,"date":"2016-07-27T14:05:59","date_gmt":"2016-07-27T18:05:59","guid":{"rendered":"http:\/\/www.iri.com\/blog\/?p=10297"},"modified":"2024-07-22T10:06:08","modified_gmt":"2024-07-22T14:06:08","slug":"creating-a-voracity-flow-from-the-palette-part-2-of-2","status":"publish","type":"post","link":"https:\/\/www.iri.com\/blog\/data-protection\/creating-a-voracity-flow-from-the-palette-part-2-of-2\/","title":{"rendered":"Creating a Voracity Flow from the Palette (Part 2 of 2)"},"content":{"rendered":"

This is the second of two articles on creating a new\u00a0<\/span>IRI Voracity\u00ae<\/span><\/a>\u00a0ETL job flow from the job design <\/span>Palette <\/b>in the <\/span>IRI Workbench<\/a><\/span>, built on Eclipse. Next, we will add more components to the existing flow, started in <\/span>part 1<\/span><\/a>, where we:<\/span><\/p>\n

    \n
  1. created a basic flowlet<\/span><\/li>\n
  2. dragged a <\/span>Transform Mapping Block<\/b> into the flowlet<\/span><\/li>\n
  3. defined the <\/span>Input Data<\/b> after dragging the <\/span>ODBC <\/b>icon into the <\/span>Input Data<\/b> block<\/span><\/li>\n
  4. defined the <\/span>Action <\/b>after dragging the <\/span>Sort <\/b>icon into the <\/span>Action <\/b>block<\/span><\/li>\n
  5. defined the <\/span>Output Data<\/b> using the <\/span>Target Field Layout<\/b> editor after dragging the <\/span>File<\/b> icon into the <\/span>Output Data<\/b> block<\/span><\/li>\n
  6. eliminated the columns AGE, EMAIL, DRIVING_LICENSE, and SSN when mapping the table columns to the fields for the output file PatientInfoProtected<\/span><\/li>\n
  7. applied a mask to the fields CREDIT_CARD1 and CREDIT_CARD2 that masked all except the last four digits of the fields<\/span><\/li>\n
  8. created a data definition format (DDF) metadata file for\u00a0PatientInfoProtected<\/span><\/li>\n<\/ol>\n

    We will resume now by adding a second\u00a0<\/span>Transform Mapping Block <\/b>to our flow.<\/span><\/p>\n

    Drag the <\/span>Transform Mapping Block<\/b> icon from the <\/span>Palette <\/b>into the Scratch flow created in the <\/span>previous article<\/span><\/a> and double click in that block. For <\/span>Representation name<\/b>, type Collections Transform Mapping Diagram and click <\/span>OK<\/b> to open the Transform Mapping Diagram in the editor. This diagram has an empty <\/span>Input Data<\/b> block, <\/span>Action <\/b>block, and <\/span>Output Data<\/b> block.<\/span><\/p>\n

    \"Transform<\/a>
    \n<\/b>Define the Input Data (Source)<\/b><\/h3>\n

    The output file PatientInfoProtected from the CoSort <\/span>SortCL<\/span><\/a> transform mapping block \u00a0<\/span>PatientInfoProtect.scl<\/span><\/i> will be one of the inputs for the next transform mapping block<\/span> Collections.scl<\/span><\/i>. Drag the <\/span>Input File<\/b> icon into the <\/span>Input Data<\/b> block.<\/span><\/p>\n

    The <\/span>Get File Format Information<\/b> window opens, and we select the DDF (data definition file) metadata file for the input. From the drop-down menu, select our project name ScratchFlow. From the list in the <\/span>Matching items<\/b> box, select PatientInfoProtected.ddf and click <\/span>OK<\/b>.<\/span><\/p>\n

    In the next window, make sure the path is to our Project. The file PatientInfoProtected is an interim file and will not exist until we execute the Flow. Type PatientInfoProtected for <\/span>File Name<\/b> and click <\/span>Open<\/b>.<\/span><\/p>\n

    In this case, we know that the input file PatientInfoProtected will be in our project directory because the previous step in this flow places it there. As long as that is true, it is not necessary to include the path as part of the file name. <\/span><\/p>\n

    Click in the <\/span>Section Options <\/b>block for PatientInfoProtected. In the <\/span>Properties <\/b>view, select the <\/span>Base <\/b>tab,<\/span> and change <\/span>Name <\/b>to In_PatientInfoProtected. Then click inside the flow diagram again.<\/span><\/p>\n

    There are two inputs required to perform a join. Drag an <\/span>Input ODBC<\/b> icon into the <\/span>Input Data<\/b> block to open the <\/span>Database Information<\/b> dialog. Select the <\/span>Data source name(DSN)<\/b>. <\/span><\/p>\n

    The <\/span>Table selection<\/b> box is then populated with the available tables for that <\/span>DSN<\/b>. Click the check box for the NIGHTLY.PATIENTBILLING table and click <\/span>Finish<\/b>.<\/span><\/p>\n

    The <\/span>Input Data<\/b> block now has the two inputs with fields and <\/span>Section Option<\/b> blocks inside each white data block.<\/span><\/p>\n

    Create a Record Filter<\/b><\/h3>\n

    We need to match each IDNUMBER with a PASTDUE value greater than zero in the PATIENTBILLING table to an IDNUMBER in the PatientInfoProtected file. Drag the <\/span>Filter Include<\/b> icon from <\/span>Section Options<\/b> in the <\/span>Palette <\/b>to the <\/span>Section Options<\/b> block for the PATIENTBILLING table. <\/span><\/p>\n

    The <\/span>Record Filtering<\/b> dialog opens. Under <\/span>Filter Action<\/b> click <\/span>Include<\/b>, and under <\/span>Condition Type<\/b>, click <\/span>New Condition<\/b>. Click the <\/span>f <\/i><\/b>\u2026<\/b> button next to the field for <\/span>New Condition<\/b>.<\/span><\/p>\n

    This opens the <\/span>Expression Builder<\/b> dialog. There is a box at the top with a red X to the right of where our expression or condition will be built. When the expression syntax is valid, the X becomes a green check mark. <\/span><\/p>\n

    There is a <\/span>Categories <\/b>box below and to the left. For each category clicked, options associated with it appear in the <\/span>Items <\/b>box on the right.<\/span><\/p>\n

    Click on the category <\/span>Field Names<\/b>, then double-click the item PASTDUE. PASTDUE is now in\u00a0<\/span>the <\/span>Expression <\/b>box. Select the category <\/span>Relational Operators<\/b> and double-click\u00a0<\/span>GT <\/b>(for greater than). The <\/span>GT <\/b>is now in the <\/span>Expression <\/b>box. <\/span><\/p>\n

    Now, type in a space and a 0 in the <\/span>Expression <\/b>box. This completes our expression and we have a green check. Click <\/span>Finish<\/b> and then <\/span>OK<\/b>.<\/span><\/p>\n

    \"Where<\/a><\/p>\n

    The record filter is now in the <\/span>Section Options<\/b> block for the input table PATIENTBILLING.<\/span><\/p>\n

    Define the Join<\/b><\/h3>\n

    We want to match the rows in the PATIENTBILLING table to records in the PatientInfoProtected file with the same IDNUMBER; then send those matched records to the target table. This is an inner join. <\/span><\/p>\n

    Drag the <\/span>Join <\/b>icon from the <\/span>Transform Action<\/b> in the <\/span>Palette <\/b>into the <\/span>Action <\/b>block. The <\/span>Join Sources <\/b>window opens.<\/span><\/p>\n

    For the join to execute correctly, the inputs must be in order by the field to be matched. We know <\/span>Data source 1<\/b> will be in order because the data was sorted in the previous step of the flow. <\/span>Data source 2<\/b> will be in order by IDNUMBER because the order is maintained in the table. <\/span><\/p>\n

    If a source is not in order, we can select <\/span>NOT_SORTED <\/b>from the <\/span>Sort order<\/b> option <\/b>for that source and CoSort will perform the sort <\/span>with<\/span><\/i> the join. The sources are assumed to be sorted by default.<\/span><\/p>\n

    Make sure <\/span>Inner join <\/b> is selected under <\/span>Join type<\/b>. Click IDNUMBER in both sources; then select <\/span>Create Condition<\/b>. The condition for matching the IDNUMBER is written into the <\/span>Join conditions<\/b> box. Click <\/span>Finish<\/b>.<\/span><\/p>\n

    \"Join<\/a><\/p>\n

    There is now a white <\/span>Join <\/b>block in the <\/span>Action <\/b>block. Contained in that <\/span>Join <\/b>block is a gray field block with all the fields from each input and an orange <\/span>Join Condition<\/b> block with the condition defined in the <\/span>Join Sources<\/b> window. The joined fields acquire the naming convention of <\/span>input_name.field_name<\/span><\/i> so you know the source of each field.<\/span><\/p>\n

    Define the Output (Target)<\/b><\/h3>\n

    The join we just defined will map fields to columns in the target table COLLECTION. Drag an <\/span>Output ODBC<\/b> icon into the <\/span>Output Data<\/b> block to open the <\/span>Database Information <\/b>dialog. Select the <\/span>DSN <\/b>from the drop-down. <\/span><\/p>\n

    Click the check box for the table NIGHTLY.COLLECTION and click <\/span>Finish<\/b>. Fields from the <\/span>Action <\/b>block are mapped to corresponding field names in the COLLECTION table.<\/span><\/p>\n

    Eliminate Double-Mapping<\/b><\/h3>\n

    When doing the join, the field IDNUMBER was used to match the records from the two inputs and thus exists in both. By default, both fields are mapped into output from the <\/span>Action <\/b>block to allow for more granular mapping control of source fields. <\/span><\/p>\n

    However, since only one is normally needed, remove the blue <\/span>Mapping Connection <\/b>from one of the sources. Click the connection arrow that goes from the PATIENTBILLING.IDNUMBER in the <\/span>Action <\/b>block to IDNUMBER in the <\/span>Output Data<\/b> block and press delete.<\/span><\/p>\n

    Match Field Names to Column Names<\/b><\/h3>\n

    Sometimes when your flow is processed, a final field name may not match the column name in the target table. The two fields MASK_CREDIT_CARD1 and MASK_CREDIT_CARD2 from the file PatientInfoProtected should map from the <\/span>Action <\/b>block to the columns CREDIT_CARD1 and CREDIT_CARD2 in the target table COLLECTION. <\/span><\/p>\n

    There are two conventions used for naming fields: one is the actual <\/span>Field Name<\/b> and the other is called <\/span>Ext Field<\/b>. The <\/span>Field Name<\/b> is used when processing the job script and the <\/span>Ext Field<\/b> is the column name in the table where a field value should load.<\/span><\/p>\n

    We, therefore, need to change the field names CREDIT_CARD1 and CREDIT_CARD2 \u00a0in the target fields block to match field names in the <\/span>Action <\/b>block. Double-click on each field in turn. This will open the <\/span>Target Field<\/b> window where we can change the <\/span>Field Name<\/b>. Change each <\/span>Field Name<\/b> to MASK_CREDIT_CARD1 and MASK_CREDIT_CARD2 respectively. <\/span><\/p>\n

    Ext Field <\/b>is found in the <\/span>Layout <\/b>tab of this window<\/span> and contains the column name of the table where the value for the field will load. No change is needed for <\/span>Ext Field<\/b> since it matches the column name in the table COLLECTION. Click <\/span>OK<\/b>.<\/span><\/p>\n

    \"Field<\/a><\/p>\n

    Now, create the new <\/span>Mapping Connection<\/b> from the <\/span>Action <\/b>block to the <\/span>Output Data<\/b> block for the fields MASK_CREDIT_CARD1 and MASK_CREDIT_CARD2. For each field, click the <\/span>Mapping Connection<\/b> icon in the <\/span>Palette<\/b>.\u00a0<\/span><\/p>\n

    Cick on the field in the <\/span>Action <\/b>block, and then on the field in the <\/span>Output Data<\/b> block. <\/span>The Collections Transform Mapping Diagram is now complete.<\/span><\/p>\n

    \"Collections<\/a><\/p>\n

    Give the Job a Name<\/b><\/h3>\n

    Click in the area outside the blocks for the overall <\/span>Properties <\/b>view and select the <\/span>Base <\/b>tab. Change both the <\/span>Name <\/b>field and the <\/span>IRI Job <\/b>field to <\/span>collections.scl<\/span><\/i>; then click in the diagram. The first field names the Transform Mapping Block represented in the above diagram, and the second field names the SortCL Job script that will be created from that diagram.<\/span><\/p>\n

    Go back to the Scratch flow diagram in the editor. Connect the outfile from the PatientInfoProtect.scl block to the PatientInfoProtected input file in the collections.scl block. Below is the complete Scratch flow diagram.<\/span><\/p>\n

    \"Complete<\/a><\/p>\n

    Serialize the Job for Execution<\/b><\/h3>\n

    To create the job scripts PatientInfoProtect.scl and collections.scl, and the batch file Scratch.bat, right-click in the editor > <\/span>IRI Diagram Actions<\/b> > <\/span>Export Flow Component<\/b>. Select <\/span>Windows <\/b>from the <\/span>Platform <\/b>drop-down and verify that Scratch.bat is in the <\/span>File name<\/b> field. <\/span><\/p>\n

    Scratch.bat is the name of the file that will execute the flow. Click <\/span>Finish<\/b>. The CoSort SortCL job scripts and the batch file are created and placed in the project.<\/span><\/p>\n

    If you have other commands that need to be executed in the batch script, see <\/span>Creating Voracity Flows Using Existing IRI Scripts (Part 3)<\/span><\/i><\/a>. This covers the use of <\/span>Decision <\/b>and <\/span>CommandLine <\/b> blocks. Note that there are other blocks available, including blocks for SQL and FTP commands, IRI FACT (fast extract), and bulk DB load blocks for several loaders.<\/span><\/p>\n

    Run the Job<\/b><\/h3>\n

    You can choose to run the individual tasks or the whole batch script in IRI Workbench through the:<\/span><\/p>\n

    \"Include-Omit-Record<\/a><\/p>\n