Modeler Guide - Part 8 – Kepion Support Center

Model Advanced Settings

Measure Groups

By default the CoreMG measure group is available on all Models. However, for certain reporting scenarios additional measure groups may be created. Use the ADD button to add additional measure groups to the Model. When a new measure group is added, you can also define the Model Dimension that participates with the measure group:

Measures

By default, the Value measure is created on the CoreMG measure group. However, if new measure groups are defined on the Model, then additional measures can also be defined on them. Use the ADD button to create additional measures on a measure group.

The Add Measure window is used to specify the settings for the new measure to create. Multiple measures can be created on the same measure group.

Partitions

When a Model is created, a default partition is made available. All Models will have the Writeback partition but additional partitions can be created and removed based on design.

Select Partitions to view all the existing partitions within the Model.

To add a new partition, click on the ADD button.

There are two choices when adding a partition to the model.

Create a new partition by providing a name and specifying the measure group with which it will belong to.
Link an existing partition into the current model. Choose this option to reuse partitions throughout the application if the data is structured similarly in other Models, such as a source data model.

Click Save to finish.

If the partition will be used to store annotations, the Has Annotation property needs to be checked. When Disabled property is checked, data in the disabled partition will not be seen in any Kepion Forms nor any other reports that are connecting to this model. MOLAP partitions will have a Process button, which allows you to reload data stored in the Model’s partition tables into the Model’s cube on the OLAP database.

Partition – Manage Data

You can manage the data stored within your partition by using the Delete Data, Import, and Export functionality. To use Delete Data, apply a set of filters onto the columns to narrow down the scope of the delete. By clicking on the Delete Data button, the filtered records will then be cleared from the partition.

Importing Data to Model

You can load data into a partition through the use of a CSV file. To import data, click on the Import button:

The Import Data window can be used to browse to the CSV file that contains the data to load for the Model. When starting with a new partition, you can first choose to Export the data to file to get the basic structure of the CSV file. All member data stored in the CSV file should reference the dimension’s MemberLabel property.

Click the Browse button to locate the CSV file that contains the data for use in loading. The data will be validating upon selecting the file. Click on the Start button to begin the data load process.

Partition – General Settings

You can configure a partition to use another table or view by setting the Table or View option. The description setting is used to capture design comments for how the partition will be used.

Partition – Performance Tuning

When performance tuning a partition, you must first determine its storage mode. The storage mode controls how data is stored in the cube when the application is deployed to OLAP. Each Model comes with a partition table by default in the application’s SQL database named by the following convention:

“F_” + Model Name + “_CoreMG_Writeback”

The Writeback table is used to store volatile data, or to put it another way stores data that is meant to be updated and refreshed on a regular basis due to data updates from the planning process.

Knowing the type of data stored in these tables can lead to a better performance design when it comes to generating the model’s cube on the OLAP database. Before continuing, please read the following on the basic storage modes for SSAS

When a cube is generated to the OLAP database, the cube will have a measure group with a partition called Writeback. By default the Writeback partition is set to ROLAP storage. However, you can configure the OLAP storage mode to be different by specifying one of the following choices from the drop-down control on the Model module:

ROLAP (Relational Online Analytical Processing)
MOLAP (Multi-dimensional Online Analytical Processing)

Note: You can control the storage mode of any partition that is available in the model.

You can configure a partition with the following performance tuning properties:

Storage Mode: The options for storage mode include MOLAP and ROLAP.

Rolap Slice: For ROLAP storage mode, you can configure the OLAP slice property of a partition to indicate the type of data stored in this partition. Setting this property can boost OLAP query performance.

Notification Table: For ROLAP storage mode, you can specify a table, or a set of tables, delimited with commas (,) within the application database from which to listen for data changes. Any changes detected in those tables will cause this partition to automatically refresh.

Partition Mode: The partition mode can be specified if the Model uses a Time Dimension. The partition Model can be set to the following properties:

By Months: Auto generate additional partitions in OLAP with the data segmented by months.
By Years: Auto generate additional partitions on OLAP with the data segmented by years.

Partition Interval: This setting determines the time interval between each generated partition as specified in the Partition Mode.

Model Process

Use the Process action to reload data stored in the Model’s partition tables into the Model’s cube on the OLAP database. You can perform this action when in the module for All Models.

Linked Model

You can bring data from other Models into your Model by using the Linked Model feature. Linked Models allow for Forms and Rules to leverage data that already exist in another. Linked Models, however, do not bring any rules along with it; rather it is just a source of fact records.

Conceptually, when multiple Models are linked to one, you can create a single report that shows data from all the Linked Models. To access data from Linked Models, use different measures during the Form design. Each Linked Model will have its own measure named by the following syntax:

<model name>_Value

The image shows one model linked to a main model:

Drop-Downs

Drop-Downs are defined in a Model to allow for Forms to use a drop-down menu. A drop-down menu allows input to writeable cells via a drop-down control.

There are four different types of Drop-Downs:

Dynamic List
Member List
Hierarchy
Calendar

Dynamic List

Dynamic List drop-downs can be configured with different drop-down members based on current writeable cell selection, or to say it another way, the available members in the drop-down can be dependent on the currently selected account, time period, or any other Model Dimension as defined by the Drop-Down. Use the Dynamic List type for advanced drop-down scenarios.

Member List

The Member List drop-down can be configured with members from an existing Member List. Whenever this drop-down is used in a Form, users will only see members from the hierarchy that they have access to.

Hierarchy

The Hierarchy drop-down can be configured with members from an existing leveled hierarchy. Whenever this drop-down is used in a Form, users will only see members from the leveled hierarchy that they have access to.

Calendar

The Calendar drop-down uses a Calendar control to allow users to easily input by date.

Configure Drop-Downs

Let’s create a Drop-Down and configure it for use in a Form. Start by clicking the Add button for Drop-Down within the Forms module.

In this example, we create a Member List Drop-Down using Position hierarchy of Position dimension.

Then you can configure the Form to use the definition of the Drop-Down as part of a Form Drop-down by doing the following in the Form Designer.

Select the ADVANCED tab
Select the DROP-DOWN tab
Click on the Add button

In the dialog box,

Select the Drop-down type
Select the Model Dimension
Select the Member from the Model Dimension

When selecting the Model Dimension and its member, it is important to know that we are configuring the location on the form where you will use the drop-down. All cells that intersect the drop-down’s model dimension and member will attempt to display drop-down values when at the writeable cell level.

Looking at the example below, we have configured a drop-down on the Position member. Thus, whenever Position is available for input, it will pull from the model’s Drop-Down for a list of valid drop-down members.

If you are adding a dynamic list drop-down, you will need to configure the options for the drop-down list. To do this, you need to populate the table in the application database with the valid account to value combinations. This mapping will determine what values are shown when in a particular writeable cell. If we did not have the Drop-Down vary by any dimension, then all values in the table will appear when the drop-down is used on the Form.

The table name in the application database for Model Combination of type List is in the following format:

Map_<MODEL_NAME>_<ModelCombination_NAME>

This table will include all the related dimension member IDs, together with value, display, and order columns. Display is used for the friendly display shown in the drop-down. Value is used as the actual record that will be stored to the fact table. Note that we must use numbers to store the value within the fact table.

Dimension1ID	Dimension2ID	Value	Display	Other

If the drop-down is a Hierarchy or Hierarchy List type, you can also configure the drop-down with URL capability. You will need to define an attribute in the dimension with the target URL. Then, go to the Model Combination tab and click the gear button of the combination you need to modify.

Select the URL attribute from the Attribute for Link drop-down. Click OK and deploy the application. The drop-downs will display an icon that when clicked, will navigate you to the intended URL in a new browser tab.

Perspective

You can simplify your Kepion cubes by using the perspectives feature. Perspectives allow you to create a more user friendly interface for 3^rd party analysis tools, such as Excel PivotTables, when connecting to your model’s data. To use perspectives, select a model in Modeler, click on the PERSPECTIVE tab, and click the Add button.

The Perspective dialog window will appear. Select the items that you would like to show for your perspective, click OK, and be sure to save your application and deploy.

Now when connecting via Excel PivotTable, you can see the perspective.

And it will only show you the fields that you would like the users to see.