Use Multiple Measure Groups

Author: Joyce Zhou

Introduction

In Kepion, the CoreMG Measure Group is generated by default when the Model is created. It contains all the Model Dimensions. However, you also have the flexibility to create more Measure Groups in the Model.

 

 

Multiple Measure Groups can be used to provide a central repository of the following types of data for easy management and configuration purpose:

1. Actual Data: All Actual Partitions in Planning and Reporting Models are MOLAP views generated from this Actual Model.
2. Reporting Data: Used as the central point for users to connect to from Pivot Tables. In this way we can report the data in whatever way the users would like.
3. Archive Data: Used to store Archive Data so that it won’t be impacted even when the Planning/Reporting Model has to be changed on the go.
4. Valid Relationship Data: So it's the only Model that needs to be linked when valid relationships are needed in other Planning/Reporting Models.

 

In any of these scenarios, each of the Measure Groups is defined by the Dimensions it needs. The Model Dimensions is the full set of Dimensions of all Measure Groups.

 

Note: All the Planning Models should stick to using CoreMG (i.e., the default) Measure Group since the Writeback Partition is defined in CoreMG.

 

 

Configure Measure Group

In this article, let's create a Measure Group called REV-VPC with three measures to store product Volume, Price, and Cost values.

Step 1. Click the first Add button in the Ribbon to create a new Measure Group. Name the Measure Group in the pop-out window and click Save. Then you’ll need to click the Save button in top-right to confirm the creation of the Measure Group.

 

 

Step 2. By default, the new Measure Group will have all the Model Dimensions. You can modify the Dimensions as needed using the Remove and the second Add button in the ribbon. In our example, we will only need the Entity, Product, and Time Dimensions.

 

 

Here is the result we get:

 

 

Step 3. Next, let’s continue on to the MEASURE tab to define the Measures. Each Measure Group can contain as many Measures as needed.

 

 

Click the Add button in the ribbon to add a new Measure. Give the Measure a name, and select its Data Type and Aggregation method. Select the Measure Group the Measure belongs to from the drop-down, then click OK.

 

 

Repeat this step to add Base Price and Base Cost measures.

 

 

 

Create Partition

Now we've created the REV-VPC Measure Group with three measures. Next, we can define partitions based on the Measure Group.

 

 

Let’s move to the Data node in the same Model.  Click Add  to add a new partition. Name the partition and select the Measure Group and the Storage Mode. Click Save.

 

 

This is the partition we defined:

 

 

In the relational database, there's no difference between CoreMG partitions and user-defined partitions.

 

 

 

Add Data

Let’s insert some records into this table and then take a look at how we can access these records in both a Form and Pivot Table.

 

 

In this example, we'll put the measures on the Column. Use of Measures in a Form is the same as Dimensions.

 

 

We define the Measures along the Column.

 

 

And this is the Form:

 

 

User-defined Measure Groups and partition data are also available in Pivot Tables. You can use the Show fields drop-down to narrow down by Measure Groups.