Welcome to the fourth and last article in the MODLR Development series. In this article we’re going to develop a simple cube data load process.
For the data load processes we need to understand the following concepts:
- Dimensions and their order
- Accessing cube cells through dimension elements
- The difference between cell overwrite and cell increment
The easiest way of thinking about cubes (in my humble and very personal opinion) is that cubes are multidimensional tables.
To understand this concept better, let’s visualise a one, two, and three dimensional cubes.
This is an example of a one dimension cube. The arrow represents a dimension, while the boxes represent five values stored in cells in the cube against the five elements in that dimension.
To access each value, we need to provide a reference, which in this case it just one element. For example, Value(element1) returns “100”.
Now, there are two dimensions and a lot more cells. We have a table of 5x3, which results in 15 cells (hence multiplication).
To get a value from the cube, we now need to provide two elements as references: Value (element1, element3) returns “102”.
Since you’re looking at this page on a screen and there are only two axes to deal with, the visual part becomes tricky, but imagine a Rubik’s cube as a visual cue for the next image.
In this case, the third dimension adds two additional tables behind the visible table. So now we have 15x3=45 available cells. Referencing cells now requires three elements: Value (element1, element1, element1) returns “100”.
MODLR cube list provides the ordered list of dimensions in each cube:
For example, there are six dimensions in the Exchange Rate cube:
- Rate Measures
The dimension order is important, because that’s how values are referenced in MODLR:
Value (1. Scenario, 2. Year, 3. Month, 4. Location, 5. Currency, 6. Rate Measures).
For example, Value(Actual, 2018, January, Australia, AUD, Rate) would result in “1”.
Last but not least there are two ways to update cube cell values using MODLR processes:
- cube.set(value, cubeName, e1 - eN) - resets the value cell to the provided string or numeric value.
- cube.increment(value, cubeName, e1 - eN) - gets the current cell value and increments it with the provided numeric value.
In the previous articles we’ve had a look at creating dimensions, but now we need to create a cube. For this tutorial, we need a simple cube with 5 dimensions:
- Geography (the one we created in the third article)
- SKU (the one we created in the second article)
Creating the first two dimensions is going to be manual:
In the right hand menu, click “New Dimension”
And then change the dimension, use standard dimension type:
Then type element names into the “Edit Hierarchy”, then click “save”.
Then repeat the process for the month dimension, but not for the measures dimension.
Once the dimensions are created and populated, it is time to create the cube. MODLR will automatically create a measures dimension for the cube.
Then check that the cube has been correctly created:
Now since the Measures dimension was created automatically, it does not have any elements. Using the same process as for the Years dimension, we can add two new elements:
- Numeric Value
- [S]String Value
Note that the “[S]” portion makes the element a string measure. At this stage, all the dimensions are populated and the cube looks something like this:
Now we can create a new process to populate cube values. As usual we’re going to define the process variables outside of the predefined functions:
varcubeName = 'Tutorial Cube';
The following code will put three values into the same cube cell (which is defined by the intersection of all elements provided in the order of the cube dimensions);
cube.set(3, cubeName, '2018', 'January', '1', 'Australia', 'Numeric Value'); cube.set(2, cubeName, '2018', 'January', '1', 'Australia', 'Numeric Value'); cube.set(1, cubeName, '2018', 'January', '1', 'Australia', 'Numeric Value');
The result of this operation will be 1, since the function sets the cube cell value to the provided value:
While this is fine for one value into one cell loads, this method won’t work for any transactional data source (many transactions form a single balance). In this case we need to use the cube.increment function.
Firstly, let’s reset the value using the cube.set function and then increment the value to get the final balance:
cube.set(0, cubeName, '2018', 'January', '1', 'Australia', 'Numeric Value'); cube.increment(1, cubeName, '2018', 'January', '1', 'Australia', 'Numeric Value'); cube.increment(10, cubeName, '2018', 'January', '1', 'Australia', 'Numeric Value'); cube.increment(100, cubeName, '2018', 'January', '1', 'Australia', 'Numeric Value');
The final value is 1+10+100=111:
Upload the attached file to the MODLR data store and then use it as the data source.
When loading CSV files into MODLR, all numeric values are presented as numbers.
For example, SKU should be text, but this is converted into 1.000 (float). This requires some handling later.
For this one, let’s create a new process called “Cube Load From Datastore”. As per our recommendations, let’s define some variables that we’ll use in the process:
var cubeName = 'Tutorial Cube'; var year = '2017'; var month = 'January'; var measure = 'Numeric Value';
In the pre function, we need to call the cube.wipe to make sure nothing is doubling up when the load is executed:
Then in the data function we code the following (converting everything into the right data type):
var sku = parseInt(record['sku'])+''; var geography = record['geography']+''; var value = parseFloat(record['value']); cube.increment(value, cubeName,year,month,sku,geography,measure);
Once loaded we can check that the data is in the cube: