Overview
In this session we’ll design a rule to calculate our base assumptions into revenue and cost of goods sold. As there are many important concepts introduced in this session, we’ve separated the three sections into separate videos. In this section, we’ll focus on the process of creating calculations on tables of data.
Transcript
[transcript]
In this session, we'll design a rule to calculate our base assumptions into revenue and cost of goods sold. Then we'll publish those results to the calculated partition of our reporting model.
As there are a lot of important concepts introduced in this session, we've separated the three sections into separate videos, with each focused on just a single concept. In this section, we'll focus on the process of creating calculations on tables of data.
Let's first review some common patterns we need to understand when defining these types of calculations.
As an example, let's take a look at this simple table. We have three brands, each divided into volume and price. We'd like to calculate revenue from these accounts by multiplying volume times price.
However, as SQL typically processes tables one row at a time, it's difficult to access both the values together. The solution is to pivot the accounts into their own separate columns.
Now all the data we need is on the same row. We can add a new column to our table called revenue to hold the result.
Now that we have the values we need, we can unpivot our table back to the original format. This process of pivot, calculate, and unpivot is a technique you'll often see when working with tables, so it's important to understand it before moving on.
Let's use this process to create the rule we need in Kepion.
Add a new data rule.
We'll call it calculate revenue and cogs and set the type as action.
As in the previous rule, we should begin by configuring the data source. We'll start by selecting the fact table option and choosing assumption write back as the source. This will get us all the volume, price, and cost data we entered.
Let's open the preview tab to confirm that our data is flowing through.
Now let's transform the data.
Just as in the example, we're going to need to pivot, calculate, and then unpivot. In the transform section at the top, select a pivot component and name it appropriately.
For the source, we need to select the set of data we want to pivot. Naturally, let's choose the records from the assumption model we brought into the rule.
Ensure account ID is selected for pivot by, and then in the pivot scope select volume, price, and cost.
If we navigate to the SQL tab, we can see that behind the scenes each component is automatically generating the SQL statements we need.
In the preview tab, we can ensure that the result of the pivot is what we intended. Volume, price, and cost have been moved into their own separate columns.
Now let's define the calculations.
Select a calculate component and name it calculate revenue and cogs.
The source of our data will be the pivot component from the previous step.
Now let's add two additional columns by clicking fx to hold the results of our calculations.
I'm going to name the first one gross sales before returns. This is the account that holds the revenue values.
It's very important that the name of the column here exactly matches the name of the dimension member we're targeting. If not, our data won't flow through to the correct account.
For the second column, let's name it cost of goods sold.
If we select the pencil icon, we can configure the definition of the column. Here we can write SQL to define the column's values. We can double click the other column names here to bring them into the calculation.
Gross sales before returns is defined as the volume of products we sold multiplied by the sale price of those products.
Now let's do the same for cost of goods sold. This account is defined as the volume times the cost to produce each product.
Let's check the preview tab again to make sure everything looks correct.
The calculations look good, so let's remove the volume, price, and cost columns, as we don't need to publish these values to the reporting model.
Now let's add the unpivot component and bring our two calculated accounts back onto the account dimension. Let's name it unpivot revenue and cogs.
Since the account dimension has two members with the exact same names, they're automatically added as the destination for the unpivot.
If we entered the names incorrectly during the calculate step or the dimension members we were trying to reference didn't exist, we would receive a warning here.
Now that our data matches the structure of the reporting model, we can publish it. We'll cover this step in the next video.
[/transcript]