Overview 12c
The last piece of configuring a Data Rule is appropriately scoping down the size of the calculation with parameters.
Transcript 12c
[transcript]
The last piece 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 assumption values. When we click post, our Data Rule executes and calculates the Revenue and COGS values for Canada, that we see on the P&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 scope the calculation.
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, 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 – Writeback partition. Currently, our rule is bringing in every value in the partition. Let’s limit this in the SCOPE tab. Under Entity, select Parameter and then choose EntityID. This means we want to scope the data in the Rule to just records from the currently selected Entity. If the selected Entity is United States, we’ll only bring in data for the United States.
As we chose Canada as the default 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.
In the Procedure step, notice that the exec statement has changed. Let’s copy this new script and update our SQL Rule. Now when we execute the script, all we need to is tell the rule which Entity to update.
Currently that value is hard-coded to Canada, which happens to have a MemberId of 2. What we want instead, is to read which Entity is selected in the Filter and then use that MemberId.
To handle this process, Kepion provides a parameter we can reference called FilterScope. FilterScope 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 1 [MemberID] FROM @FilterScope and then we need to provide the Dimension and Hierarchy we’re trying to reference. In this case it’ll be WHERE [Dimension] = N’Entity’ and [Hierarchy] = N’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 on 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 Post, 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&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 SQL 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 our On-Sale Discounts.
[/transcript]
Next Steps
- Session 12b- Calculating Revenue and COGS
- Session 12c - Calculating Revenue and COGS
- Session 13 - Calculating On-Sale Discounts