Submit a request

Rule Editor Overview

Avatar
Kepion Support
  • Updated

Introduction

Kepion's Rule Editor allows users to create powerful relational Data Rules for all your business planning scenarios. In this overview, we will explain the many functions and options the Rule Editor provides. 

 

 

Before you Start

Navigate to the Data Rules section by hovering over Modeler and selecting the Application of your choice.

 

navigation_bar.png

 

Select All Data Rules.

 

alldatarulesrightmenu.PNG

 

 

All Data Rules

All Data Rules allows Modelers to manage all of their Data Rules. 

 

Note: Before adding a Data Rule, ensure you know what name and type (Action or View) you want as you can't edit them. VIEW Rules generate SQL Views, which are virtual tables. Views are dynamic and update automatically as the data changes. ACTION Rules, on the other hand, are updated on demand and allow you to insert or delete the records in a partition.

 

Actions

alldatarulesribbon.PNG

 

Action Function
Add Create a Data Rule. For more details, refer to Session 11 - Building Data Rules or Part 9 - Data Rules.
Remove* Delete a Data Rule.
Move Up* Move the Data Rule up the list.
Move Down* Move the Data Rule down the list.
Import Import a Data Rule from an .xml file.
Export* Export a Data Rule as an .xml file.

*You must first select the checkbox to the left of the Data Rule(s) and then click the desired action. 

 

Data Rule list

alldatarulesrulelist.PNG

 

Field Function
Data Rule Name of the Data Rule.
Group Create a folder to organize the right menu by entering a name (e.g., 0-Source Data).
Type The Data Rule Type (Action or View Rule).
Modified By Last user to modify the Data Rule.
Modified Last date and time the Data Rule was modified. 
Restore Revert the Data Rule back to a previous version. 

 

 

Rule Editor

When you open a Data Rule, you should land on the Rule Editor. This is your tool to design your Rule.

 

To make any changes to the Rule, you need to go into Edit mode by selecting Edit.svg Edit. 

 

edit.png


While Action Rules and View Rules share most of their components, they do differ in a few important ways. Let's take a look at how the Components and Management Actions are set up for each Rule type. 

 

Components and Management (View Rule)

componentribbon.png

 

Data Source

Action Function
Fact Use a fact table (i.e., Model partition) in your calculation. 
Dimension Use a Dimension Hierarchy in your calculation. 
Link Use Transform components from the Data Rules above the current one in your Data Rule list. 

 

Note: The target data source must be in your Application for you to use it in a Data Rule. 

 

Transform

Action Function
Pivot Moves unique values from one column to multiple columns.
Unpivot Perform Pivot's reverse operation.
Join Joins columns from one or more result sets together.
Union Joins rows from one or more result sets together.
Calculate Manipulates the columns of a result set. This step is useful when adding or removing columns to create the core rule logic.

 

Publish

Action Function
Model Publish results to a Model.
View Publish results to a SQL View. 

 

Manage

Action Function
Validate Validate by each component used in your Data Rule.
Refresh Refresh the workspace. 

 

Components and Management (Action Rule)

actioncompononentribbon.png

 

Action

Action Function
Delete Delete records from fact tables in the current Application.
Insert Insert records into fact tables in the current Application.
SQL Allows you to add a free-form SQL query. Refer to the Example tab for sample queries.

 

Publish

Action Function
Procedure Create a stored procedure. 

 

Manage

Action Function
Run Test actions used in your component. 

 

Tip: To learn more about the individual components, refer to our Rule Editor Components guide. 

 

 

Definition

Components will populate the left panel as you add them to your Data Rule. You can view them while in the Definition tab. 

 

  • Drag any components to re-order within the group (Data Source, Transform, Publish, and Action).
  • Double-click the component or click edit.png to rename it. 

 

definition.PNG

 

Tip: Name components clearly for better organization and knowledge sharing.

 

  • Right-click a component for further actions: 

 

rightclick.PNG

 

    • Remove: Remove the component.
    • Duplicate: Creates a copy of the component.
    • Disable: Disable the component (only Action components)
    • Find All References: Find all components that depend on selected component.
    • Find All Dependencies: Find all components on which selected component depends.

 

 

Settings

You can manage parameters and variables in the Settings tab. The list contains the name and default value of each parameter/variable.

 

Note: Parameters are valid only for Action Rules.

 

settings.PNG

 

Actions

Add.svg - Adds a parameter or variable. 

Remove.svg - Removes the selected parameter(s) and/or variable(s).

MoveUp.svg - Moves up the selected parameter(s) and/or variable(s).

MoveDown.svg - Moves down the selected parameter(s) and/or variable(s).

SettingsGear.svg - Edit an existing parameter or variable. 

 

Parameter (Action Rule Only)

Parameters will be used as input parameters for all published stored procedures in the Rule.

 

parameter.png

 

You will need to specify the parameter name and choose its data type from the following:

 

  • Member: One or more Hierarchy Member(s). The SQL data type is decided by the Dimension size and whether the parameter allows multi-select or not.

 

Dimension Size Small Medium Large
Single-select TINYINT SMALLINT INT
Multi-select tID32Set  tID32Set tID32Set

 

  • String: Its SQL data type is NVARCHAR(MAX).
  • FLOAT: Its SQL data type is FLOAT.
  • INT: Its SQL data type is INT.

 

You might have to specify a default value for the parameter. These default values will be used only for design and display purpose.

 

Note: Although SQL requires a parameter name to start with an at (@) sign, you don't need to do so in Kepion because the @ will be automatically added. Use @Name when you refer to the parameter in the Rule.

 

Variable

Use variables to hold data values of a specific type.

 

variable.png

 

Specify the variable name and choose its data type from the following:

 

  • Member
  • String
  • FLOAT
  • INT
  • SQL

 

You'll need to specify a value, Member, or definition for your variables. Unlike parameters, these values will persist in design mode and production mode. Use ${Name} when you refer to the variable in the Rule. All the Application variables can be referred to using the same syntax ${ApplicationVariableName}.

 

 

Workspace

The workspace changes slightly depending on the component, but it generally has the same format. You should see some or all of the following tabs:

 

  • Preview
  • Design
  • Column (if you can't define the columns in the Design tab) 
  • Scope
  • SQL

 

Preview

The Preview tab displays the results generated by your Data Rule. The Dimension columns are white, whereas the measure columns are blue.

 

preview.PNG

 

The Dimension columns display the Member Label by default. Select the Display drop-down to view by Member Name or MemberId instead. 

 

You can also use the filter within the column to scope down the display result:

 

filter.png

 

Note: These are cascading filters, which means the selection on the first filter causes the second to be limited to only the relevant values. Select the Clear Filter icon in the first column to remove filter selections. 

 

removefilter.png

 

The bottom of the tab shows:

  • Total row count in the result table.
  • Row count displayed in the preview window.
  • Duration of the refresh.

 

previewresults.PNG

 

Tip: This tab can be refreshed even if the Rule is not in Edit mode.

 

Design

The Design tab is the primary place where you configure your component. 

 

workspace.PNG

 

Fields

  • Source: Select a data source from the drop-down. 
  • Define component (e.g., Define columns): Use the actions to configure the component.
  • Select from available columns: Double-click from the list to quickly use available columns in your component.
  • DISTINCT: Skips duplicate records when enabled.
  • Remove zeros: Exclude any NULL or zero values from the result set.
  • Has annotation: Includes any annotations made. 
  • Notes: Enter your design notes and comments to this field. Anything typed here is only for your own reference and will not show up anywhere else.

 

Actions

Add.svg - Adds column to component. 

Remove.svg - Removes the selected column.

MoveUp.svg - Moves up the selected column.

MoveDown.svg - Moves down the selected column.

Edit.svg - Edit the column. 

 

To edit a column:

1. Go to Advanced. 

 

columnadvanced.png

 

2. Select from the Aggregation drop-down.

3. Click OK to save.

 

Scope

The Scope tab is used to extract only those records that fulfill specified criteria on specific columns.

 

scope.PNG

 

You can specify the criteria in five ways:

 

  • Members: Only include selected Members in associated hierarchy.
  • Exclude members: Exclude selected Members from associated hierarchy.
  • Parameter: Only when the column value is equal to a parameter value.
  • Variable: Only when the column value is equal to a variable value.
  • SQL: Free-form SQL query; use the Example tab for reference.

 

scopetype.png

 

Note: All five types are available for Dimension columns, whereas only the latter three are applicable to measure columns.

 

For Dimension data sources, there is a Show All option. When checked, you will be able to put scope on the system-defined technical columns of the Dimension table.

 

showwall.png

 

SQL

The SQL tab displays the SQL query that is automatically generated from your design. This is a read-only tab.

 

sql.PNG

Related articles