Tutorial 1 - Basic Model and View Configuration

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:

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:

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

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:

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,,,,,,,,,,,,,

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>.

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.

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.

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.

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.

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:

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

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:

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:

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:

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:

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.

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:

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:

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.

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.

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:

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"
 }

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:

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?

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:

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.

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.

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.