Modeler Guide - Part 4 – Kepion Support Center

Model Concept

A Model is an object that holds data that have common characteristics. For instance, you may have a Model that stores data relating to forecasts and have another that stores budgets. But what are those characteristics that would make one Model different from the next? We will see that an important method of differentiating Models is by their use of Model Dimensions. We will explore this further in the Model Dimension section.

Create a Model

To create a new Model, select the All Models node under the Designer pane and click on the Add button. Provide the Model with a name and optionally, select an existing Model to copy from which creates a new Model with the same structure as the source Model.

Rename a Model

Click the Rename button in the ribbon to rename a Model. You will need to select the Model you want to rename from the Model drop-down, and enter its new name in the Name field. Click Save to apply the change. Please note, renaming a Model will change its associated fact tables’ name, therefore may cause Views and Rules you created to break. You need to manually update the Views and Rules to make them work again.

Model Dimension

Model dimensions are used to specify how the data is organized within a Model. For instance a Model with three Model dimensions of Account, Time, and Scenario will have its data stored as follows:

Account	Time	Scenario	Value
Revenue Item 1	Jan-10	Forecast	1500
Revenue Item 1	Feb-10	Forecast	1500
Revenue Item 1	Mar-10	Forecast	3000
Revenue Item 1	Apr-10	Forecast	3000
Revenue Item 1	May-10	Forecast	3000
Revenue Item 1	Jun-10	Forecast	3000
Revenue Item 1	Jul-10	Forecast	3000
Revenue Item 1	Aug-10	Forecast	3000
Revenue Item 1	Sep-10	Forecast	4500
Revenue Item 1	Oct-10	Forecast	4500
Revenue Item 1	Nov-10	Forecast	4500
Revenue Item 1	Dec-10	Forecast	4500

Looking at the example above, we can see that the values are sliced by the members of each Model Dimension. It is important for a Model to have a well thought-out grouping of Model Dimensions. By defining your Model with Model Dimensions in mind, you define how data can be gathered, reported, and analyzed.

If you have data stored in a Model and you find yourself wondering what default values to use for Model Dimensions that do not relate directly to the data, then it’s a clear indication that you may want to separate that data out of the Model and perhaps create a separate Model that stores that data with only the relevant Model Dimensions defined.

Imagine yourself with a Pivot Table, how best do you want to organize that data so that you can easily slice and dice it to the view you want? Perhaps you may want multiple tables, and if so, that may indicate the need for multiple Models. Remember, the better the data is organized, the easier it will be to create Forms and Rules and gather data for planning input.

You can add Model Dimensions by selecting the Add button under the MODEL DIMENSION tab.

Once you add a dimension to the Model, you will need to specify the Member List (ML) hierarchy. The ML hierarchy is used to define the valid members that are available within the Model. Use the drop-down under Model Hierarchy to make your ML hierarchy selection. After configuring the Model Dimensions on the Model, click the SAVE button to save the changes to the server.

When all Models are configured and saved, you can click on the DEPLOY to OLAP button to generate the application database and then start Form and rule design.

Model Rules

Kepion supports the following two types of rules: MDX and SQL.

MDX Rule

MDX rules are essentially MDX Scripts that are deployed along with the Model to an OLAP cube. All valid MDX Script syntax is supported.

For ease of maintenance, it is recommended to create a single MDX rule with all the valid MDX scripts contained within. This allows for easy versioning and ordering of MDX scripts from a single point of maintenance.

It's also recommended to use Microsoft’s SQL Server Business Intelligence Development Studio (BIDS) to develop MDX script logic with a deployed application OLAP database. After building and testing the scripts from BIDS, you can copy and paste the MDX Script to the MDX rule for auto deployment each time the application is deployed to OLAP.

MDX rules are useful when you have calculations such as those on the Account, Time, and Scenario dimensions. For example, you may have a variance calculation that compares one scenario versus another. MDX rules can compute results on the fly for what-if scenarios.

SQL Rule

SQL rules can run on the application database. SQL rules are designed to allow any business user to easily run a calculation at the database level while being flexible enough for any technical user to configure.

Skill set 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 based 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.
Cross Model and dimension updates capable.

You can define a SQL rule on a Model by clicking on the Rules node and then clicking on the Add button.

Give the Rule a name and select the Rule Type as SQL.

You now have an empty Rule that you can begin to configure. We will see shortly how we can fill in the Definition area of a SQL rule as shown below.

Parameters

Parameters are surfaced to end users to allow for context driven Rule execution. You can create parameters by clicking on the Add button.

The available parameter types include: Float, String, and Member Picker.

Process

The PROCESS tab allows processing of OLAP objects after the SQL rule has executed. This can be useful for such scenarios as creating a Rule to either add, remove, or update Dimension Members.

Advanced

The ADVANCED options allow the Modeler to configure the behavior of the Rule.

Apply calculate results to

This option determines where the system will apply the results of a rule execution. The possible values include:

No action – Do not apply any additional processing on the results
User highlighted cells – Apply changes only on those cells that are selected
Current writeable cells – Apply changes to all visible writeable cells

Command timeout second(s)

You can specify a timeout in seconds for the execution of the SQL rule.

Include form scope

The current cells on an active form can be configured to be sent as part of the parameters for the rule execution. For performance consideration, this option is unchecked by default.

Include form selection

The current selected cells on an active form can be configured to be sent as part of the parameters for the rule execution. For performance consideration, this option is unchecked by default.

Include selection facts

The context of current selected cells on an active form can be configured to be sent as part of the parameters for the rule execution. For performance consideration, this option is unchecked by default. Examples of using this option can be found in this article.

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

This options simulates full application deployment from Modeler.

Track Changes

This options enables spRegisterProgress procedures that reports rule progress. Examples of using this option can be found in this article.

Update form filters

Check this option if the rule updates form filter selection.

Deploy MDX

If the rule updates application standard variables, and the variables are used as part of MDX rules in any cubes, we would need to deploy those cubes in order to push the changes through.

Rule Definition

The large white area is used to define the body of the Rule and should be done using valid T-SQL syntax for the SQL Rule. When a SQL Rule is saved, a stored procedure is created on the application database. The image below shows a typical SQL Rule as deployed to the application database:

System Parameters

The following parameters will always be populated when the stored procedure is executed by the system. Rule authors can use the pre-defined parameters as part of the rule logic to better design for business rule execution.

@UserID bigint: The User Id that invoked the Rule

@AuthorizationID bigint: The authorization context from which the Rule was invoked

@SubmissionID bigint: The submission id

@FilterScope dbo.tFilters READONLY: The current filter selections on the Form when the Rule was invoked

Dimension	Hierarchy	MemberId	Name
Geography	Geographies	1	SEA
Version	Versions	1	CURRENT

@Changelist [<table_type>] READONLY: The current changes in the change list when the Rule was invoked

AccountID	TimeID	ScenarioID	GeographyID	ProductID	VersionID	Value	Comment	Status
1	20090700	2	1	4	1	34500	NULL	NULL
1	20090800	2	1	4	1	34500	NULL	NULL
1	20090900	2	1	4	1	34500	NULL	NULL

@SelectionScope [<table_type>] READONLY: The current cell selections on the Form when the Rule was invoked. Please note, this parameter is only available when Include form selection option is checked in rule Advanced setting tab.

AccountID	TimeID	ScenarioID	GeographyID	ProductID	VersionID	Value	Comment	Status
1	20090700	2	1	4	1	34500	NULL	NULL
1	20090800	2	1	4	1	34500	NULL	NULL
1	20090900	2	1	4	1	34500	NULL	NULL

@Scope [<table_type>] READONLY: The current Form scope when the Rule was invoked. Please note, this parameter is only available when Include form scope option is checked in rule Advanced setting tab.

AccountID	TimeID	ScenarioID	GeographyID	ProductID	VersionID	Value	Comment	Status
1	20090700	2	1	4	1	NULL	NULL	NULL
1	20090800	2	1	4	1	NULL	NULL	NULL
1	20090900	2	1	4	1	NULL	NULL	NULL

Share Rules to App Users

Rules can be shared to App users by associating the Rule to one or more Forms within an App. Please refer to the Kepion Administrator Guide.

The image below is an example of what a SQL rule that is shared to the app user looks like.

Note: App users will need to belong to the Advanced Contributor role in order to select individual rules for execution.

Restoring a Rule

All Rules saved to the Model will contain a history of its previous definitions. These historic Rule definitions can be restored to the current version by clicking on the Restore action on a Rule under the Historic column.

The restore window allows you to choose a version of the Rule to restore from. By clicking on Restore, the Rule will be opened in MODELER in Edit mode. After reviewing the Rule, you can choose to save the Rule by clicking on the SAVE button from the MODELER.