{"id":9831,"date":"2016-04-20T12:10:27","date_gmt":"2016-04-20T16:10:27","guid":{"rendered":"http:\/\/www.iri.com\/blog\/?p=9831"},"modified":"2017-11-06T13:45:35","modified_gmt":"2017-11-06T18:45:35","slug":"creating-voracity-flows-using-existing-iri-scripts-part-2","status":"publish","type":"post","link":"https:\/\/beta.iri.com\/blog\/data-transformation2\/creating-voracity-flows-using-existing-iri-scripts-part-2\/","title":{"rendered":"Creating a Voracity Flow Using Existing IRI Scripts (Part 2 of 3)"},"content":{"rendered":"

This is the second in series of articles illustrating on how to use existing IRI CoSor<\/a>t (SortCL) jobs in graphical IRI Voracity<\/a> ETL workflows, or more simply, flows. In the first article<\/a>, we dropped an existing SortCL<\/a> job that processes month-end sales transactions into Voracity. Specifically, we:<\/p>\n

    \n
  1. Created a flow diagram with a start block, connector, and transform mapping block representing the SortCL job script.<\/li>\n
  2. Modified the Transform Mapping Diagram for that script by
    \na. \u00a0deleting the output file Out_CashRecords.dat from the flow.
    \nb. \u00a0adding an Action Key for the Sort.<\/li>\n
  3. Saved the changes made in the diagram to the SortCL script, and created a batch file to run the job in the flow by \u201cExporting the Flow Component.”<\/li>\n<\/ol>\n

    In this article, we will add another job script into a flow, or a \u201cflowlet.\u201d Below is that new SortCL script, MonthEndSales.scl:<\/p>\n

    \/STATISTICS=monthendsales.stat\r\n\/INFILE=C:\/IRI\/CoSort95\/workbench\/workspaceFlow\/MonthEnd\/AllSales.dat\r\n     \/PROCESS=RECORD\r\n     \/ALIAS=AllSales\r\n     \/FIELD=(DEPT, TYPE=ASCII, POSITION=1, SEPARATOR=\u201d|\u201d)\r\n     \/FIELD=(YEARMONTH, TYPE=ASCII, POSITION=2, SEPARATOR=\u201d|\u201d)\r\n     \/FIELD=(TRANSAMT, TYPE=NUMERIC, POSITION=3, SEPARATOR=\u201d|\u201d, PRECISION=2)\r\n\r\n\/SORT\r\n     \/KEY=(DEPT, TYPE=ASCII)\r\n\r\n\/OUTFILE=\u201dNIGHTLY.SALESBYDEPT;DSN=Oracle_qa2;\u201d\r\n     \/PROCESS=ODBC\r\n     \/CREATE\r\n     \/FIELD=(DEPT, TYPE=ASCII, POSITION=1, SEPARATOR=\u201d|\u201d)\r\n     \/FIELD=(YEARMONTH, TYPE=ASCII, POSITION=2, SEPARATOR=\u201d|\u201d)\r\n     \/FIELD=(SALESAMT, TYPE=NUMERIC, POSITION=3, SEPARATOR=\u201d|\u201d, PRECISION=2)\r\n     \/SUM SALESAMT FROM TRANSAMT BREAK DEPT\r\n<\/pre>\n
    Drag the script file\u2019s icon from the Project Explorer<\/span> to the right of the Transform Mapping Block named SortTransSelect.scl to create the Transform Mapping Block named MonthEndSales.scl.<\/p>\n
    Connect the Blocks<\/span><\/span><\/p>\n
    Notice that the input file for MonthEndSales.scl is AllSales.dat, which is also one of the targets in SortTransSelect.scl. Using the Connection<\/span> component from the Palette<\/span>, connect the icon for Out_AllSales.dat in the SortTransSelect.scl block, to In_AllSales.dat in the MonthEndSales.scl block, by clicking on the component, then each file icon. The order that you click the blocks determines the direction of the connection arrow.<\/p>\n
    The MonthEnd Flow Diagram now appears as below with two transform mapping blocks:<\/p>\n
    \"MonthEnd<\/a><\/p>\n
    Double-click on the block for MonthEndSales.scl to create the MonthEndSales Mapping Diagram. This is a detailed diagram of the script MonthEndSales.scl.<\/p>\n
    \"Detailed<\/a><\/p>\n
    Append (Don\u2019t Truncate) the Table<\/span><\/span><\/p>\n
    Looking at the Output Data<\/span> block, the Section Options<\/span> block of the output NIGHTLY.SALESBYDEPT has Process ODBC<\/span>. This means the output will be loaded into a database for the named output. The Section Options<\/span> block also contains the option Output Write Type \/Create<\/span>. This directs the job to truncate the table before loading the new records. Instead, we want to keep the existing records and add the new records to the table.\u00a0<\/span><\/p>\n
    Click Output Write Type<\/span> in the Section Options<\/span>. In the Properties View<\/span> on the Base<\/span> tab, select \/APPEND<\/span> from the drop-down. Or double-click Output<\/span> Write Type<\/span> and select \/APPEND<\/span> from the Output WriteType<\/span> dialog, and click Finish<\/span>. In Section Options<\/span>, Output Write Type<\/span> now displays \/APPEND<\/span>.<\/p>\n
    Create a New Target with Aggregation<\/span><\/span><\/p>\n
    The output is performing a Sum <\/span>from the input field TRANSAMT and placing the value in the column named SALESAMT in the table NIGHTLY.SALESBYDEPT. The aggregation for the table has a BREAK condition for each time a new department is encountered, so the monthly subtotals for each department can load into the table.<\/p>\n
    It was decided that a monthly grand total for sales will load to a separate table called MONTHENDSALES. Therefore, an aggregation needs to be created similar to the one in SALESBYDEPT, but without a BREAK condition.<\/p>\n
    Drag an Output<\/span> ODBC<\/span> from the Palette<\/span> into the Output Data<\/span> block. This brings up the Table Selection<\/span> dialog.<\/p>\n
    Select your Connection profile<\/span> from the drop-down, check the box for the table that will receive the data, and click Finish<\/span>.<\/p>\n
    \"Get<\/a><\/p>\n
    A block for the output NIGHTLY.MONTHENDSALES has now been created and links the field AllSales.YEARMONTH to the YEARMONTH column in the table NIGHTLY.MONTHENDSALES. The field SALESAMT needs to be deleted because we are going to define it with an aggregation. Right-click on the field SALESAMT, select Edit<\/span> > Delete from Model<\/span>.<\/p>\n
    From Field Options<\/span> in the Palette<\/span>, drag the Aggregation <\/span>to the blue field block for NIGHTLY.MONTHENDSALES. Be sure the block is large enough to add this to the bottom of the field list. The Aggregation <\/span>dialog pops up.<\/p>\n
    Click the Edit<\/span> button next to the Field Name<\/span>, and change the Field Name<\/span> from AGGREGATION to SALESAMT. Change the Data Type<\/span> to NUMERIC<\/span>, change the Precision<\/span> to 2 (for 2 decimal places), and click OK<\/span>.<\/p>\n
    \"Field<\/a><\/p>\n
    This takes you back to the Aggregation<\/span> dialog. Under From<\/span>, select the Field<\/span> radio button > choose the field TRANSAMT > click OK<\/span>.<\/p>\n
    \"Aggregation\"<\/a><\/p>\n
    Now, connect the field AllSales.TRANSAMT to the column SALESAMT by clicking on Mapping Connection<\/span> in the Palette<\/span>, then on the field AllSales.TRANSAMT in the Sort<\/span> box, and finally on the column SALESAMT in the table NIGHTLY.MONTHENDSALES.<\/p>\n
    Save the Changes<\/span><\/span><\/p>\n
    Right-click in the white background of the diagram, select IRI Diagram Actions<\/span> > Export Flow Component<\/span>, then verify that the File name<\/span> is MonthEndSales.scl, and click Finish<\/span>.<\/p>\n
    The MonthEndSales Mapping Diagram now appears as follows:<\/p>\n
    \"MonthEndSales<\/a><\/p>\n
    With the edits complete, go back to the flow diagram and right-click the flowlet to create the .bat file by Exporting Flow Component<\/span>. The .bat file will contain commands to execute the blocks in the flow in sequence, beginning with the Start <\/span>block. It can be executed from within Workbench directly, through its task scheduler, using any third-party job scheduler, or from the command line.<\/p>\n
    In the next article<\/a>, we will expand the flow by adding:<\/p>\n