Overview 12b
In the second part of session 12, we’ll walk through the steps to publish an ACTION Rule.
Transcript 12b
[transcript]
In the second part of session 12, we’ll walk through the steps to publish an ACTION Rule.
At the top, you may have noticed there’s an additional section of components available in ACTION Rules.
These allows us to DELETE the old values in the Model and INSERT new ones. Or attach any custom SQL.
Let’s select a DELETE Action and then choose the Reporting Model (Calculated) partition as the target. This will delete all records in the partition. In a real project, we would likely need to scope this action down to just the records affected by this Rule, however as this is the only Rule affecting the Calculated Partition, we can simply delete all the old records and re-insert the new ones, every time.
The Preview Tab doesn’t show any records as the Calculated Partition is currently empty.
Now let’s select an INSERT Action. Again, we’ll choose the Calculated Partition from the Reporting Model as the target. For the source, we’ll select the records from the last step of our calculation.
Recall that with VIEW Rules, the results of the calculation are automatically updated as the data changes in real-time, but with ACTION Rules, we need to manually decide when to execute the Rule. If we’re in the Rule Editor, we can execute the Rule by selecting Run at the top, however, we need a way to reference this Rule outside the Rule Editor, so that we can update the records as the data changes.
To do this, let’s add a new Procedure. We’ll name it ‘Revenue and COGS’. We can see on the right that this creates a Stored Procedure we can execute called [spR_Publish_Revenue_and_COGS]. Now we have to define which ACTIONS the Stored Procedure will execute from our Rule, and in which order. Let’s choose the DELETE and INSERT Actions we defined, and then save the Rule.
Now whenever we execute this SQL statement, our calculation will run. To execute raw SQL in Kepion, we can add a SQL Rule Component to a Model. This component will serve as the hook to execute our Data Rule in the Application.
To do this, let’s navigate to the Assumption Model and create a new SQL Rule. We’ll name it ‘Calculate Revenue and COGS’. Here we can enter raw SQL to be executed. For now, let’s just add the one statement we need to call the Stored Procedure.
Now we need to decide when to execute this script. We could tie it to a button in our Dashboard and execute it manually, but this just adds another step to the workflow. As we want to run the rule every time the data changes, let’s instead tie it directly to the Post Action. That way whenever users post new data, the calculated results get immediately refreshed.
To configure this, let’s move to the Administrator Module. Then select the Dashboard App and navigate to the Rule tab. Here we can associate a script with a page in our Dashboard. Let’s select Base Assumptions and choose add. Here’s the script we just added to the model. Let’s check it, and click okay. Now we can run this script in the Dashboard by right-clicking on the associated Form.
Checking the on-post option here will execute the script whenever users select the Post Action for this Dashboard page, which is exactly the behavior we want. Hide will remove the Rule from the right-click menu, preventing users from executing the script manually. Let’s check this as well, and then save.
In our Dashboard App, if we trigger the Post Action by clicking the Update Button, our SQL Script should automatically execute in the background. We can confirm by navigating to the P&L Report. It looks like our Gross Sales Before Returns and Cost of Goods Sold have both been calculated correctly!
In the last part of session 12, we’ll layer in parameters, which greatly improve the performance of Action Rules.
[/transcript]
Next Steps
- Session 12a- Calculating Revenue and COGS
- Session 12b- Calculating Revenue and COGS
- Session 12c - Calculating Revenue and COGS