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.
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 not empty or NULL |
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
In order to select all tables from a data source and bring into your staging database, click Select All in the Data Selection Pane.
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
The wizard will appear and allow you to proceed:
👉 Join our Jet Analytics Training and master building data warehouses, cubes, and advanced dashboards.
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.
- 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.
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.
- 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
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.
- 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.