Author: Ian Britz
Now that we have data in our Application, let's create a Data Rule to calculate our Revenue & Cost Form. Kepion’s Rule Designer simplifies and expedites the process of writing business rules by eliminating the need to write SQL scripts directly.
The first Rule will need to take the data we entered through the Quantity Form and multiple it by the data we entered in the Price Form and publish the results to the Assumption Model. At the same time, it will need to make the same calculation with the Cost of Goods and Quantity Form.
Note: This is an advanced section that might take some additional time and effort. Before starting this section, here is a quick overview of the data transformation components you should understand.
|When you want to move unique values from one column to multiple columns.|
|When you want to perform the reverse operation of PIVOT.|
|When you want to include columns from one or more result sets together.|
|When you want to include rows from one or more result sets together.|
|When you want to manipulate the columns of a result set. This step is useful when adding or removing columns to create the core rule logic.|
For more information on how to design Data Rules, please see Rule Designer Guide - Part 2.
Step 1. To configure a new Data Rule, navigate to the MODELER and select the All Data Rules node and click Add.
Step 2. Name the Data Rule Calculate Revenue and Cost and select the Type as VIEW.
Step 3. Then open up the new Data Rule and select Edit.
Step 4. First we need to get the data that we want to work with. All of the data for the Quantity, Price, and Cost of Goods Forms is contained within the Assumption Model, so select Fact from the Data Source section and choose Assumption - (Writeback) as the Source.
Tip: During every step in the Data Rule, check out the PREVIEW tab to see a live example of the table and data we're working with.
Step 5. In order to make the calculation we outlined, we need to pivot the data around the Metric Dimension. This is the Dimension that contains the Cost of Goods, Price, and Quantity members. For Source, choose the data we just selected, Assumption - Writeback, and configure the Pivot as in the example below.
Step 6. Now that the data is in a format we can work with, let's define the actual calculation. Click Calculate in the Transform ribbon and select Pivot as the Source. We'll need two more columns to hold the results of our calculations, so click the Add button twice.
Step 7. On the first NULL column, click on the settings icon. Change the Type to Custom expression.
Step 8. Now we can define the exact calculation we need. In this case, it's as simple as:
Step 9. Click OK. Now define the the other NULL column:
[Cost of Goods]*[Quantity]
Once finished, make sure you change the names of the Calculated columns to Product Revenue and Product Cost.
Note: These names need to exactly match the Dimension Member names, so we can target the correct Dimension.
Step 10. Finally, we don't need to republish the Cost of Goods, Price, and Quantity columns, so we can select those three and click the Remove icon.
Step 11. Now that our Calculation is defined, we need to unpivot our data so it matches the format of the other tables. Select the Source as Calculate, and check both Product Revenue and Product Cost to unpivot. Choose Metric.Metric for the Dimension.
Step 12. Now we can publish the data back to our Assumption model. Select Assumption for the Target, and Unpivot for the Source. The Source and Target columns should automatically line up as the two tables are structured the same way.
Step 13. Deploy the Application again, and then check the results. Both the Product Revenue and Product Cost Forms should be fully populated with data. Check the calculation manually with the data from the input Forms to make sure everything is working as expected.
Now all that's left to do is define the calculations for the Net Income Form and our App will be complete.