Tutorial 1 - Basic Model and View Configuration
Table of Contents
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
- LogicBlox Developer website. It contains blogs, tutorials, and white papers you may find interesting.
- LogicBlox Reference Manual
- LogicBlox Administration Guide
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
![]() |
-
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 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. -
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.
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:
![]() |
-
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. -
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. -
Can you configure the screen such that we see both
SKU
andStore
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. -
How about now showing ONLY the measures
SKU Description
,Sales $ TY
andSales Unit TY
, for eachSKU
andStore
?Tip
To find theSales $ TY
andSales 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. -
As you can see, the
SKU Description
is actually the same for eachStore
. This is because the value of the measure is only keyed at theSKU
level. TheSales $ TY
andSales Unit TY
are keyed at theSKU
,Store
andMonth
levels which means their values may differ perStore
(and even byMonth
orYear
).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 TotalsPlease note, this video is based on an older version of LogicBlox.
-
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 theReset 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! -
Can you configure the
SKU Maintenance
to show subtotals of measures atSubclass
andClass
levels? Your screen should be shown as follows.Tip
Add the
Subclass
andClass
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 ValuesPlease note, this video is based on an older version of LogicBlox.
Let's now exercise the sorting options shown in the video.
-
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 theSKU
field to accomplish this: -
Now, how about sorting the
Cost
of eachSKU
within theirSubclass
, in ascending order? -
Finally, place
SKU
in descending order according to theirCost
, regardless of theirSubclass
.Tip
For this one, you need to remove the subtotals to
Subclass
andClass
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
![]() |
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:
-
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 theProduct
dimension can be found in<TRAINING_HOME>/data/dev_data/initial/hierarchy/product.csv
. -
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.
-
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 themaster.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 refmodeDimensionId:LevelId:id
. The type of the refmode is theElementType
specified inLevels.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
, orCost
-
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 useR
for retail dollar,C
for cost dollar,U
for unit,P
for percentages,B
for boolean, andS
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 toSKSTMO
.
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 dividingtotal
bycount
.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 discussrecalc
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 theSKU Description
,Photo
, orLink
) an aggregation method doesn't make much sense, so we configured those asnone
. -
For the
Cost
andRetail
price measures perSKU
we are using theaverage
aggregation method. -
For the
Sales
andCost of Goods Sold
measures we are usingtotal
as the aggregation method. -
We used the method
ambig
forSkuBestSeller
. This means the value forSkuBestSeller
for an aggregated level, e.g.Subclass
, will only betrue
if allSKU
's in thatSubclass
is a best seller; or, the aggregated value will befalse
if allSKU
's in thatSubclass
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 highestSales $ TY
in 2015? -
Which
City
had the lowestCost of Goods Sold TY
in 2015? -
What is the average
Cost
of theSubclass
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
toMonth 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 eithertrue
orfalse
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 that0
for measures of typedecimal
is represented as0d
, and as0f
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 containstrue
,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
orright
. -
Readonly
: Allows you to configure a measure to be rendered read-only. If you set this property totrue
, it will not be possible for users to directly update this measure through the UI. The default isfalse
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
andSkuLink_S_SK
measures are configured withmouseover
andlink
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 withtotal
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 withreplicate
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 istotal
.replicate
is often used for percentage measures or boolean-typed measures. For percentage measures, the method of aggregation is oftenrecalc
, which we will discuss in Tutorial 3. For boolean measures, the method of aggregation is oftenambig
. These aggregation methods in combination withreplicate
avoids unexpected and confusing results.none
No spreading is applied. When a measure is configured withnone
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 asnone
. -
For the cost and retail price measures per Sku we
are using the
average
aggregation method. We are using thereplicate
spreading method. As a result, if you update these measures on the aggregate level, for instance updateCost
atSubclass
, 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 thereplicate
method. Setting the value at theSubclass
level totrue
, for instance, will make it true for allSKU
's of thatSubclass
. -
For the
TY
Sales $, Sales Units and Cost of Goods Sold measures we are usingtotal
as the aggregation method andratio
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, specifyingProduct
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:
- 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).
- Create a canvas to lay out your view(s).
- 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
: Theid
property of this canvas issku-maintenance-canvas
. This is theid
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 viewsku-maintenance-sheet
is included. Themodule
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. Thedirection
property of this object specifies the direction in which the views defined in the list ofpanes
are split. Userow
to split panes from left to right (within the same row) and usecolumn
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
: Themodule
property with the valueCanvasView
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 sameid
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 thepivotConfig
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:
-
The levels
Product:Subclass
andProduct:Sku
are displayed on the rows. -
The level
Location:City
is displayed together with theMeasures
field on the columns. -
The level
Calendar:Year
is shown on the slice. -
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 they
axis and configure to show theDEFAULT
rollup method. This means that each measure will be aggregated using its default aggregation method (that we configured earlier via theMeasures.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
orRollup
checkboxes. -
We add the
Location:ALL
level to thex
axis. Notice how we don't just add arollup
entry, but also configure thedisplayMode
to beNONE
. 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:
-
Use
sortOrder
to configure whether the sort should beasc
(short for "ascending") ordesc
(short for "descending"). -
The
floatingAxis
in our example is they
axis, that's where we have theProduct:Sku
level. ThefloatingAxis
is always the opposite of the axis where the measures are on. -
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.
-
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" } ] } ] } ] }
-
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
andwp-data-bottom-sheet
). Make sure that you position the two views below each other, by setting the rightdirection
in thetileLayout
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:
-
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 towp-data-bottom-sheet
. Note that you need to now modify theid
andtitle
properties from this sheets, to reflect its new identity.id
ofwp-data-bottom-sheet.json
should bewp-data-bottom-sheet
title
ofwp-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!
-
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 theCity
,Year
intersection. (Typically, strategic plans are set at very high levels, and thus the coarse levels in the measure's intersection) -
Create the measure
Sales_SP_U_SY
, which will hold the SP version of the Sales Units. Similar toSalesRetail_R_SP_CIYE
, this measure should also be defined at theCity
,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
. -
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.
-
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.
-
Update the top view to show the aggregation to
Location:ALL
. - 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:
-
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,,,,,
-
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
-
Modifying the
pivotConfig
object in thewp-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" }
-
To add the aggregation to
Location:ALL
by adding it to theaxis
inpivotConfig
:{ "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.