Use of Multiple Measures
By Joyce Zhou - Feb 17, 2015
In Kepion, the CoreMG measure group is generated by default when the model is created. It contains all the model dimensions. Kepion also gives you the flexibility to create more measure groups in the model.
We see multiple measure groups can be used to provide a central repository of the following types of data for easy management and configuration purpose:
- Actual Data: All actual partitions in planning and reporting models are MOLAP views generated from this actual model.
- Reporting Data: Used as the central point for users to connect to from Pivot Tables. In this way we can massage the data in whatever way the users would like.
- Archive Data: Used to store Archive data so that it won’t be impacted even though planning/reporting model has to be changed on the go.
- Valid Relationship Data: So it is the only model that needs to be linked when valid relationships are needed in other planning/reporting model
In any of these scenarios, each of the measure group is defined by the dimensions it needs. The model dimensions is the full set of dimensions of all measure groups.
Please note, all the planning model should stick to CoreMG (i.e. default) measure group since the Writeback partition is defined on CoreMG.
In this article, we will create a measure Group called REV-VPC with three measures to store product Volume, Price, and Cost values. Click the first Add button in the Ribbon to create a Measure Group. Name the new Measure Group in the pop-out window and click Save.
Then you’ll need to click the Save button on top right to confirm the creation of the Measure Group. 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 Entity, Product, and Time dimensions.
Here is the result we get.
Next, let’s continue 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 Dropdown. Then Click OK.
Repeat this step to add Base Price and Base Cost measures.
Now we have created the REV-VPC measure group with three measures. Then we define partitions based on the measure group.
Let’s move to the Partition section 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 relational database, there is no difference between CoreMG partitions and user-defined partition.
Let’s insert some records into this table. Let’s see how to access these records from a Kepion form. In this example, we put the measures on the Column. Use of the Measures in the 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 also use the Show fields dropdown to narrow down by measure groups.