Data Selection Rules

Overview

This article explains what data selection rules are and how to create them. It explains their various operators and how to apply data selection rules with Mappings.

Data Selection Rule

They are a set of rules that must be satisfied during the selection of data. After satisfying selection rules, data is loaded into the staging database. Data selection rule is a filter applied for the staging database at the field level in the data source tree. Similarly, data selection rules are applied for the data warehouse database at the field level of the data warehouse tree.

Types of Operators

Values must be either integers or letters. You can also specify a list of values by entering comma-separated values. The following operators are available when you create selection rules.

OperatorDefinition
Not EmptySelects records where the value of a field is not empty or NULL
EqualSelects records where the value of a field is not empty or NULL
Greater thanSelects records where the value of a field is greater than the specified value.
Less ThanSelects records where the value of a field is less than the specified value
Not EqualSelects records where the value of a field is not equal to the specified value
Greater or EqualSelects records where the value of a field is greater than or equal to the specified value
Less or Equal
Selects records where the value of a field is less than or equal to the specified value
Min. Length
Selects records that contain at least the specified number of characters
Max. Length
Selects records that contain no more than the specified number of characters
List
Selects records where the value of a field is equal to one of the specified comma separated values
EmptySelects records where the value of a field is empty or NULL
Not in List
Selects records where the value of a field is not equal to one of the specified comma separated values
Like
Selects records where the value of a field is similar to the specified value. A percent sign (%) can be used as a wildcard. ABC% will return all records where the value in the specified field starts with ABC.
Not Like
Selects records where the value of a field is not similar to the specified value. A percent sign (%) can be used as a wildcard. ABC% will return all records where the value in the specified field does not start with ABC.

Data Selection for All Tables

In order to select all tables from a data source and bring into your staging database, click Select All in the Data Selection Pane.

DATA SELECTION RULES

Another way to select tables and bring into staging database is to right-click the data source, click Automate, and then click Select Tables and Fields

DATA SELECTION RULES

The wizard will appear and allow you to proceed:

DATA SELECTION RULES

Creating a Data Selection Rule on the Data Source

  • Expand Business Units, select the preferred business unit, and then expand Data Sources.
  • Expand the preferred data source, and then select the table to which you want to apply a selection rule.
  • Right-click the table, and then choose Add Data Selection Rule.
DATA SELECTION RULES
  • In the Data Selection pane, select the field to which you want to apply a selection rule.
  • In the Operator box, select the preferred operator.
  • In the Value field, type the preferred value if applicable, and then click Add.

    All selection rules that you have applied to a table are displayed in the Project tree below the relevant table.

Creating a Data Selection Rule using AND / OR

You have the option to specify an AND operator, as well as an OR operator while creating a data selection rule.

Using an OR Operator

By adding additional filter on a table, we can create an OR operator. Our first example will use the Item table.

  • Expand Business Units, select the preferred business unit, and then expand Data Sources.
  • Expand the preferred data source, and then select the Item table
  • Right-click the table, and then choose Add Data Selection Rule.
  • In the Data Selection pane, we selected Item Category Code to apply a selection rule.
  • In the Operator box, select the preferred operator. In this example we selected the Equal operator.
  • In the Value field, type the preferred value if applicable, and then click Add. In this example we selected BAGS as our value.

    You will not notice the filter icon under the Item table which indicates it will only bring in values where Item Category Code equals BAGS
  • If we deploy and execute the Item table in the staging database and then preview the table you will notice that the table only contains records were the Item Category equals BAGS and the row count is 40.
  • Navigate to the Item, right-click the table, and then choose Add Data Selection Rule.
  • In the Data Selection pane, select the field to which you want to apply a selection rule. In this example we again selected Item Category Code.
  • In the Operator box, select the preferred operator. In this example we selected the Equal operator.
  • In the Value field, type the p referred value if applicable, and then click Add. In this example we selected CAPS as our value.
  • You should now notice two filter icons which indicates it will only bring in values where Item Category Code equals BAGS OR where Item Category Code Equals CAPS
  • Once you deploy and execute the Item table in the staging database you will now notice that we are pulling both BAGS and CAPS. You will also notice the row count has increased to 80.
Jet Data Manager Server

Using an AND Operator

  • Expand Business Units, select the preferred business unit, and then expand Data Sources.
  • Expand the preferred data source, and then select the Item table.
  • Right-click the table, and then choose Add Data Selection Rule.
  • In the Data Selection pane, select the field to which you want to apply a selection rule. In this example we selected Item Category Code.
  • In the Operator box, select the preferred operator. In this example we selected the Equal operator.
  • In the Value field, type the preferred value if applicable, and then click Add. For example, we selected BAGS as our value.

    You will not notice the filter icon under the Item table which indicates it will only bring in values where Item Category Code equals BAGS.
DATA SELECTION RULES
  • Click on the data selection rule you just created and in the Data Selection pane select the field you would like to add your selection rule to. In this example we selected Product Group Code.
  • In the Operator box, we will select the Equal operator.
  • In the Value field, type the preferred value if applicable, and then click Add. In this example we selected SPORTS BAG as our value
DATA SELECTION RULES

A filter icon is created under the Item table that indicates to only allow values where Item Category Code equals BAGS and Product Group Code equals SPORTS BAG.

DATA SELECTION RULES
  • Once you deploy and execute the Item table in the staging database you will now notice that we are pulling values that meet our specific criteria of BAGS and SPORTS BAG. You will also notice the row count has decreased 10.
DATA SELECTION RULES

If You Need Any Help, Contact us Now!

Scroll to Top