Kepion supports the following two types of Rules: MDX and SQL.
MDX Rules are essentially MDX Scripts deployed with the Model to an OLAP cube. All valid MDX Script syntax is supported.
For ease of maintenance, we recommend creating a single MDX Rule that contains all your MDX scripts. This allows for easy versioning and ordering of MDX scripts from a single point of maintenance. We also recommend using Microsoft’s SQL Server Business Intelligence Development Studio (BIDS) to develop MDX script logic. After building and testing the scripts from BIDS, you can copy and paste the Script to the Rule to auto-deploy whenever the Application is deployed.
MDX Rules are useful when you have calculations using the Account, Time, and Scenario Dimensions, for instance. For example, you may have a variance calculation that compares two scenarios. MDX Rules can compute results on the fly for what-if scenarios.
SQL Rules run on the Application database. They allow business users to easily run calculations at the database level, while being flexible and robust enough for technical users to optimize configuration.
Skillset required to implement:
- Intermediate understanding of T-SQL with good understanding of relational concepts.
- Familiarity with SQL Server Management Studio and or/ Microsoft SQL Server Business Intelligence Development Studio.
SQL Rules have the following benefits:
- Fast response time for complex calculations.
- Leverage full power of the SQL platform using T-SQL and all that is capable within stored procedures.
- Easier to develop and maintain SQL based rules versus MDX rules.
- Capable of cross Model and Dimension updates.
To start working with Rules, expand your target Model and select Rules.
You should land on a page like this:
Create a Rule. Use the Rule Type drop-down to configure the Rule as MDX or SQL.
Select a Rule and click Remove to delete it.
|Move Up / Move Down||
Select a Rule and click Move Up or Move Down to change the deployment order.
|Import||Import a Rule (JSON file) from your files.|
Select a Rule and click Export to export the Rule as a JSON file.
|Deploy||Deploy the Application.|
Note: To learn how to configure a Rule, refer to this article.
Name of Rule.
|Group||Enter a name for a Rule Group, which helps organize the right menu.|
SQL or MDX
|Active||Check to make the Rule Active, and un-check to deactivate the Rule.|
The last user to modify the Rule.
|Modified||The last date and time the Rule was modified.|
|Restore||Restore the Rule to previous versions.|
The Definition tab contains the Rule's MDX or SQL Script in our Code Editor. You can write, edit, copy/paste your Script into the Editor.
Tip: To learn more about the Code Editor, refer to this article.
To make any changes, you need to select Edit.
Reverse any changes made after selecting Edit.
Save changes and checks the Rule.
|Undo / Redo||Undo or Redo the last change made to the Script.|
You should work on your script in Authoring mode. Publish (Read-only) shows how your Script will look when deployed.
The images below illustrate how the variable Current Month changes depending on the display mode (Authoring vs. Publish (Read-only)).
Note: MDX Rules will have only the Definition tab. SQL Rules have the Parameter, Process, and Advanced tabs as well.
The Parameter tab allows you to manage parameters necessary for context-driven Rule execution.
Add: Add a parameter.
Remove: Select a parameter and click Remove to delete it.
Move Up / Move Down: Select a parameter and click Move Up or Move Down to change its order in the parameter list.
Name of parameter
Value assigned to parameter
Parameter syntax you can reference in Scripts
|Type||The parameter type (i.e., Float, String, or Member Picker|
Enable to hide the parameter
Edit any of the above fields by selecting
Tip: To learn more about using parameters with Rules, refer to this article.
The Process tab allows processing of OLAP objects after the SQL Rule has been executed. This can be useful for scenarios such as creating a Rule to either add, remove, or update Dimension Members.
Add: Add a process.
Remove: Select a process and click Remove to delete it.
Process OLAP database: Processes all Data Partitions, Dimensions, and Hierarchies in the Application so that all data will be up-to-date. When the rule updates a large number of Dimensions/Hierarchies and MOLAP Partitions, we recommend using this action instead of processing each Hierarchy and Partition individually.
Name of parameter.
|Type||The process type (i.e., Process Add, Process Update, Process Full).|
Tip: To learn more about process an OLAP database with a SQL Rule, refer to this article.
The Advanced tab allows the Modeler to configure the Rule behavior.
|Apply calculated results to||
Determines where the system will apply the results of a Rule execution. The possible values include:
|Command timeout (seconds)||Specify a timeout for the execution of the SQL rule.|
|Include form scope||An active Form's scope can be included as part of the parameters for Rule execution. This option is unchecked by default to optimize performance.|
|Include form selection||The selected cells on an active Form can be sent as part of the parameters for Rule execution. This option is unchecked by default to optimize performance.|
|Include selection facts||The context of current selected cells on an active Form can be sent as part of the parameters for Rule execution. This option is unchecked by default to optimize performance. Refer to this article for use cases involving this option.|
|Deploy Security||Check this option when the Rule makes security changes (e.g., updating user permission, adding or deleting users, etc.) that requires a security deployment.|
|Deploy to OLAP||Simulates full Application deployment from the Modeler.|
|Track Progress||Enables spRegisterProgress procedures that report Rule progress. Refer to this article for use cases involving this option.|
|Update form filters||Enable if the rule updates Form Filter selection.|
If the Rule updates an Application's standard variables, and the variables are used in MDX Rules, we would need to deploy those cubes in order to push the changes through. Select the target Models from the list.