Overview
In the second part of session 12, we’ll walk through the steps to publish an ACTION Rule.
Transcript
[transcript]
In the second part of session twelve, 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 allow us to delete the old values in a 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 reinsert 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 in 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 are 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 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 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 SQL statements 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 the 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 here's the script we just added to the model. Let's check it and click add.
Run on update is automatically enabled and will execute the script whenever users select the update option for this dashboard page, which is exactly the behavior we want.
Run on demand lets users manually execute the script from the right click menu whenever needed. Let's keep this unchecked to prevent users from running the script manually, and then save.
In our dashboard page, if we click the update button, our SQL script should automatically execute in the background.
We can confirm this by navigating to the P and 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 twelve, 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