Overview
In this session, we’ll create the last data rule we need to calculate our on-sale discounts. We’ll begin by outlining the design of the calculation, then we’ll implement that design to build the rule in Kepion, and lastly, we’ll map the deflator % accounts to their related P&L accounts.
SQL Script
This script maps the [Deflator % AccountID] to the [P&L AccountID]. It does this by checking the AccountID in each row, and attempting to match that ID to one of the 8 Deflator % IDs. If any of those IDs are matched, it replaces it with the associated P&L Account IDs.
CASE
WHEN A.[AccountID] = 24 THEN 5
WHEN A.[AccountID] = 25 THEN 6
WHEN A.[AccountID] = 26 THEN 8
WHEN A.[AccountID] = 27 THEN 9
WHEN A.[AccountID] = 28 THEN 17
WHEN A.[AccountID] = 29 THEN 18
WHEN A.[AccountID] = 30 THEN 19
WHEN A.[AccountID] = 31 THEN 20
ELSE A.[AccountID]
END
Note: Double check the Account MemberIds in your application, as they may not align exactly with the application in the video.
Transcript
[transcript]
In this session, we'll create the last data rule we need to calculate our on sale discounts.
We'll begin by outlining the design of the calculation. Then we'll implement that design to build the rule in Kepion. And lastly, we'll map the deflator percent accounts to their related P and L accounts.
For this calculation, we need to multiply gross sales before returns with each of these eight deflator percent accounts. While we could use the method from the previous calculation and pivot all eight of the accounts and then define eight individual calculations, this would be a bit tedious. In addition, the data from these two sources doesn't quite line up, as the deflated percent values were entered at the brand level and the gross sales before returns is at the SKU.
To solve both of these issues, let's utilize the join component. In SQL, a join is an operation that combines rows from two or more tables based on a related column.
Joining these tables together will naturally line up the calculation for us without the need to pivot. In addition, we can join both of these tables with the brand attribute in order to distribute the deflated percent values to the SKU level. As both brands and SKUs have an associated brand attribute, we can use this column to map the product IDs together.
Let's start by adding a new data rule. In the all data rules tab, select add and name it calculate on sale discounts. To keep things simple, we'll create it as a view.
In the rule editor, we need to select two data sources: the deflator percent write back partition and the reporting model calculated partition.
For the calculated data, we can scope it down to just the account gross sales before returns, as we don't need the other values in our calculation.
Let's also select dimension from the data source and choose product. This brings the product dimension table into the calculation. Only the member ID is brought in by default, but we can choose to include additional columns from the table. Let's select brand.
Now select a join component. We'll use this to join the revenue data with the brand attribute. For the source, select reporting calculated, and for the join, select the product dimension table. Kepion will automatically try to find shared columns to join the data along.
Now that these two tables have been joined together, in the column tab we can include the brand information.
Now let's create a second join and then add the same column to the deflated percent data.
Now let's join both of these tables together.
As these tables have the exact same structure, every column is included as a potential piece of the join condition. However, we already know that the product values between these tables don't line up, so let's remove that from the join. We also know that these tables use different accounts, so we can remove that as well.
In the column tab, the first table in the join condition is specified as table A, while the second table is B.
We want our calculation at the SKU level, so let's first remove the brands. Since table A is our deflator percent data, A dot product ID is the brand information. Let's replace this with B dot product ID.
Let's also include the values from both tables. I'm going to rename the value columns for clarity.
In the preview tab, we now have one combined table of data at the SKU level with both values. In addition, the revenue and deflator percent values are already lined up for us. All we need to do is multiply these values together.
In the column tab, add another column and define it as the deflator percent value times the reporting value. Then let's remove the individual value columns.
Everything is looking good, but there's one additional detail to address. The accounts listed here are the deflator percent statistical accounts. However, we need the values to flow into the related P and L accounts.
Again, we could pivot these members into their own columns, rename them, and then unpivot, but there's a quicker solution here.
Let's configure the column definition for account ID and choose custom member expression.
I prepared a simple SQL statement beforehand that simply maps the statistical account ID to the P and L account ID. This script, and a quick explanation of how it works, are provided below the video.
Now all the data is flowing into the correct accounts. Let's publish it.
Then save and deploy the application.
If we navigate to the reporting model, we can see that the P and L report is now nearly complete.
In this session, we outlined the design of the calculation and then implemented that design in Kepion. And lastly, we used SQL to map the deflator percent accounts to their related P and L accounts.
In the next session, we'll use MDX to calculate the gross margin percent.
[/transcript]