Look up data using a dictionary in Jitterbit Design Studio

Use case

In this pattern, a source record does not contain all the data required by the target. For example, in the case of a parent-child relationship, if a child record is to be updated in a target, typically a parent record reference is required by the target. Or, if working with a product record, the source record may only have a SKU number, but the target also wants a group or class reference.

One way to handle this is to do in-line or dynamic lookup, by inserting a function call in the transformation, such that as each record is read, a lookup is made to a different object, and that value is populated in the field. Functions that can perform this are:

DBLookup is useful for getting a single field in a database source.
DBLookupAll and DBExecute, are helpful if more than one field needs to be fetched.
If SFDC holds the data needed, then SFLookup, SFLookupAll and SFCacheLookup could be used. SFCacheLookup has the added efficiency of caching such that the login process can be skipped if the SFDC session is still active.

However, there is a cost to using these functions. Take using SFLookup in a transformation as an example, where it is being used to get an ID from an object. When a source data set is passed through a transformation, each record is evaluated and all script functions are executed individually. If there are 10 records to be processed, there will be 10 calls to SFDC. If the integration processes a small number of records, then using in-line lookups is a great solution. But what if the universe of valid lookup values is not 1,000, but 10,000 records are to be processed. Performing an additional 10,000 calls in a transformation, where each call takes at least a second, is very inefficient and slow. The far better way is to pay the small time penalty to lookup the 1,000 records into a dictionary and use that for lookups.

What is a dictionary in Jitterbit?

In Jitterbit, a dictionary is a special type of global variable array that holds a key-value pair. The steps and functions are:

Initialize the dictionary using the Dict function.
Load data with key-value pairs, such as '4011' and 'Banana', '4061' and 'Lettuce', '4063' and 'Tomato', etc. Use the AddtoDict function.
Lookup a key after first checking if the key already exists using the HasKey function.
Lookup data by passing the key (4011) and getting back the value (Banana). The script uses a '[ ]' after the dictionary name, such as: $value = $d["4011"]

A dictionary enables our scenario such that one operation can get data from the source and load the dictionary, making it available to other operations for lookups. An initial operation can load the dictionary with 10,000 records and a later operation can quickly pass a key to the dictionary and get a value.

A number of things to keep in mind with dictionaries:

The scope of dictionaries are limited to the instance of the chain of operations. For example, if operation A loads $MyDict with 10,000 records, only those operations that are linked using Success or Failure paths, or with RunOperation() will have access to that dictionary. But, if an operation uses chunking and threading, and has a transformation that populates a dictionary, the dictionary will be inconsistent. This is because Jitterbit does not take the values assigned to variables by multiple operation threads and concatenate into a single value set. This is true for all global variables or arrays. Use the default chunking/threading values when building an operation that populates dictionaries.
Dictionaries, because they use a binary search, are very fast at finding keys and returning values. A key can usually be found within five to six tries. In contrast, compare this type of search to looping through a 10,000 record array to find a key.
Dictionaries are not written to memory, so they will not materially impact available server memory for processing.

Example 1

The customer has two tables, one with product information and the other with product categories, both of which are needed to update a 3rd table. The source is a view on a data warehouse, which is optimized to provide data in bulk, but not for rapid lookups. Using the DBLookup function for thousands of records would be quite slow. Also, the customer has a CSV file that contains information used to filter out data from the source

attachment

PWE.01 Get LR List

This reads an external file into temporary storage

PWE.02 Set Product Dict

A script initializes the Dictionary: $Product.Dict.LR_List=Dict();
Reads from a source temporary file
Transformation loads the values into a dictionary:

attachment

Script: AddtoDict($Product.Dict.LR_List,Style_Color,Flag+"|"+Launch_Release_Date);
Note here that the value is actually 2 values, separated by a '|'. The alternative would be to create 2 dictionaries, one for Flag and another for Launch_Release_Date, which would be unnecessary complexity.

PWE.03 Query Product from Teradata

The transformation has a condition to filter out products not in the CSV file, and which also assigns values to variables that are used in the transformation.
Note that we are splitting out (with a '|') what was loaded in the previous operation and loading into $Product.Flag and $Product.ReleaseDate.

result=false;
$Product.Dict.Key=PROD_CD;
If(HasKey($Product.Dict.LR_List,$Product.Dict.Key),
result=true;
value=$Product.Dict.LR_List[$Product.Dict.Key];
arr=Split(value,"|");
$Product.Flag=arr[0];
$Product.ReleaseDate=arr[1];
);
result

PWE.04 Product Categories Query

This is a bulk SFDC query (not SOAP), which does not impact the customer API limits. It loads data into a local temporary file.

PWE.05 Product Category Dict

This loads a dictionary with the Code as a key, and the SFDC data as a value

attachment

$Code__c=Code__c;
AddToDict($Dict.Product.Category.Line, $Code__c, Id)

PWE.06 Process Style

The dictionary is initialized: $Style.Dict.Unique=Dict();
Transformation looks up the ID in the Product_Category field

attachment

$GBL_CAT_CORE_FOCS_CD=GBL_CAT_CORE_FOCS_CD;
If(HasKey($Dict.Product.Category.Line,$GBL_CAT_CORE_FOCS_CD),result=$Dict.Product.Category.Line[$GBL_CAT_CORE_FOCS_CD],result="");
result

Note the use of HasKey to check if the key exists, instead of just ...$Dict.Product.Category.Line[$GBL_CAT_CORE_FOCS_CD].
If a key is passed that does not exist in the dictionary, then an error will be thrown since we are trying to lookup a non-existent value.

PWE.07 Bulk Upsert Styles

Finally the file is bulk loaded to SFDC.

Example 2

In this example we are processing an XML response, extracting the SKUs and values into a dictionary for use in a later operation.

attachment

This script is in the second to last post-op script:

arr=Array();
$jitterbit.scripting.while.max_iterations=10000;
arr=Split($IFMS.Response,'<');
cnt=Length(arr);i=0;j=0;
$DCL.LineItem=Dict();
While(i<cnt,
If(CountSubString(arr[i],'RemoteOrderNumber xmlns=""')==1,
order=arr[i];
start=Index(order,'>');
end=Length(order);
myorder=Mid(order,start+1,(end-1)-start);
$ACM.Shipment.Number=myorder;
);
If(CountSubString(arr[i],'ShipDate xmlns=""')==1,
shipdate=arr[i];
start=Index(shipdate,'>');
end=Length(shipdate);
shipdate=Mid(shipdate,start+1,(end-1)-start);
if(Length(shipdate)>7,
shipdate=Left(shipdate, Index(shipdate, ' '));
shipdate=CVTDate(shipdate,'?m/?d/yyyy','yyyy-mm-dd');
);
$ACM.ShipDate=shipdate;
WriteToOperationLog("Shipment: "+myorder+" ShipDate: "+shipdate);
);
If(CountSubString(arr[i],'ShippingTrackingNumber xmlns=""')==1,
tracking=arr[i];
start=Index(tracking,'>');
end=Length(tracking);
tracking=Mid(tracking,start+1,(end-1)-start);
$ACM.Tracking.Number=tracking;
WriteToOperationLog("Shipment: "+myorder+" Tracking: "+tracking);
);
If(CountSubString(arr[i],'SKU xmlns=""')==1,
sku=arr[i];
start=Index(sku,'>');
end=Length(sku);
mysku=Mid(sku,start+1, (end-1)-start);
WriteToOperationLog("SKU: "+mysku);
j++;
);
If(CountSubString(arr[i],'QuantityShipped xmlns=""')==1,
qty=arr[i];
start=Index(qty,'>');
end=Length(qty);
myqty=Mid(qty,start+1, (end-1)-start);
WriteToOperationLog("QuantityShipped: "+myqty);
$DCL.LineItem[mysku]=myqty
);
If(CountSubString(arr[i],'/OrderList')==1,
WriteToOperationLog("End of OrderList. " + String(j) + " items found.");
$ACM.Login.Counter=1;
RunOperation("<TAG>Operations/2. Fulfillment/F.5.0.D Get Shipments</TAG>");
$DCL.LineItem=Dict();
);
++i);

This script does a number of things, so these comments refer to the actions taken relative to dictionaries.
The dictionary DCL.LineItem is initialized.
Local variables 'mysku' and 'myquantity' are populated from the XML source.
DCL.LineItem is filled in using 'mysku' as the key, and 'myquantity' as the value.
The dictionary is used in a later operation, F.5.2.4

attachment

The transformation displays the mapping to Shipped Qty.

attachment

ShippedQty script uses the source field as a key to retrieve the shipped quantity and fill in the value.
Note that we check (using HasKey) if the key exists or not.

$myVal=Envelope$Body$GetResponse$GetResult$Details$ShipmentDetail.InventoryID$Value$;
WriteToOperationLog("Shipped Qty: "+Envelope$Body$GetResponse$GetResult$Details$ShipmentDetail.ShippedQty$Value$+" ------- "+
"InventoryID: "+Envelope$Body$GetResponse$GetResult$Details$ShipmentDetail.InventoryID$Value$);
If(HasKey($DCL.LineItem, $myVal),
output=$DCL.LineItem[$myVal],
WriteToOperationLog("Could not find: "+$myVal+" in Dict");
output=0
);
output