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.
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 we need at the same time.
The solution is to Pivot the Accounts into their own separate columns. Now all the data we need for each calculation 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 – Writeback 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, Cost, and Price have been pivoted into their own separate columns.
Now let’s define the Calculations. Select a Calculate component. Name it Calculate Revenue & COGS. The source of our data will be the Pivot Component from the previous step. Now let’s add two additional columns 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 gear icon, we can configure the definition of the column. Here we can write SQL to define the column values. We can double-click the column names 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. COGS 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. Since the Account Dimension has two Members with the exact same names, they’re automatically added as the destination for our 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.