Lab Info

🎯 Learning Outcomes

At the end of the Lab, you should be able to:

...

Figure 1. Sneakpeek of the allocated Rebate in Datamart rows - the goal of this exercise

Pre-requisites

In order to be able to complete this laboratory exercise, please complete the prerequisites before you proceed.

Labs

This lab expects, that you already have in your partition a solution of the preceding labs:

• Rebates Configuration - Lab 1

• Rebates Configuration - Lab 2

Prerequisites

• Provided partition with pre-configured

  • Product master data table

  • Customer master data table

• Datamart "Transactions"

Requirement Broad Overview

...

The customer needs to be able to manage their rebates in Pricefx to be able to reward their customers for performance. The process has several phases:

Phase 1: Prepare Rebate Agreement

1. Sales Manager needs to negotiate the Rebate Agreement – to capture the negotiated parameters/conditions.

2. The system generates the Rebate Records to be used for regular recalculations during the year.

Phase 2: Regular actions during the year

1. Payout (Rebate Records) calculations – to capture the current state of the rebates and to get better estimations.

2. Prepare the Rebate Record for Export and for Allocation

3. Rebate Allocation (of the Rebate Records created from approved Rebate Agreements) – every time new data is loaded into transactions.

4. [OOS] Sales Managers review the report on customer rebates.

   1. During the year they want to be able to see the updates of the estimated rebates per customer (targets, payouts) to be able to discuss with the customer when they’re not compliant.

5. [OOS] Rebate Records (aka "Payouts") approvals

6. [OOS] Export of approved Rebate Records (or Payout Records).

Phase 3: After a year

1. [OOS] Payouts approval of the yearly payouts.

2. Final allocation of the approved rebates.

3. [OOS] Export of approved payouts (Rebate Records).

4. [OOS] Review of last year rebates impact.

   1. For analytics purposes, they need to see the actual rebate paid to the customer reflected/allocated in the transactions (to track the effectiveness, to see the real margin).

Important Assumptions for this Exercise

• You are working on a given testing transactions dataset which has data for several past years, but also future years. (Remember, in a regular project you would have transactional data only until the present day.) Of course for testing, you need to somehow simulate the state of having certain partial dataset (i.e. until "today") in the Datamart. To handle this, use the Calculation Date as indication of "today" – i.e. in your calculation logic you will pretend that there’s nothing in the Datamart after that date. You do not want to physically remove this data from Datamart because you will need to test calculations simulating different calculation dates.

• Whenever the instructions mention "today", use the Calculation/Target date for the actual calculations.

User Story / Requirements for Phase 3: Rebate Allocation

As a Pricing Manager I want to Enrich a Datamart with rebate data (requires mapping of datamart and field for enrichment) so I can see and check agreed rebate data in Datamart.

Acceptance Criteria

...

• Rebate data are visible in Datamart after rebate agreement is approved.

...

• Allocate the Rebate Records related to your two approved agreements.

Acceptance criteria Details

• The name of Datamart column with the rebate money is "Rebate".

• The rebate is an off-invoice discount, so its effect must be reflected in the waterfall calculation.

• Distribution of the rebate is done based on the Total Sales.

Implementation Steps for Phase 3: Rebate Allocation process setup

Allocation takes the estimated rebate [money] calculated on the RR, and distributes it to the historical Transactions, so that various chart analysis based on Transactions shows more accurate results (which consider also Rebates to be paid to customers).

...

• Calculation Base – filter specified on the Rebate Records. Describes, which Transactions will receive the Rebate from this Rebate Record.

• Feeder logic - prepares list of Rebates Records to be allocated

• Allocation logic - takes Rebate money from the Rebate Record and split it among Transactions specified by the Calculation Base filter [of the Rebate Record]

• Dataload (of "Calculation" type) – definition of the process, specifies which Feeder and Allocation logic should be used. You can also specify input parameters for those logics here.

Calculation Base

"Calculation Base" is a Datamart filter. It describes which Datamart records should be used as a destination for allocation of a Rebate calculated on the Rebate Record. Every Rebate Record has its own "Calculation Base" filter.

...

• Review the Datamart query used in ActualSales calculation. Which filters are we using? Write them down.

• Use the Datamart viewer (at Analytics → Data Manager → Datamart) and filter the same transactions, which are used in the ActualSales calculation. How many transactions were used to calculate the Sales?

• So, for example, if we will calculate (on a Rebate Record), that we should pay rebate $100 to the customer, that money must be split among all those transactions [from the previous step].

Steps

...

1. Create the Calculation Base filter for a Rebate Record

   1. in Pricefx Studio, edit the logic BonusOnSales

   2. Create a new element "CalculationBase" in which you will create the filter.

      Example 1. Code of Element "CalculationBase", with Calculation Context "Rebate Record"

      Code Block

      import net.pricefx.common.util.CalendarUtil.TimePeriod import net.pricefx.common.util.CalendarUtil.TimeUnit /* The CalculationBase filter will be used for Rebate Allocation process. We redefine the default filter here, because we want to use different end date. Otherwise we would not need to change it. */ calculationBase.clear() calculationBase.include(out.CustomerGroup) calculationBase.include(out.ProductGroup) calculationBase.include( new TimePeriod(out.StartDate, out.EndDateOrToday, TimeUnit.DAY) ) /* this date field will be later redefined in the Rebate Allocation process, but if we set it up here, we can use see the effect of this filter already in the RR detail screen, under sub-screen "Calculation Base" */ calculationBase.setDateFieldName("InvoiceDate") api.trace("calculationBase", api.jsonEncode(calculationBase)) //TODO comment out in production code //api.trace("calculationBase.dump", calculationBase.dump()) //if you want to verify the DateFieldValue

      the variable "calculationBase" is created automatically and is pre-populated by a datamart filter based on the out-of-the-box inputs of the Rebate Record (like startDate, customerGroup, …).

      But there’s a chance you will need to change it based on specific needs of your project implementation. In our case we change it simply because we need to simulate different "Today".

   3. Test the logic in Studio:

      1. Switch the Context to "Rebate Record"

      2. In RebateRecordName enter ID of your RR, on top of which you will test the execution.

      3. the CalculationDate will simulate Today

      4. Note: if your dialog show also "Target Date", you can ignore it, because the Calculation Date will be used as value of api.targetDate()

         Image Modified

         Figure 2. Setup of Parameters for Test of the Rebate Record logic

   4. Test the Logic, and review the content of the CalculationBase filter

      Image Modified

      Figure 3. Results of Test of the Rebate Record logic

      Image Modified

      Figure 4. Traced detail of CalculationBase filter

   5. Deploy the logic to the partition

Feeder Logic

It supplies a list of Rebate Records to be allocated.

1. In Studio, create a new "Pricing Logic" of "Default" Nature (i.e. a "Generic Calculation Logic")

   1. name it "Feeder"

   2. set the Valid After to 2000-01-01 (we need to be able to use it "backwards")

2. Create a new element "Inputs"

   Example 2. Code of element "Inputs"

   Code Block
   if (api.isInputGenerationExecution()) { api.dateUserEntry("StartDate") api.dateUserEntry("EndDate") api.abortCalculation() }

   1. Set Display Mode to "Never"

3. Create a new element "EmitRebateRecords"

   Example 3. Code of Element "EmitRebateRecords"

   Code Block

   def startDateAsString = input.StartDate def endDateAsString = input.EndDate /* if the user does not specify a date range, we will use year which contains the target date of the rebate allocation DL process */ if (!startDateAsString || !endDateAsString) { def dmCalendar = api.getDatamartContext().calendar() def yearTimePeriod = dmCalendar.getTimePeriod(dmCalendar.getYear(api.targetDate())) startDateAsString = yearTimePeriod.getStartDate().format("yyyy-MM-dd") endDateAsString = yearTimePeriod.getEndDate().format("yyyy-MM-dd") } def filterForRRs = Filter.and( Filter.greaterOrEqual("startDate", startDateAsString), Filter.lessOrEqual("endDate", endDateAsString) ) final String dmDateFieldName = "InvoiceDate" final String sortRRByField = "startDate" api.emitRebateRecords(dmDateFieldName, sortRRByField, filterForRRs) return null

   this will provide the list of RRs to the Allocation process (which is technically a special DL Calculation which uses the Feeder to get the list of RRs)

   in this exercise we do not distinguish, if the Rebate Records are approved or not. We simply allocate (emit) all Rebate Records from within a given Date Range, which in our case will be the whole year (date-range entered later in the Dataload definition). On an actual customer project, you might have different needs.

   1. Set Display Mode to "Never"

4. Test the logic.

   Note: Here the test is important to prove, that the Groovy code can execute without failure. But as you execute it outside of a Dataload, it will not show you here the list of the Rebate Records emitted.

   1. on Parameters tab, do Generate Parameters, to see the inputs generated by the Logic

   2. Note: you do not need to care about the context, as it will not run in any of the available contexts during the real execution (in the DL)

   3. experiment with Testing the Logic with or without the input values for StartDate and EndDate.

   4. add Tracing and look at the content of the Filter for Rebate Records, you’re building in the logic

5. Deploy the logic to the partition

Allocation Logic — Row Calculation Context Phase

Allocation logic is used to split the actual calculated rebate (or accruals) in between the Transactions.

...

1. You need a column in the Datamart, where you will store the allocated Rebate money amount of every Transaction line

   Image Modified

   Figure 5. Datamart field settings - new field "Rebate"

   1. add money column "Rebate" to your Datamart "Transactions"

      • The field must be owned by (must belong to) the Datamart (i.e. the Owner field will be empty), otherwise the Dataload process will not be able to write into it the result of the logic.

      • set Label of the column to "Rebate"

      • Note: After click on the "Add Field" button, the new field appears at the end of the list, which can be on another page, if your Datamart has many fields.

      • datatype of the column should be "Money"

      • move the column just after the InvoicePrice column. This is not needed from technical perspective, but it will give you better feeling about the flow of the calculation.

   2. remember, the allocated Rebate will have impact on the size of the Margin, so you must reflect it in the Margin calculation

      • change the Expression of the Margin column to "InvoicePrice - Rebate - Cost"

   3. Deploy the Datamart

2. Create the Allocation logic

   1. Navigate to Administration → Logics → Analytics

   2. Make a new logic – name it "RebateAllocation", Valid After 2000-01-01, Active

   3. Create new element, name it "Rebate". This is intentionally exactly the name of column in the Datamart because the result will be mapped to the Rebate column in the Datamart.

      • keep Calculation Context as "Row". Remember, this will be a Row logic used in the Calculation Dataload, which iterates the Transactions one by one.

      • Ensure the result of the element is visible - again, that’s important, to be able to map the calculated rebate value back to the Datamart.

        Example 4. Code of Element "Rebate"

        Code Block

        /* read data from Transaction row */ def trxPreviousRebate = api.userEntry("Rebate") ?: 0.0 def trxInvoicePrice = api.userEntry("InvoicePrice") ?: 0.0 //def transactionId = api.userEntry("TransactionId") if (api.isSyntaxCheck()) return /* read data from the Rebate Record */ def rebateRecord = api.currentItem() if (api.isDebugMode()) { // ONLY for testing purposes rebateRecord = api.find("RR",0,1,"uniqueName")?.getAt(0) } def rrActualSales = rebateRecord?.attribute1 def rrActualRebate = rebateRecord?.attribute2 /* api.logInfo("RR vs TransactionID", (rebateRecord?.uniqueName ?: "") + " vs " + transactionId) */ if (!(null in [rrActualSales,rrActualRebate,trxInvoicePrice])) { return trxPreviousRebate + rrActualRebate * (trxInvoicePrice / rrActualSales) } else { api.addWarning("Cannot calculate Rebate for RR:" + rebateRecord?.uniqueName + " because either RR's ActualSales, RR's ActualRebate" + " or TX Invoice Price is empty." ) return null }

        values from the Datamart rows are read in the same way as in Row logics in DL Calculationswe do not want to proceed, if the logic was executed just to collect the definitions of the input fields.api.currentItem() returns the RR being processed. For testing purposes, you can temporarily find some existing saved RR, for example api.find("RR",0,1,"uniqueName")?.getAt(0), because in Test Logic mode, the api.currentItem() returns null.each line of a Datamart has the field Warnings, where you can see those messages after the calculation process is over.

        you must consider the previously (already) calculated rebate of the Transaction row, because several of your Rebate Records might need to allocate the money into the same Transaction row. That’s quite common business scenario.

   4. Test the logic:

      1. For the test, you will have to temporarily read the Rebate Record directly from the table of Rebate Records, but be sure to comment it out after you’re done with testing.

      2. On Parameters tab:

         Image Modified

         Figure 6. Setup of Parameters for Test of the "allocation" Logic

         • click on Generate Parameters to see the inputs Rebate and InvoicePrice, you’re using. This is not necessary for the test itself, but good to verify, that they’re coming up correctly, as it’s needed in the DataLoad later.

         • Select Datamart context

         • Select your Datamart Transactions

         • fill in the ID of the Datamart row, on which you will test. If you cannot see this field, then re-select the Datasource name field (even if you have only one)

         • Set targetDate to the date, which you would like to use as "today"

      3. click "Test Logic"

      4. Verify the calculated Rebate for your selected Transactions, if it is what you would expect. Warning: the numbers on the screenshots are only illustrative

         Image Modified

         Figure 7. Results of Test of the "allocation" Logic

         • ensure your results are correct

         • why do you see the Warning, even if the code works?

         • review the content of the _ROW_. What does it contain?

   5. Remember to comment out the testing code - the direct reading of Rebate Record from table

   6. Deploy the logic to the partition

Dataload

The allocation Dataload process puts together all the pieces you’ve created until now.

1. Navigate to Analytics  DataManager  Data Loads

2. Add new DataLoad:

   1. Click on Add Data Load

      Image Modified

      Figure 8. Adding new DataLoad

   2. Enter the Label "Rebate Allocation"

   3. Set Type to "Calculation

   4. And click Add.

      • Warning: Your new Dataload is added to the END of the table, so there’s a chance you cannot see it right away.

      • Also, your new Dataload will likely show an error because of missing Target, but that’s what you will set in the next steps

3. Edit the detail of the new Dataload:

   1. click on the name of the new Data Load, to open its detail screen.

      Image Modified

      Figure 9. List of DataLoads with the newly added DataLoad

   2. Setup the Options:

      Image Modified

      Figure 10. Setup of DataLoad’s OPTIONS

      • untick the "Incremental"

      • for the "Target" select your Datamart.

      • No filters needed (on the "Target" tab)

        Remember – this is an Allocation process (not common enrichment) – so the filter, which transactions will be used, is taken from the Rebate Record’s "Calculation Base" filter

4. Setup the Calculation:

   1. Open the "Calculation" tab

      Image Modified

      Figure 11. Configuration of DataLoad’s logics and field mappings

   2. Logic/Formula section

      • For "Logic" – select your "RebateAllocation" logic. Once you do it, the content of Formula Fixed Inputs, Source Field Mapping, and Target fields should get populated.

   3. Source Field Mapping section

      • Verify the Source Field Mapping, if it came up automatically correctly. If the "Input" field name (defined in the logic) is the same as the datamart "Field" name, the mapping comes up automatically. Otherwise ensure, that you’ve set it up.

   4. Target Fields section

      • ensure, that your displayed logic element "Rebate" shows up here. Also, the checkbox next to "Rebate" must be checked, otherwise the result will not be mapped to the Datamart row.

   5. Feeder Formula section

      • select your "Feeder" logic. Your 2 input parameters "StartDate" and "EndDate" should appear.

      • Set the StartDate to 2020-01-01 and EndDate to 2020-12-31.

5. Save the Data Load

6. Click on Run Data Load to execute the allocation process.

   Note: Later you can schedule this DataLoad to run regularly, or it can be executes from Integration Manager.

7. Navigate back to the Schedule tab

8. Refresh the Job/Task Tracking, until the process is done (Ready).

9. Click on the "Info" icon (located at the end of the line) and review all the Messages:

   • You should be able to find a line describing, how many Transaction rows were affected by the process, for example: Target rowset has 99 rows after calculation [RebateAllocation[1280]]

   • also, in the performance log (down in the Messages), you should see, how many times your logic RebateAllocation was executed, something like this: 103.14 4% ░░░░░░░░░░ 99 │ ├── RebateAllocation

10. Verify the results in the Datamart

    Image Modified

    Figure 12. Datamart with Rebate allocated to the appropriate Transactions

    1. Check values of the Rebate column of those Transactions

    2. Review the range of Invoice Dates, if it matches the CalculationBase filter from the Rebate Record

    3. Check Customer Group and Product Group, if they match to those selected on the Rebate Record

11. Reset of Rebate column value to zero:

    1. our Allocation process expected to have the Rebate column empty (or zero), before it starts, because we might need to allocate rebate from more Rebate Records into the same Transaction row. So, we cannot simply override the Rebate by every Allocation logic call.

    2. For you it means, that you need to ensure, that the Rebate column will be empty (or zero) before the Allocation Data Load starts.

       • To set the Rebate column to zero manually (quite helpful when you want to re-test the Rebate Allocation again), you can use the Mass Edit operation on the DataRows screen.

         Image Modified

         Figure 13. Reseting the Rebate value column to 0 using the Mass Edit functinality

Allocation Logic — Init Calculation Context Phase

Rather than updating the Rebate column manually, with mass edit, it is better to reset it to 0 in the allocation logic — in the init calculation context phase.

...