Tutorial 1 - Basic Model and View Configuration


1. Getting Started

This tutorial introduces you to the basic concepts involved in building a Modeler application. You will:

  • get an overview of the user interface features provided by Modeler components,
  • learn how to configure a basic data model used for simple data analysis and manipulation, and
  • learn the basic configuration options of Modeler UI components in order to provide your users with default views

We learn these concepts by building a small application together, which then becomes the basis for later tutorials.

Watch the following video to get an idea of what you'll accomplish at the end of this tutorial.

This tutorial tells you everything you need to know to successfully build the example application shown above.

See also

If you have the desire to learn a bit more about the overall architecture of LogicBlox applications, the tools in its ecosystem, LogicBlox's value proposition (e.g. why would you want to build on top of LogicBlox at all?), you may find the following links useful

1.1. Getting Oriented Around Modeler Applications

The base training application is a working Modeler application that already allows us to get a sense of the different features Modeler components offer. Figure 1, “UI components of a Modeler application” is a screenshot of one of the views in the training application. It illustrates the key components shared by all Modeler applications: a menu pane, a navigation pane, and a canvas pane.

Figure 1. UI components of a Modeler application

UI components of a Modeler application

  1. The menu pane contains a customizable logo on the left-hand side, and the name of the authenticated user on the right-hand side. To the left of the user display, there is:
    • a lock icon which is activated when the authenticated user has created cell locks and/or position locks.
    • an active user widget, which displays other users also viewing the application.
    The menu pane can be further customized to contain other buttons or links. We will not be covering how to do so in this tutorial.
  2. The navigation pane contains links to the main views within the application, displayed in the canvas pane. Navigation pane is always pre-configured per application, and can be a tree of arbitrary shape and depth. The user can hide or display the navigation pane using the hamburger icon located next to the logo on the menu pane. When enabled in the applications, users can also create their own user-defined sheets under My Views using the + button.
  3. The canvas pane displays the data analysis and manipulation views of the application. A canvas can contain one or more views, with flexible placement and sizing. There are currently three types of views distributed with Modeler:
    • Sheet View, which has four modes: Grid, Chart, Form, and JSON (for debugging sheet configurations).
    • Action Buttons, which is a panel of buttons each invoking a web service call
    • Shortcut Buttons, which is a panel of buttons, each applying configuration changes to some other view on the canvas.
    Most applications find these pre-distributed views sufficient. If needed, applications developers can develop their own custom views (React components). We do not discuss custom views in these tutorials.

1.2. Configuring Views as an End-User

In this tutorial, we focus on the configuration of the pivot table view and constructing canvases using them. We have prepared some videos that demonstrate the basic capabilities of the pivot table. Let's take a look at them and perform some exercises to get acquinted what you will be configuring.

After watching the video showing you the basics of pivoting, open up the SKU Maintenance canvas in the tutorial application:

  1. Can you configure the screen such that, we see SKU's across the column headers, and the measures down the rows, such that there is one row for each measure? See image below for the configuration we would like.
  2. Can you configure the screen such that we can see measures for only one SKU at a time? See image below for the configuration we would like.
  3. Can you configure the screen such that we see both SKU and Store on the screen?

    Tip

    To find the Store level, open configuration mode with the icon on the upper-right-side of the toolbar. Look up the level in the configuration panel and drag it to the rows axis.

  4. How about now showing ONLY the measures SKU Description, Sales $ TY and Sales Unit TY, for each SKU and Store?

    Tip

    To find the Sales $ TY and Sales Unit TY measures, use configuration mode again. Look up the measures in the configuration panel and drag them to the measures axis. Remove any measure you don’t want to be displayed from the grid by dragging them off the measures axis.
  5. As you can see, the SKU Description is actually the same for each Store. This is because the value of the measure is only keyed at the SKU level. The Sales $ TY and Sales Unit TY are keyed at the SKU, Store and Month levels which means their values may differ per Store (and even by Month or Year).

    Try to display the SKU Description as an attribute instead of a visible measure so that its value doesn't get repeated for each store.

1.3. Subtotals and Grand Totals

Video

Configuring Subtotals and Grand Totals

Please note, this video is based on an older version of LogicBlox.

  1. After watching the video, visit the SKU Maintenance canvas of the tutorial application again. If you followed the exercises above, you probably have a slightly different configuration than the application default. Restore to the default using the Reset State option which can be found under the cogwheel icon in the toolbar, or in the context menu that appears when you right-click in a cell. Any time you want to reset the state of a view back to the original configuration, “Reset State” is your friend! Any time you want to reset the state of a view back to the original configuration, "Reset State" is your friend!

  2. Can you configure the SKU Maintenance to show subtotals of measures at Subclass and Class levels? Your screen should be shown as follows.

    Tip

    Add the Subclass and Class level using configuration mode and choose to display their rollup values. Right-click on the level fields you just added to the rows axis to find the ‘Display Rollup’ option.

1.4. Sorting level members and measure values

Video

How to Sort either Level Members or Measure Values

Please note, this video is based on an older version of LogicBlox.

Let's now exercise the sorting options shown in the video.

  1. Starting from where we left off from the last exercise, can you sort the SKU alphabetically, in descending order? You can use the sort icon on the SKU field to accomplish this:

  2. Now, how about sorting the Cost of each SKU within their Subclass, in ascending order?

  3. Finally, place SKU in descending order according to their Cost, regardless of their Subclass.

    Tip

    For this one, you need to remove the subtotals to Subclass and Class on the screen. Having subtotals will always constrain the sorting within the hierarchical groupings.

2. Configuring the Data Model

If you built the application following the instructions in the previous lesson, or watched the videos, then you have seen that the training application contains a non-trivial data model, with many measures and dimensions that can be visualized using the pivot table. In this section, we learn how to configure such a data model.

Before proceeding with this section, please restore the training application to the base version of this tutorial. Go to the directory <TRAINING_HOME> and run the following command:

$ ./tools/restore-base tutorial-1

Build the application as follows:

$ make clean
$ lb config
$ make

2.1. A Brief Primer on Data Modeling for Modeler Applications

There are two key concepts in the data model of a Modeler application: measures, and levels. For instance, a common measure is Sales: a relation that contains values of sales, often keyed by the combination of SKU (Stock Keeping Unit, a common way of identifying products), Store, and Week. For instance, sales of toothpaste in store 17353, in the week of March 14, 2018, may be $279, and the sales of toothpaste in the same store, week of March 21, 2018, may be $350.

SKU, Store, and Week, are all levels. A level is a collection of discrete items used as keys for measures. Levels can be further grouped into dimensions, where levels of the same dimension represent conceptually related collections. For instance, Week, Month, and Year are all levels, and often grouped into the Calendar dimension together, as they are all collections that represent various discrete entities of in a calendar.

The combination of levels that form the keys of a measure, is called an Intersection. In our example, the intersection of the Sales measure is SKU,Store,Week.

Equipped with this basic understanding, we are ready to expand our data model to track more measurements of data!

See also

To learn more about measures, dimensions, and other related concepts, see the section on Measure Service in the LogicBlox Reference Manual.

2.2. Adding Levels and Dimension for Products

The base version of the training application doesn’t contain any pre-configured views yet. To explore the data model, we first create a new user-defined sheet called Ad Hoc Canvas using the + button next to ‘My Views’ in the navigation pane.

Figure 2. User-defined sheets

User-defined sheets

Open the configuration panel of your sheet and browse through the list of available dimensions. You will see that our application has two dimensions defined: Location, and Calendar. Each dimension has several levels in them: the Location dimension has levels Store, City etc.; the Calendar dimension has Day, Month, etc. These dimensions and levels are defined in <TRAINING_HOME>/src/config/Dimensions.csv and <TRAINING_HOME>/src/config/Levels.csv, respectively.

Let's take a look at <TRAINING_HOME>/src/config/Dimensions.csv:

Dimension,Label
//tutorial-1 base configuration
Location,Location
Calendar,Calendar

The first line (in bold) is the header, indicating that each data line that follows should contain two values separated by comma: the first value should be the unique identifier for a dimension, and the second value should be the label. The label does not need to be the same as the identifier, and is used for display in the configuration panel in the UI. It can contain spaces, unicode, or anything else that may make it more meaningful to the users.

Tip

As you can see in the code sample above, it is possible to add comments to the .csv files. Comments are indicated by two slashes //. You will notice in some files that we have added comments to mark entries that we have added as preparation for a tutorial.

Let's add a new dimension Product, so we can begin tracking measurements that require products in its keys. To do so, add the following line to <TRAINING_HOME>/src/config/Dimensions.csv:

Product,Product

Note that we are using the same label and identifier here. Please feel free to experiment with the label. But keep the identifier as Product, as the rest of the tutorial relies on this identifier.

Now that we have added the Product dimension, let's add some levels for it. Take a look at <TRAINING_HOME>/src/config/Levels.csv:

Level,Label,Dimension,ElementType,IsOrdered,OrderAttribute,OrderTransform,TransformedType,TransformDerivationType
//tutorial-1 base configuration
Store,Store,Location,string,false,,,,
City,City,Location,string,false,,,,
...

Similar to Dimensions.csv, this file has its first line (in bold) as headers, which tells us that the first value should be the level identifier, the second value the label, and the third value the dimension that the level belongs to. ElementType indicates the type of values used for the identifier. For the moment, we always use the string type.

Note

You probably have noticed that the last 4 columns of Levels.csv do not have values for several of the levels. They are used to specify levels that must be sorted in a custom way, rather than the default -- alphanumeric by label. The levels in Calendar dimension are prime examples where alphanumeric sorting by label would provide confusing displays (e.g. sorting February ahead of January). For this tutorial, we are not discussing how to configure such special sorts, as it is uncommon for non-Calendar levels.

Let us now add some levels for the Product dimension: Sku, Subclass, and Class. You can do so by adding the following lines to Levels.csv:

Sku,SKU,Product,string,false,,,,
Subclass,Subclass,Product,string,false,,,,
Class,Class,Product,string,false,,,,

Finally, we need to define a hierarchy within the Product dimension. You can do so by adding the following lines to Hierarchies.csv:

Default,Product,true,Sku,Subclass,0,false,false
Default,Product,true,Subclass,Class,1,false,false

The configuration of hierarchies is further explained in Section 2.4, “Displaying Measures at Aggregated Levels”.

When the data model changes, we need to rebuild our application:

$ make

If you point your browser to http://localhost:8086, create an Ad Hoc Canvas view and browse the configuration panel in your view, you should see a new dimension Product, along with the three new levels we just defined:

Populating the Product dimension with data

Before moving on, let's make sure that the levels in the Product dimension are populated with some data. There are 3 steps involved in populating an application with data:

  1. You need the data itself. We have prepared a small test dataset in the <TRAINING_HOME>/data/dev_data/initial folder. The test data for the Product dimension can be found in <TRAINING_HOME>/data/dev_data/initial/hierarchy/product.csv.
  2. You need to install a TDX (Tabular Data eXchange) service to import the data. All of the pre-defined TDX services for this application can be found under <TRAINING_HOME>/src/logiql/services/tdx. We have prepared all the services that are needed for this tutorial, you therefore do not need to make changes to any of these files.

    See also

    If you have not worked with TDX services before, you can learn more about them in the Data Exchange Services section of the LogicBlox Reference Manual.

  3. Finally, we need to actually call the service from the workflow that we use to import data (as part of running make). We have created all the workflow tasks that you need to run in this tutorial, but you will be asked from time to time to comment out or un-comment rows in the workflow to import an additional file or to prevent certain files from being imported. Our workflows can be found in the <TRAINING_HOME>/src/workflows folder. In this tutorial, you will only need to make changes to the master.wf file.

As all the components needed to import the data are in place already, all you need to do is to un-comment the rows in the workflow in which we call this service. Open the master.wf file under <TRAINING_HOME>/src/workflows and search for workflow master.import_hierarchy. This workflow contains tasks that call TDX services to import data into our various hierarchies. As you can see, the rows that reference the product.csv file and the TDX service to import the product hierarchy are commented out (as indicated by the 2 backslashes). Go ahead and uncomment these rows, such that master.import_hierarchy workflow looks as follows:

workflow master.import_hierarchy(app_prefix,location,timeout)[] {
  master.TdxImport(
    app_prefix=$app_prefix,
    timeout=$timeout,
    transport="http://localhost:55183",
    full=true,
    input={
    // Tutorial 1 - Lesson 2 - uncomment rows 41 - 45 to import product levels
       "{
         service: '/$(app_prefix)/delim-file/hierarchy/product'
         file:    '$(location)/hierarchy/product.csv'
         error:   '$(location)/error/product.csv'
       }",
       ...
    }
  )
}

There are still certain rows commented out. You are going to un-comment these later, once the required changes have been made to the data model. You need to rebuild the application after the workflow changes to cause the product data to be loaded, we will do this later in the tutorial, but feel free to rebuild already, if you want to just validate that the changes that you have made were correct!

To load the data, you can run the following command:

$ make load-test-data

You can verify that the data is loaded using your Ad Hoc Canvas, by placing Day level onto the grid. You should see something like the following screen:

Under the hood

Every level is represented by an entity in LogiQL. The build process reads the levels and dimensions information, and generates the LogiQL code needed to represent these entities. If you are interested, you can find the generated file in <TRAINING_HOME>/build/srcgen/logiql/generated_schema/levels.logic. Open up this file and take a closer look at its content. Notice that, for the Store level in the Location dimension, the following declarations were generated:

Location:Store(x), Location:Store:id(x:id) -> string(id).
Location:Store:label[x] = y -> Location:Store(x), string(y).

This is the general pattern used for all levels:

  • An entity named DimensionId:LevelId is declared, with a refmode DimensionId:LevelId:id. The type of the refmode is the ElementType specified in Levels.csv
  • A predicate DimensionId:LevelId:label is declared to store the label of each element in that level.

For the ordered level Calendar:Month, you'll notice that a handful of additional predicates were generated:

Calendar:Day(x), Calendar:Day:id(x:id) -> string(id).
Calendar:Day:label[x] = y -> Calendar:Day(x), string(y).
Calendar:Day_first[] = x -> Calendar:Day(x).
Calendar:Day_last[] = x -> Calendar:Day(x).
Calendar:Day_next[x] = y -> Calendar:Day(x), Calendar:Day(y).
Calendar:Day_index[x] = i -> Calendar:Day(x), int(i).
Calendar:Day_offset[x,y] = o -> Calendar:Day(x), Calendar:Day(y), int(o). 

These will come in very handy later in this tutorial, when we'll try to compare values over time.

You can go ahead and run some queries to take a look at the content of these predicates. They should correspond to what you see in the pivot grid, when you place a level onto the row or column axis. For example, to print all Calendar:Day entities, run the following command:

$ lb print /modeler-training Calendar:Day

You can also print the value of Calendar:Day_first, Calendar:Day_last, and Calendar:Day_next, which can give you an idea of the semantics of these predicates.

2.3. Adding Measures

Having defined levels of the Product dimension, we can now define some measures that are keyed by products, such as sales dollar amount, sales units, etc. As we described in Section 2.1, “A Brief Primer on Data Modeling for Modeler Applications”, a measure has a set of keys, called an intersection. Adding new measures thus means defining new intersections, if they do not already exist.

Configuring Intersections

Intersection configurations are stored in <TRAINING_HOME>/src/config/Intersections.csv. As you can see, that file is currently empty. Replace the existing file with the content below:

Intersection,Order,Dimension,Level
SkuStoreMonth,0,Product,Sku
SkuStoreMonth,1,Location,Store
SkuStoreMonth,2,Calendar,Month

Here, we are creating an intersection identified by the name SkuStoreMonth. The value for the Order column indicates in what position a level appears in the intersection. In this case, Sku is the first level, Store the second, and Month the last.

By convention, we always name the intersection by concatenating level names together in their order of appearance in the intersection, using camel case. This name is then used to configure measures in a different file. Following this convention, you would not need to refer back to Intersection.csv frequently to look up the name of the intersection you need for a measure.

Let's add another intersection, this time with only one level, Sku. This intersection allows us to define measures that are keyed by Sku only:

Sku,0,Product,Sku

Adding Measures

Measures are configured using <TRAINING_HOME>/src/config/Measures.csv. Do not be frightened by the sheer number of columns in this file. The first 4 columns are the only required columns, the rest are optional. We will only use a few of these options in this tutorial.

You can probably guess by now what the first three columns are being used for:

  • The value for Measure column is the identifier for a measure, used to refer to that measure in business logic or in view configurations. This value is not visible to the end users.
  • The Label value is used for display to end users, in the configuration panel or in pivot grid headers.
  • The intersection of a measure is specified via the Intersection column.

Another mandatory column in this file is the DataType column, where you specify the data type of the measure. The following data types are supported: string, int, decimal, float, boolean, or level types. Most of these types are self-explanatory, with the level types requiring a bit more explanation.

When a level is used as the type, the fully qualified name of the level must be specified. That is DimensionId:LevelId. For instance, if the type of a measure is the level Month, then it must be specified as Calendar:Month in the DataType column of Measures.csv

As we have covered all the mandatory columns, let's just start creating some measures. Below is a list of measures with various data types, such as strings, decimals, a measure of type boolean and a measure of type Calendar:Day. Add them to your <TRAINING_HOME>/src/config/Measures.csv:

SkuDescription_S_SK,SKU Description,Sku,string,,,,,,,,,,,,,
SkuCost_C_TY_SK,Cost,Sku,decimal,,,,,,,,,,,,,
SkuRetail_R_TY_SK,Retail,Sku,decimal,,,,,,,,,,,,,
SkuMarkdownPct_P_TY_SK,Markdown %,Sku,decimal,,,,,,,,,,,,,
SkuFirstSold_SK_SK,Date First Sold,Sku,Calendar:Day,,,,,,,,,,,,,
SkuBestSeller_B_SK,Best-selling SKU,Sku,boolean,,,,,,,,,,,,,
SkuPhoto_S_SK,Photo,Sku,string,,,,,,,,,,,,,
SkuLink_S_SK,Link,Sku,string,,,,,,,,,,,,,
SalesRetail_R_TY_SKSTMO,Sales $ TY,SkuStoreMonth,decimal,,,,,,,,,,,,,
SalesUnits_U_TY_SKSTMO,Sales Units TY,SkuStoreMonth,int,,,,,,,,,,,,,
COGS_R_TY_SKSTMO,Cost of Goods Sold TY,SkuStoreMonth,decimal,,,,,,,,,,,,,

Naming Conventions

You might be wondering why our measure names look so long and cryptic. Through experience, we have developed a useful naming convention that allow configuration consultants to easily identify various important properties of a measure without having to consult multiple configuration files. The convention is as follows: <METRIC>_<UOM>_<VERSION>_<INTERSECTION>.

  • METRIC is the name of a measurement that is meaningful to the application. E.g. Sales, or Cost
  • UOM is the unit of measure used for the measure. For instance, Sales can be measured in terms of dollar amount, or in units. Furthermore, dollar could be cost, or retail. We use R for retail dollar, C for cost dollar, U for unit, P for percentages, B for boolean, and S for string.
  • VERSION indicates whether measure values are actuals, planned, or approved. Common actual versions are TY ("This Year"), LY ("Last Year", time-shifted TY), LLY ("Last Last Year", time-shifted LY). Common planned versions are WP ("Working Plan"), or WI ("What If"). Common approved versions are OP ("Original Plan"), or CP ("Current Plan").
  • INTERSECTION indicates the intersection of the measure. Having the intersection directly in the name allows the keys of a measure to be easily identifiable, without having to look them up in the various configuration files. We frequently use abbreviations for intersections rather than using the full identifier. E.g., SkuStoreMonth is abbreviated to SKSTMO.

Using these conventions, the measure SalesRetail_R_TY_SKSTMO, for instance, is a measure for the actual sales retail values, measured in dollars, at the intersection SkuStoreMonth.

Tip

Naming conventions are exactly that: conventions. We have found our set of conventions to be useful in our development. We encourage you to use them if they fit your domain. If not, it is highly advisable that you develop a set of conventions that everyone on your team(s) follow.

Import Measure Data

Let's now modify the project to import some data into the measures we just defined. You will need to make changes to the master.import_measures workflow in <TRAINING_HOME>/src/workflows/master.wf, similar to what you did earlier, to populate the Product dimension. We have prepared the test data and the TDX services already, therefore all you need to do is un-comment the import of the sku_properties.csv and ty_data_skstmo.csv files. Once you have performed the changes, your workflow should look 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/ty_data_skstmo'
         file:    '$(location)/measures/ty_data_skstmo.csv'
         error:   '$(location)/error/ty_data_skstmo.csv'
       }",
       "{
         service: '/$(app_prefix)/delim-file/measures/today'
         file:    '$(location)/measures/today.csv'
         error:   '$(location)/error/today.csv'
       }"
    }
  )
}

Rebuild the application by running the commands below to incorporate the product dimension and measure changes made above:

$ make

Now that we have configured some new measures and loaded some test data, let's see them in our application! Point your browser to http://localhost:8086 and create an Ad Hoc Canvas. Add the new level SKU onto the rows; then add measure SKU Description onto the grid, by dragging it from the configuration panel to the measures axis. You should see a screen as follows:

Next, let's add the levels Store and Month onto the rows axis as well. Notice that it is the same SKU Description that is repeated for the same SKU. Now also add measure Sales $ TY and Sales Units TY to the screen. You should see a screen as follows:

If you tried to simply put a measure onto the grid without adding any levels to the axis (rows, columns, slice), you have probably noticed that there is no data displayed and the cells are grayed out. When the levels that are keys for a measure are not on the axis, an aggregation is required to show the measure's values. We have not yet specified for our measures how they should be aggregated. We are going to do exactly this in the next section.

Under the Hood

During build time, all the predicate declarations are generated from intersection and measure configuration files. You can find the source LogiQL of these generated predicate declarations in <TRAINING_HOME>/build/srcgen/logiql/generated_schema/measures.logic. You should find a close correspondence between the generated declarations and your measure configuration: a predicate using the name of the measure is generated, with keys indicated by the intersection.

2.4. Displaying Measures at Aggregated Levels

A common scenario in data analysis is to view various measures at not only their base intersections, but also aggregated ones. For instance, a user is not only interested in Sales for each SKU, Store, Month, but more likely, is also interested in Sales for each product Subclass or Class, City, and Year. In order to support this type of analysis, we need to configure two things: hierarchies, and default aggregation methods for measures.

Configuring Hierarchies

A hierarchy is a named sequence of levels from the same dimension along which a measure can be aggregated. For instance, by defining a hierarchy from Store to City to Country, a measure containing Store as a key can be viewed at the aggregated levels of City or Country.

Hierarchies are defined in <TRAINING_HOME>/src/config/Hierarchies.csv. Open up this file and you will see the following lines:

Hierarchy,Dimension,IsDefault,FromLevel,ToLevel,Index,HasFirst,HasLast
//tutorial-1 base configuration
Default,Location,true,Store,City,0,false,false
Default,Location,true,City,Country,1,false,false
...

As is the case with other configuration files we've seen, the first line contains column headers. The two non-header lines define a hierarchy named Default for the Location dimension. The hierarchy maps Store to City, and City to Country, as indicated by the values in the FromLevel and ToLevel columns. The Index column indicates the order in which these mappings take place. The hierarchy has the isDefault property is set to true, meaning that, if no specific hierarchy is specified in an aggregation request, this is the hierarchy used.

A hierarchy mapping is a many-to-one relation. For instance, in the Default Location hierarchy, each Store can only map to one City, and each City can only map to one Country. An implicit All level is included in every hierarchy definition, allowing measures to be viewed by aggregating away a key entirely. E.g., Sales can be viewed for all Location's, regardless of their Store, City, or Country.

Tip

By convention, we always name the default hierarchy of a dimension Default.

Let's take a look at the default hierarchy for our Product dimension which we've previously configured when we were adding the Product dimension to our application. Our three levels map to each other in the following order: the level Sku maps to Subclass which then maps to Class. We therefore needed to add two entries to the <TRAINING_HOME>/src/config/Hierarchies.csv file, one for each mapping:

Default,Product,true,Sku,Subclass,0,false,false
Default,Product,true,Subclass,Class,1,false,false

The levels of the Product hierarchy are not ordered, we therefore set both the HasFirst and HasLast columns to false, similar to how the levels in the Location hierarchy are configured. We will discuss the utility of these columns in later tutorials.

Importing Hierarchy Data

Typically, hierarchy mappings are imported together with the level members themselves. Take a look at the development data set for Product, in <TRAINING_HOME>/data/dev_data/initial/hierarchy/product.csv:

SKU ID|SKU|SUBCLASS|CLASS
sku-01|Blood Orange|Citrus|Fruit
sku-02|Clementines|Citrus|Fruit
...

Each line associates every SKU with a Subclass, and subsequently, a Class. To import this relationship, open up the definition of the import service for Product: <TRAINING_HOME>/src/logiql/services/tdx/product.logic. Uncomment the lines that are currently commented. They specify the import of relationships into Product:Sku:subclass and Product:Subclass:class.

Since we've changed the logic, we need to rebuild the project and import the data again.

$ make

Using an Ad Hoc Canvas, add SKU and Subclass onto the rows axis. You should see the association of SKU to Subclass, e.g. each Subclass has different members of the level SKU associated with it:

Under the Hood

For every mapping defined in <TRAINING_HOME>/src/config/Hierarchies.csv, a relation is generated to track the mapping. These relations are declared in <TRAINING_HOME>/build/srcgen/logiql/generated_chema/levelmaps.logic. For instance, for the Product mappings we just added, you'll find the following declarations in levelmap.logic:

Product:Sku:subclass[from] = to -> Product:Sku(from), Product:Subclass(to).

Configuring Aggregation Methods

Hierarchies provide a path for computing a measure's value at a higher intersection than its defined key space. E.g., when SalesRetail_R_TY_SKSTMO is requested at intersection Class, Store, Month, the hierarchy from Sku to Class (via Subclass) is used to compute the aggregation. What is left to be specified is the exact method of aggregation.

LogicBlox supports the following aggregation methods:

  • total Returns the summation of the values in a measure, whose keys map up to the requested key through some hierarchy.
  • min Returns the smallest value in a measure, whose keys map up to the requested key through some hierarchy. For level-typed measures, this method only applies if the level is ordered, e.g. Calendar:Day.
  • max Returns the largest value in a measure, whose keys map up to the requested key through some hierarchy. For level-typed measures, this method only applies if the level is ordered, e.g. Calendar:Day.
  • count Returns the number of values in a measure, whose keys map up to the requested key through some hierarchy.
  • count_distinct Returns the number of unique values in a measure, whose keys map up to the requested key through some hierarchy.
  • average Returns the average value of a measure, whose keys map up to the requested key through some hierarchy. average is implemented by dividing total by count.
  • ambig Returns a value if all keys (that map up to the requested key) in the measure have the same value. Does not have a value otherwise.
  • none The measure is not aggregated and no value is provided when measure is requested at any intersection other than its base -- the one it is defined for.
  • recalc The measure is not aggregated, but rather, is calculated using a rule. We discuss recalc in Tutorial 3.

A measure's default aggregation is specified in the DefaultAgg column of <TRAINING_HOME>/src/config/Measures.csv. Let's add some aggregation methods for our measures. Modify your <TRAINING_HOME>/src/config/Measures.csv such that it contains the following lines. Note in bold that we are specifying aggregation methods for each measure:

SkuDescription_S_SK,SKU Description,Sku,string,,none,,,,,,,,,,,
SkuCost_C_TY_SK,Cost,Sku,decimal,,average,,,,,,,,,,,
SkuRetail_R_TY_SK,Retail,Sku,decimal,,average,,,,,,,,,,,
SkuMarkdownPct_P_TY_SK,Markdown %,Sku,decimal,,none,,,,,,,,,,,
SkuFirstSold_SK_SK,Date First Sold,Sku,Calendar:Day,,min,,,,,,,,,,,
SkuBestSeller_B_SK,Best-selling SKU,Sku,boolean,,ambig,,,,,,,,,,,
SkuPhoto_S_SK,Photo,Sku,string,,none,,,,,,,,,,,
SkuLink_S_SK,Link,Sku,string,,none,,,,,,,,,,,
SalesRetail_R_TY_SKSTMO,Sales $ TY,SkuStoreMonth,decimal,,total,,,,,,,,,,,
SalesUnits_U_TY_SKSTMO,Sales Units TY,SkuStoreMonth,int,,total,,,,,,,,,,,
COGS_R_TY_SKSTMO,Cost of Goods Sold TY,SkuStoreMonth,decimal,,total,,,,,,,,,,,

Let's take a closer look at some of the changes that we have made:

  • For the measures of type string in our example (such as the SKU Description, Photo, or Link) an aggregation method doesn't make much sense, so we configured those as none.
  • For the Cost and Retail price measures per SKU we are using the average aggregation method.
  • For the Sales and Cost of Goods Sold measures we are using total as the aggregation method.
  • We used the method ambig for SkuBestSeller. This means the value for SkuBestSeller for an aggregated level, e.g. Subclass, will only be true if allSKU's in that Subclass is a best seller; or, the aggregated value will be false if all SKU's in that Subclass are not best sellers.
  • Percentage metrics are usually calculated using rules at the aggregated levels. You will learn about recalc rules, the rules that are used to recalculate the measure at the agg level, in Tutorial 3. Until then, let's not allow users to aggregate / spread the SkuMarkdownPct_P_TY_SK measure.

Note

Specifying an aggregation method means the same method is used to aggregate up all keys of a measure. Sometimes, there is a need to apply different aggregation methods for different keys, e.g. using total for Product and Location keys, but "first" for the Calendar key. These can be configured using composite aggregations, which we describe in Tutorial 2.

Rebuild the application by running the following command:

$ make

Now that you have refreshed the measure model with the aggregation and spreading methods, you can view and update data on different levels of aggregation! Go to an Ad Hoc Canvas and try to answer the following questions:

  • Which SKU had the highest Sales $ TY in 2015?
  • Which City had the lowest Cost of Goods Sold TY in 2015?
  • What is the average Cost of the Subclass Apples?

Alternate Hierarchies

So far, we only have one hierarchy defined for each of our dimensions. Often times, the dimensions are more complex and multiple hierarchies exist, allowing users to view data aggregated in different ways.

One frequently-occurring example of multiple hierarchies is with the Calendar dimension, where Month, in addition to mapping to a Year, can also map to MonthOfYear. You can see the available levels for all hierarchies of a dimension in the configuration panel. The figure below shows the Calendar dimension with additional levels originating from the two user defined hierarchies: the Default hierarchy that is configured already in our training application, and an additional hierarchy called "Month of Year", which we are going to configure next.

Let's extend our Calendar dimension with this alternate hierarchy, representing the Month to Month of Year relation. We first need to add the new level MOY (Month of Year) to the <TRAINING_HOME>/src/config/Levels.csv file:

Level,Label,Dimension,ElementType,IsOrdered,OrderAttribute,OrderTransform,TransformedType,TransformDerivationType
MOY,Month of Year,Calendar,string,true,Calendar:MOY_index,,,

Let's now configure a new hierarchy called Month of Year in <TRAINING_HOME>/src/config/Hierarchies.csv by adding the following entry:

Hierarchy,Dimension,IsDefault,FromLevel,ToLevel,Index,HasFirst,HasLast
Month of Year,Calendar,false,Month,MOY,0,false,false

Notice how the IsDefault column is set to false for this hierarchy entry? That's because a dimension can only have one default hierarchy.

Before rebuilding the application, let's make some changes again to the workflow that is responsible for importing data, so that our new levels get populated. Update the master.import_hierarchy workflow in src/workflows/master.wf by removing the task that imports the "simple calendar" hierarchy and uncomment the import of the calendar.csv file. After you have made your changes, the workflow should look as follows:

workflow master.import_hierarchy(app_prefix,location,timeout)[] {
  master.TdxImport(
    app_prefix=$app_prefix,
    timeout=$timeout,
    transport="http://localhost:55183",
    full=true,
    input={
      "{
         service: '/$(app_prefix)/delim-file/hierarchy/product'
         file:    '$(location)/hierarchy/product.csv'
         error:   '$(location)/error/product.csv'
       }",
      "{
         service: '/$(app_prefix)/delim-file/hierarchy/calendar'
         file:    '$(location)/hierarchy/calendar.csv'
         error:   '$(location)/error/calendar.csv'
       }",
      "{
         service: '/$(app_prefix)/delim-file/hierarchy/location'
         file:    '$(location)/hierarchy/location.csv'
         error:   '$(location)/error/location.csv'
       }"
    }
  )
}

Alright, now we can rebuild the application and validate that we can see the new levels in the configuration panel. Run the following commands:

$ make

Create an Ad Hoc Canvas and configure the view as follows:

  • Add Month to the rows.
  • Display the measure Sales $ TY by dragging it onto the Visible Measures panel.
  • Finally, let's add a roll-up from Month to Month of Year by adding the level to the rows to see the total sales for each month.
  • To view the roll-up for January only (the sum of Sales in January 2015 to 2017) add a filter to the Month of Year level. Your view should look similar to the figure below.

2.5. Common Optional Measure Options

Configuring default values for measures

You might have noticed that there are some metrics that have no values for certain keys. For instance, in the screenshot below, you can see that Sales $ TY does not have values for certain months:

More likely than not, measures should be configured with a default value. A default value means that a measure is fully populated for every tuple in the key space, even if a value is not explicitly set. For instance, SalesRetail_R_TY_SKSTMO has SKU, Store, Month as keys. Setting a default value of 0 for this measure means there is a sale for every combination of SKU, Store, and Month. This is a useful feature as it is very often that these measures are sparse: most products do not have any sales!

Let's set some default values for our existing measures where it makes sense (e.g. string- or level-typed measures do not usually have default values, whereas numeric or boolean-typed measures do). Update <TRAINING_HOME>/src/config/Measures.csv with the following lines after the header. Note that we have given values to the DefaultValue column of some of the rows in bold:

SkuDescription_S_SK,SKU Description,Sku,string,,none,,,,,,,,,,,
SkuCost_C_TY_SK,Cost,Sku,decimal,0d,average,,,,,,,,,,,
SkuRetail_R_TY_SK,Retail,Sku,decimal,0d,average,,,,,,,,,,,
SkuMarkdownPct_P_TY_SK,Markdown %,Sku,decimal,0d,none,,,,,,,,,,,
SkuFirstSold_SK_SK,Date First Sold,Sku,Calendar:Day,,,,,,,,,,,,,
SkuBestSeller_B_SK,Best-selling SKU,Sku,boolean,,ambig,,,,,,,,,,,
SkuPhoto_S_SK,Photo,Sku,string,,none,,,,,,,,,,,
SkuLink_S_SK,Link,Sku,string,,none,,,,,,,,, ,,
SalesRetail_R_TY_SKSTMO,Sales $ TY,SkuStoreMonth,decimal,0d,total,,,,,,,,,,,
SalesUnits_U_TY_SKSTMO,Sales Units TY,SkuStoreMonth,int,0,total,,,,,,,,,,,
COGS_R_TY_SKSTMO,Cost of Goods Sold TY,SkuStoreMonth,decimal,0d,total,,,,,,,,,,,

Rebuild the project:

$ make 

Construct the same view as the screenshot above. You should see these default values reflected in the pivot grid and there should be no empty cells.

Tip

  • Measures of type boolean can have either true or false as default value. Which one you choose depends on the intended meaning of the measure.
  • For numeric types, the default value is usually 0. Note that 0 for measures of type decimal is represented as 0d, and as 0f for floating point measures.
  • string-typed measures may have a default value of any string, though it is uncommon that these measures have default values.
  • Level-typed measures cannot have default values.

Display Options for Measures

When configuring measures, you often also want to specify how they should be displayed in the grid, e.g. should the number be displayed as a percentage? How many places after the decimal point should be displayed? Should the measure allow editing? The following columns in <TRAINING_HOME>/src/config/Measures.csv can be used for specifying display options:

  • Format: You can use this column to add a currency or percentage sign to the measure, or simply configure how many decimal points should be displayed.

    The measure format definition has to be specified as a JSON object. This JSON format is based on numbro.js format with some additional properties specifically added for LogicBlox:

    • currencySymbol - allows you to define a currency symbols. For example for Euros.
    • scaleFactor - the scale factor changes the displayed value of a scaled metric.

    Below you can find examples of the most common formatting configurations.

    Example 1. 

    Below you can find examples of formatting configurations for numeric measures:

    // e.g. 1,000 (typically used for integers)
    {"thousandSeparated":true, "mantissa":0}
    
    // e.g. 1,000.99 or 2,001.00 (typically used for decimals/floats)
    {"thousandSeparated":true, "mantissa":2}
    
    // e.g. $1,000.99 or $2,001.00
    {"currencyPosition":"prefix", "currencySymbol":"$", "thousandSeparated":true, "mantissa":2}
    
    // e.g. 99%
    {"thousandSeparated":true, "mantissa":0, "output":percent"}
    
    //e.g. %99.9
    {"thousandSeparated":true, "mantissa":1, "output":"percent"}

    Four special formatting options exist for string typed measures:

    • The link value can be used to add a link to a website or a file.

    • The mouseover value can be used to display either text or a picture (based on the URL pointing to the picture) as a mouseover.

    • The image value can be used to display a picture (based on the URL pointing to the picture) within the cell in the pivot table.

    • The wrapped-text value can be used to make sure long text gets wrapped within the cell in the pivot table.

    One special formatting options exist for boolean typed measures:

    • By default, Modeler will display a checkbox to display and edit the measure value. When this checkbox is unchecked, it's not visible to the user whether the cell contains an empty value or the value false.

      The booleandropdown value can be used to display a dropdown list within the cell in the pivot table. This dropdown contains true, false, and also the empty value.

    Tip

    For measures that are configured as a percentage in the Format column, end users can enter a percentage value rather than the decimal equivalent in the pivot table. For example: entering "5" would result in 5%.

  • HAlignment: Via this column you can configure the horizontal alignment of the measures. Valid inputs are: left, center or right.

  • Readonly: Allows you to configure a measure to be rendered read-only. If you set this property to true, it will not be possible for users to directly update this measure through the UI. The default is false

Alright, time to make sure that our measures are being displayed in a nicer way! Let's add some formatting to our measures. Replace your existing non-header lines in <TRAINING_HOME>/src/config/Measures.csv with the following lines:

SkuDescription_S_SK,SKU Description,Sku,string,,none,,,,,,left,,,,,
SkuCost_C_TY_SK,Cost,Sku,decimal,0d,average,,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""$"",""thousandSeparated"":true,""mantissa"":2}",right,,,,,
SkuRetail_R_TY_SK,Retail,Sku,decimal,0d,average,,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""$"",""thousandSeparated"":true,""mantissa"":2}",right,,,,,
SkuMarkdownPct_P_TY_SK,Markdown %,Sku,decimal,0d,none,,,,,"{""thousandSeparated"":true,""mantissa"":1,""output"":""percent""}",right,,,,,
SkuFirstSold_SK_SK,Date First Sold,Sku,Calendar:Day,,,,,,,,,,,,,
SkuBestSeller_B_SK,Best-selling SKU,Sku,boolean,,ambig,,,,,,center,,,,,
SkuPhoto_S_SK,Photo,Sku,string,,none,,,,,mouseover,,,,,,
SkuLink_S_SK,Link,Sku,string,,none,,,,,link,,,,,,
SalesRetail_R_TY_SKSTMO,Sales $ TY,SkuStoreMonth,decimal,0d,total,,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""$"",""thousandSeparated"":true,""mantissa"":2}",right,,,,,
SalesUnits_U_TY_SKSTMO,Sales Units TY,SkuStoreMonth,int,0,total,,,,,"{""thousandSeparated"":true,""mantissa"":0}",right,,,,,
COGS_R_TY_SKSTMO,Cost of Goods Sold TY,SkuStoreMonth,decimal,0d,total,,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""$"",""thousandSeparated"":true,""mantissa"":2}",right,true,,,,

Let's look in a bit more detail in the changes that we have just made:

  • We configured our decimal-typed dollar measures to be displayed with 2 decimal places and with the $ currency symbol.
  • Percentage measures, such as the Markdown % measure are configured to show 1 decimal place and displayed with a % symbol.
  • We also configured the alignment of measures: string values are left aligned, boolean values are center aligned, and all numeric measures are right aligned.
  • We have specified that COGS_R_TY_SKSTMO should be displayed as a read-only measure to our users.
  • Finally, SkuPhoto_S_SK and SkuLink_S_SK measures are configured with mouseover and link display types. You're probably dying to know how they will get displayed, so let's just check our changes via the UI!

Rebuild the application by running the following command:

$ make

You will need to refresh your browser to see your latest changes. Are measures are starting to look pretty good, don't you think? The screenshot below shows what the view looks like with the formatting and default values.

Did you try if you can update the COGS_R_TY_SKSTMO measure in the grid? As we have configured it to be read-only, this should not be possible.

2.6. Spreading

LogicBlox is not just a read-only BI platform. It allows users to not only analyze data, but also to modify them. Data edits can be at either the base intersection of a measure, or at an aggregated intersection. For instance, in planning, it is common for a user to want to adjust the value of Sales for the entire product Class, and for the entire year. This allows a user to set high level goals for the plan. The modified measure value at the aggregated levels gets spread down to the measure values at the base levels. Applications can specify how, by default, such modification should be spread. As you will see in the video later, users can change this spread method on-the-fly, as well.

To specify the default spreading policy, the column DefaultSpread should be modified to contain supported spread methods:

  • ratio The updated value at the aggregated intersection is spread proportionally among each child value. If there are no values or only default values at the child levels, then the new value is divided evenly among all children. ratio is the most commonly used spreading method for numeric measures. It is often used in combination with total aggregation method.
  • even The new value gets evenly spread to all children. The result is that all children end up with the same value, and re-aggregated back up to the new value entered.
  • replicate Each child gets exactly the new value at the aggregated intersection. Note that with replicate spreading, it is entirely possible that after spreading, the re-aggregated value at the aggregated intersection is not the same as the one that was entered to cause the spreading. This is especially true if the aggregation method is total. replicate is often used for percentage measures or boolean-typed measures. For percentage measures, the method of aggregation is often recalc, which we will discuss in Tutorial 3. For boolean measures, the method of aggregation is often ambig. These aggregation methods in combination with replicate avoids unexpected and confusing results.
  • none No spreading is applied. When a measure is configured with none spreading method, the UI disables it from being editable when shown at aggregated levels.

There are some methods we are not discussing in this lesson: recalc, first, and last. We discuss them in subsequent tutorials when we introduce the concepts of rules and ordered levels.

Similarly to aggregation methods, spreading policies by default apply to all keys in a measure. You can also specify a composite spreading method, where different spreading policies are used for each key. You will learn about the configuration and usage of composite aggregation and spreading methods in Tutorial 2.

Let's add a spreading method to all of our measures in the <TRAINING_HOME>/src/config/Measures.csv. Replace the data lines in Measures.csv with the following:

SkuDescription_S_SK,SKU Description,Sku,string,,none,none,,,,,left,,,,,
SkuCost_C_TY_SK,Cost,Sku,decimal,0d,average,replicate,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""$"",""thousandSeparated"":true,""mantissa"":2}",right,,,,,
SkuRetail_R_TY_SK,Retail,Sku,decimal,0d,average,replicate,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""$"",""thousandSeparated"":true,""mantissa"":2}",right,,,,,
SkuMarkdownPct_P_TY_SK,Markdown %,Sku,decimal,0d,none,none,,,,"{""thousandSeparated"":true,""mantissa"":1,""output"":""percent""}",right,,,,,
SkuFirstSold_SK_SK,Date First Sold,Sku,Calendar:Day,,min,replicate,,,,,,,,,,
SkuBestSeller_B_SK,Best-selling SKU,Sku,boolean,,ambig,replicate,,,,,center,true,,,,
SkuPhoto_S_SK,Photo,Sku,string,,none,none,,,,mouseover,,,,,,
SkuLink_S_SK,Link,Sku,string,,none,none,,,,link,,,,,,
SalesRetail_R_TY_SKSTMO,Sales $ TY,SkuStoreMonth,decimal,0d,total,ratio,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""$"",""thousandSeparated"":true,""mantissa"":2}",right,,,,,
SalesUnits_U_TY_SKSTMO,Sales Units TY,SkuStoreMonth,int,0,total,ratio,,,,"{""thousandSeparated"":true,""mantissa"":0}",right,,,,,
COGS_R_TY_SKSTMO,Cost of Goods Sold TY,SkuStoreMonth,decimal,0d,total,ratio,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""$"",""thousandSeparated"":true,""mantissa"":2}",right,true,,,,

Let's take a closer look at some of the changes that we have made:

  • For the measures of type string in our example (such as the Sku description, photo or link) a spreading method doesn't make much sense, so we configured those as none.
  • For the cost and retail price measures per Sku we are using the average aggregation method. We are using the replicate spreading method. As a result, if you update these measures on the aggregate level, for instance update Cost at Subclass, the value that you entered will be replicated over all of the Skus belonging to the subclass.
  • For boolean measure SkuBestSeller_B_SK, we use the replicate method. Setting the value at the Subclass level to true, for instance, will make it true for all SKU's of that Subclass.
  • For the TY Sales $, Sales Units and Cost of Goods Sold measures we are using total as the aggregation method and ratio as the spreading method. These are the most common aggregation and spreading methods for unit and retail values.
  • Percentage metrics are usually recalculated on the aggregate level. You will learn about recalc rules, the rules that are used to recalculate the measure at the agg level, in Tutorial 3. Until then, let's not allow users to aggregate / spread the SkuMarkdownPct_P_TY_SK measure.

Rebuild the application by running the command:

$ make

Now that we have specified the spreading methods, let's take a look at what happens when we change measure values at aggregated intersections. For instance, configure your Ad Hoc Canvas as shown below by adding the SKU level onto the rows and placing Sales $ TY onto the measures axis. Let's also display the rollups to subclass by adding the Subclass level onto the rows and displaying its rollup. What happens when you change the value of Sales $ TY for Apples to 14M? Do you notice what happens to the values of Sales $ TY for Granny Smith and Honeycrisp? They should have changed proportionally relative to their old values, to sum up to 14M

See also

The following video demonstrates some other ways you can change values in a Modeler pivot grid. Click here to view video.

Please note, this video is based on an older version of LogicBlox. Calculator expressions (e.g. *2 for multiplying the current value by 2) and spreading policy postfixes (e.g. 200000E for even spreading) need to be preceeded by =.

2.7. Percent-Parent Measures

A percent-parent measure contains percentage values, computed using the value of a measure at one intersection, divided by the value of that same measure, at a "parent" intersection. For example, we may want to measure the percentage of Sales for one product, over Sales of all products, or the percentage of Sales for one subclass, over the Sales of its parent class.

We can define a percent-parent measure by specifying two parameters in <TRAINING_HOME>/src/config/Measures.csv:

  • PercentBase: This is the measure we are interested in computing percentages of. In our example, this would be the "Sales $"" measure.
  • PercentParentDimension: The dimension along which to compute percent-parent. For instance, specifying Product for this dimension would mean the resulting percent-parent measure is defined to compute the percentages of Sales of one product level, over a "parent" product level (e.g. Sku sales over Sales of its parent Class).

Let's add a percent parent measure to our application. It should become much clearer how these calculations work, when you see them live in action! Add the following entry to <TRAINING_HOME>/src/config/Measures.csv:

SalesRetailPpt_TY_P_SKSTMO,Sales $ PctParent TY,SkuStoreMonth,decimal,0d,,,,SalesRetail_R_TY_SKSTMO,Product,"{""thousandSeparated"":true,""mantissa"":1,""output"":""percent""}",right,true,,,,

We use the Sales $ TY measure as the base of our measure and specified the Product dimension along which we would like to compute the percent-parent. As we are making changes to the data model itself (by adding new measures), we need to rebuild the application before we can verify the changes via the UI:

$ make

Time to see this measure in action! Create an Ad Hoc Canvas, move SKU to the rows and the measures Sales $ TY (our "percent base predicate") and Sales $ PctParent TY on the measures axis. Your view should now look similar to the figure below:

Notice how all of the percentage figures together add up to 100%? Each percentage is the Sales $ TY of each SKU, over the total Sales $ TY over all products.

Let's now add an aggregation from SKU to Subclass to the grid and observe how the values of the percent parent measure change:

The values per SKU represent the percentage distribution within the Subclass. Per Subclass, you can see the percentage distribution over the total Sales $ TY (as we are not displaying other levels higher up the Product hierarchy on the grid).

Let's add two more aggregations along the Product hierarchy to our grid: from Subclass to Class and from Class to All Product:

Do you see how the values at the Subclass level have changed? This is due to the fact that the percentage now indicates the percentage distribution with respect to the parent level of Subclass, visible on the grid. In this case, that is the Class level. For instance, only the subclass Root Vegetables rolls up to the class Vegetables, therefore the percent parent metric shows a value of 100%. Whereas in the previous example the value for the subclass Root Vegetables was 24%, as it calculated the percentage distribution over all subclasses.

Now add the Year level from the Calendar dimension onto the grid and add the aggregation to all years. As you can see, the addition of a dimension that is not the PercentParentDimension does not have an impact on the calculation itself. The values at the All Calendar level match the numbers that were also calculated previously.

3. Basic View Configuration

In the previous lesson we started to give shape to our training application by defining the data model. So far, we performed ad hoc data analysis using user-defined sheets. In this lesson, we will learn how to create views that are pre-configured with certain levels and measures.

We will not be making changes to the data model. Therefore, updating the view/UI configuration is easy and fast with the following command:

$ make load-ui

In Lesson 1 - Getting Started, we introduced the three key components shared by all Modeler applications: the (1) menu pane, (2) navigation pane and the (3) canvas pane. In this lesson, we focus on the configuration of the navigation and canvas panes. The navigation pane contains links to the main contents of the application, which are displayed in the canvas pane.

Adding a new canvas to an application consists of three steps:

  1. Update the navigation pane with the link to the new canvas (you can either add it under an existing node or create a new node).
  2. Create a canvas to lay out your view(s).
  3. Configure one or multiple views, such as a pivot table, a panel of buttons, panel with shortcut buttons and/or a chart. In this tutorial you will only learn to configure pivot tables.

3.1. Configuring the Navigation Pane

The navigation pane is always custom-configured per application and can be a tree of arbitrary shape and depth. It is currently being configured using JSON. You can find the configuration of the navigation tree that we use in our training application under <TRAINING_HOME>/src/config/views/main/navigation.json:

{
    "id": "main",
    "nodes": [
        {
            "id": "tutorial",
            "properties": [
                {
                    "key": "caption",
                    "value": "Tutorial"
                }
            ],
            "children": [
            ]
        }
    ]
}

At the moment, this navigation tree JSON does not contain any pre-configured views. It only contains the empty navigation tree node Tutorials. Earlier in this tutorial, we learned that the user can create user-defined sheets under ‘My Views’. We used this feature to create our Ad Hoc Canvas while exploring and configuring the data model.

Let's add a link to the canvas sku-maintenance-canvas that we will create next, and display the link to our users as SKU Maintenance. To accomplish this, we need to add it as a new canvas to the list of children in <TRAINING_HOME>/src/config/views/main/navigation.json. The changes to this file are displayed in bold:

{
   "id": "main",
   "nodes": [
     {
       "id": "tutorial",
       "properties": [
         {
           "key": "caption",
           "value": "Tutorial"
         }
       ],
       "children": [
         {
           "id": "sku-maintenance-canvas",
           "properties": [
             {
               "key": "type",
               "value": "canvas"
             },
             {
               "key": "caption",
               "value": "SKU Maintenance"
             }
           ]
         }
       ]
     }
   ]
}

Let's refresh the UI by running make load-ui and verify that the node with the link was added. The navigation tree should now look as follows:

While you should be able to see the changes in the navigation tree, the link does not work yet, as we haven't yet created the canvas to which it points to. Let's do that next.

Under the Hood

The navigation tree is imported into the workspace during build time via a service that is distributed together with the LogicBlox platform. The build will pick up all JSON files in <TRAINING_HOME>/src/config/views/main automatically and post them to the http://localhost:55183/<app-prefix>/navtree service.

Modeler actually allows you to configure multiple navigation trees. Each navigation tree and its corresponding views (canvases and sheets) should be stored in the folder <TRAINING_HOME>/src/config/views/<navigationTreeId>/navigation.json.

The default navigationTreeId of a Modeler application is configured in the modelerConfiguration object used to instantiate the application. For instance, take a look at <TRAINING_HOME>/frontend/index.html. In our training application we’re using main as the navigationTreeId which means we should store our views in <TRAINING_HOME>/src/config/views/main.

3.2. Configuring Canvases

Each leaf of the navigation pane links to a canvas, which is used to lay out our views. In this lesson we will only build some basic views containing a pivot table with its axis configuration panels. You will learn how to configure more complex canvases in Tutorial 2. Canvases are currently also configured in JSON and need to be stored under <TRAINING_HOME>/src/config/views/main/canvas, so that they can be automatically imported during build time.

The most basic canvas is one that contains only one pivot table and optionally, its axis panels. The pivot table view is referred to as a SheetView. Start by creating a new file called sku-maintenance-canvas.json in <TRAINING_HOME>/src/config/views/main/canvas with the following content:

{
  "id": "sku-maintenance-canvas",
  "views": {
    "sku-maintenance-sheet": {
      "module": "SheetView"
    }
  },
  "tileLayout": {
    "direction": "row",
    "panes": [
      {
        "id": "sku-maintenance-sheet"
      }
    ]
  },
  "module": "CanvasView"
}

There are for key components to a canvas JSON specification:

  • id: The id property of this canvas is sku-maintenance-canvas. This is the id used in the navigation tree JSON to specify which canvas is linked to.
  • views: In this object we list the views that should be displayed in this canvas. In our case, only the view sku-maintenance-sheet is included. The module property specifies the type of view. SheetView is a pivot table.
  • tileLayout: In this object we specify how different views are laid out in a canvas. The direction property of this object specifies the direction in which the views defined in the list of panes are split. Use row to split panes from left to right (within the same row) and use column to split panes from top to bottom (within the same column).

    Note

    In this example, we are only including one view. Thus, we simply add a single panel for our view sku-maintenance-sheet. In later tutorials, you will see how to add more panels to compose together multiple views into the same canvas.

  • module: The module property with the value CanvasView is required to indicate that this JSON specifies the configuration of a canvas.

Under the Hood

All the canvases are imported during the same build step as the navigation tree via the service http://localhost:55183/<app-prefix>/canvas that is distributed together with the LogicBlox platform. The build will pick up all JSON files in <TRAINING_HOME>/src/config/views/<navigationTreeId>/canvas and its sub-folders (as when your application grows, it often makes sense to group the canvases into folders, so that you can keep a good overview).

3.3. Configuring a basic sheet with a pivot grid

Pivot grids are configured using JSON, as well. A pivot grid view is comprised of multiple axis: Measures, Rows, Columns, Slices, and of course, the Pivot Grid itself. In this lesson, we first learn how to specify what levels and measures are shown on a pivot grid. In the next lesson, we will discuss how you can configure which of these panels are shown.

Let's create the JSON configuration of the sku-maintenance-sheet, which is included in sku-maintenance-canvas. Create a new file <TRAINING_HOME>/src/config/views/main/sheets/sku-maintenance-sheet.json. Copy the JSON below into the file:

{
  "id": "sku-maintenance-sheet",
  "title": "SKU Maintenance",
  "pivotConfig": {
    "axis": {
      "y": [
      ],
      "x": [
        {
          "qualifiedName": "-:Measures"
        }
      ],
      "z": [
      ],
      "slice": []
    }
  },
  "views": {
    "rows": {
      "module": "Rows"
    },
    "columns": {
      "module": "Columns"
    },
    "measures": {
      "module": "Measures"
    },
    "slices": {
      "module": "Slices"
    },
    "grid": {
      "module": "PivotGrid"
    }
  },
  "module": "SheetView"
}

Let's take a closer look at the configuration above. There are four key components:

  • id: This must be the same id referenced in canvas configurations that include this view.
  • title: This is the sheet of the title that should be displayed in the sheet’s toolbar.
  • views: We list here all the UI components (such as the row, column, measure and slices axis configuration panels) that we would like to have displayed on our view. This is necessary, as there are situations in which you would not like to show each of these, but maybe only a grid. We will discuss later how to hide panels. Take a look at the figure below, where we shows each of these components: (1) Measures Axis Panel, (2) Columns Axis Panel, (3) Rows Axis Panel, (4) Slices Axis Panel, and (5) Pivot Grid.
  • pivotConfig: The final step when configuring a view is the configuration of the pivotConfig object. This is where all the exciting stuff happens, as this is where you define what you would like to see on the pivot table itself. In the current configuration, only the -:Measures tile is placed on the grid, namely onto the x-axis (Columns). As you will see later, this will also be the place where we can configure default aggregations, filters, default sorting, and all those other useful configuration options that make our views much more dynamic.

You need to store all of your views under <TRAINING_HOME>/src/config/views/main/sheets or its sub directories for the build to pick up automatically.

We will configure our pivot grid shortly, but let's first take a look at our new canvas and view via the browser. After re-loading the UI, you should see the following when clicking on Sku Maintenance in the navigation pane:

Adding Measures to a View

The basic pivot grid view is not very exciting, as it looks the same as our Ad Hoc Canvas. Let's add a few measures to our view. You can do so by modifying the pivotConfig object in the sku-maintenance-sheet.json file according to the code sample below.

Modify the axis object in your pivotConfig object as shown below:

{
   "id": "sku-maintenance-sheet",
   "title": "SKU Maintenance",
   "pivotConfig": {
     "axis": {
       "y": [
         {
           "qualifiedName": "Product:Sku"
         }
       ],
       "x": [
         {
           "qualifiedName": "-:Measures"
         }
       ],
       "z": [
         {
           "qualifiedName": "SkuDescription_S_SK"
         },
         {
           "qualifiedName": "SkuCost_C_TY_SK"
         },
         {
           "qualifiedName": "SkuRetail_R_TY_SK"
         },
         {
           "qualifiedName": "SkuMarkdownPct_P_TY_SK"
         },
         {
           "qualifiedName": "SkuFirstSold_SK_SK"
         },
         {
           "qualifiedName": "SkuBestSeller_B_SK"
         },
         {
           "qualifiedName": "SkuPhoto_S_SK"
         }
       ],
       "slice": []
     }
   },
   "views": {
     "rows": {
       "module": "Rows"
     },
     "columns": {
       "module": "Columns"
     },
     "measures": {
       "module": "Measures"
     },
     "slices": {
       "module": "Slices"
     },
     "grid": {
       "module": "PivotGrid"
     }
   },
   "module": "SheetView"
 }

Reload the UI configuration:

$ make load-ui

Let's now take a look at how your view looks like. Much better, right? So what this configuration says is that the Product:Sku level field should be placed onto the y-axis, which is the Rows panel. We refer to the Measures panel as the z-axis (an unfortunate legacy misnomer, as the Slice panel is really the z-axis), and we put all of our measure with a SKU intersection onto it.

One More Canvas to Build On

Now that we have a view where we display all of our Sku properties, let's create another canvas, where we can display all of the TY measures. We will then build upon this canvas to learn some other configuration options.

Let's start by adding a new link to the navigation tree that will take us to our new canvas. The piece of JSON that you need to add to the navigation pane configuration file under <TRAINING_HOME>/src/config/views/main/navigation.json is displayed in bold:

{
   "id": "main",
   "nodes": [
     {
       "id": "tutorial",
       "properties": [
         {
           "key": "caption",
           "value": "Tutorial"
         }
       ],
       "children": [
         {
           "id": "sku-maintenance-canvas",
           "properties": [
             {
               "key": "type",
               "value": "canvas"
             },
             {
               "key": "caption",
               "value": "SKU Maintenance"
             }
           ]
         },
         {
           "id": "ty-data-canvas",
           "properties": [
             {
               "key": "type",
               "value": "canvas"
             },
             {
               "key": "caption",
               "value": "TY Data"
             }
           ]
         }
       ]
     }
   ]
 }

Now, let's add a new file called ty-data-canvas.json in <TRAINING_HOME>/src/config/views/main/canvas with the following content:

{
  "id": "ty-data-canvas",
  "views": {
    "ty-data-sheet": {
      "module": "SheetView"
    }
  },
  "tileLayout": {
    "direction": "row",
    "panes": [
      {
        "id": "ty-data-sheet"
      }
    ]
  },
  "module": "CanvasView"
}

At this point, this canvas is simply a copy of our sku-maintenance-canvas, where we updated the id of the canvas to ty-data-canvas (which we have called earlier from the navigation tree) and the two references to the view ty-data-sheet that we will create next.

Create a file called ty-data-sheet.json under <TRAINING_HOME>/src/config/views/main/sheets with the following base configuration:

{
  "id": "ty-data-sheet",
  "title": "TY Data",
  "pivotConfig": {
    "axis": {
      "y": [
      ],
      "x": [
        {
          "qualifiedName": "-:Measures"
        }
      ],
      "z": [
      ],
      "slice": [
      ]
    }
  },
  "views": {
    "rows": {
      "module": "Rows"
    },
    "columns": {
      "module": "Columns"
    },
    "measures": {
      "module": "Measures"
    },
    "slices": {
      "module": "Slices"
    },
    "grid": {
      "module": "PivotGrid"
    }
  },
  "module": "SheetView"
}

The views of the view we will leave the same as in our Sku Maintenance view, as we are interested in making all of the configuration panels available to our users.

Let's now make some modifications to the pivotConfig object so that:

  1. The levels Product:Subclass and Product:Sku are displayed on the rows.
  2. The level Location:City is displayed together with the Measures field on the columns.
  3. The level Calendar:Year is shown on the slice.
  4. The following measures are being displayed:
    • SalesRetail_R_TY_SKSTMO
    • SalesUnits_U_TY_SKSTMO
    • COGS_R_TY_SKSTMO

Since we have updated our UI configuration, let's reload it:

$ make load-ui

The figure below shows how we expect our view to look like, once you reload the UI. Feel free to try to configure this view yourself, or simply copy the pivotConfig from below over the existing pivot configuration.

{
   "id": "ty-data-sheet",
   "title": "TY Data",
   "pivotConfig": {
     "axis": {
       "y": [
         {
           "qualifiedName": "Product:Subclass"
         },
         {
           "qualifiedName": "Product:Sku"
         }
       ],
       "x": [
         {
           "qualifiedName": "Location:City"
         },
         {
           "qualifiedName": "-:Measures"
         }
       ],
       "z": [
         {
           "qualifiedName": "SalesRetail_R_TY_SKSTMO"
         },
         {
           "qualifiedName": "SalesUnits_U_TY_SKSTMO"
         },
         {
           "qualifiedName": "COGS_R_TY_SKSTMO"
         }
       ],
       "slice": [
         {
           "qualifiedName": "Calendar:Year"
         }
       ]
     }
   },
   "views": {
     "rows": {
       "module": "Rows"
     },
     "columns": {
       "module": "Columns"
     },
     "measures": {
       "module": "Measures"
     },
     "slices": {
       "module": "Slices"
     },
     "grid": {
       "module": "PivotGrid"
     }
   },
   "module": "SheetView"
 }

Displaying Aggregations

We discussed earlier how we can configure an aggregation and spreading method for each measure, to display and update measures at a higher intersection than the base intersection at which they are defined. Let's make some configuration changes to our TY Data canvas, so that the aggregates at both the Product:Subclass and the ALL level along the Location hierarchy are visible, as shown in the screenshot below. Note how we show the Product:Subclass level as outline, while the outline is hidden for the Location:ALL level.

Aggregations are configured by adding the aggregated levels to the axes in the pivotConfig as you can see in the JSON below in bold. Modify the view configuration in <TRAINING_HOME>/src/config/views/main/sheets/ty-data-sheet.json such that the pivotConfig object contains the following:

{
   "id": "ty-data-sheet",
   "title": "TY Data",
   "pivotConfig": {
     "axis": {
       "y": [
         {
           "qualifiedName": "Product:Subclass",
           "rollup": {
             "methodName": "DEFAULT"
           }
         },
         {
           "qualifiedName": "Product:Sku"
         }
       ],
       "x": [
         {
           "qualifiedName": "Location:ALL",
           "displayMode": "NONE",
           "rollup": {
             "methodName": "DEFAULT"
           }
         },
         {
           "qualifiedName": "Location:City"
         },
         {
           "qualifiedName": "-:Measures"
         }
       ],
       "z": [
         {
           "qualifiedName": "SalesRetail_R_TY_SKSTMO"
         },
         {
           "qualifiedName": "SalesUnits_U_TY_SKSTMO"
         },
         {
           "qualifiedName": "COGS_R_TY_SKSTMO"
         }
       ],
       "slice": [
         {
           "qualifiedName": "Calendar:Year"
         }
       ]
     }
   },
   "views": {
     "rows": {
       "module": "Rows"
     },
     "columns": {
       "module": "Columns"
     },
     "measures": {
       "module": "Measures"
     },
     "slices": {
       "module": "Slices"
     },
     "grid": {
       "module": "PivotGrid"
     }
   },
   "module": "SheetView"
 }

Now, let's take a closer look at what this configuration does exactly:

  • We first add the Product:Subclass level to the y axis and configure to show the DEFAULT rollup method. This means that each measure will be aggregated using its default aggregation method (that we configured earlier via the Measures.csv file.

    Note

    The default method is used most commonly in applications, but it is possible for developers to override it. The methodName entry can contain any of the supported aggregation methods (e.g. TOTAL, MIN, MAX) - note that the name of the method needs to be in capital letters. If a non-default method is configured, then that method will be applied to all the measures on the view.

  • If only the rollup method is configured, then both the outline and the rollup will be displayed on the view - similar to what happens when you manually add a level to the axis and you do not explicitly remove the Inline or Rollup checkboxes.

  • We add the Location:ALL level to the x axis. Notice how we don't just add a rollup entry, but also configure the displayMode to be NONE. With this configuration option we indicate that we want to hide the outline and only display the rollup.

As the changes that you have made are again only in the view configuration file, you only need to reload the UI:

$ make load-ui

Go to your browser again. Does your view match the figure from above?

Sort View by Measure Value

Let's finish the pivot grid configuration of our views by adding a default sort based on the value of a measure. If no sorting by a measure is configured, we sort by the level headers on the rows. If you take a look at the SKU Maintenance view, you will see that it is sorted by the SKU label. Imagine now that you would by default like to see the SKUs sorted by their cost price, where the SKU with the lowest cost price should be displayed first. You would need to make the following changes to the configuration of your sku-maintenance-sheet (the changes are displayed in bold):

{
   "id": "sku-maintenance-sheet",
   "title": "SKU Maintenance",
   "pivotConfig": {
     "axis": {
       "y": [
         {
           "qualifiedName": "Product:Sku"
         }
       ],
       "x": [
         {
           "qualifiedName": "-:Measures"
         }
       ],
       "z": [
         {
           "qualifiedName": "SkuDescription_S_SK"
         },
         {
           "qualifiedName": "SkuCost_C_TY_SK",
           "sortByMeasureValue": {
             "floatingAxis": "y",
             "sortPositions": [],
             "sortOrder": "asc"
           }
         },
         {
           "qualifiedName": "SkuRetail_R_TY_SK"
         },
         {
           "qualifiedName": "SkuMarkdownPct_P_TY_SK"
         },
         {
           "qualifiedName": "SkuFirstSold_SK_SK"
         },
         {
           "qualifiedName": "SkuBestSeller_B_SK"
         },
         {
           "qualifiedName": "SkuPhoto_S_SK"
         }
       ],
       "slice": []
     }
   },
   "views": {
     "rows": {
       "module": "Rows"
     },
     "columns": {
       "module": "Columns"
     },
     "measures": {
       "module": "Measures"
     },
     "slices": {
       "module": "Slices"
     },
     "grid": {
       "module": "PivotGrid"
     }
   },
   "module": "SheetView"
 }

When configuring a sortByMeasureValue, you need to define the following properties:

  1. Use sortOrder to configure whether the sort should be asc (short for "ascending") or desc (short for "descending").
  2. The floatingAxis in our example is the y axis, that's where we have the Product:Sku level. The floatingAxis is always the opposite of the axis where the measures are on.
  3. The sortPositions can be left empty. These only need to be configured in special cases, when you would like to sort on a specific level member (e.g. "Sales @ SKU1")

Reload your UI configuration again:

$ make load-ui

After you reload the UI configuration, visit SKU Maintenance canvas. You should see something as follows. Notice the ascending icon next to the column for Cost? That's the result of our configuration change.

3.4. Grid Options

Setting Default Column/Row Width and Height

Did you notice when looking at your previously created views, that certain headers were not fully visible unless you resized these headers manually? We can easily fix that by setting the default width or height of certain columns and rows. Let's take a look first at the TY Data view. As you can see, we cannot see the full header of all the measures. Additionally, the column displaying the Sku label is not wide enough. We can fix this by adding the gridOptions object to our view, where we configure the height and the width of our level members/measures. Modify your <TRAINING_HOME>/src/config/views/main/sheets/ty-data-sheet.json, by adding the gridOptions object below, at the same level as pivotConfig:

{
   "id": "ty-data-sheet",
   "title": "TY Data",
   "pivotConfig": {
     "axis": {
       "y": [
         {
           "qualifiedName": "Product:Subclass",
           "rollup": {
             "methodName": "DEFAULT"
           }
         },
         {
           "qualifiedName": "Product:Sku"
         }
       ],
       "x": [
         {
           "qualifiedName": "Location:ALL",
           "displayMode": "NONE",
           "rollup": {
             "methodName": "DEFAULT"
           }
         },
         {
           "qualifiedName": "Location:City"
         },
         {
           "qualifiedName": "-:Measures"
         }
       ],
       "z": [
         {
           "qualifiedName": "SalesRetail_R_TY_SKSTMO"
         },
         {
           "qualifiedName": "SalesUnits_U_TY_SKSTMO"
         },
         {
           "qualifiedName": "COGS_R_TY_SKSTMO"
         }
       ],
       "slice": [
         {
           "qualifiedName": "Calendar:Year"
         }
       ]
     }
   },
   "gridOptions": {
     "display": {
       "-:Measures": {
         "height": 40
       },
       "Product:Sku": {
         "width": 100
       }
     }
   },
   "views": {
     "rows": {
       "module": "Rows"
     },
     "columns": {
       "module": "Columns"
     },
     "measures": {
       "module": "Measures"
     },
     "slices": {
       "module": "Slices"
     },
     "grid": {
       "module": "PivotGrid"
     }
   },
   "module": "SheetView"
 }

This configuration states that the height of the measure headers should be 40px and the width of the Product:Sku level header 100px.

Reload the UI configuration:

$ make load-ui

Tadaaaa! Looks much better, right? Ok, time to also fix the headers of the Sku Maintenance view, as the column where we display the Sku Description measure is not wide enough and we should also increase the height of the measure headers so that all the headers are visible. Add the gridOptions object as shown below to your sku-maintenance-sheet:

{
   "id": "sku-maintenance-sheet",
   "title": "SKU Maintenance",
   "pivotConfig": {
     "axis": {
       "y": [
         {
           "qualifiedName": "Product:Sku"
         }
       ],
       "x": [
         {
           "qualifiedName": "-:Measures"
         }
       ],
       "z": [
         {
           "qualifiedName": "SkuDescription_S_SK"
         },
         {
           "qualifiedName": "SkuCost_C_TY_SK",
           "sortByMeasureValue": {
             "floatingAxis": "y",
             "sortPositions": [],
             "sortOrder": "asc"
           }
         },
         {
           "qualifiedName": "SkuRetail_R_TY_SK"
         },
         {
           "qualifiedName": "SkuMarkdownPct_P_TY_SK"
         },
         {
           "qualifiedName": "SkuFirstSold_SK_SK"
         },
         {
           "qualifiedName": "SkuBestSeller_B_SK"
         },
         {
           "qualifiedName": "SkuPhoto_S_SK"
         }
       ],
       "slice": []
     }
   },
   "gridOptions": {
     "display": {
       "-:Measures": {
         "height": 50,
         "members": {
           "SkuDescription_S_SK": {
             "width": 200
           }
         }
       },
       "Product:Sku": {
         "width": 100
       }
     }
   },
   "views": {
     "rows": {
       "module": "Rows"
     },
     "columns": {
       "module": "Columns"
     },
     "measures": {
       "module": "Measures"
     },
     "slices": {
       "module": "Slices"
     },
     "grid": {
       "module": "PivotGrid"
     }
   },
   "module": "SheetView"
 }

After re-loading the UI, you should now be able to see all the headers completely, as well as the complete SKU description.

4. Canvas Configuration Options

In the previous lesson you have learned how to configure basic canvases with a single pivot grid view. In this lesson we will show you how to turn off configuration mode on a view by default and how configure a canvas with multiple views. The lesson will be concluded with an exercise that will let you practice all the new skills that you have learned in this tutorial!

4.1. Creating a split canvas

In the previous lessons, we built canvases that contain one view each. You can use the canvas to lay out multiple views. For instance, we can have two pivot views side-by-side, or one on top of another. Let's start by creating a new canvas called "Working Plan". This canvas should display two pivot grids, where one is on top of another.

  1. Start by updating the navigation tree with the new view. You can use "Working Plan" as the caption, and reference the canvas that you are going to create next, via its id wp-data-canvas.
    {
       "id": "main",
       "nodes": [
         {
           "id": "tutorial",
           "properties": [
             {
               "key": "caption",
               "value": "Tutorial"
             }
           ],
           "children": [
             {
               "id": "sku-maintenance-canvas",
               "properties": [
                 {
                   "key": "type",
                   "value": "canvas"
                 },
                 {
                   "key": "caption",
                   "value": "SKU Maintenance"
                 }
               ]
             },
             {
               "id": "ty-data-canvas",
               "properties": [
                 {
                   "key": "type",
                   "value": "canvas"
                 },
                 {
                   "key": "caption",
                   "value": "TY Data"
                 }
               ]
             },
             {
               "id": "wp-data-canvas",
               "properties": [
                 {
                   "key": "type",
                   "value": "canvas"
                 },
                 {
                   "key": "caption",
                   "value": "Working Plan"
                 }
               ]
             }
           ]
         }
       ]
     }
  2. Create a new canvas with id wp-data-canvas in <TRAINING_HOME>/src/config/views/main/canvas. As opposed to the previous canvases that you have created, this one should contain two views (wp-data-top-sheet and wp-data-bottom-sheet). Make sure that you position the two views below each other, by setting the right direction in the tileLayout object:
    {
      "id": "wp-data-canvas",
      "views": {
        "wp-data-top-sheet": {
          "module": "SheetView"
        },
        "wp-data-bottom-sheet": {
          "module": "SheetView"
        }
      },
      "tileLayout": {
        "direction": "column",
        "panes": [
          {
            "id": "wp-data-top-sheet",
          },
          {
            "id": "wp-data-bottom-sheet",
          }
        ]
      },
      "module": "CanvasView"
    }

    Diving deeper

    The canvas above defines a view with two panels which both contain a sheet. As defined by the direction column, the sheets are put below each other.

    You can create even move complex views by nesting panels within panels. See the following canvas configuration for an example.

    {
       "id": "three-sheet-canvas",
       "views": {
         "three-sheet-top-sheet": {
           "module": "SheetView"
         },
         "three-sheet-bottomL-sheet": {
           "module": "SheetView"
         },
         "three-sheet-bottomR-sheet": {
           "module": "SheetView"
         }
       },
       "tileLayout": {
         "direction": "column",
         "panes": [
           {
             "id": "three-sheet-top-sheet"
           },
           {
             "direction": "row",
             "panes" : [
               {
                 "id": "three-sheet-bottomL-sheet"
               },
               {
                 "id": "three-sheet-bottomR-sheet"
               }
             ]
           }
         ]
       },
       "module": "CanvasView"
     }

    Modeler will render this layout configuration like this:

  3. Let’s start configuring our sheets. For the top sheet, add a new sheet with id wp-data-top-sheet in <TRAINING_HOME>/src/config/views/main/sheets/wp-data-top-sheet.json:

    {
      "id": "wp-data-top-sheet",
      "title": "Working Plan (top sheet)",
      "pivotConfig": {
        "axis": {
          "y": [
          ],
          "x": [
            {
              "qualifiedName": "-:Measures"
            }
          ],
          "z": [
          ],
          "slice": [
          ]
        }
      },
      "views": {
        "rows": {
          "module": "Rows"
        },
        "columns": {
          "module": "Columns"
        },
        "measures": {
          "module": "Measures"
        },
        "slices": {
          "module": "Slices"
        },
        "grid": {
          "module": "PivotGrid"
        }
      },
      "module": "SheetView"
    }

    For the bottom sheet, we copy one of our existing sheets that resembles what we would like to have.

    $ cd <TRAINING_HOME>
    $ cp src/config/views/main/sheets/ty-data-sheet.json src/config/views/main/sheets/wp-data-bottom-sheet.json

    Here, we copied the ty-data-sheet view to wp-data-bottom-sheet. Note that you need to now modify the id and title properties from this sheets, to reflect its new identity.

    • id of wp-data-bottom-sheet.json should be wp-data-bottom-sheet
    • title of wp-data-bottom-sheet.json should be "Working Plan (bottom sheet)"

    Tip

    It is not necessary for the view id to be the same as the view file name. We always do so by convention though. It makes it easier to find the configuration file you are looking for.

Reload the UI configuration and validate that your new view looks as expected and similar to the figure below:

$ make load-ui

Extra Credit

How would you need to change the tileLayout object, to display the views next to each other ?

Remember, it is all about the direction of your view panels in wp-data-canvas.json

Controlling Sizes of Views

Our canvas looks a bit odd, as half of the vertical space is taken up by an empty grid. You can configure the exact sizes (pixels) or relative sizes of the views (ratio or percentages) in your canvas, by manipulating the size property of each panel under the tileLayout object.

Modify the tileLayout object in <TRAINING_HOME>/src/config/views/main/canvas/wp-data-canvas.json, such that the size property of each panel contains a ratio. You can also define a minSize property for each view to prevent the user from completely hiding a panel when manually resizing the panels within the canvas.:

{
   "id": "wp-data-canvas",
   "views": {
     "wp-data-top-sheet": {
       "module": "SheetView"
     },
     "wp-data-bottom-sheet": {
       "module": "SheetView"
     }
   },
   "tileLayout": {
     "direction": "column",
     "panes": [
       {
         "id": "wp-data-top-sheet",
         "minSize": "40px",
         "size": "1"
       },
       {
         "id": "wp-data-bottom-sheet",
         "minSize": "40px",
         "size": "2"
       }
     ]
   },
   "module": "CanvasView"
 }

In this canvas, we’ve defined a ratio of 1:2 to make the top sheet cover one-third of the column and the bottom sheet cover two-thirds of the column. Let's reload the UI configuration:

$ make load-ui

You should see that the top grid in Working Plan canvas is about half as high as the bottom grid, as shown below.

4.2. Wrapping it all up

Let's finish up the configuration of our WP Data canvas by adding some new measures to the top grid. We will further extend this view in Tutorial 2 by replacing all the TY data with new "working plan" measures, but for now you only need to make sure that the top view is also displaying some measures by default.

Tip

If you are having problems with the exercise below, scroll down a bit, we will walk you through all the changes that you need to make!

  1. Create the measure SalesRetail_R_SP_CIYE, which will hold the SP (= strategic plan) version of the Sales $. The strategic plan should store data at the City,Year intersection. (Typically, strategic plans are set at very high levels, and thus the coarse levels in the measure's intersection)
  2. Create the measure Sales_SP_U_SY, which will hold the SP version of the Sales Units. Similar to SalesRetail_R_SP_CIYE, this measure should also be defined at the City,Year intersection.

    Tip

    Notice how these are the first measures that you have defined at the City/Year intersection? You will therefore also need to update another csv file, not just <TRAINING_HOME>/src/config/Measures.csv.

  3. Make sure you configure their aggregation and spreading methods and formatting. For these measures, a total aggregation and ratio spreading would make most sense.

    Tip

    This might be a good moment to rebuild your application and verify that the measures have been added correctly! Note that as you have made changes to the data model, you will need to rebuild it.

  4. Display these two new measures in the top view of the Working Plan canvas. The view should be configured as follows:
    • The two new measures should be displayed on the rows.
    • The City level on the columns.
    • The Year level on the slice.
  5. Update the top view to show the aggregation to Location:ALL.
  6. As these most recent changes were only made to the views and canvas configuration, you only need to re-load the UI to validate your changes.

You view should now look similar to the figure below.

If your view does not quite look as expected or have trouble with one of the steps above, verify that you have performed the following changes to the configuration:

  1. Add the following lines to <TRAINING_HOME>/src/config/Measures.csv:

    SalesRetail_R_SP_CIYE,Sales $ SP,CityYear,decimal,0d,total,ratio,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""$"",""thousandSeparated"":true,""mantissa"":2}",right,,,,,
    SalesUnits_U_SP_CIYE,Sales Units SP,CityYear,int,0,total,ratio,,,,"{""thousandSeparated"":true,""mantissa"":0}",right,,,,,
  2. As the CityYear intersection has not yet been used for other measures, you also need to add the following entry to <TRAINING_HOME>/src/config/Intersections.csv:

    CityYear,0,Location,City
    CityYear,1,Calendar,Year 
  3. Modifying the pivotConfig object in the wp-data-top-sheet according to the code sample below to display the levels and measures according to the requirements:

    {
      "id": "wp-data-top-sheet",
      "title": "Working Plan (top sheet)",
      "pivotConfig": {
        "axis": {
          "y": [
            {
              "qualifiedName": "-:Measures"
            }
          ],
          "x": [
            {
              "qualifiedName": "Location:City"
            }
          ],
          "z": [
            {
              "qualifiedName": "SalesRetail_R_SP_CIYE"
            },
            {
              "qualifiedName": "SalesUnits_U_SP_CIYE"
            }
          ],
          "slice": [
            {
              "qualifiedName": "Calendar:Year"
            }
          ]
        }
      },
      "views": {
        "rows": {
          "module": "Rows"
        },
        "columns": {
          "module": "Columns"
        },
        "measures": {
          "module": "Measures"
        },
        "slices": {
          "module": "Slices"
        },
        "grid": {
          "module": "PivotGrid"
        }
      },
      "module": "SheetView"
    }
  4. To add the aggregation to Location:ALL by adding it to the axis in pivotConfig:

    {
      "id": "wp-data-top-sheet",
      "title": "Working Plan (top sheet)",
      "pivotConfig": {
        "axis": {
          "y": [
            {
              "qualifiedName": "-:Measures"
            }
          ],
          "x": [
            {
              "qualifiedName": "Location:ALL",
              "displayMode": "NONE",
              "rollup": {
                "methodName": "DEFAULT"
              }
            },
            {
              "qualifiedName": "Location:City"
            }
          ],
          "z": [
            {
              "qualifiedName": "SalesRetail_R_SP_CIYE"
            },
            {
              "qualifiedName": "SalesUnits_U_SP_CIYE"
            }
          ],
          "slice": [
            {
              "qualifiedName": "Calendar:Year"
            }
          ]
        }
      },
      "views": {
        "rows": {
          "module": "Rows"
        },
        "columns": {
          "module": "Columns"
        },
        "measures": {
          "module": "Measures"
        },
        "slices": {
          "module": "Slices"
        },
        "grid": {
          "module": "PivotGrid"
        }
      },
      "module": "SheetView"
    }

Congratulations, you have completed tutorial 1! In the next tutorial you will learn about configuring more complex views as well as the more advanced data model related configuration options.