Tutorial 2 - Advanced Model and View Configuration


1. Data Modeling and View Configuration Refresher

In Tutorial 1, you learned how to make changes to the data model and create some basic views. Watch this video to get an idea of what you'll accomplish at the end of this tutorial. Please note, this video is based on an older version of LogicBlox.

We begin this tutorial by refreshing your memory on how to extend the data model with new measures and how to make changes to the view configurations. You are going to create new working plan ("WP") measures and update the Working Plan view from tutorial 1 to show these new measures instead of the TY measures.

Before you get started with your exercises, make sure that you restore the application to the base version of this tutorial, by running the following command:

$ ./tools/restore-base tutorial-2

Tip

Note that your answers from the previous exercises will be overwritten. If you would like to keep them for later reference, make sure that you make a local copy, before running the script

1.1. Adding New Measures

Let's start by adding the new measures. Add the following lines to <TRAINING_HOME>/src/config/Measures.csv:

SalesRetail_R_WP_SKSTMO,Sales $ WP,SkuStoreMonth,decimal,0d,total,ratio,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""$"",""thousandSeparated"":true,""mantissa"":2}",right,,,,,
SalesUnits_U_WP_SKSTMO,Sales Units WP,SkuStoreMonth,int,0,total,ratio,,,,"{""thousandSeparated"":true,""mantissa"":2}",right,,,,,
Margin_R_WP_SKSTMO,Gross Margin $ WP,SkuStoreMonth,decimal,0d,total,ratio,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""$"",""thousandSeparated"":true,""mantissa"":2}",right,,,,,
MarginPct_P_WP_SKSTMO,Gross Margin % WP,SkuStoreMonth,decimal,0d,average,replicate,,,,"{""thousandSeparated"":true,""mantissa"":1,""output"":""percent""}",right,,,,,
COGS_R_WP_SKSTMO,Cost of Goods Sold WP,SkuStoreMonth,decimal,0d,total,ratio,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""$"",""thousandSeparated"":true,""mantissa"":2}",right,,,,,
AvgSellingPrc_R_WP_SKSTMO,Average Selling Price WP,SkuStoreMonth,decimal,0d,average,replicate,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""$"",""thousandSeparated"":true,""mantissa"":2}",right,,,,,

These measures all follow the guidelines for creating the measures in the section called “Naming Conventions”. They are relatively straight-forward:

  • SalesRetail_R_WP_SKSTMO, SalesUnits_U_WP_SKSTMO and COGS_R_WP_SKSTMO are working plan version of SalesRetail_R_TY_SKSTMO, SalesUnits_U_TY_SKSTMO and COGS_R_TY_SKSTMO respectively.
  • Margin_R_WP_SKSTMO, MarginPct_P_WP_SKSTMO and AvgSellingPrc_R_WP_SKSTMO are the working plan versions of the Gross Margin, Gross Margin Percent and Average Selling Price measures which we have provided you with in the base configuration of this tutorial..

Once you have built your application running the commands below, validate via an Ad Hoc Canvas that all the measures have been added correctly.

$ make clean
$ lb config
$ make

1.2. Configuring Views

Now that we have all of our working plan measures defined, let's update the bottom view of the "Working Plan" canvas, wp-data-bottom-sheet under <TRAINING_HOME>/src/config/views/main/sheets, such, that it displays our new measures instead of the TY measures:

  • SalesRetail_R_WP_SKSTMO
  • SalesUnits_U_WP_SKSTMO
  • Margin_R_WP_SKSTMO
  • MarginPct_P_WP_SKSTMO
  • COGS_R_WP_SKSTMO
  • AvgSellingPrc_R_WP_SKSTMO

Tip

If you can't recall how to specify measures to be displayed on a view, you should modify the "z" entry, under "pivotConfig" : { "axis" : { ... }:

{
  "z": [
    {
      "qualifiedName": "SalesRetail_R_WP_SKSTMO"
    },{
      "qualifiedName": "SalesUnits_U_WP_SKSTMO"
    },{
      "qualifiedName": "Margin_R_WP_SKSTMO"
    },{
      "qualifiedName": "MarginPct_P_WP_SKSTMO"
    },{
      "qualifiedName": "COGS_R_WP_SKSTMO"
    },{
      "qualifiedName": "AvgSellingPrc_R_WP_SKSTMO"
    }
  ]
}

Next, modify the roll-up levels such that the only roll-up shown on the screen is to Product:ALL.

Tip

Need a little help with this? Remove the roll-up level Location:ALL from the "x" entry, under "pivotConfig" : { "axis" : { ... }.

Finally, if any of the headers are not all fully visible, make the necessary adjustments to the height and width of the column/row headers.

Re-load the UI and validate that your view looks as expected and similar to the figure below.

2. Advanced View Configuration

Hopefully the previous lesson reminded you a bit about the basic configurations you learned in Tutorial 1. In this lesson, you will learn some more advanced view configuration options that are frequently used in real applications.

2.1. Restricting Measures and Levels in the Configuration Panel

By default, all measures, dimensions and levels are listed both in the Configuration Panel (that is available in configuration mode). If you switch in any of our current views to Configuration Mode (available from the settings icon on the upper right corner of a view), you'll see that all measures defined in Measures.csv are listed. This is not always desirable, for a couple of reasons:

  • Not all measures are relevant for a particular view. The configuration changes that an end user makes from a view might benefit from a restricted list of measures.
  • Not all measures are meant for end users to see at all. Frequently application configurations contain measures that are useful for development, but not so useful for end users. In fact, they might be confusing to the end users if shown.

The same counts for dimensions and levels.

We can restrict the measures shown by configuring a displayableMeasures object in either a canvas or a sheet configuration JSON.

Tip

Note that the sheet configuration precedes the canvas configuration!

We can use the keywords exclude and include to indicate whether the measures listed should be included in the list of available measures that the user can add to the view or not. For instance, to restrict measures displayed in the configuration panel for the top view in Working Plan to exactly the two measures already shown, we need to add an entry displayableMeasures under <TRAINING_HOME>/src/config/views/main/sheets/wp-data-top-sheet.json at the same level as the id, title, pivotConfig objects:

"displayableMeasures": {
  "include": true,
  "measures": [
    "SalesRetail_R_SP_CIYE",
    "SalesUnits_U_SP_CIYE"
  ]
}

Reload your UI configuration. From the top view of Working Plan. Go into the Configuration Mode. In the configuration panel, you should only see two measures, as shown below:

Can you restrict the measures shown in the bottom view of Working Plan to measures of WP version?

Tip

Uncertain what to do? Modify <TRAINING_HOME>/src/config/views/main/sheets/wp-data-bottom-sheet.json by adding a new displayableMeasures object as follows:

"displayableMeasures": {
  "include": true,
  "measures": [
    "SalesRetail_R_WP_SKSTMO",
    "SalesUnits_U_WP_SKSTMO",
    "Margin_R_WP_SKSTMO",
    "MarginPct_P_WP_SKSTMO",
    "COGS_R_WP_SKSTMO",
    "AvgSellingPrc_R_WP_SKSTMO"
  ]
}

Measures can be restricted for all views in a canvas, by specifying the displayableMeasures object in the canvas JSON. Let's restrict the measures shown in the dimension browser from the SKU Maintenance canvas, by adding a displayableMeasures object to <TRAINING_HOME>/src/config/views/main/canvas/sku-maintenance_canvas.json:

"displayableMeasures": {
  "include": true,
  "measures": [
    "SkuDescription_S_SK",
    "SkuCost_C_TY_SK",
    "SkuRetail_R_TY_SK",
    "SkuMarkdownPct_P_TY_SK",
    "SkuFirstSold_SK_SK",
    "SkuBestSeller_B_SK",
    "SkuPhoto_S_SK"
  ]
}

Re-load the UI and validate that your changes are correct and only the measures that you have explicitly listed as displayableMeasures are available to the users.

In a very similar way we can also configure which levels/dimensions should be available to users to add onto the views. We can configure this using the dimensionsConfig at either the canvas or the sheet level. Just like with displayableMeasures, the configuration at the sheet level precedes the configuration at the canvas level.

Let's configure our SKU Maintenance canvas such that the dimensions Calendar and Location are not available to the user. From the Product dimension, only the level Sku should be available. We can do this by adding the following object to <TRAINING_HOME>/src/config/views/main/canvas/sku-maintenance_canvas.json:

"dimensionsConfig": [
    {
      "dimension": "Product",
      "include": true,
      "levels": ["Sku"]
    },
    {
      "dimension": "Calendar",
      "include": false
    },
    {
      "dimension": "Location",
      "include": false
    }
]

In this configuration, we specify that both the Location and Calendar should be excluded. Additionally we specify that from the Product dimension only the level Sku should be included.

Re-load the UI and validate that your changes are correct and only the level Product:Sku is available to the user in the configuration panel.

Diving deeper

Modeler also supports application-wide displayable measures and dimensions configurations. This can be accomplished by creating a settings.json file in the <TRAINING_HOME>/src/config/views/main directory with a properly formatted displayable measures and/or dimensions configuration JSON.

Example 1. 

For example, the JSON below will restrict all views in the application to only the measures of the SKU Maintenance canvas and will only show the Sku level in the Product dimension.

{
    "displayableMeasures": {
        "include": true,
        "measures": [
            "SkuDescription_S_SK",
            "SkuCost_C_TY_SK",
            "SkuRetail_R_TY_SK",
            "SkuMarkdownPct_P_TY_SK",
            "SkuFirstSold_SK_SK",
            "SkuBestSeller_B_SK",
            "SkuPhoto_S_SK"
        ]
    },
    "dimensionsConfig": [
        {
            "dimension": "Product",
            "include": true,
            "levels": ["Sku"]
        }
    ]
}

2.2. Hiding Panels

Usually, you want to allow your users to make changes to the levels or measures that are being displayed on the various axis. There are situations, though, when you would like to restrict the activities that they can perform. For these situations, you may want to hide the column, row and measures axis panels (or even slice sometimes), such that the user can only perform limited configuration changes.

Let's change the top view of the Working Plan canvas, to hide Rows, Columns, and Measures. The following screenshot shows our goal:

To accomplish this task, we need to remove the entries for the rows and columns panels in "views" of <TRAINING_HOME>/src/config/views/main/sheets/wp-data-top-sheet.json. This prevents the panels from showing up in the view. Make the necessary changes, so that your views object looks as follows:

"views": {
    "slices": {
        "module": "Slices"
    },
    "grid": {
        "module": "PivotGrid"
    }
}

Re-load the UI and validate that your view looks similar to the screenshot shown above.

Note

Disabling axis panels via the view configuration doesn’t prevent the user from displaying them manually using the Layout menu in the toolbar of the sheet:

2.3. Set Slice Selection Using Signals

Let's take a look at what happens when we change the selected value in a Slice. In the top view of Working Plan canvas, go ahead and change the selected value for Year. What is the selected value for Year on the bottom view? You should have noticed that changing the selected value of Year on the top view does not change the selected value of Year on the bottom view. Often, it is desirable for these selections to be synchronized. You can configure the synchronization of selected values in slices through the UI as an end user or pre-configure it through the view JSON.

Slices can be synchronized with one another by emitting so called signals, and catching and reacting to signals. To synchronize a slice A with another B, we can configure slice A to emit a signal when its selection changes, and configure slice B to take that signal as an input, and change itself based on that signal. The end-user configuration options will always synchronize two (or more) slices to each other, such that when slice A changes, slice B will change, and vice versa. Using the JSON configuration, we can additionally configure slice B to react to slice A but not vice versa.

Let's synchronize the Year selection on the two views of the Working Plan by configuring a signals object in both the top and bottom views of the Working Plan. Add the snippet below in bold both to the wp-data-top-sheet and the wp-data-bottom-sheet:

"signals": {
  "output": [
    {
      "dataType": "POSITIONS",
      "emitterId": "Slice@Calendar:Year",
      "eventName": "Slice Change",
      "name": "Sync Slice@Calendar:Year"
    }
  ],
  "input": [
    {
      "handler": "changeSliceSignalHandler",
      "listenerId": "Slice@Calendar:Year",
      "signalName": "Sync Slice@Calendar:Year"
    }
  ]
}

Let's take a closer look at what we are doing here exactly:

  • The signals section of the configuration consists of two parts:
    • output describes signals that components send on the specified events.
    • input describes listeners that handle incoming signals.
  • Our eventName is called Slice Change, and the emitterId is in this case Slice@Calendar:Year. This means that a change to the Calendar:Year level on the slice should send out a signal.
  • We gave our signal the name Sync Slice@Calendar:Year. Note that this entry can be any string, we recommend to use a meaningful name which clearly indicates which level is used for the synchronization.
  • In the input section, the changeSliceSignalHandler is listening to the listenerId "Slice@Calendar:Year", thus the Calendar:Year level on the slice, for the signal with the name Sync Slice@Calendar:Year. This allows us to 'listen' to changes to the Calendar:Year level on the slice from other sheets that have been configured such that they emit a signal with the same name.

Re-load the UI and validate that updating the slice selection in the bottom view also updates the slice in the top view.

It's also possible to set an initial value for the slice selection using a measure. In our example, we are synching the Calendar:Year level on the slice with the slice value on another view, the data type of our measure therefore needs to be Calendar:Year. As there can only be one initial value for the year, this measure should not have a key. We refer to key-less measures as a scalar measure. As it is keyless, it should have no value for the Intersection column. If you take a look at the Measures.csv file, you can see that we actually already have a scalar measure of type Calendar:Year defined, namely the CurrentYear measure. Let's therefore use that measure as the initial value for the slice selection on our views. We can do this by simply adding the init property (displayed in bold) to the configuration of the output signal Slice@Calendar:Year.

"signals": {
   "output": [
     {
       "dataType": "POSITIONS",
       "emitterId": "Slice@Calendar:Year",
       "eventName": "Slice Change",
       "name": "Sync Slice@Calendar:Year",
       "init": {
          "value" : [{
             "qualifiedName": "Calendar:Year",
             "measure": "CurrentYear"
          }]
       }
     }
   ]
}

Re-load the UI and validate that the initial slice selection in the top and bottom view are equal to the current year.

Tip

Not sure if your solution is correct? You can look up the value of the CurrentYear predicate by running the query below:

$ lb print /modeler-training CurrentYear 

Also notice that, as you change the Year slice in the Working Plan canvas, it does not affect the Year slice selection in the canvas TY Data. This is because we have not configured slice synchronization for the TY Data view. This behavior can be configured by adding the same signals object in the view JSON of the TY Data canvas.

Tip

You can configure slice synchronization in the UI via the context menu of slice fields, as illustrated in the figure below:

2.4. Mask Filters

Mask Filters can be used to restrict the level members shown on a view. For instance, on a view for planning for the future you may want to only show future years when the level Year is added onto an axis. We learn how to use a mask filter to accomplish exactly this for the views on the Working Plan canvas.

A mask filter is specified using a CubiQL expression. Because we haven't covered CubiQL in this tutorial yet, we'll keep this CubiQL expression as simple as possible by using on a mask filter measure that holds all the positions that should be shown on a view. If a level member appears in a tuple of the mask filter measure, then it is included in the filter. Otherwise, it is excluded. For instance, we can define a measure CurrentOrFutureYear, keyed by Year only (add the following line in <TRAINING_HOME>/src/config/Measures.csv):

CurrentOrFutureYear,Current or Future Year,Year,boolean,,none,none,,,,,center,true,,DerivedAndStored,,

Derivation Type

We set the derivation type of this measure to DerivedAndStored because we are going to use a LogiQL rule to populate it. If we don't set a derivation type, LogicBlox will assume the derivation type of a measure to be Extensional which is commonly used for measures that receive their data via user input and/or TDX data imports.

As we don't have other measures defined with only Year in the key, we also need to update the configuration file for intersections. Add the following entry in <TRAINING_HOME>/src/config/Intersections.csv:

Year,0,Calendar,Year,

We need to also define some logic that populates this new measure. Create the file <TRAINING_HOME>/src/logiql/util/filters.logic, and add the following:


block(`filters) {

  clauses(`{

  CurrentOrFutureYear[year] = true <-
    util:model:current_or_future_years(year).

  })

} <-- .

Go ahead and rebuild the project entirely (starting from make), and load the test data. Once it is finished, check the content of CurrentOrFutureYear:

$ lb print /modeler-training CurrentOrFutureYear
[10000005106] "2018" true
[10000005117] "2019" true

You should see two rows printed: year 2018, and 2019. When we use this measure as a mask filter, we should expect to only see data for year 2018 and 2019, and not any other year (e.g. 2017).

To actually set the top and bottom views of Working Plan canvas to use this mask filter, you need to set the property maskFilter, under the pivotConfig object with a CubiQL expression that queries the data in the CurrentOrFutureYear measure:

"maskFilter": [
    "CurrentOrFutureYear"
]

Add the maskFilter section to the configuration for both the top and bottom views of Working Plan canvas. Reload your UI configuration and reload the view. What years do you see available in the slice drop-down? You should only see 2018 and 2019.

Note that, even though CurrentOrFutureYear is defined to have a boolean value, the actual value for keys 2018 and 2019 does not matter for filtering. It is the fact that the tuple with keys 2018 and 2019 are in the measure at all that matters. That means, we could have defined CurrentOrFuterYear to be position-only or to have a numeric value, or, we could set the value of 2018 and 2019 to be false, the filter would still work to include 2018 and 2019. Go ahead and try this out by modifying <TRAINING_HOME>/src/logiql/util/filters.logic, to contain the following rule instead:

CurrentOrFutureYear[year] = false <-
  util:model:current_or_future_years(year).

You now need to rebuild the project completely as we have modified logic. Once it is finished building, what is the content of CurrentOfFutureYear?

$ lb print /modeler-training CurrentOrFutureYear
[10000005106] "2018" false
[10000005117] "2019" false

Notice that it still contains two tuples, one for each year of 2018 and 2019. But the values are now false. Reload the canvas Working Plan. What years do you see in the drop-down? You should still see both 2018 and 2019.

Typically, we use a position-only measure for mask filters because it is less confusing that way. The above detour we just took illustrates a point of confusion that may arise when configurators accidentally set the mask filter measure to have a default value. Recall that a default value measure is completely populated for all keys in the key space. That is, if we had set CurrentOrFutureYear to have a default value (regardless of whether it is true or false), every single year would be in CurrentOrFutureYear, rendering it a useless filter.

Tip

The point of the exercise then, is to point out that you should never set a default value on a measure used for mask filter!

3. Advanced Configuration Options of the Data Model

In the following exercises you are going to learn more about the advanced configuration options of the data model, such as different ways to sort level members, defining composite aggregation and spreading methods as well as spreading by a measure.

3.1. Defining a Custom Sort

We have made a small change to the TDX service that we used in Tutorial 1 to import the Calendar hierarchy. Instead of the ID of the Months we are now importing the label. Open an Ad Hoc Canvas and add the Month level to one of the axis; you will notice that the members of level Month are sorted alphabetically by their label, which is the default sorting behavior for level members in Modeler Applications:

The default sort by label works for most levels, but there are many situations in which you would like to configure a different sorting method. For members of calendar levels, this clearly does not work, as April should not be sorted before December.

For levels that need to be sorted differently, we allow you to be specify an OrderAttribute, which is part of <TRAINING_HOME>/src/config/Levels.csv. Take a look at the specification for Month:

Level,Label,Dimension,ElementType,IsOrdered,OrderAttribute,OrderTransform,TransformedType,TransformDerivationType
...
Month,Month,Calendar,string,true,,,,
...

Notice that Month has the value true for the IsOrdered column? The true value indicates two things:

  • A sorting other than alphanumeric by label should be used for this level. To support this sorting, some extra relations are defined for this level. In the case of Month, the key relation for ordering is Calendar:Month_index, which maps each Month member to an integer index.

    Tip

    Go ahead and print out the content of Calendar:Month_index:

    $ lb print /modeler-training Calendar:Month_index
    [10000003907] "2017-01" 0
    [10000003920] "2017-04" 3
    [10000003921] "2017-03" 2
    [10000003922] "2017-06" 5
    [10000003923] "2017-05" 4
    [10000003926] "2017-02" 1
    [10000003928] "2017-12" 11
    [10000003929] "2017-11" 10
    [10000003930] "2018-02" 13
    [10000003931] "2018-01" 12
    [10000003932] "2017-08" 7
    [10000003933] "2017-07" 6
    [10000003934] "2017-10" 9
    [10000003935] "2017-09" 8
    ..

    Note that this measure is generated because the isOrdered column in the level configuration file is set to true.

  • By default, the index relation, e.g. Calendar:Month_index is computed by alphanumerically sorting the identifier of the level members. As you can see above, 2017-01 (which has the label 2017-Jan) has index 0, and 2017-02 has index 1, etc. This happens to work as a sort for our Month members (and this is frequently the case, where the identifier of members work for alphanumeric sorting, rather than labels). We can ask Modeler to use Calendar:Month_index for sorting in the views, by setting it as the value of the OrderAttribute column:
    Level,Label,Dimension,ElementType,IsOrdered,OrderAttribute,OrderTransform,TransformedType,TransformDerivationType
    ...
    Month,Month,Calendar,string,true,Calendar:Month_index,,,
    ...

Now that you have modified the order attribute, go ahead and rebuild the project.

$ make

Take a look at the Ad Hoc Canvas again by adding the Month level field on the rows. Notice that now our months are sorted according to the common expectation of months, e.g. January before February, etc.:

Under the Hood

You can take a look at the logic generated to populate Calendar:Month_index in <TRAINING_HOME>/build/srcgen/logiql/generated_schema/levels.logic. Notice also that a few more relations are defined for "ordered" levels such as Calendar:Month: Calendar:Month_first, Calendar:Month_next, Calendar:Month_last Calendar:Month_offset, Calendar:Month_lt2 (less-than comparison). These are all derived from Calendar:Month_index. They can come in handy in business logic that involves comparing or iterating over elements of time.

See also

The documentation on how to configure order attributes can be found in the section on configuring levels in the Modeler Configuration Guide.

Providing Your Own Sort Function

While sorting by identifiers of Month conveniently worked for us, this is not always the case. For instance, take a look at the content of Location:Store:label:

$ lb print /modeler-training Location:Store:label
[10000005200] "store-4"  "store-4"
[10000005201] "store-3"  "store-3"
[10000005206] "store-13" "store-13"
[10000005207] "store-1"  "store-1" 

Notice that neither the identifier nor the label of Store lend itself to proper sorting: e.g. store-13 would be sorted before store-3 alphanumerically, when in fact, we want store-13 to be sorted after store-3 (and store-4).

We can accomplish this by providing our own sorting function. Take a look at <TRAINING_HOME>/src/logiql/util/ordering.logic, and the definition of store_index:

store_index[store] = index <-
  Location:Store:id[store] = storeid,
  string:split[storeid, "-", 1] = id,
  string:int:convert[id] = index.

This rule says that, split the store id by "-", convert the second value after the split into an integer, and use that as an index. What this effectively does, is to extract the number that follows store-, and use the number for indexing. Take a look at the content of util:ordering:store_index:

$ lb print /modeler-training util:ordering:store_index
[10000005200] "store-4"  4
[10000005201] "store-3"  3
[10000005206] "store-13" 13
[10000005207] "store-1"  1 

Note that the index is exactly as we expect, and will allow store-13 to be sorted ahead of any store store-n, where n is smaller than 13

We can now use this relation as our OrderAttribute, in <TRAINING_HOME>/src/config/Levels.csv:

Level,Label,Dimension,ElementType,IsOrdered,OrderAttribute,OrderTransform,TransformedType,TransformDerivationType
Store,Store,Location,string,false,util:ordering:store_index,,

Rebuild the application using make, and take a look at how Store members are displayed in the grid, using an Ad Hoc Canvas. Notice that the store headers are sorted as we expect:

Using Custom Sort for Ordered Levels

In the previous example, we defined a custom sort for Location:Store by parsing its id. However, we did not define Location:Store as an ordered level, which means it does not get ordering relations generated for it, e.g. Location:Store_index, Location:Store_first, etc.

You may indeed want to configure Location:Store as an ordered level and get these automatically defined relations with it. You also want to make sure that the ordering uses your custom ordering function. We can accomplish this using the last three columns of <TRAINING_HOME>/src/config/Levels.csv:

Level,Label,Dimension,ElementType,IsOrdered,OrderAttribute,OrderTransform,TransformedType,TransformDerivationType
Store,Store,Location,string,true,Location:Store_index,util:ordering:store_index,int,DerivedAndStored

Note the values in bold in the code sample from above:

  • We have set IsOrdered value to true, and we use the auto-generated relation Location:Store_index as the OrderAttribute
  • We specify a value for OrderTransform: this should be a function that take a value of the level Location:Store, and returns something that can be used for alphanumeric sort. In order case, the value returned is an integer, which is why we specify TransformedType to be int. Additionally, we set the TransformDerivationType value to DerivedAndStored which indicates that the function gets its values from a LogiQL rule.

After rebuilding the application (you need to rebuild it completely, starting from make, as the (generated) data model changed), take a look at the contents of Location:Store_index: it should be exactly as you would expect from util:ordering:store_index.

3.2. Composite Aggregations

When defining an aggregation or spreading method in previous lessons, the same method is used to aggregate for all the keys of a measure. For certain measures, this is not sufficient. For instance, let us consider a measure that holds the available stock per SKU, Store, Day. The available stock for a product Class for a given City and Day, can be the total aggregation across all SKU's of the Class, and all Store's of the City, for that Day. However, the value of available stock for a given SKU, Store, Month cannot be the total aggregation for all Day's in that Month. Instead, it should be the available stock for the last Day of the Month.

In these situations, we need a Composite Aggregation, where we specify the method of aggregation used for each key separately: e.g. total for keys Product and Location, but last for Calendar. Composite aggregations need a name, so we can refer to them when configuring measures. Let's call ours EOP. Define EOP by making the following lines the content of <TRAINING_HOME>/src/config/CompositeAggs.csv:

Name,Order,AggMethod,Key
EOP,1,last,Calendar
EOP,2,total,Product
EOP,3,total,Location

These lines define the composite aggregation EOP. It says that the order of aggregation should be to aggregate along the key for Calendar first, using the method last. last, along with first, are special aggregation methods that are available for levels that are ordered (i.e. have IsOrdered value of true in <TRAINING_HOME>/src/config/Levels.csv). The last aggregation uses a mapping that maps only the last Day of a Month to that month. This relationship is represented by Calendar:Day:month_last. You can check out the contents of this relation to get a better sense of what Day can be mapped to what Month:

$ lb print /modeler-training Calendar:Day:month_last
[10000004015] "20170131" [10000005158] "2017-01"
[10000004034] "20170228" [10000005166] "2017-02"
[10000004067] "20170331" [10000005161] "2017-03"
[10000004097] "20170430" [10000005160] "2017-04"
[10000004134] "20170531" [10000005163] "2017-05"
[10000004172] "20170630" [10000005162] "2017-06"
..

Notice that there is exactly one mapping per Month, and it is always the last Day of the Month, as defined by the ordering of Calendar:Day_index. What this means is that, when aggregating along Calendar using the last method, only values of the last Day is aggregated to the Month level, and only value of the last Month is aggregated to the Year level.

Tip

You can get an idea of what first aggregation method does by inspecting the content of Calendar:Day:month_first and Calendar:Month:year_first.

Let's also define a composite spreading method to be used together with this composite aggregation. Modify the content of <TRAINING_HOME>/src/config/CompositeSpreads.csv to be as follows:

Name,Order,SpreadMethod,Key
EOP,1,last,Calendar
EOP,2,ratio,Product
EOP,3,ratio,Location

Let's now create a measure for Available Stock, which will use this composite aggregation. Add the following line to your <TRAINING_HOME>/src/config/Measures.csv:

AvlStockOnHand_U_TY_SKSTDY,Available Stock on Hand,SkuStoreDay,int,0,EOP,EOP,,,,"{""thousandSeparated"":true,""mantissa"":0}",right,,,,,

Note that we have specified EOP as the default aggregation and spread methods.

You also need to add new entries to the intersection configuration file:

SkuStoreDay,0,Product,Sku
SkuStoreDay,1,Location,Store
SkuStoreDay,2,Calendar,Day

See also

The documentation on how to configure composite aggregations can be found in the section on configuring the data model in the Modeler Configuration Guide. Respectively, composite spreads documentation can be found here.

We have prepared some sample data to test out our new measure and composite aggregation and spread. Once you have added the new measure and configured the composite aggregation, make sure to uncomment the the import of the ty_avlstock_skstdy.csv file in the master.wf workflow, so that your workflow master.import_measures looks as follows:

workflow master.import_measures(app_prefix,location,timeout)[] {
  master.TdxImport(
    app_prefix=$app_prefix,
    timeout=$timeout,
    transport="http://localhost:55183",
    full=true,
    input={
      "{
         service: '/$(app_prefix)/delim-file/measures/sku_properties'
         file:    '$(location)/measures/sku_properties.csv'
          error:   '$(location)/error/sku_properties.csv'
       }",
      "{
         service: '/$(app_prefix)/delim-file/measures/today'
         file:    '$(location)/measures/today.csv'
         error:   '$(location)/error/today.csv'
       }",
        "{
         service: '/$(app_prefix)/delim-file/measures/ty_data_skstmo'
         file:    '$(location)/measures/ty_data_skstmo.csv'
         error:   '$(location)/error/ty_data_skstmo.csv'
       }",
        "{
         service: '/$(app_prefix)/delim-file/measures/ty_avlstock_skstdy'
         file:    '$(location)/measures/ty_avlstock_skstdy.csv'
         error:   '$(location)/error/ty_avlstock_skstdy.csv'
       }"
    }
  )
}

Testing out Composite Aggregations

Let's now rebuild the project (starting from make) and view via an Ad Hoc Canvas the behavior of the composite aggregation and spreading methods:

The screenshot above shows the Available Stock on Hand measure, for each Month, Quarter, and Year. As you can see, the value for 2017-Q1, is exactly the value of the measure at 2017-Mar, i.e. the last Month of the Quarter. Similarly, the value for year 2017 is exactly the value of 2017-Q4, the last Quarter of the Year.

Go ahead and add product Subclass to the view, with aggregation up to Class:

You can verify that the quarterly value for each product Class is exactly the total of the value for each Subclass for that Class. For instance, the value of Fruit for 2017-Q1, is the total over Apples and Citrus.

3.3. Spread By Measure

We've so far discussed spreading using ratio, even, replicate, or defining a composite spreading method that uses different spreading for each key of a measure. Another method of spreading commonly occurring is to spread using values of another measure. For instance, whenever the value of Sales $ is edited, we may want to spread the value proportionally based on the value of Gross Margin %, rather than using the values of existing Sales $.

Let's change our SalesRetail_R_WP_SKSTMO measure to do exactly this. Replace the line in <TRAINING_HOME>/src/config/Measures.csv for SalesRetail_R_WP_SKSTMO with the following line:

SalesRetail_R_WP_SKSTMO,Sales $ WP,SkuStoreMonth,decimal,0d,total,ratio,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""$"",""thousandSeparated"":true,""mantissa"":2}",right,,MarginPct_P_WP_SKSTMO,,,

Notice that in the column for SpreadByMetric, we have put the value (in bold) MarginPct_P_WP_SKSTMO. This means we want to spread using the ratio of marginPct_P_WP_SKSTMO.

Refresh your measure meta model and let's give this a try. Using an Ad Hoc Canvas, create the following view:

Go ahead and populate some values for Gross Margin % WP the way they are populated in the screenshot below. Notice that for the subclass Root Vegetables, every Sku has the Gross Margin % WP of 50%. Let's now modify the value of Sales $ WP for Root Vegetables. You should see that the result is an evenly spread value to each Sku, because of the even margin percentages.

However, go ahead and update Gross Margin % WP to have different values, and update Sales $ WP again. Do you observe how Sales $ WP is spread? Is it according to the ratio of values in Gross Margin % WP?

Congratulations, you have completed Tutorial 2!