Hi Supriyo

A data selection rule is working as a filter on the original data. It is a rule that is applied to the transfer process going into the table.

Lookups and transformations are applied in the data cleansing process which happen after the transfer. Therefore they are not available fore Table Data Selection Rules.

You would normally have several options to limit data after data cleansing:

• Setup a data validation rule that invalidates the unwanted rows - kind of noisy and not considered best practice.


• Place the Table Data Selection Rule on the next table that are going to use the data (typical in the MDW layer if you have done the transformation/lookup in stage/business unit)


• Create a new table with a table insert from T1 with the Selection rule applied - it you need a physical copy of the data in the same database. Perhaps deselect physical valid table on T1.


• Create a custom view of T1 as SELECT * FROM T1 Where (Row I like).


• Create a custom script on T1 for post data cleansing execution as DELETE FROM T1_V WHERE (rows I don't like).

There is probably more options but this is some of the ones that I have used before to manage the requirement that you are describing.

I hope this will help you :)

/Jan Bøllingtoft

Hi Supriyo, 

Jan, Thank you for your great description of the process and suggestions. I also have another suggestion in mind. 

You can create a custom data selection rule. I'll show an example using PurchaseOrderHeader and PurchaseOrderDetail from the Adventureworks database. These two tables encounter the same issue as your situation, where the order date is only located in the header table. 

So once you have brought both tables into the solution

1. Right click on PurcahaseOrderDetail > Add Data Selection Rule. 
2. Then in the bottom of the Data Selection Pane on the right, be sure the operator is set to Custom, then click Add. This opens up a scripting window. 
3. I can then use the following script to filter the PurchaseOrderDetail table based on the OrderDate located in the Header table:

lPurchaseOrderID] > =

(SELECT MIN(PurchaseOrderID)
FROM PurchaseOrderHeader
WHERE OrderDate >='01-01-2014')

This method is actually filtering on the PurcahseOrderID (the join of the two tables) based on the OrderDate of my choice. 

One limitation here is that you would need to ensure the PurchaseOrderHeader table is executed prior to the PurcahseOrderDetail. But if you are doing lookups into Detail then this would always be the case. 

Once done, you can click OK and Deploy/Execute the table to validate your changes. The final solution should look something like this:

Hope that works for you, let me know if you have any questions. 

Happy Developing!

Thanks Jan and Joseph for your responses.

Though I have a question:

In the 'Data Fields' section of the 'Custom Selection Rule' window the PurchaseOrderID from PurchaseOrderHeader is not available. Do I have to pull all the columns from looked up tables in the main table on which I have to the put the filters?

Hi Supriyo, 

Nope, in this case you just type in the table/field names. If you like you can first type the where clause into SSMS so you have the benefit of intellisense then copy everything after the "Where" and paste it into this window.