Horizontal Partitioning with Multi-Table Load

You can use DataMigrator to build a flow that extracts data using a single pass of the data and loads multiple data targets. This can be useful if, for example, you need to create separate databases for each department or region in your enterprise and you can split the extracted data based on the value of a department code or region code. The term for splitting the data based on the value of a column in the extracted data is horizontal partitioning. Horizontal partitioning of the data is just one useful way to take advantage of the ability to load multiple data targets in DataMigrator.

Implement horizontal partitioning in DataMigrator by building a data flow using multiple data targets together with VALIDATE to extract data from a data source, and split the output to two different data targets based on the value of an input column. This can be accomplished in one pass of the data.

To do this, create a data flow with one or more data sources and multiple data targets. Each data target has its own VALIDATE statement based on the value of a particular column. When the data target is loaded, only records with the appropriate values are accepted; all other records are rejected.

In the following example, DMINV, an inventory table, is the data source, and PRODTYPE is the input column used to split the output into two data targets. Records containing a value of Analog for PRODTYPE are loaded into a data target named DMINVA. Records containing a value of Digital for PRODTYPE are loaded into a data target named DMINVD.

Refer to the sample data flow DMSPLIT for the complete example.

Note: DMSPLIT in the ibisamp directory writes to FOCUS/FDS data targets. If you installed the samples for a different adapter, you will need to open and resave DMSPLIT before running it.


Top of page

Example: Creating a Data Flow for Horizontal Partitioning

In the DMC:

  1. Right-click an application directory in the navigation pane, select New, and then click Flow. The Data Flow tab opens in the workspace with the SQL object displayed.
  2. Drag the data source object DMINV from the ibisamp application directory in the navigation pane into the workspace, to the left of the SQL object.
  3. Right-click the SQL object and click Column Selection.

    The Column Selection window opens.

  4. Select each column in the Available Columns list and click the double arrow to move them into the Selected Columns list. Click OK.

    Tip: To select all columns, click any cell and press Ctrl + A.

  5. Drag the data target object DMINVA from the ibisamp application directory into the workspace, to the right of the SQL object.
  6. Right-click the target object DMINVA, and click Properties.

    The Properties dialog box opens.

  7. If the flow may be rerun, you should clear the target, using the Prior to Load Option. For a relational target, select Delete all rows from table or Truncate Table. For a FOCUS/FDS target, select Drop Table. Click X to close the window.
  8. Right-click the target object DMINVA and click Target Transformations.

    The Transformations window opens.

  9. Click the Automap button.
  10. Click the Validates tab, and then click the Insert Transforms button.

    The Transformation Calculator opens with the Relational Expression tab selected.

  11. Double-click PRODTYPE from the Columns/Variables tab.
  12. Enter ‘Analog’, with the quotes, under Value, to complete the calculation, and click OK.

    When you have completed the validation, the Transformations window should appear as shown in the following image:

    Transformations window

  13. Click OK to close the Transformations window.
  14. Drag a second target object DMINVD from the ibisamp application directory into the workspace, to the right of the SQL object.
  15. Right-click the target object DMINVD and click Properties.

    The Properties dialog box opens.

  16. If the flow may be rerun, you should clear the target, using the Prior to Load Option. For a relational target, select Delete all rows from table or Truncate Table. For a FOCUS/FDS target, select Drop Table. Click X to close the Properties page.
  17. Right-click the DMINVD target object and click Target Transformations.

    The Transformations window opens with the Relational Expression tab selected.

  18. Click the Automap button.
  19. Click the Validates tab, and then click the Insert transforms button.

    The Transformation Calculator opens.

  20. Double-click PRODTYPE from the Columns/Variables tab.
  21. Enter 'Digital', with the quotes, under Value to complete the calculation and click OK.
  22. Click OK to close the Transformations window.

    When you have completed these steps, your data flow should appear as shown in the following image:

    Data Flow

  23. Save the data flow as DMXSPLIT.
  24. On the Flow tab, in the Run group, click Run and select Submit from the drop-down menu.

    Two data targets are loaded, DMINVA with records containing a PRODTYPE of Analog and DMINVD with records containing a PRODTYPE of Digital.

    To view the log, right-click DMXSPLIT in the navigation pane, select Logs, and then click Last Log. The log of the data flow, shown in the following image, indicates that 17 rows were processed, 7 rows loaded into DMINVA, and 10 rows loaded into DMINVD.

    Last log report


iWay Software