How to Setup a SQL Rule

By - Updated June 3, 2019

In Kepion we can setup T-SQL rules to be executed by the end-user. We can create these rules in the Kepion Modeler. In this article, we will go through the steps for creating the SQL rule and associating it with a Form.

How to Associate Existing Rules to Forms?

We can associate existing rules to forms in the same model. We’ll explain how to associate Top-down Update Rule to Top-down Form in the OPEX model.

Step 1. In Administrator Module, click the plan we want to work on, and then click Forms.

Step 2. Select the Top Down Form in the form list, click Rule in ribbon.

Step 3. In the pop-out window, check the rules we what to add, click OK.

Step 4. Check the On Post box if you wish the rule runs automatically when users Post the plan. Click Save on top right.

Now the Top-down update rule has been associated with Top-down form. It will run automatically whenever the user clicks Post in the Top-down form.

If we didn’t check On Post box in Step 4, the rule can only be run through operation in explore> expanded window.

Create a SQL Rule

If we are creating a new rule, then we will need to create it in the Kepion Modeler. Under the model that you want to create the rule in, you would select Add to add a new rule and select SQL as rule type. In SQL rules, we can filter records by filter selection. We’ll use the Assumption model in CPG Revenue model as an example to illustrate how to get filter values.

Step 1. Create a Rule in target model in Modeler Module. Select SQL as the rule type.

Step 2. Associate the rule to ‘Price’ in Administrator. Check the On Post box.

Step 3. In SQL Server, locate the stored procedure of the rule we created in the first step.

We can see a set of stored procedures named as ‘dbo.sp_R_{Model Name}_{Rule Name} under Revene of Consumer Package Goods -> Programmability -> Stored Procedure. They are the stored procedures of rules.

Step 4. Select the procedure. Click right button and click Modify.

Step 5. As shown in the screenshot below, uncomment the highlight queries. Then execute the rule.

Please ensure the commands are executed successfully.

Step 6. Go to Planning in Kepion. Make sure the new rule has been added to the form. Click Post to run the rule.

Step 7. In SQL Server, use the following script to see the filter values that have been captured by the rule.

For more details regarding to the System Parameters that can be captured by a rule, refer to P44 of Kepion Modeler Guide.

Step 8. Now we can use the filter values in rules.

Please note: In this example, we write queries in rule directly in Kepion UI. We don’t do this normally unless the rule is very simple. Generally, we would create a stored procedure to store the queries. Then we call this procedure in rule. Refer to the rules structure created in the sample application for details.

Step 9. We have already associate the rule to the form in Step 2. Now we can go to Planning in Kepion and run the rule directly.

Kepion also allows SQL rule to use user input parameters.  Please refer to Use Parameters in SQL Rule to see how.