In this session, we’ll examine the way Model data is stored inside Kepion. We’ll begin by looking at Measure Groups. Then we’ll examine separating our data into partitions, and the benefits of different storage modes such as ROLAP and MOLAP. And finally, we’ll examine methods of importing and managing data in the Application.
For more information on supported features in SQL Standard, click here.
In this session, we’ll examine the way Model data is stored inside Kepion and the different options we have to enter, manage, and configure that data for best performance.
We’ll begin by looking at Measure Groups and the way we can scope the dimensionality of the data in our Model. Then we’ll look at separating our data into partitions, and the benefits of different storage modes such as ROLAP and MOLAP. And finally, we’ll examine various methods of importing and managing data in the Application.
When it comes to managing data, there are two main components to be aware of: Measure Groups and Partitions.
Measure Groups are a collection of Measures, and the way we consolidate the data within a particular Model. To explain Measures, let’s revisit the structure of a Model. A Model is defined by a number of Dimensions. By default, all Models also contain a single Core Measure Group, which contains the entire Dimensionality of the Model.
If we were to select Dimension Members for each of these Dimensions, at their intersection we would find a value. Every unique combination of Dimension Members would hold a different Value. The collection of these Values is a Measure.
In this way, you can think of a Measure as an additional Dimension in the Model. The CoreMG always contains a single Measure called Value. If we wanted, we could define additional Measure Groups.
For example, we could create a Measure Group called Assumption that scoped the data down to just these three dimensions. Then we could define different Measures to view the values we’re interested in, for various Reporting purposes.
In Kepion, we can manage our Measures and Measure Groups here.
The data within a Measure Group can be further organized by Partitions. By default, every Model comes with two partitions already configured: A Rule partition, which is only used to store data generated by Data Rule Editor, and a Writeback Partition, which stores user-entered data from Input Forms.
In Kepion, you’re not limited by the number of additional partitions you can add to a Model, however certain versions of SQL may impose limits. See the documentation linked below for more information.
Additionally, there are three different Storage Modes we can choose from when configuring a Partition: ROLAP, or Relational OLAP; MOLAP, or Multidimensional OLAP; or HOLAP, a Hybrid of these two options.
MOLAP partitions are generally faster and better for working with static data, as the Multi-Dimensional view is cached. However, if the underlying data is changed, the cache needs to be refreshed before the changes are reflected in the Model.
ROLAP partitions are generally slower, as the multi-dimensional views are generated dynamically, as needed. But they’re better for working with dynamic data, as the data is pulled directly from the Relational Tables.
Let’s try configuring some partitions. In Reporting Model, let’s begin by disabling the Writeback Partition. Since we’re not going to input data directly into the P&L Report, we don’t need a Writeback Partition in this Model. Instead, let’s add a new Partition. We’ll call it Actual.
As Actuals tend to be static records, let’s change the storage mode to MOLAP, to improve performance.
Then let’s create a second partition to hold all the calculations that will populate the report. We can call this one Calculated. And we’ll set to the storage mode to ROLAP, as the calculated results may change more frequently.
Now that our partitions are configured, let’s take a look at the various methods we have for getting data into our Application. We can post data directly from the Form Editor, enter data through a Form in a Dashboard App, import data in bulk from .csv files, write SQL queries to insert directly into the Fact Tables, or create SSIS packages to bring in data from other systems.
In the database, all Fact Tables are named F underscore the Model Name, followed by the Measure Group, and then the Partition. For example, F underscore Assumption - CoreMG – Writeback.
Let’s examine the writeback partition of our Assumption Model. Here are all the records we created in the previous session. We can filter the data or delete it. We can also export it to a csv file.
Let’s import some historic data into our Actual partition. A sample data file is provided below the video. In this view we can investigate any warnings or errors we may have. Now we have all the data we need for our Application.
In this session we talked about Measure Groups and the way we can scope the dimensionality of the data in our Model. Then we looked at separating our data into partitions, and the benefits of different storage modes such as ROLAP and MOLAP. And finally, we looked at how to manage the data in our Application, and imported a few years of Actuals.
This concludes Part III of our series. In Part IV we’ll configure the business rules and calculations that will drive the data in our P&L Report.