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.
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.