Sign Up for Training |
insightsoftware Company Site
Community
Downloads
Training
Submit a Request
Become a Jet Insider
Give Feedback

Data Selection Rules


Overview

This article explains the following:

What is a Data Selection Rule?

Data selection rules are used to specify a set of conditions that must be satisfied. By applying selection rules, only the subset of data that you actually need is loaded into the staging database. You can think of a data selection rule as a filter. You apply data selection rules for the staging database at the field level in the data source tree. You apply data selection rules 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.

Operator Definition
Not Empty Selects records where the value of a field is not empty or NULL
Equal Selects records where the value of a field is equal to the specified value
Greater Than Selects records where the value of a field is greater than the specified value.
Less Than Selects records where the value of a field is less than the specified value
Not Equal Selects records where the value of a field is not equal to the specified value
Greater or Equal Selects 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
Empty Selects 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

There will be times when you will want to select all tables from a data source to bring into your staging database.

In JDM version 2017 and higher, click Select All in the Data Selection Pane:

alltable-datasel1.png

 

To start the data selection wizard, right-click the data source, click Automate, and then click Select Tables and Fields

alltable-datasel2.png

The wizard will appear and allow you to proceed:

alltable-datasel3.png

Creating a Data Selection Rule on the Data Source

  1. 1. Expand Business Units, select the preferred business unit, and then expand Data Sources.

  2. 2. Expand the preferred data source, and then select the table to which you want to apply a selection rule.

  3. 3. Right-click the table, and then choose Add Data Selection Rule.

  4. 4. In the Data Selection pane, select the field to which you want to apply a selection rule.

  5. 5. In the Operator box, select the preferred operator.

  6. 6. 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.

    Note: To add Data Selection Rules to the data warehouse you will locate the table in the data warehouse tree and follow steps 3 through 6 above.

Creating a Data Selection Rule using AND / OR

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

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.

  1. 1. Expand Business Units, select the preferred business unit, and then expand Data Sources.

  2. 2. Expand the preferred data source, and then select the Item table

  3. 3. Right-click the table, and then choose Add Data Selection Rule.

  4. 4. 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.

  5. 5. In the Operator box, select the preferred operator. In this example we selected the Equal operator.

  6. 6. 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.

  7. 7. Navigate to the Item, right-click the table, and then choose Add Data Selection Rule.

  8. 8. 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.

  9. 5. In the Operator box, select the preferred operator. In this example we selected the Equal operator.

  10. 6. 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.

Using an AND Operator

  1. 1. Expand Business Units, select the preferred business unit, and then expand Data Sources.

  2. 2. Expand the preferred data source, and then select the Item table.

  3. 3. Right-click the table, and then choose Add Data Selection Rule.

  4. 4. 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.

  5. 5. In the Operator box, select the preferred operator. In this example we selected the Equal operator.

  6. 6. 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.

  7. 7. Click on the data selection rule you just created.

  8. 8. 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.

  9. 9. In the Operator box, select the preferred operator. In this example we selected the Equal operator.

  10. 10. In the Value field, type the preferred value if applicable, and then click Add. In this example we selected SPORTS BAG as our value.

    You should now notice a filter icon under the Item table that indicates to only allow values where Item Category Code equals BAGS and Product Group Code equals SPORTS BAG.

    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.

Mappings

In Jet Data Manager (17.12 and higher), you can apply different data selection rules to each table coming into the data warehouse from the stage via mappings:

Simply open the mappings table, right click on the incoming table an click Add Data Selection rule then define the rule the same way you would for one that applies to the entire data warehouse table.  The rule in the mapping will only be applied to data coming in from the specified staging table.

Was this article helpful?
0 out of 0 found this helpful

Comments