Overview
In this session we’ll start building the data rules we need. First, we’ll take a look at the data rule editor and learn the difference between VIEW rules and ACTION rules. Then we’ll organize the calculations we outlined into different data rules. And lastly, we’ll configure the calculate trade spend rule.
Transcript
[transcript]
In this session, we'll start building the data rules we need to calculate the values that populate our P & L form. First, we'll take a look at the data rule editor and learn the difference between view and action rules. Then we'll organize the calculations we outlined in the architecture document. And lastly, we'll configure the first data rule we need in Kepion.
A data rule is a component used to define business rules through the Kepion UI. These rules generate SQL scripts in the application. The use of data rules is highly recommended, as they're both easier to use and maintain than raw SQL scripts.
There are two types of data rules: view rules and action rules. View rules generate SQL views, which are virtual tables. Views are dynamic and update automatically as the data changes. Action rules are updated on demand and allow you to insert or delete the records in a partition. Action rules are preferred when the resulting values need to be referenced in other rules.
Let's return to the table we created in our architecture document and organize the calculations.
The accounts gross sales before returns and cost of goods sold are both calculated from values in our assumption model, so let's calculate both these values in the same data rule. As we'll need to reference the revenue numbers in other calculations, let's make this one an action rule.
All of these values are calculated by multiplying gross sales before returns with rates from the deflator percent model, so let's perform all of them in the same rule as well. For simplicity, let's make this one a view.
For the post invoice accounts, we don't actually need to calculate anything here. We just need to move the data from the trade spend model to the reporting model. We can do this with a simple view rule as well.
And finally, the gross margin percent is the ratio of gross margin to gross sales after returns. As neither of these accounts are on the lowest level of the hierarchy, this calculation wouldn't be a good candidate for data rules, as the values don't exist as fact records.
This calculation would be best represented with MDX, which we'll get to later.
Here are the three data rules we'll build in this application. Let's get an overview of how these rules will interact with the reporting model.
Calculate revenue and cogs is an action rule that will insert records into the calculated partition. Then calculate on sale discounts will create a view using deflator percent values and some of the records created in the previous rule. And finally, calculate trade spend will create a view to display the post invoice accounts.
As this is the simplest rule, let's begin there.
To create a new data rule, navigate to the data rules tab and then click add. Name it calculate trade spend and select rule type as view. Click add.
In the data rule editor, click edit.
Now we have a setup similar to the dashboard editor, with the ribbon at the top containing different components to add, a panel on the side that will be an overview of the steps in our rule, and the editor window in the middle where we'll configure each component.
There are three steps to building a data rule. First, we'll need to choose where the data we're using is coming from. Often this will be a partition, but we can also bring in data from the dimension tables or another data rule.
The next step is making any transforms or calculations that we need, such as joining two tables together or multiplying values.
And then the last step is choosing where to publish the results to. In a view rule, the data will always be published to the rule partition of the model we choose.
For this rule, all we need to do is take the data from the trade spend model and move it to the reporting model. So for our data source, we'll select trade spend write back.
If you switch to the preview tab, we can see a live version of the data we're working with at each step.
As the reporting model has the exact same dimensionality as the trade spend model, we don't need to transform the data in any way. We can just select publish and then choose the source as trade spend and the target as the reporting model.
Now we need to save and deploy the application.
In the rule partition, we can see all the data we published.
Also note the rule column here, which shows us which data rule these entries are associated with.
If we open our P & L form, we can see the post invoice accounts have been filled in. The aggregation has also been calculated automatically.
In this session, we looked at the data rule editor and the difference between view rules and action rules. Then we organized the calculations into the data rules we need for the application. And lastly, we configured our calculate trade spend rule.
In our next session, we'll look at designing an action rule to handle the calculations we need for our revenue and cost of goods sold.
[/transcript]