Overview
The last piece of configuring a data rule is appropriately scoping down the size of the calculation with parameters.
Transcript
[transcript]
The last step of configuring a data rule is appropriately scoping down the size of the calculation with parameters.
To explain, let's change the entity selection to Canada and enter some new values.
When we click update, our data rule executes and calculates the revenue and COGS values for Canada that we see on the P and L report. However, behind the scenes, the rule actually recalculated all the values for both the US and Canadian entities, even though we didn't change the US data.
In a real application, with potentially hundreds of thousands of records, this process is needlessly inefficient. Rather, we should build the rule in such a way that if we change the data for Canada, only the data related to Canada is recalculated. Kepion can do this by reading the current filter selections and then passing those into our data rule.
To configure this, let's begin by opening up our action rule. On the left-hand side, let's select the settings and click the plus icon to add a new parameter. We'll continue to use the entity filter as an example, so let's name the parameter entity ID, and then select the relevant hierarchy.
The member picker here allows us to simulate how the parameter will scope our data when this member is picked up in the filter selection. Note that this is just for debugging purposes and doesn't affect the functionality of the parameter in any way. Let's use Canada as our selection to test the parameter.
Now let's navigate back to the definition tab, expand our data sources, and select the assumption write back partition. Currently, our rule is bringing in every value in the partition. Let's limit this in the scope tab. Under entity, choose parameter, and then select entity ID.
This means we want to scope the data of the rule to just records from the currently selected entity. If the selected entity is United States, we'll only bring in the data for the United States. As we chose Canada as the test member for our parameter, we can see that only Canadian values are flowing through in the preview tab.
This tells us that the parameter selection is working. This also means that the entity column is no longer useful, as we're only bringing in values from one entity at a time. That means we can remove the entity column entirely from our table without affecting the data. This is helpful because the less data we're moving around, the more performant the rule will be.
Now that we've removed the entity column from our source table, we'll also need to remove it from the transform steps. As always, it's helpful to continually check the preview tab for any errors.
In the delete action, we no longer want to delete the entire partition. This would delete data from all entities. Instead, let's scope this down to just the entity selected in our parameter.
For the insert statement, again let's map everything to the entity parameter.
Let's save all the changes.
In the procedure step, notice that the execute statement has changed. Let's copy this new script and update our SQL rule.
Now when we execute the script, all we need to do is tell the rule which entity to update. Currently, that value is hard coded to Canada, which happens to have a member ID of two.
What we want instead is to read which entity is selected in the filter and then use that member ID. To handle this process, Kepion provides a parameter we can reference called filter scope.
Filter scope is a table we can read from that contains each of the filters in the form and their current selections.
The SQL query we need here is select top one member ID from filter scope, and then we need to provide the dimension and hierarchy we're trying to reference. In this case, it'll be where dimension equals entity and hierarchy equals entity hierarchy.
Let's save the application.
Back in the dashboard, let's post some data and ensure that the rule is still calculating correctly.
Before we conclude this session, let's take a step back and examine the structure of this process at a high level.
First, we have our two models, assumption and reporting. Each of these models also have an associated form so that we can interact with the data. When we enter values in the assumption form and click update, two things happen.
First, the values in the model get updated. Second, we trigger the SQL rule linked to the post action.
As that rule executes, it reads from the filter scope table to determine which entity is selected. Then we call the data rule and pass in the entity we want to calculate as a parameter. The data rule reads the values from the assumption model and then calculates the revenue and COGS. Then it deletes the old records for the selected entity and inserts new ones. The P and L report then reflects these changes in the form.
While there are a lot of moving parts here, remember that most of this process is handled automatically by Kepion. As modelers, we only need to configure three different pieces: the steps of the calculation in the data rule, the script to execute that rule, and the parameters to scope the calculation.
In the next session, we'll create the third and final data rule we need to calculate the on sale discounts.
[/transcript]
Next steps
- Session 12b- Calculating revenue and COGS
- Session 12c - Calculating revenue and COGS
- Session 13 - Calculating on-sale discounts