Overview
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.
Transcript
[transcript]
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 define 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 measures as an additional dimension in the model.
The core measure group 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 scopes 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 the data rule editor, and a write back 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 two different storage modes available when configuring a partition: ROLAP, or relational OLAP, and MOLAP, or multidimensional OLAP.
MOLAP partitions are generally faster and better for working with static data, as the multidimensional 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 multidimensional 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 the reporting model, let's begin by disabling the write back partition. Since we're not going to input data directly into the P and L report, we don't need a write back 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 the storage mode to ROLAP, as the calculated results may change more frequently.
Let's deploy the application, as structural changes were applied.
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_ followed by the model name, the measure group, and then the partition. For example, F_Assumption_CoreMG_Writeback.
Let's examine the write back partition of our assumption model.
Here are all the records we created in the previous session. We can filter the data, delete it, or 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.
Here, we can enable the show data errors checkbox to 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 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 three of our series. In part four, we'll configure the business rules and calculations that will drive the data in our P & L report.
[/transcript]