Add Nodes to Complete the Data Processing Engine Definition
Learning Objectives
After completing this unit, you’ll be able to:
- Use the Groups and Aggregates node in the definition.
- Create a formula to compute the liability.
Additional Nodes to Calculate Liability
Mary Levy, the Cloud Kicks loyalty manager, requires details of the liability that accrues to the program partner. A liability is accrued every time a member makes a purchase or redemption. Depending on the type of transaction, accrual or redemption, the program and the partner compensate for the liability. Understanding the liability helps Mary evaluate the financial viability of the partnership.
In order to calculate the liability, Linda needs details of the non-qualifying points associated with the transaction. These are points that members earn for their loyalty program-related activities, which they can redeem for rewards. Details of these points are available in the Loyalty Ledger object, so Linda creates a data source for this. In the object, she selects the Event Type, Points, and Transaction Journal fields.
She now adds the journal information to the ledger details. Linda uses the inner join because she wants only the matching fields from both source nodes to be part of the output.
- Click New Node and specify the following details.
- Select Node Type: Join
- Name:
Add Journal Info to Ledger
- Click Save.
- Select Join Type as Inner.
- Specify the following details under First Node.
- Source Node: Loyalty Ledger
- Select Fields: Points, TransactionJournalId
- Click Done.
- Specify the following details under Second Node.
- Source Node: Filter Based on Date and Program
- Select Fields: Select all available fields
- Click Done.
- Specify the following details under Map Fields.
- First Node Field: TransactionJournalId
- Second Node Field: Id
- Click Done.
Creating this join results in data about transaction journals and the loyalty program for all the ledgers. Linda will also get details of all the non-qualifying points, so she can group the journal types and then aggregate the points for each.
- Click New Node and specify the following details.
- Select Node Type: Group and Aggregate
- Name:
Aggregate Points Based on Journal Type and Partner
- Click Save.
- Specify the following details under Source Node.
- Select Source Node: Add Journal Info to Ledger
- Select Fields: JournalTypeName, PartnerId
- Click Done.
- Specify the following details under Aggregate.
- Alias:
TotalPoints
- Select Function: Sum
- Select Aggregate Field: Points
- Alias:
- Click Done.
She now has to split the data based on the journal type. To do this, she filters the data by accrual and redemption transactions.
- Click New Node and specify the following details.
- Select Node Type: Filter
- Name:
Filter Accrual Transactions
- Click Save.
- Select Source Node as Aggregate Points Based on Journal Type and Partner.
- Specify the following details for the filter condition.
- Field: JournalTypeName
- Operator: Equals
- Type: Field
- Value:
Accrual
- Click Done.
- Repeat steps 1 to 5 to create the Filter Redemption Transactions filter with the following details.
Source Node | Field | Operator | Type | Value |
---|---|---|---|---|
Aggregate Points Based on Journal Type and Partner | JournalTypeName | Equals | Field | Redemption |
Until now, Linda only has details of the total points. She still doesn’t have data related to the accrual and redemption costs per unit. She needs these details to calculate the liability. She will get the per-unit costs from the program partner object.
Linda follows the steps to create a data source for the Loyalty Program Partner. From this, she needs the Accrual Cost per Unit, Loyalty Program Partner ID, Program, Program Partner, and Redemption Cost per Unit.
She can now join the accrual and redemption aggregated information to the program partner. Linda uses the left outer join because she wants all the fields from the first source node and the matching fields from the second to be part of the output.
- Click New Node and specify the following details.
- Select Node Type: Join
- Name:
Add Accrual Points to Partner
- Click Save.
- Select Join Type as Left Outer.
- Specify the following details under First Node.
- Source Node: Program Partner
- Select Fields: Select all available fields
- Click Done.
- Specify the following details under Second Node.
- Source Node: Filter Accrual Transactions
- Select Fields: TotalPoints
- Edit Alias:
AccrualPoints
- Click Done.
- Specify the following details under Map Fields.
- First Node Field: Id
- Second Node Field: PartnerId
- Click Done.
The above node will result in all accrual transactions related to Ursa Major Solar. To this, Linda adds the redemption transactions using the left outer join.
- Click New Node and specify the following details.
- Select Node Type: Join
- Name:
Add Redemption Points to Partner
- Click Save.
- Select Join Type as Left Outer.
- Specify the following details under First Node.
- Source Node: Add Accrual Points to Partner
- Select Fields: Select all available fields
- Click Done.
- Specify the following details under Second Node.
- Source Node: Filter Redemption Transactions
- Select Fields: TotalPoints
- Edit Alias:
RedemptionPoints
- Click Done.
- Specify the following details under Map Fields.
- First Node Field: Id
- Second Node Field: PartnerId
- Click Done.
With this, Linda can now create the formula. To calculate the liability, she needs the difference between accruals and redemptions. The total value of accruals and redemptions is calculated by multiplying the points by their cost per unit.
- Click New Node and specify the following details.
- Select Node Type: Formula
- Name:
Calculate Liability
- Click Save.
- Select Source Node as Add Redemption Points to Partner.
- Specify the following details under Formulas.
- Alias:
Liability
- Field Type: Number
- Length:
16
- Decimal Places:
2
- Using the Search fields and Select operator options, enter formula: IF(ISNULL({AccrualPoints}) , 0-({RedemptionPoints} * {RedemptionCostperUnit}), ({AccrualPoints} * {AccrualCostperUnit}) - ({RedemptionPoints} * {RedemptionCostperUnit}))
- Alias:
- Click Validate Syntax.
- Click Done.
Finally, the liability is updated to the liability field in the partner object.
- Click New Node and specify the following details.
- Select Node Type: Writeback Object
- Name:
Update Liability
- Click Save and specify the following details.
- Source Node: Calculate Liability
- Action Type: Update
- Target Object: Loyalty Program Partner
- User with Writeback Permission: Admin User
- Select Update changed records only
- Writeback Sequence:
1
- Specify the following details under Field Mapping.
- Source Node Field: Id
- Target Field: Id
- Click Add Row
- Source Node Field: Liability
- Target Field: Liability_c
- Click Done.
Once all the nodes are created, Linda clicks Save and Activate.
As soon as Linda clicks Activate, validation rules run in the background to check if the definition is correct and can be run. If there are any errors in the definition, they are highlighted. Warnings are provided to help identify issues to fix or ignore. The definition will only activate once the errors are fixed.
The DPE definition is now ready. All that’s left to do is run it so that the data transformation can begin. DPE orchestrates the execution of data transformation in flows/apex. In the next unit, we take a look at how to do that.