Part 9 - Data Rules

Author: Ian Britz

Introduction

Now that we have data in the application, let's create two Data Rules (Calculate OPEX and Calculate Net Income) to calculate the values in the Reporting Model. Kepion's Data Rule Editor simplifies and expedites the process of writing business rules by eliminating the need to write SQL scripts directly.

 

Note: For more information on how to design Data Rules, please see Part IV of the Kepion Training Video Series.

 

 

Calculate OPEX

Here, we'll create the first Data Rule for the Reporting Model. This rule will transfer the values over from the Expense Model. Remember that these were the values we entered manually through the Operating Expenses Form.

 

Step 1. In the Modeler, navigate to the All Data Rules section and click Add.

 

 

Name the Data Rule Calculate OPEX and ensure the type is set to VIEW.

 

 

Tip: VIEW Rules generate SQL Views, which are virtual tables. Views are dynamic and update automatically as the data changes. ACTION Rules, on the other hand, are updated on demand and allow you to INSERT or DELETE the records in a partition.

 

Step 2. Select the new Data Rule from the right-hand navigation pane. Here, we have a setup similar to the Dashboard Editor.

1. The various components we have to move and manipulate data within the rule.
2. The current list of components in the rule.
3. The current selected component.

 

 

Step 3. There are typically three steps to building a Data Rule. Selecting the Data Source, manipulating the data with Transform components, and then Publishing the results to a Model.

 

 

Let's begin by selecting the Fact component under Data Source. This brings in Fact Records from a selected Model Partition. We can choose the partition under Source. Let's bring in the Expense - (Writeback) records.

 

 

In the Preview Tab we can see all the records we entered in the previous article.

 

 

Step 4. For this rule, all we need to do is move these records to the Reporting Model— so we can skip the Transform components. Instead, let's select Model from the Publish section.

 

 

The Target is the Model we want to publish records to and the Source is the records we want to publish. Let's select Reporting for Target and Expense - Writeback for Source.

 

 

Step 5. Save the Data Rule.

 

 

Calculate Net Income

Now let's create the second Data Rule we need for the Reporting Model. This Data Rule will use the metrics we entered into the Assumption Model from our Assumptions Form. We'll then use those metrics to calculate Product Sales and Cost of Goods Sold.

 

[Product Sales] = [Quantity] * [Price]
[Cost of Goods Sold] = [Quantity] * [Cost]

 

Step 1. Create a new Data Rule.

 

 

Step 2. Again, let's begin by selecting our Data Source. For this rule, we'll need the Assumption Model, which is where we entered the Quantity, Price, and Cost values.

 

 

Step 3. To multiply these values together, we'll need to utilize a few different Transform components. A brief explanation of each component is provided below. For a more detailed explanation of table calculations, refer to this video.

 

	To move unique values from one column to multiple columns.
	To perform the reverse operation of PIVOT.
	To include columns from one or more result sets together.
	To include rows from one or more result sets together.
	To manipulate the columns of a result set. This step is useful when adding or removing columns to create the core rule logic.

 

To perform this calculation, we need to move all three of these metrics onto the same row. To do so, let's utilize a Pivot Component.

 

 

We should configure the Pivot in the following way:

• Source: Assumption - Writeback
• Aggregate by: Value
• Pivot by: MetricID
• Pivot scope: Quantity, Price, Cost

 

 

In the Preview tab, we can see that all three values are now lined up on the same row.

 

 

Step 4. Now we can define the calculation. Select a Calculate component and set the Pivot component we just configured as the Source.

 

 

Add two additional columns. These columns will hold the results of the calculation.

 

 

In order for the data to flow to the correct accounts, we need to name these columns the exact Member Label of the Accounts we're targeting. In this case, they should be Product Sales and Cost of Goods Sold.

 

 

To configure the calculation, select the gear icon for the Product Sales column.

 

 

In this window, we can write SQL to define the column definition. Let's define it as:

 

[Quantity] * [Price]

 

 

Tip: You can double-click on the column names on the right, to bring them into the calculation.

 

Now let's define the other column in the same way. This one will be defined as:

 

[Quantity] * [Cost]

 

 

Let's check the Preview tab again to make sure the calculation looks correct.

 

 

We only need to publish the two calculated accounts, so let's remove the three metrics from the table.

 

 

Step 5. Now that we have the correct data, we need to Unpivot the table back into the original format. This is so the structure of the table matches the structure of the Model we're publishing the data to.

 

 

For the Source choose the Calculate step, and then select both Accounts to unpivot.

 

 

Note: If you receive a warning here, double-check the names of the columns in the Calculate step to make sure they exactly match the Dimension Member Labels.

 

Step 6. Now select Model under the Publish section, and choose the Reporting Model as the Target and the Unpivot step as the Source.

 

 

Step 7. Save the Data Rule.

 

 

Publishing Data Rules

To ensure the Data Rules are in effect, we need to deploy the application.

 

 

The data from VIEW Rules is always published in the Rule Partition of the Model we targeted. Also note the Rule Column, which tells us which Data Rule is generating these records.

 

 

If we navigate back to our Dashboard App, the Net Income Report should now be complete. In addition, the aggregated totals have been automatically calculated. 

 

 

 

Conclusion

While this is just a simple example, imagine expanding this application across an entire business with thousands of products, where each user or manager can contribute their own piece to the planning process together in real-time.

 

Now that the application is finished, try planning out different business scenarios, or changing the values in the input forms to see how they affect the Net Income Report. If you haven't already, it's strongly encouraged to watch the Kepion Training Video Series, which will cover this entire process in much more depth.

 

If you have any questions about this content, check out the Knowledge Base to learn best practices and find documentation on all of Kepion's features. If your question isn't answered in the Knowledge Base, please submit a ticket.