Table

The table node is used to connect a single table to a data source node. Unlike the Multi Tables node, many nodes can be connected to a single data source, with each node representing a different table. A range of operations and functions can then be connected to any given table node in order to perform any required data cleansing or manipulation on the data set before loading it into a target.

Table nodes are a good option if your data set contains tables on which you need to perform any kind of data preparation; the alternative to the table node is the tables node, which does not support data cleansing processes in the Data Flow.

Configure Table Nodes

There are two methods of adding Table nodes to the Data Flow.:

Method 1

The first method is to select all the required tables from the Properties panel of the data source node, and then click 'Add Tables' (green highlight below):

Each selected table is now represented by its own node:

Method 2

The second method requires you to add each Table node individually, connecting them to the data source node (green arrow below), and to then inject the relevant table (blue arrow) into each Table node from the Properties panel. You can also rename the table from each node (red highlight).

Note: If the tables include value-based filters, you can select the checkbox (green highlight) to remove them for the new model, to make the entire dataset available for further analysis.

Preview Result Set

The result set of each table can be previewed by clicking on the relevant node, and then clicking the preview icon from the node's Properties panel, or from the Preview panel. By default, the preview is limited to the first 50 rows, but this can be changed from the Preview Size drop down in the ribbon.

  • Click here to learn more about the Preview panel.

Filters

The inline filter allows you to limit the query results by defining a query condition, rather than having to apply a filter after the query has been run. Click on the + sign for the query condition.

  • Select the column names to be used as the operand for the filter (red box below).
  • Then select the operator (green box below) - (Equals, Greater than, etc.)

  • Select the comparison type (orange box below) for the second operand - (Column, variable, or value)

Second Operand

  • The second operand selected (blue box below) will depend on the selected comparison type
    • If column is selected, then a dropdown list of all columns will be displayed, allowing you to select a column
    • If variable is selected, then a dropdown list of all variables will be displayed, allowing you to select a variable
    • If value is selected, then enter the actual value.

Multiple conditions

  • If multiple conditions are required, click on the "+" sign (red box below) to add an additional query condition.
  • Select AND or OR (yellow box) to determine how the two conditions are to be joined.

Group Conditions

  • If more than two conditions are required, you might want to group conditions together:
    • Check the boxes of the columns that you want to group together (orange box)
    • Click on the group button (red box) to group the columns

Column Selection

Expand the Column Selection window to update the column selection for the given table. By default, all columns in the table are selected but you can remove columns by deselecting them. Columns that have been deselected will not be copied to the new data model.

Variables

Expand the Set Variable Values window to pass a variable to the node. To do this, click the plus sign and then select the relevant variable, the aggregation type, and the relevant column.

ClosedExample. Load Variable into Table Node

In this example, the table contains the income column on the left in the image below. We want to create a variable that will be used to load only rows where the income was 75000 and above (as in the column on the right).

To accomplish this, a variable is created named VarIncome, where the value is set to 75000:

This variable is then injected into the table node where the Income column is located:

Finally, the Filter node is connected and used to filter the table by the Income column, using the variable:

Description

Expand the Description window to add your own description to the node's properties. This is useful for keeping track of the ETL process, especially if multiple users are working with the same ETL.

Edit Table Nodes

To edit a Table node, click on it and go to its Properties panel. Here you can update the node's preview and its various settings.

You can rename the table (red box below), change the table selection (green box),change the filter selection (yellow box) change the column selection (orange box), set incremental loading via a variable, (yellow arrow),and add a description (red arrow) that will be visible only in the Model app.