Calculation Data Loads

In Pricefx there is a special type of process which allows us to manipulate data in the Analytics tables. This process is called a Data Load. In this handbook the focus will be on Calculation Data Load which is a type of a Data Load that applies a Logic to create new rows, or change/update values of the existing rows in the target Data Source or Datamart.

Business Use Case

Let’s mention couple business use cases which could lead to usage of Calculation Data Load:

As a Price Manager, I want to analyze the evolution of our product prices within Price Lists, so that I can find issues in pricing.
- This use case can lead to the need to copy Price List data to the Datamart.
As a Pricing Analysts, I want to be able to review and analyze/aggregate the evolution of the costs of the product, so that I can understand better the historical evolution of the cost.
- This use case can lead to the regular scheduled copy of the product cost from Product Extension table to the Data Source.
As a Pricing Analysts, I want to be able to analyze the Invoice Price of the Transactions, in order to understand the historical evolution of the products' per-unit prices.
- This use case could lead to pre-calculation of the Invoice Price in the Datamart (e.g., where the Invoice Price was not supplied by the ERP system).

Concept

For these specific Use Cases and many other things, a process called Data Load is used. Data Loads are usually performed on a regular schedule but they can also be done just as a one time thing. Typically what is done, is that the integration manager schedules a data load process just after data upload.

Data Loads

A Data Load represents a process for data manipulation of the Analytics tables, such as uploading data from Data Feed to Data Source, deleting rows from Datamart or calculation of new values of the Data Source or Datamart fields.

Many types of Data Loads are created automatically (when you deploy a Data Source or Datamart) but some of them need to be created manually, such as a Calculation Data Load to manipulate data.

Many types of Data Loads exist: Truncate, Flush, Refresh, Calculation, etc. In this handbook, the focus will be on Calculation Data Loads.

What Is a Calculation Data Load

Calculation Data Load applies a Logic to create new rows or change/update values of the existing rows in the target Data Source or Datamart.

The calculation can take data from anywhere, e.g., Price Setting tables.

When working with Calculation Data Loads, the system doesn’t modify data straight in the database but first copies them to a new source rowset and save the results in a target rowset, which is then used to insert and update data in the database.

Flow

Figure 1. Enrichment of DataSource/Datamart rows

The diagram shows that the first step when doing data enrichment is to copy the data from the source table. This way, the data modification is not done straight on the data from the database but on a copy of them. Because the data is copied, the database doesn’t have to keep a connection open to the Data Load for the whole process, which can take some time to finish. The next step is to apply the row logic on the copied data and create a new rowset which contains all the modified data. After this, the modified data are inserted in to the database, to the target table.

Figure 2. Adding rows to Data Source / Datamart

When adding new rows, the steps are similar. The big difference here is the type of logic used, since we don’t modify the data, only add new rows.

Implementation / Coinfiguration

The setup process for a Calculation Data Load requires:

Calculation Logic name and target column name to be exactly the same.
Target date is the same or later than the target date of the logic.
Correct mapping of the column in the target table.

Logics

When working with Data Loads we use Analytics logics. Logics for Data Loads in the Analytics module should have the prefix "DL_". This is specified in the best practices section of the Knowledge Base.

Nature

When creating a logic used for Calculation Data Load, it is important to pick the PA dataload (paDataLoad) Formula nature.

Context

There are three main types of Element Calculation Context for the Analytics logic:

init : This context is executed only once at the beginning of the Data Load process. It is usually used to copy the data from some Pricefx table to the DF/DS/DM. It can be also used for some preparation before the "row" calculations.
- In this context/phase, you can get a reference to the "source" and "target" rowset via a call to api.getDatamartRowSet().
row: Elements with "row" context are intended for column values modification. Those logic elements are executed again and again for each row, which is processed by Data Load.
- In this context/phase, you can read the values of the fields using the functions like api.userEntry() (which are originally used to build the input definition and then read the value).
- Note: In "allocation" logics, you can also use api.currentItem() which returns reference to the Rebate Record being allocated (see Rebates module for details about allocation logics).
summary: This context is executed only once, at the end of the Calculation Data Load. It’s generally used for calculation of summaries on the target rowset, before it’s written back to the Target table.
- In this context/phase, you can get a reference to the "source" and "target" rowset via call to api.getDatamartRowSet().

init logic

def target = api.getDatamartRowSet("target")       // ❶

def filters = [
        Filter.equal("name", "ProductCost")
]

def pxIterator = api.stream("PX", "sku", *filters) // ❷

def newRow
pxIterator.each { row ->

    newRow = [
            "ProductId": row.sku,
            "AvgCost"  : row.attribute1
    ]

    target?.addRow(newRow)                         // ❸

    api.trace(row.sku, row.attribute1)             // ❹
}

pxIterator?.close()                                // ❺

❶ Reference to the target.
❷ Iterator over products from Product Extensions.
❸ Adding the new row to the referenced target.
❹ This line is not necessary but it is useful for testing purposes.
❺ Always close the iterator after you have finished working with it.

When creating an init element, it is important to pick the correct context, which in this case is Init.

row logic

if(api.isInputGenerationExecution()) {
    api.userEntry("ListPrice")
    api.userEntry("Discount")
} else {
    if(input.ListPrice == null || input.Discount == null) {
        return null
    }
    return input.ListPrice - input.Discount
}

The method api.userEntry() is used to generate the input parameters, which will be mapped to the columns in the Datamart but will not be used for actual user input.

When creating an init element, it is important to pick the correct context, which in this case is Row. The name of the element must match the name of the column in the Datamart. In this example, the column in Datamart is named InvoicePrice_CDL so the name of the element must also be InvoicePrice_CDL.

Testing

init

When testing the init elements of the Calculation Data Load, you can do it in InteliJ, however Pricefx Studio will not write to the target by default.Therefore you should trace the rows with the rows api.trace() and view the trace after you click on Test Logic.

row

Testing of row element can also be done in Pricefx Studio. The example of row logic from the previous section would be tested by first generating input parameters, setting the correct context (DMDATAMART in this case) and picking the Datasource, from which the values of ListPrice and Discount will be read for each line item of that source.Entering the values for the ListPrice and Discount would override the values from the source table, if no values are entered in the input fields, the original values from the source table are used.After testing the logic, it is possible to view the result in the Results tab.

Configuration

After testing in Pricefx Studio, the logics should be deployed to the partition where a new Calculation Data Load can be created using the these logics.

Create and Run Calculation Data Load

In the Pricefx web application, navigate to Analytics › Data Manager › Data Loads.
Click on the + Add Dataload button to create a new Data Load → Type a label → Set the Type to “Calculation”.
Tip: Use the simple filters to decrease the number of visible Data Loads in the list.
Click on the Label of your created Data Load (in this example it is InvoicePrice_CDL).
Click on Target to pick your desired Datamart or Data Source.
In the Calculation tab, pick your logic and
1. Set the Target Date to a day after the PA calculation logic’s target date.
2. In the sub-window Formula Input → Source Field Mapping, verify that the mapping of the Datamart fields into the PA logic’s inputs are correct. If not, it’s possible to change it.
3. In the sub-window Formula Outputs → Target Fields, verify that the element used for output is visible. If not, it’s likely due to an incorrect Display Mode.
Save and Run the Dataload.

Summary

The Calculation Data Load is a process which allows us to manipulate data from Analytics tables. The Calculation Data Load uses a Logic to copy data from source to target using the init Calculation Context. Modify values in columns using the row Calculation Context and summaries calculations using the summary Calculation Context.

References

Groovy API

Documentation