Tutorial 07 – Filtering Data

What We Learn

How to filter loaded content in mapping file.

Scenario

We are not happy about the fact that loading transactional data fails if the incoming data results in an existing hash, and we want to change that. Since this is a feature that is not parameterized, we need to address this manually.

The problem is that a transaction class is implemented by a link table that has a load procedure which, for the sake of processing speed, does not check whether an incoming hash value already exists in the target link or not. Hence an existing hash value will break the primary key, and none of the rows in the current batch will be loaded into the link. As part of the definition of transaction here is that we don’t expect the same hash to be loaded again, but there are a number of scenarios where this still might happen, and we don’t want to break our design for just practical reasons. So we need to make sure existing hashes are not processed again.

We can limit the rows that are sent to the hashing procedures by writing any valid SQL code starting with the where keyword above the mapping group. If an alias is used, write the where clause to the right of it.

Use the SQL pane to locate the hash procedure code for Accounting entry. Copy the part of the code that calculates the hash value for the row, and make it into a where clause that does not permit rows that have a calculated hash that already exists in the target link table.

Note: the SQL code should be pasted into the mapping file as one row. It also cannot contain semicolons.

The original code before formatting:

where not exists (
  select 1 from dwStorage.lnk_AccountingEntry where lnk_AccountingEntry_hash = 
  dwETL.CalculateHash(concat(
    try_cast(data.YEAR as int), '.', 
    ltrim(rtrim(data.VNUMBER)),'.', 
    try_cast(data.RNUMBER as int),'.', 
    ltrim(rtrim(data.COSTCENTER))
  )))

Flatten the code and add it to a cell just above the mapping for Accounting entry. Save, run the model and deploy the Accounting entry class.

Run Tutorial Scripts

Run the following tutorial script commands fron the Help -> Tutorials -> DSharp Studio Professional Course -> Filter Data menu, and inspect the results.

Next tutorial