Tutorial 3 - Getting Started With CubiQL


1. Introduction to CubiQL

Tutorial 1 and 2 introduce the basics of data modeling and view configuration. In this tutorial, we learn how to query data using CubiQL expressions. We will also introduce a number of Modeler features that are configured using CubiQL expressions.

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

Run Me

$ ./tools/restore-base tutorial-3
$ make clean
$ lb config
$ make

Tip

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

1.1. Welcome to the CubiQL REPL

We'll start our experimentation with CubiQL in the CubiQL REPL. The REPL (standing for Read-Eval-Print Loop) is a programming environment provided by LogicBlox which enables you to interact with the measure service and query it, by evaluating CubiQL one expression at a time. Start the REPL by running the following command:

Run Me

$ lb measure-service repl --uri ws://localhost:55183/modeler-training/websocket/measure

You should now see the following output in your terminal:

Welcome to the
   ___     _    _  ___  _      ___ ___ ___ _
  / __|  _| |__(_)/ _ \| |    | _ \ __| _ \ |
 | (_| || | '_ \ | (_) | |__  |   / _||  _/ |__
  \___\_,_|_.__/_|\__\_\____| |_|_\___|_| |____|

Enter ':help' for a list of commands.

Connecting to measure service at ws://localhost:55183/modeler-training/websocket/measure ...
>

1.2. Querying measure data

It's time to write our very first CubiQL expression. Let's start by querying some sales data. Remember that the sales are stored in the measure SalesRetail_R_TY_SKSTMO.

To query the values stored in this measure, simply type its name in the REPL.

Run Me

> SalesRetail_R_TY_SKSTMO

       Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
     "Navel Oranges" ┊       "store-13" ┊       "2017-Jan" ┊        83824.66d
      "Blood Orange" ┊       "store-13" ┊       "2017-Jan" ┊        29860.53d
             "Beets" ┊       "store-13" ┊       "2017-Jan" ┊         55172.8d
   "Pink Grapefruit" ┊       "store-13" ┊       "2017-Jan" ┊        50920.07d
      "Blood Orange" ┊        "store-1" ┊       "2017-Jan" ┊        10042.97d
"Honeyscrisp Apples" ┊       "store-13" ┊       "2017-Jan" ┊       136162.05d
          "Radishes" ┊       "store-13" ┊       "2017-Jan" ┊         1124.35d
       "Clementines" ┊       "store-13" ┊       "2017-Jan" ┊       143093.83d
      "Granny Smith" ┊       "store-13" ┊       "2017-Jan" ┊        40006.73d
            "Ginger" ┊       "store-13" ┊       "2017-Jan" ┊        73832.96d
   "Pink Grapefruit" ┊        "store-1" ┊       "2017-Jan" ┊         29843.1d
      "Granny Smith" ┊        "store-1" ┊       "2017-Jan" ┊       129746.79d
"Honeyscrisp Apples" ┊        "store-1" ┊       "2017-Jan" ┊       138699.96d
             "Beets" ┊        "store-1" ┊       "2017-Jan" ┊        55164.54d
       "Clementines" ┊        "store-1" ┊       "2017-Jan" ┊        46730.02d
     "Navel Oranges" ┊        "store-1" ┊       "2017-Jan" ┊         75296.1d
            "Ginger" ┊        "store-1" ┊       "2017-Jan" ┊         97953.3d
          "Radishes" ┊        "store-1" ┊       "2017-Jan" ┊        38874.65d
      "Granny Smith" ┊        "store-4" ┊       "2017-Jan" ┊         2831.43d
            "Ginger" ┊        "store-4" ┊       "2017-Jan" ┊        77533.08d
             "Beets" ┊        "store-4" ┊       "2017-Jan" ┊        75894.57d
   "Pink Grapefruit" ┊        "store-4" ┊       "2017-Jan" ┊         9024.07d
     "Navel Oranges" ┊        "store-4" ┊       "2017-Jan" ┊       141998.94d
      "Blood Orange" ┊        "store-4" ┊       "2017-Jan" ┊         7194.91d
          "Radishes" ┊        "store-4" ┊       "2017-Jan" ┊        10871.37d
       "Clementines" ┊        "store-4" ┊       "2017-Jan" ┊       122569.66d
            "Ginger" ┊        "store-3" ┊       "2017-Jan" ┊        77098.97d
          "Radishes" ┊        "store-3" ┊       "2017-Jan" ┊         30884.8d
   "Pink Grapefruit" ┊        "store-3" ┊       "2017-Jan" ┊         1084.18d
      "Granny Smith" ┊        "store-3" ┊       "2017-Jan" ┊        119511.6d

   Displayed 30 out of 1296 result entries.

Congratulations! You've just written your first CubiQL expression.

Now let's take a closer look at the result of our query:

  • The measure SalesRetail_R_TY_SKSTMO is keyed by the combination of Product:Sku, Location:Store, and Calendar:Month. The data is returned in a table that contains a row for each position at this intersection holding a value.
  • The value of measure SalesRetail_R_TY_SKSTMO at each position is returned in the last column. The datatype of SalesRetail_R_TY_SKSTMO is decimal, this is reflected in the header name of the column (value of decimal) and the notation of the numbers (e.g. 83824.66d).
  • By default, the REPL only displays the first 30 results. This default can be overridden using the :limit REPL command. Type :help in the REPL to find more information on how to use this and other commands.

Tip

As you've just experienced, when you type a CubiQL expression in the CubiQL REPL and press ENTER, it will be automatically evaluated. This is great for simple one-line CubiQL expressions, but at some point, you'll find yourself writing multi-line CubiQL expressions.

For instance, to improve readability and prevent duplication of certain expressions inside your query, you could use a let expression which typically spans multiple lines. To evaluate this expression in the REPL, type :query to open multi-line mode. Then, type or paste your expression. Press CTRL+D when ready.

> :query
// Entering multi-line mode (ctrl-D to finish)
  let (
    sales = SalesRetail_R_TY_SKSTMO
  ) in (
    sales
  )
// Exiting multi-line mode, now evaluating.

       Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
            "Ginger" ┊       "store-13" ┊       "2017-Feb" ┊        52086.66d
      "Granny Smith" ┊       "store-13" ┊       "2017-Feb" ┊        24265.17d
       "Clementines" ┊       "store-13" ┊       "2017-Feb" ┊       260898.79d
             "Beets" ┊       "store-13" ┊       "2017-Feb" ┊        37061.53d
"Honeyscrisp Apples" ┊       "store-13" ┊       "2017-Feb" ┊        53653.08d
      "Blood Orange" ┊       "store-13" ┊       "2017-Feb" ┊        28697.53d
      "Granny Smith" ┊        "store-1" ┊       "2017-Feb" ┊         69411.5d
"Honeyscrisp Apples" ┊        "store-1" ┊       "2017-Feb" ┊       143629.08d
             "Beets" ┊        "store-1" ┊       "2017-Feb" ┊        51764.55d
            "Ginger" ┊        "store-1" ┊       "2017-Feb" ┊        96345.73d
     "Navel Oranges" ┊       "store-13" ┊       "2017-Feb" ┊        19094.92d
   "Pink Grapefruit" ┊       "store-13" ┊       "2017-Feb" ┊        16484.34d
      "Blood Orange" ┊        "store-1" ┊       "2017-Feb" ┊        13530.75d
          "Radishes" ┊       "store-13" ┊       "2017-Feb" ┊         2163.13d
"Honeyscrisp Apples" ┊        "store-4" ┊       "2017-Feb" ┊         3442.56d
      "Blood Orange" ┊        "store-4" ┊       "2017-Feb" ┊         7349.32d
            "Ginger" ┊        "store-4" ┊       "2017-Feb" ┊        13569.39d
      "Granny Smith" ┊        "store-4" ┊       "2017-Feb" ┊         21106.1d
   "Pink Grapefruit" ┊        "store-1" ┊       "2017-Feb" ┊        56713.77d
       "Clementines" ┊        "store-1" ┊       "2017-Feb" ┊       162611.12d
          "Radishes" ┊        "store-1" ┊       "2017-Feb" ┊        31288.77d
     "Navel Oranges" ┊        "store-1" ┊       "2017-Feb" ┊       135470.44d
      "Blood Orange" ┊        "store-3" ┊       "2017-Feb" ┊        28337.24d
          "Radishes" ┊        "store-4" ┊       "2017-Feb" ┊         3239.72d
      "Granny Smith" ┊        "store-3" ┊       "2017-Feb" ┊       125013.07d
"Honeyscrisp Apples" ┊        "store-3" ┊       "2017-Feb" ┊        30762.99d
       "Clementines" ┊        "store-4" ┊       "2017-Feb" ┊       216083.64d
             "Beets" ┊        "store-4" ┊       "2017-Feb" ┊        70720.32d
     "Navel Oranges" ┊        "store-4" ┊       "2017-Feb" ┊         1579.56d
   "Pink Grapefruit" ┊        "store-4" ┊       "2017-Feb" ┊         5299.98d

   Displayed 30 out of 1296 result entries.

Using aggregations

When you specify only the name of a measure in your CubiQL expression, the measure service returns measure values at the base intersection as defined in the data model. In this case that would be Sku, Store, Month. To inspect the data of measure SalesRetail_R_TY_SKSTMO at a more specific intersection, you can aggregate its values by specifying the intersection you're interested in.

For instance, use the following CubiQL expression to query data at the Class, Country, Year intersection. The measure service will use measure SalesRetail_R_TY_SKSTMO's default aggregation method total to aggregate values to this higher intersection.

Run Me

> SalesRetail_R_TY_SKSTMO[{Class,Country,Year}]

Product : Class ┊ Location : Country ┊ Calendar : Year ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
   "Vegetables" ┊        "country-1" ┊          "2017" ┊      4653783.78d
        "Fruit" ┊        "country-2" ┊          "2017" ┊      4881256.29d
   "Vegetables" ┊        "country-2" ┊          "2019" ┊             0.0d
        "Fruit" ┊        "country-1" ┊          "2017" ┊     14918917.87d
   "Vegetables" ┊        "country-2" ┊          "2017" ┊      1672881.48d
   "Vegetables" ┊        "country-1" ┊          "2018" ┊        316532.7d
        "Fruit" ┊        "country-2" ┊          "2018" ┊       209727.71d
        "Fruit" ┊        "country-1" ┊          "2018" ┊      1253599.77d
   "Vegetables" ┊        "country-1" ┊          "2019" ┊             0.0d
        "Fruit" ┊        "country-2" ┊          "2019" ┊             0.0d
   "Vegetables" ┊        "country-2" ┊          "2018" ┊       150776.07d
        "Fruit" ┊        "country-1" ┊          "2019" ┊             0.0d

   Displayed all 12 result entries.

Please note that this expression will only be valid if the Class, Country, and Year levels are unique and are not used in distinct dimensions. This is indeed the case in our training application. However, if a level name is used in multiple dimensions, you should explicitly qualify the dimension name in the intersection:

> SalesRetail_R_TY_SKSTMO[{Product.Class,Location.Country,Calendar.Year}]

Product : Class ┊ Location : Country ┊ Calendar : Year ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
   "Vegetables" ┊        "country-1" ┊          "2017" ┊      4653783.78d
        "Fruit" ┊        "country-1" ┊          "2017" ┊     14918917.87d
   "Vegetables" ┊        "country-2" ┊          "2017" ┊      1672881.48d
        "Fruit" ┊        "country-2" ┊          "2017" ┊      4881256.29d
   "Vegetables" ┊        "country-1" ┊          "2019" ┊             0.0d
        "Fruit" ┊        "country-1" ┊          "2019" ┊             0.0d
   "Vegetables" ┊        "country-2" ┊          "2019" ┊             0.0d
        "Fruit" ┊        "country-2" ┊          "2019" ┊             0.0d
   "Vegetables" ┊        "country-1" ┊          "2018" ┊        316532.7d
        "Fruit" ┊        "country-1" ┊          "2018" ┊      1253599.77d
   "Vegetables" ┊        "country-2" ┊          "2018" ┊       150776.07d
        "Fruit" ┊        "country-2" ┊          "2018" ┊       209727.71d

   Displayed all 12 result entries.

You might be tempted to write intersections in your expressions in the order as they are configured in Intersections.csv, but you don't have to. The measure service will automatically resolve intersections in CubiQL expressions to the exact order of arguments to the underlying predicates.

For instance, the intersection of our measure SalesRetail_R_TY_SKSTMO is configured as Sku,Store,Month, but CubiQL allows you to write the intersection in any order:

Run Me

> SalesRetail_R_TY_SKSTMO[{Year,Class,Country}]

Product : Class ┊ Location : Country ┊ Calendar : Year ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
   "Vegetables" ┊        "country-2" ┊          "2017" ┊      1742623.17d
        "Fruit" ┊        "country-2" ┊          "2017" ┊       4971440.2d
   "Vegetables" ┊        "country-1" ┊          "2017" ┊      4796014.97d
        "Fruit" ┊        "country-1" ┊          "2017" ┊     15186215.65d
   "Vegetables" ┊        "country-2" ┊          "2019" ┊             0.0d
        "Fruit" ┊        "country-2" ┊          "2019" ┊             0.0d
   "Vegetables" ┊        "country-1" ┊          "2019" ┊             0.0d
        "Fruit" ┊        "country-1" ┊          "2019" ┊             0.0d
   "Vegetables" ┊        "country-2" ┊          "2018" ┊       152400.96d
        "Fruit" ┊        "country-2" ┊          "2018" ┊       212716.45d
   "Vegetables" ┊        "country-1" ┊          "2018" ┊       328569.15d
        "Fruit" ┊        "country-1" ┊          "2018" ┊      1250598.17d

   Displayed all 12 result entries.

You can also aggregate away a dimension by omitting a level for that dimension while specifying the intersection. Run the following CubiQL expression to see the total sales per Class and Country:

Run Me

> SalesRetail_R_TY_SKSTMO[{Class,Country}]

Product : Class ┊ Location : Country ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
   "Vegetables" ┊        "country-2" ┊      1823657.55d
   "Vegetables" ┊        "country-1" ┊      4970316.48d
        "Fruit" ┊        "country-2" ┊       5090984.0d
        "Fruit" ┊        "country-1" ┊     16172517.64d

   Displayed all 4 result entries.

And finally, aggregate away all dimensions in your CubiQL expression to see the total sales over all data stored in the measure SalesRetail_R_TY_SKSTMO.

Run Me

> SalesRetail_R_TY_SKSTMO[{}]

value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
    28057475.67d

   Displayed all 1 result entries.

CubiQL doesn't limit you to using the default aggregation method. In your aggregation expression, you can also specify which aggregation method you want to use.

For instance, you can use the following expression to calculate the average sales per Class and Country:

Run Me

> average SalesRetail_R_TY_SKSTMO @ {Class,Country}

Location : Country ┊ Product : Class ┊          value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
       "country-2" ┊    "Vegetables" ┊ 16885.718055555555555556d
       "country-1" ┊         "Fruit" ┊ 24957.588950617283950617d
       "country-2" ┊         "Fruit" ┊  23569.37037037037037037d
       "country-1" ┊    "Vegetables" ┊ 15340.482962962962962963d

   Displayed all 4 result entries.

Please note that the syntax of this aggregation expression is different than the one using the default aggregation method.

Let's revisit our first aggregation expression SalesRetail_R_TY_SKSTMO[{Class,Country}]. Instead of relying on the default aggregation method as defined the data model, you could have also specified the total aggregation method in your expression:

Run Me

> total SalesRetail_R_TY_SKSTMO @ {Class,Country}

Product : Class ┊ Location : Country ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
   "Vegetables" ┊        "country-2" ┊      1823657.55d
        "Fruit" ┊        "country-1" ┊     16172517.64d
        "Fruit" ┊        "country-2" ┊       5090984.0d
   "Vegetables" ┊        "country-1" ┊      4970316.48d

   Displayed all 4 result entries.

Alternatively, you can also use grouping instructions in your aggregation expressions to get the same result:

> total SalesRetail_R_TY_SKSTMO by to Class to Country all Calendar

Product : Class ┊ Location : Country ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
   "Vegetables" ┊        "country-2" ┊      1823657.55d
        "Fruit" ┊        "country-1" ┊     16172517.64d
        "Fruit" ┊        "country-2" ┊       5090984.0d
   "Vegetables" ┊        "country-1" ┊      4970316.48d

   Displayed all 4 result entries.

Similar to how we can define intersections in CubiQL expressions, the order of grouping instructions in aggregation expressions doesn’t matter.

When using grouping instructions, make sure to explicitly define an all grouping instruction when you want to aggregate away a dimension. For instance, when you omit a grouping instruction for the Calendar dimension, the measure service will use the base level of the omitted dimension. In this case, that would be Calendar:Month:

> total SalesRetail_R_TY_SKSTMO by to Class to Country

Product : Class ┊ Location : Country ┊ Calendar : Month ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
        "Fruit" ┊        "country-1" ┊       "2017-Mar" ┊      1321390.32d
   "Vegetables" ┊        "country-1" ┊       "2017-Mar" ┊        235887.4d
        "Fruit" ┊        "country-2" ┊       "2017-Mar" ┊       463512.27d
   "Vegetables" ┊        "country-2" ┊       "2017-Mar" ┊       191630.17d
        "Fruit" ┊        "country-1" ┊       "2017-May" ┊       995194.01d
   "Vegetables" ┊        "country-1" ┊       "2017-May" ┊       511090.14d
        "Fruit" ┊        "country-2" ┊       "2017-May" ┊       425538.59d
   "Vegetables" ┊        "country-2" ┊       "2017-May" ┊       168020.97d
        "Fruit" ┊        "country-1" ┊       "2017-Feb" ┊      1244276.21d
   "Vegetables" ┊        "country-1" ┊       "2017-Feb" ┊       368167.61d
        "Fruit" ┊        "country-2" ┊       "2017-Feb" ┊       254861.16d
   "Vegetables" ┊        "country-2" ┊       "2017-Feb" ┊        87529.43d
        "Fruit" ┊        "country-1" ┊       "2017-Jan" ┊      1313379.98d
   "Vegetables" ┊        "country-1" ┊       "2017-Jan" ┊       497718.28d
        "Fruit" ┊        "country-2" ┊       "2017-Jan" ┊        425140.5d
   "Vegetables" ┊        "country-2" ┊       "2017-Jan" ┊       164299.02d
        "Fruit" ┊        "country-1" ┊       "2017-Apr" ┊       1021401.1d
   "Vegetables" ┊        "country-1" ┊       "2017-Apr" ┊       368304.55d
        "Fruit" ┊        "country-2" ┊       "2017-Apr" ┊       566475.16d
   "Vegetables" ┊        "country-2" ┊       "2017-Apr" ┊       212932.77d
        "Fruit" ┊        "country-1" ┊       "2017-Oct" ┊      1522273.26d
   "Vegetables" ┊        "country-1" ┊       "2017-Oct" ┊       417694.22d
        "Fruit" ┊        "country-2" ┊       "2017-Oct" ┊       440196.15d
   "Vegetables" ┊        "country-2" ┊       "2017-Oct" ┊        48343.71d
        "Fruit" ┊        "country-1" ┊       "2017-Nov" ┊      1377694.95d
   "Vegetables" ┊        "country-1" ┊       "2017-Nov" ┊       311403.63d
        "Fruit" ┊        "country-2" ┊       "2017-Nov" ┊       321581.77d
   "Vegetables" ┊        "country-2" ┊       "2017-Nov" ┊       115464.17d
        "Fruit" ┊        "country-1" ┊       "2017-Dec" ┊      1090261.04d
   "Vegetables" ┊        "country-1" ┊       "2017-Dec" ┊       489266.24d

   Displayed 30 out of 144 result entries.

Dimension splitting

In the previous section, we used aggregation expressions to roll up values to a higher level within their dimension. For instance, we rolled up Product:Sku to Product:Class using the Default hierarchy of the Product dimension:

Now let's also add a level Vendor to the Product dimension. We also configure an alternate hierarchy within the Product dimension of our training application which defines that:

  • Every Sku is supplied by some Vendor. So, a Product:Sku can now also roll up to a Product:Vendor.
  • Every Vendor only supplies Skus for some Class. So, Product:Vendor itself rolls up to Product:Class.

After adding the Vendor level and the alternate hierarchy, the Product dimension level relationships will look like this:

We've prepared this extension of the Product dimension with an additional Vendor level in the "base" of tutorial 3. Additionally, the <TRAINING_HOME>/src/config/Hierarchies.csv file now also contains the alternate hierarchy named Vendor. Take a look at <TRAINING_HOME>/src/config/Hierarchies.csv to see this new hierarchy.

Ok, let's write some CubiQL again! We start by writing an aggregation expression that aggregates to the new Vendor level.

Run Me

> total SalesRetail_R_TY_SKSTMO @ {Vendor}

    Product : Vendor ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
   "Vegistatic Inc." ┊      2372928.83d
"Fruit Masters Inc." ┊      8638148.73d
   "We love Veggies" ┊       4421045.2d
    "Crispiest Inc." ┊      7282780.67d
       "Best Citrus" ┊      5342572.24d

   Displayed all 5 result entries.

As expected, this query gives us the sales totals per Vendor.

Now, what if we want to discover the sales totals per Subclass and Vendor? Let's try to write an aggregation expression and run it in the CubiQL REPL:

Run Me

> total SalesRetail_R_TY_SKSTMO @ {Subclass,Vendor}
Problems during preprocessing in REPL:
[ERROR]  com.logicblox.common.ProblemException: AtomicProblem {
      code = Exception
  severity = Error
   message = Levels in intersection { Product.Subclass, Product.Vendor } are not all uniquely labeled
...

Computer says no. This error message tells us that the levels Subclass and Vendor are not uniquely labeled. To understand this error, you should first realize levels in an intersection are labeled with their dimension name by default. So, the Subclass and Vendor levels in our intersection can actually be written as Product:Subclass and Product:Vendor, or Product:Product.Subclass and Product:Product.Vendor if you also qualify the dimension names of these levels. As you might have guessed, the prefix Product: is the label of the level.

Go ahead and give some unique labels to the levels in the intersection in our aggregation expression:

> total SalesRetail_R_TY_SKSTMO @ {Product:Subclass,ProductVendor:Vendor}
Problems during preprocessing in REPL:
[ERROR]  com.logicblox.common.ProblemException: AtomicProblem {
      code = Exception
  severity = Error
   message = Cannot aggregate from { Product.Sku, Location.Store, Calendar.Month } to { Product.Subclass, ProductVendor: Product.Vendor } using total.
...

Still no luck. Apparently, also the base levels in the keys of the measure are labeled with their dimension name by default. What if we could split the Product dimension of our measure? We're lucky, CubiQL supports dimension splitting!

Dimension splitting allows us to write a query that aggregates the values along more than one hierarchy.

First, we duplicate the key (or "column") of SalesRetail_R_TY_SKSTMO referring to the Product dimension using a split expression. For instance, to be able to aggregate along the Default and Vendor hierarchy, we can split the column of the Product dimension into two separate columns labeled as Product: and ProductVendor:. We can then refer to these columns when we specify the intersection Subclass,Vendor in our aggregation expression:

Run Me

> total ( split Product to ProductVendor in SalesRetail_R_TY_SKSTMO ) @ {Product:Subclass, ProductVendor:Vendor}

Product : Subclass ┊ ProductVendor : Vendor ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
 "Root Vegetables" ┊      "We love Veggies" ┊       4421045.2d
          "Apples" ┊       "Crispiest Inc." ┊      7282780.67d
 "Root Vegetables" ┊      "Vegistatic Inc." ┊      2372928.83d
          "Citrus" ┊   "Fruit Masters Inc." ┊      8638148.73d
          "Citrus" ┊          "Best Citrus" ┊      5342572.24d

   Displayed all 5 result entries.

Yes, you did it! The levels in our intersection now have unique labels. And even more important, you just discovered new data using dimension splitting!

Filtering

Thus far, all our CubiQL expressions returned every available value for some measure. Oftentimes, you'll be interested in restricting your measure to a subset of positions by comparing the measure's value to a value you specify or to the value of some other measure at the same position.

As an example, let's put a filter on the sales data we're querying. Run the following CubiQL expression to restrict the results to all positions that hold a value less than 1000.

Run Me

> filter SalesRetail_R_TY_SKSTMO by < 1000
Problems during preprocessing in REPL:
[ERROR] (row 0 and column 7 to row 0 and column 30) The type of the expression being filtered, SalesRetail_R_TY_SKSTMO, is decimal, but the comparison, < 1000, has type int

Auwch! Make sure to use compatible values in your conditions. In this case, we used the integer value 1000 instead of the required decimal value 1000d in our filter condition.

Let's try again:

> filter SalesRetail_R_TY_SKSTMO by < 1000d

       Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
      "Blood Orange" ┊        "store-1" ┊       "2017-Aug" ┊          296.57d
          "Radishes" ┊        "store-3" ┊       "2017-Sep" ┊          772.12d
             "Beets" ┊        "store-3" ┊       "2018-Jul" ┊             0.0d
      "Blood Orange" ┊        "store-3" ┊       "2018-Jul" ┊             0.0d
      "Granny Smith" ┊        "store-4" ┊       "2017-Nov" ┊          801.99d
      "Granny Smith" ┊        "store-3" ┊       "2018-Jul" ┊             0.0d
       "Clementines" ┊        "store-3" ┊       "2018-Jul" ┊             0.0d
      "Blood Orange" ┊       "store-13" ┊       "2018-Jul" ┊             0.0d
   "Pink Grapefruit" ┊        "store-3" ┊       "2018-Jul" ┊             0.0d
          "Radishes" ┊        "store-3" ┊       "2018-Jul" ┊             0.0d
"Honeyscrisp Apples" ┊        "store-3" ┊       "2018-Jul" ┊             0.0d
     "Navel Oranges" ┊        "store-3" ┊       "2018-Jul" ┊             0.0d
            "Ginger" ┊        "store-3" ┊       "2018-Jul" ┊             0.0d
       "Clementines" ┊       "store-13" ┊       "2018-Jul" ┊             0.0d
     "Navel Oranges" ┊       "store-13" ┊       "2018-Jul" ┊             0.0d
   "Pink Grapefruit" ┊       "store-13" ┊       "2018-Jul" ┊             0.0d
      "Granny Smith" ┊       "store-13" ┊       "2018-Jul" ┊             0.0d
"Honeyscrisp Apples" ┊       "store-13" ┊       "2018-Jul" ┊             0.0d
             "Beets" ┊       "store-13" ┊       "2018-Jul" ┊             0.0d
            "Ginger" ┊       "store-13" ┊       "2018-Jul" ┊             0.0d
          "Radishes" ┊       "store-13" ┊       "2018-Jul" ┊             0.0d
     "Navel Oranges" ┊        "store-4" ┊       "2018-Jul" ┊             0.0d
            "Ginger" ┊        "store-4" ┊       "2018-Jul" ┊             0.0d
      "Granny Smith" ┊        "store-4" ┊       "2018-Jul" ┊             0.0d
       "Clementines" ┊        "store-4" ┊       "2018-Jul" ┊             0.0d
             "Beets" ┊        "store-4" ┊       "2018-Jul" ┊             0.0d
      "Blood Orange" ┊        "store-4" ┊       "2018-Jul" ┊             0.0d
          "Radishes" ┊        "store-4" ┊       "2018-Jul" ┊             0.0d
"Honeyscrisp Apples" ┊        "store-4" ┊       "2018-Jul" ┊             0.0d
            "Ginger" ┊        "store-1" ┊       "2018-Jul" ┊             0.0d

   Displayed 30 out of 831 result entries.

You can further refine this query by adding an additional condition to the filter expression. This can either be a conjunction (condition1 and condition2) or a disjunction (condition1 or condition2). In our case, let's also filter out all positions with no sales at all.

Run Me

> filter SalesRetail_R_TY_SKSTMO by < 1000d and != 0d

 Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Blood Orange" ┊        "store-1" ┊       "2017-Aug" ┊          296.57d
    "Radishes" ┊        "store-3" ┊       "2017-Sep" ┊          772.12d
"Granny Smith" ┊        "store-4" ┊       "2017-Nov" ┊          801.99d

   Displayed all 3 result entries.

Position-only measures

Our first example with measure SalesRetail_R_TY_SKSTMO shows how data can be queried for measures that hold a value of some datatype. Modeler applications can also contain position-only measures. These type of measures do not contain a value, they encode positions at which there's a relation between its keys.

The "base" version of this tutorial contains a new measure called SkuAvailable_B_SK which encodes whether some Product:Sku is available at some Location:Store.

Run Me

> SkuAvailable_B_SK

Product : Sku ┊ Location : Store
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈

   Displayed all 0 result entries.

Apparently, this measure has not been populated yet. Open up your Modeler application and create a new Ad Hoc Canvas. Add the Sku and Store fields to the rows axis. Keep the Measures field on the columns axis. Add SkuAvailable_B_SK to the measures axis so we can make changes to this measure. Now, populate some values by clicking on the checkbox at a number of positions.

When you now run your CubiQL expression query in the REPL, the result will list all positions which you've just checked in the application.

Run Me

> SkuAvailable_B_SK

  Product : Sku ┊ Location : Store
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Navel Oranges" ┊       "store-13"
 "Blood Orange" ┊       "store-13"
     "Radishes" ┊        "store-4"
  "Clementines" ┊       "store-13"
"Navel Oranges" ┊        "store-3"
        "Beets" ┊        "store-3"
 "Blood Orange" ┊        "store-1"
 "Granny Smith" ┊        "store-4"
     "Radishes" ┊        "store-1"
        "Beets" ┊        "store-1"

   Displayed all 10 result entries.

Dropping values

When writing CubiQL expressions, there will be times you'll be more interested in positions at which data lives than the actual values. CubiQL allows you to query a measure with its values removed, obtaining a position-only measure.

Remember when we filtered our sales data using a filter expression? We applied that filter to a valued measure. So, our query returned the values at the filtered positions. Let's say we're just interested in the positions that satisfy the filter conditions. To achieve this, we can drop the values of the queries measure which gives us a position-only measure:

Run Me

> #(filter SalesRetail_R_TY_SKSTMO by < 1000d and != 0d)

 Product : Sku ┊ Location : Store ┊ Calendar : Month
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Blood Orange" ┊        "store-1" ┊       "2017-Aug"
    "Radishes" ┊        "store-3" ┊       "2017-Sep"
"Granny Smith" ┊        "store-4" ┊       "2017-Nov"

   Displayed all 3 result entries.

Please note that in many situations CubiQL requires you to add parentheses around the expression of which you want to drop the values: #( expression ).

Scalar measures

In our previous examples, we've seen some occasions where we specified some value within the condition of a filter. We call such an expression a literal. These literals we've seen so far are called scalar measures and have a pretty straight-forward syntax (e.g. 1.0d or "Beets").

Diving Deeper

A more verbose way of specifying the decimal 10d is { (10d) } : {} => decimal. Similarly, the string "Beets" can also be specified as { ("Beets") } : {} => string. This syntax starts to expose the anatomy of how a literal is actually defined in CubiQL. We'll see more uses of this syntax in the next section when we start discussing literal measures.

Literal measures

A literal measure is an explicit (literal) representation of all the data contained in the measure.

So, let's say you're only interested in the data related to one or specific level members of the Sku level. A literal expression allows you to define these exact level members. For instance, when you're only interested in the Sku with id equal to sku-01 you can use the following CubiQL expression to get a literal position-only measure:

Run Me

> {("sku-01")}:{Product.Sku}

 Product : Sku
┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Blood Orange"

   Displayed all 1 result entries.

And, you can also refer to multiple level members.

> {("sku-01"),("sku-02")}:{Product.Sku}

 Product : Sku
┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Blood Orange"
 "Clementines"

   Displayed all 2 result entries.

Dicing

These literal measures we just saw are useful when you want to specify which level members you're interested in when querying measure data. For instance, if we're only interested in the sales for the Sku with an id of sku-01 we can use a dice expression. Dicing is a special case of filtering where the measure service only looks at positions.

As an example, consider the sales data measure SalesRetail_R_TY_SKSTMO.

Run Me

> dice SalesRetail_R_TY_SKSTMO by {("sku-01")}:{Product.Sku}

 Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Blood Orange" ┊       "store-13" ┊       "2017-Mar" ┊         6791.72d
"Blood Orange" ┊        "store-3" ┊       "2017-Mar" ┊         9730.47d
"Blood Orange" ┊        "store-1" ┊       "2017-Mar" ┊        34742.93d
"Blood Orange" ┊        "store-4" ┊       "2017-Mar" ┊        10982.93d
"Blood Orange" ┊       "store-13" ┊       "2017-May" ┊        22394.79d
"Blood Orange" ┊        "store-3" ┊       "2017-May" ┊        21691.35d
"Blood Orange" ┊        "store-1" ┊       "2017-May" ┊        18138.63d
"Blood Orange" ┊        "store-4" ┊       "2017-May" ┊        16920.48d
"Blood Orange" ┊       "store-13" ┊       "2017-Feb" ┊        28697.53d
"Blood Orange" ┊        "store-3" ┊       "2017-Feb" ┊        28337.24d
"Blood Orange" ┊        "store-1" ┊       "2017-Feb" ┊        13530.75d
"Blood Orange" ┊        "store-4" ┊       "2017-Feb" ┊         7349.32d
"Blood Orange" ┊       "store-13" ┊       "2017-Jan" ┊        29860.53d
"Blood Orange" ┊        "store-3" ┊       "2017-Jan" ┊         4367.68d
"Blood Orange" ┊        "store-1" ┊       "2017-Jan" ┊        10042.97d
"Blood Orange" ┊        "store-4" ┊       "2017-Jan" ┊         7194.91d
"Blood Orange" ┊       "store-13" ┊       "2017-Apr" ┊        15938.85d
"Blood Orange" ┊        "store-3" ┊       "2017-Apr" ┊         8859.14d
"Blood Orange" ┊        "store-1" ┊       "2017-Apr" ┊         6725.54d
"Blood Orange" ┊        "store-4" ┊       "2017-Apr" ┊        18513.63d
"Blood Orange" ┊       "store-13" ┊       "2017-Oct" ┊        21017.33d
"Blood Orange" ┊        "store-3" ┊       "2017-Oct" ┊        11511.12d
"Blood Orange" ┊        "store-1" ┊       "2017-Oct" ┊        36747.84d
"Blood Orange" ┊        "store-4" ┊       "2017-Oct" ┊        29207.34d
"Blood Orange" ┊       "store-13" ┊       "2017-Nov" ┊        18505.05d
"Blood Orange" ┊        "store-3" ┊       "2017-Nov" ┊        16090.82d
"Blood Orange" ┊        "store-1" ┊       "2017-Nov" ┊        16963.37d
"Blood Orange" ┊        "store-4" ┊       "2017-Nov" ┊        14479.28d
"Blood Orange" ┊       "store-13" ┊       "2017-Dec" ┊         7208.39d
"Blood Orange" ┊        "store-3" ┊       "2017-Dec" ┊        34663.27d

   Displayed 30 out of 144 result entries.

Let's further refine the results of this query by also looking at the values of the returned positions. We just stated that a dice expression will only look at positions. So, to achieve this we also need to apply a filter on the returned positions.

Try to write a query which restricts the results to all positions of sku-01 that hold a value less than 1000. One approach could be to first dice and then apply a filter on the returned positions:

Run Me

> :query
// Entering multi-line mode (ctrl-D to finish)
  let (
    sales = ( dice SalesRetail_R_TY_SKSTMO by {("sku-01")}:{Product.Sku} )
  ) in (
    filter sales by < 1000d
  )
// Exiting multi-line mode, now evaluating.

 Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Blood Orange" ┊        "store-1" ┊       "2017-Aug" ┊          292.82d
"Blood Orange" ┊       "store-13" ┊       "2018-Apr" ┊             0.0d
"Blood Orange" ┊        "store-3" ┊       "2018-Apr" ┊             0.0d
"Blood Orange" ┊        "store-1" ┊       "2018-Apr" ┊             0.0d
"Blood Orange" ┊        "store-4" ┊       "2018-Apr" ┊             0.0d
"Blood Orange" ┊       "store-13" ┊       "2018-Feb" ┊             0.0d
"Blood Orange" ┊        "store-3" ┊       "2018-Feb" ┊             0.0d
"Blood Orange" ┊        "store-1" ┊       "2018-Feb" ┊             0.0d
"Blood Orange" ┊        "store-4" ┊       "2018-Feb" ┊             0.0d
"Blood Orange" ┊       "store-13" ┊       "2018-Oct" ┊             0.0d
"Blood Orange" ┊        "store-3" ┊       "2018-Oct" ┊             0.0d
"Blood Orange" ┊        "store-1" ┊       "2018-Oct" ┊             0.0d
"Blood Orange" ┊        "store-4" ┊       "2018-Oct" ┊             0.0d
"Blood Orange" ┊       "store-13" ┊       "2018-Mar" ┊             0.0d
"Blood Orange" ┊        "store-3" ┊       "2018-Mar" ┊             0.0d
"Blood Orange" ┊        "store-1" ┊       "2018-Mar" ┊             0.0d
"Blood Orange" ┊        "store-4" ┊       "2018-Mar" ┊             0.0d
"Blood Orange" ┊       "store-13" ┊       "2018-Dec" ┊             0.0d
"Blood Orange" ┊        "store-3" ┊       "2018-Dec" ┊             0.0d
"Blood Orange" ┊        "store-1" ┊       "2018-Dec" ┊             0.0d
"Blood Orange" ┊        "store-4" ┊       "2018-Dec" ┊             0.0d
"Blood Orange" ┊       "store-13" ┊       "2018-Sep" ┊             0.0d
"Blood Orange" ┊        "store-3" ┊       "2018-Sep" ┊             0.0d
"Blood Orange" ┊        "store-1" ┊       "2018-Sep" ┊             0.0d
"Blood Orange" ┊        "store-4" ┊       "2018-Sep" ┊             0.0d
"Blood Orange" ┊       "store-13" ┊       "2018-Jun" ┊             0.0d
"Blood Orange" ┊        "store-3" ┊       "2018-Jun" ┊             0.0d
"Blood Orange" ┊        "store-1" ┊       "2018-Jun" ┊             0.0d
"Blood Orange" ┊        "store-4" ┊       "2018-Jun" ┊             0.0d
"Blood Orange" ┊       "store-13" ┊       "2018-Nov" ┊             0.0d

   Displayed 30 out of 93 result entries.

Even though this query is correct, you could also write it using a single filter expression, because filter can actually subsume dice.

Run Me

We first need to associate our filter value 1000d, of which we already know it's defined as { (1000d) } : {} => decimal, to the sku-01 position. We can do this by writing the following valued literal measure:

> {("sku-01",1000d)}:{Product.Sku} => decimal

 Product : Sku ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Blood Orange" ┊          1000.0d

   Displayed all 1 result entries.

Okay, now let's use this literal measure in our filter expression:

> filter SalesRetail_R_TY_SKSTMO by < {("sku-01",1000d)}:{Product.Sku} => decimal

 Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Blood Orange" ┊        "store-3" ┊       "2018-Feb" ┊             0.0d
"Blood Orange" ┊        "store-1" ┊       "2017-Aug" ┊          292.82d
"Blood Orange" ┊        "store-4" ┊       "2018-Apr" ┊             0.0d
"Blood Orange" ┊       "store-13" ┊       "2018-Apr" ┊             0.0d
"Blood Orange" ┊        "store-3" ┊       "2018-Mar" ┊             0.0d
"Blood Orange" ┊        "store-1" ┊       "2018-Apr" ┊             0.0d
"Blood Orange" ┊        "store-4" ┊       "2018-Feb" ┊             0.0d
"Blood Orange" ┊       "store-13" ┊       "2018-Feb" ┊             0.0d
"Blood Orange" ┊        "store-3" ┊       "2018-Apr" ┊             0.0d
"Blood Orange" ┊        "store-1" ┊       "2018-Feb" ┊             0.0d
"Blood Orange" ┊        "store-4" ┊       "2018-Oct" ┊             0.0d
"Blood Orange" ┊       "store-13" ┊       "2018-Oct" ┊             0.0d
"Blood Orange" ┊        "store-3" ┊       "2018-Sep" ┊             0.0d
"Blood Orange" ┊        "store-1" ┊       "2018-Oct" ┊             0.0d
"Blood Orange" ┊        "store-4" ┊       "2018-Mar" ┊             0.0d
"Blood Orange" ┊       "store-13" ┊       "2018-Mar" ┊             0.0d
"Blood Orange" ┊        "store-3" ┊       "2018-Oct" ┊             0.0d
"Blood Orange" ┊        "store-1" ┊       "2018-Mar" ┊             0.0d
"Blood Orange" ┊        "store-4" ┊       "2018-Dec" ┊             0.0d
"Blood Orange" ┊       "store-13" ┊       "2018-Dec" ┊             0.0d
"Blood Orange" ┊        "store-3" ┊       "2018-Nov" ┊             0.0d
"Blood Orange" ┊        "store-1" ┊       "2018-Dec" ┊             0.0d
"Blood Orange" ┊        "store-4" ┊       "2018-Sep" ┊             0.0d
"Blood Orange" ┊       "store-13" ┊       "2018-Sep" ┊             0.0d
"Blood Orange" ┊        "store-3" ┊       "2018-Dec" ┊             0.0d
"Blood Orange" ┊        "store-1" ┊       "2018-Sep" ┊             0.0d
"Blood Orange" ┊        "store-4" ┊       "2018-Jun" ┊             0.0d
"Blood Orange" ┊       "store-13" ┊       "2018-Jun" ┊             0.0d
"Blood Orange" ┊        "store-3" ┊       "2018-May" ┊             0.0d
"Blood Orange" ┊        "store-1" ┊       "2018-Jun" ┊             0.0d

   Displayed 30 out of 93 result entries.

That works! As you can see, this CubiQL expression will simultaneously dice by the Sku yet also filter by the value.

Finally, let's clean up the results by also filtering out all positions with no sales at all using a conjunction.

Run Me

> filter SalesRetail_R_TY_SKSTMO by < {("sku-01",1000d)}:{Product.Sku} => decimal and != 0d

 Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Blood Orange" ┊        "store-1" ┊       "2017-Aug" ┊          292.82d

   Displayed all 1 result entries.

1.3. Querying level attributes

Up until now, we've only queried measure data. In CubiQL, you can also query attribute data of levels.

Use the following expression to query the value of the id attribute of our Skus:

Run Me

> Product.Sku.id

       Product : Sku ┊ value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
      "Blood Orange" ┊        "sku-01"
             "Beets" ┊        "sku-07"
"Honeyscrisp Apples" ┊        "sku-06"
          "Radishes" ┊        "sku-09"
            "Ginger" ┊        "sku-08"
     "Navel Oranges" ┊        "sku-03"
       "Clementines" ┊        "sku-02"
      "Granny Smith" ┊        "sku-05"
   "Pink Grapefruit" ┊        "sku-04"

   Displayed all 9 result entries.

The previous expression explicitly qualifies the dimension Product in which the level Sku is defined. As we've mentioned before, this syntax is useful when the same level name is used in distinct dimensions. In our case, a level named Sku has only been defined in the Product dimension which allows us to omit the dimension name in our expression:

> Sku.id

       Product : Sku ┊ value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
      "Blood Orange" ┊        "sku-01"
             "Beets" ┊        "sku-07"
"Honeyscrisp Apples" ┊        "sku-06"
          "Radishes" ┊        "sku-09"
            "Ginger" ┊        "sku-08"
     "Navel Oranges" ┊        "sku-03"
       "Clementines" ┊        "sku-02"
      "Granny Smith" ┊        "sku-05"
   "Pink Grapefruit" ┊        "sku-04"

   Displayed all 9 result entries.

To query the value of the label attributes of these Skus:

Run Me

> Product.Sku.label

       Product : Sku ┊      value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
      "Blood Orange" ┊       "Blood Orange"
             "Beets" ┊              "Beets"
"Honeyscrisp Apples" ┊ "Honeyscrisp Apples"
          "Radishes" ┊           "Radishes"
            "Ginger" ┊             "Ginger"
     "Navel Oranges" ┊      "Navel Oranges"
       "Clementines" ┊        "Clementines"
      "Granny Smith" ┊       "Granny Smith"
   "Pink Grapefruit" ┊    "Pink Grapefruit"

   Displayed all 9 result entries.

Observe that the values of the label attribute are used in REPL as the keys in result tables.

The CubiQL expressions learned earlier in this tutorial, such as the filter expression, can also be applied to the results of an attribute expression. For instance, to find all Sku labels which are not equal to Beets you can apply the following filter:

Run Me

> filter Product.Sku.label by != "Beets"

       Product : Sku ┊      value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
      "Blood Orange" ┊       "Blood Orange"
"Honeyscrisp Apples" ┊ "Honeyscrisp Apples"
          "Radishes" ┊           "Radishes"
            "Ginger" ┊             "Ginger"
     "Navel Oranges" ┊      "Navel Oranges"
       "Clementines" ┊        "Clementines"
      "Granny Smith" ┊       "Granny Smith"
   "Pink Grapefruit" ┊    "Pink Grapefruit"

   Displayed all 8 result entries.

2. Historical locking

In the previous tutorial, you might have noticed that all the writable measures are writable for both future and past months. Often times, though, you would like to block the user from being able to update data from the past. We can configure this using Historical Locks. In this lesson, you will need to apply a lock on all the working plan measures, so that they can only be updated for the current and future months.

The way to define historical locks is similar to the way we define mask filters or filtered dropdowns: we use a measure to contain the keys that need to be locked. Since we want to lock measures based on the value of their Month key, we define a position-only measure Locked_Month, that contains all Month's that should be locked.

Modify Me

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

Locked_Month,Hist. Locked Data,Month,,,none,none,,,,,,,,DerivedAndStored,,

Again, we define the derivation type to be DerivedAndStored because we are going to use LogiQL to populate the measure.

Notice that we are using an intersection Month. Let's make sure to define it.

Modify Me

Add the following line to <TRAINING_HOME>/src/config/Intersections.csv:

Month,0,Calendar,Month,

Now we need to populate this measure with all the months that are "historical": earlier than the current month, as indicated by CurrentMonth. We can derive this using a LogiQL rule.

Modify Me

Create a file <TRAINING_HOME>/src/logiql/util/lock.logic, and place the following content in there:

block(`lock) {
  clauses(`{

   Locked_Month(month) <-
    Calendar:Month_offset[CurrentMonth[],month] < 0.
  })

} <-- .

The rule above says that, for all Month's that have an index smaller than the index of CurrentMonth[], which is indicated by a negative Calendar:Month_offset, derive that month into Locked_Month.

Rebuild your project and check the content of Locked_Month.

Run Me

Since this change involves declaring new measure and logic, we rebuild from the beginning:

$ lb config
$ make

Let's now check the content of our new measure Locked_Month:

$ lb print /modeler-training Locked_Month
[10000007152] "2017-04"
[10000007153] "2017-03"
[10000007154] "2017-06"
[10000007155] "2017-05"
[10000007158] "2017-02"
[10000007159] "2017-01"
[10000007160] "2017-12"
[10000007161] "2017-11"
[10000007164] "2017-08"
[10000007165] "2017-07"
[10000007166] "2017-10"
[10000007167] "2017-09"

Now that we have Locked_Month populated, we need to configure the measures to be locked using the months in Locked_Month.

Modify Me

Create a new file, <TRAINING_HOME>/src/logiql/util/lock_pivot.logic, with the following content:

block(`lock_pivot) {

    inactive(),

    clauses(`{

        //Associate historical lock with all WP measures
        +pivot:config:lock:installed_target_locks(target_name, expr_string, convertible, up_on_some) <-
        (
            target_name = "SalesRetail_R_WP_SKSTMO",
            expr_string = "refine Locked_Month @ {Sku, Store, Month}",
            convertible = false,
            up_on_some = false
            ;
            target_name = "SalesUnits_U_WP_SKSTMO",
            expr_string = "refine Locked_Month @ {Sku, Store, Month}",
            convertible = false,
            up_on_some = false
            ;
            target_name = "Margin_R_WP_SKSTMO",
            expr_string = "refine Locked_Month @ {Sku, Store, Month}",
            convertible = false,
            up_on_some = false
            ;
            target_name = "MarginPct_P_WP_SKSTMO",
            expr_string = "refine Locked_Month @ {Sku, Store, Month}",
            convertible = false,
            up_on_some = false
            ;
            target_name = "COGS_R_WP_SKSTMO",
            expr_string = "refine Locked_Month @ {Sku, Store, Month}",
            convertible = false,
            up_on_some = false
            ;
            target_name = "AvgSellingPrc_R_WP_SKSTMO",
            expr_string = "refine Locked_Month @ {Sku, Store, Month}",
            convertible = false,
            up_on_some = false
        ).
    })
} <-- .

What is going on in this file?

  • It contains a rule that derives the tuple, (target_name, expr_string, convertible, up_on_some) into the relation pivot:config:lock:installed_target_locks. This relation is then used by Modeler to make sure that update requests to the measure specified in target_name will not alter values for months that are present in the CubiQL expression expr_string.
  • In our tutorial application, the intersection of the measures we want to lock is Sku,Store,Month. The CubiQL expression defined in expr_string should thus create a list of all positions to lock at the Sku,Store,Month intersection based on the months in the measure Locked_Month.

    To achieve this, we must first realize that our measure Locked_Month is keyed at Month.

    Run Me

    Run the following expression in the CubiQL REPL:

    > Locked_Month
    
    Calendar : Month
    ┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
          "2017-Apr"
          "2017-Mar"
          "2017-Jun"
          "2017-May"
          "2017-Feb"
          "2017-Jan"
          "2017-Dec"
          "2017-Nov"
          "2017-Aug"
          "2017-Jul"
          "2017-Oct"
          "2017-Sep"
    
       Displayed all 12 result entries.

    So, we should refine the Month intersection of the measure Locked_Month to the Sku,Store,Month intersection of the measures we want to lock by applying a refine expression.

    Run Me

    Run the following expression in the REPL to see the effect of the refine expression:

    > refine Locked_Month @ {Sku, Store, Month}
    
    Calendar : Month ┊ Location : Store ┊        Product : Sku
    ┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
          "2017-Jan" ┊        "store-1" ┊       "Blood Orange"
          "2017-Jan" ┊        "store-1" ┊ "Honeyscrisp Apples"
          "2017-Jan" ┊        "store-1" ┊              "Beets"
          "2017-Jan" ┊        "store-1" ┊    "Pink Grapefruit"
          "2017-Jan" ┊        "store-1" ┊       "Granny Smith"
          "2017-Jan" ┊        "store-3" ┊              "Beets"
          "2017-Jan" ┊        "store-3" ┊       "Blood Orange"
          "2017-Jan" ┊        "store-1" ┊             "Ginger"
          "2017-Jan" ┊        "store-1" ┊           "Radishes"
          "2017-Jan" ┊        "store-1" ┊        "Clementines"
          "2017-Jan" ┊        "store-1" ┊      "Navel Oranges"
          "2017-Jan" ┊        "store-3" ┊       "Granny Smith"
          "2017-Jan" ┊        "store-3" ┊        "Clementines"
          "2017-Jan" ┊       "store-13" ┊       "Blood Orange"
          "2017-Jan" ┊        "store-3" ┊    "Pink Grapefruit"
          "2017-Jan" ┊        "store-3" ┊           "Radishes"
          "2017-Jan" ┊        "store-3" ┊ "Honeyscrisp Apples"
          "2017-Jan" ┊        "store-3" ┊      "Navel Oranges"
          "2017-Jan" ┊        "store-3" ┊             "Ginger"
          "2017-Jan" ┊       "store-13" ┊        "Clementines"
          "2017-Jan" ┊       "store-13" ┊      "Navel Oranges"
          "2017-Jan" ┊       "store-13" ┊    "Pink Grapefruit"
          "2017-Jan" ┊       "store-13" ┊       "Granny Smith"
          "2017-Jan" ┊       "store-13" ┊ "Honeyscrisp Apples"
          "2017-Jan" ┊       "store-13" ┊              "Beets"
          "2017-Jan" ┊       "store-13" ┊             "Ginger"
          "2017-Jan" ┊       "store-13" ┊           "Radishes"
          "2017-Jan" ┊        "store-4" ┊      "Navel Oranges"
          "2017-Jan" ┊        "store-4" ┊             "Ginger"
          "2017-Jan" ┊        "store-4" ┊       "Granny Smith"
    
       Displayed 30 out of 432 result entries.

  • Finally, notice that this block, lock_pivot, is an inactive block. This means we need to execute it for the logic to take effect. You can think of inactive blocks as prepared queries: pre-compiled, but must be executed when needed.

One final step before we can test our lock: we need to add the execution of the util:lock_pivot inactive block after the data load. We also need to restart the measure service, otherwise Modeler won’t be able to find out which cells it needs to lock.

Modify Me

Find master.deploy_initial in <TRAINING_HOME>/src/workflow/master.wf, and uncomment the block that begins with //. After this, your workflow should look as follows:

workflow master.deploy_initial(app_prefix,constant_location,location,timeout) [] {
   master.deploy_initial_data($app_prefix, $constant_location, $location, $timeout)
   ;
   lb.ExecuteLogiQL(
      transport   = "http://localhost:55183",
      txn_timeout = $timeout,
      service     = "/modeler-training/connectblox/exec_inactive",
      block       = "util:lock_pivot"
   )
}

At this point, we can run only the workflow for loading data. by running the following command:

Run Me

$ make load-test-data

Using an Ad Hoc Canvas, place the working plan measures we just associated with the historical lock in the pivot view, with Year along the rows. Can you edit values for the year 2017? What about 2018 and 2019?

3. Filtered Dropdowns

When you restored the tutorial application to the "base" of tutorial 3, certain configuration files were updated to contain some additional data.

The measure configuration file under <TRAINING_HOME>/src/config/Measures.csv has been extended with a new measure SkuSimilar_SK_SK of type Sku. This measure can be used to store a reference to another (similar) SKU.

SkuSimilar_SK_SK,Similar SKU,Sku,Product:Sku,,none,none,,,,,,,,,,

Level-typed measure values, by default, are displayed using dropdowns. If you take a look at this measure via an Ad Hoc canvas (add first the SKU level on either the rows or the columns), you will see that each value of the measure Similar SKU is a dropdown, containing all available Skus in the database.

Sometimes in applications, it might make sense for the selections in the dropdown to be restricted, and often, the selection changes based on the key of the measure. To learn how this is done, we'll add some dropdown filters to the SKU Maintenance view in our application.

3.1. Selecting every other SKU

The newly added measure SkuSimilar_SK_SK can store a reference to a similar SKU. In this case, it makes sense to restrict the choices within the dropdown to every SKU except the one that's currently selected. To add such a filter to our dropdown, we can create a dropdown filter expression in CubiQL that calculated the difference between all SKUs and the SKU at the current position.

Modify Me

First, create a new file where we can define our dropdown filter expressions: <TRAINING_HOME>/src/rules/tutorial3.logic. Then, add the following logic to this file:

^modeler_config:metamodel:cubiql:dropdown_filter_expr[filterName] = expr <-
  filterName = "SimilarSkuFilter",
  expr = """Product.Sku.id \ CURRENT_POS""".

What is going in in this file?

  • It contains a rule that sets the CubiQL dropdown filter expression Product.Sku.id \ CURRENT_POS into the relation modeler_config:metamodel:cubiql:dropdown_filter_expr which is used by Modeler to install the dropdown filter expression into the measure model.

    Notice the use of a multi-line string literal to define the CubiQL expression. This allows us to use line breaks and makes sure the backslash \ of our difference expression doesn't need to be escaped with an additional backslash for the string to be valid.

  • The filter expression is stored under the name SimilarSkuFilter. So, whenever you want to apply this filter within a view, you can refer to it using its name SimilarSkuFilter.

  • Our CubiQL expression uses a difference expression to calculate the list of all Skus except the one at the current position. Notice how the current position on the grid is conveniently provided by Modeler as CURRENT_POS. Let's use the CubiQL REPL to mimic how the expression will be evaluated by Modeler:

    Run Me

    First, take a look at the contents of Product.Sku.id:

    > Product.Sku.id
    
           Product : Sku ┊ value of string
    ┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
          "Blood Orange" ┊        "sku-01"
         "Navel Oranges" ┊        "sku-03"
           "Clementines" ┊        "sku-02"
              "Radishes" ┊        "sku-09"
                "Ginger" ┊        "sku-08"
          "Granny Smith" ┊        "sku-05"
       "Pink Grapefruit" ┊        "sku-04"
                 "Beets" ┊        "sku-07"
    "Honeyscrisp Apples" ┊        "sku-06"
    
       Displayed all 9 result entries.

    When we've currently selected "Beets" as we did in our screenshot, the value of CURRENT_POS will be equal to sku-07. As you might remember from the first lesson, we can represent this in CubiQL using a literal measure:

    > {("sku-07")}:{Product.Sku}
    
    Product : Sku
    ┈┈┈┈┈┈┈┈┈┈┈┈┈
          "Beets"
    
       Displayed all 1 result entries.

    When we use a let expression to bind our literal measure to CURRENT_POS, we see the result of the expression as if you've selected "Beets" in the view:

    > :query
    // Entering multi-line mode (ctrl-D to finish)
      let (
        CURRENT_POS = {("sku-07")}:{Product.Sku}
      ) in (
        Product.Sku.id \ CURRENT_POS
      )
    // Exiting multi-line mode, now evaluating.
    
           Product : Sku ┊ value of string
    ┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
          "Blood Orange" ┊        "sku-01"
         "Navel Oranges" ┊        "sku-03"
           "Clementines" ┊        "sku-02"
              "Radishes" ┊        "sku-09"
                "Ginger" ┊        "sku-08"
          "Granny Smith" ┊        "sku-05"
       "Pink Grapefruit" ┊        "sku-04"
    "Honeyscrisp Apples" ┊        "sku-06"
    
       Displayed all 8 result entries.
    

Modeler will apply dropdown filter expressions using a dice expression to determine which level members should appear in the dropdown. This means our expression will be evaluated as dice Product.Sku.id by ( Product.Sku.id \ CURRENT_POS ).

Run Me

Try evaluating your expression in the same way as Modeler by running the following let expression in the CubiQL REPL:

> :query
// Entering multi-line mode (ctrl-D to finish)
  let (
    CURRENT_POS = {("sku-07")}:{Product.Sku},
    expr = ( Product.Sku.id \ CURRENT_POS )
  ) in (
    dice Product.Sku.id by expr
  )
// Exiting multi-line mode, now evaluating.

       Product : Sku ┊ value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
      "Blood Orange" ┊        "sku-01"
     "Navel Oranges" ┊        "sku-03"
       "Clementines" ┊        "sku-02"
          "Radishes" ┊        "sku-09"
            "Ginger" ┊        "sku-08"
      "Granny Smith" ┊        "sku-05"
   "Pink Grapefruit" ┊        "sku-04"
"Honeyscrisp Apples" ┊        "sku-06"

   Displayed all 8 result entries.

That looks exactly as expected! Every SKU except "Beets" will appear in the dropdown.

Okay, now let's apply this dropdown filter to the Similar SKU measure on the SKU Maintenance view.

Modify Me

Add the new dropdown filter by adding the SkuSimilar_SK_SK measure to the "z" object in <TRAINING_HOME>/src/config/views/main/sheets/sku-maintenance-sheet.json. Furthermore, add the following fragment as a child of the pivotConfig object, to filter the SKUs shown in the dropdown using our CubiQL dropdown filter expression SimilarSkuFilter:

"dropdownMeasureFilter" : [{
  "qualifiedName" : "SkuSimilar_SK_SK",
  "filterName"    : "SimilarSkuFilter"
}]

Rebuild the application to install the dropdown filter expression and apply the changes to the view configuration.

Run Me

$ make

Now check the contents of the dropdown for Similar SKU. Do you notice that it is now restricted based on your current position because of our dropdown filter?

3.2. Selecting vendors that are known to sell the SKU

Use an Ad Hoc Canvas to take a look at the level members of Class, Vendor, and SKU. This will expose the hierarchical relationship between these levels:

The members for all levels in the Product dimension are defined in the data file <TRAINING_HOME>/data/dev_data/initial/hierarchy/product_vendor.csv which is imported using the TDX service defined in <TRAINING_HOME>/src/logiql/services/tdx/product_vendor.logic. In addition to creating the default product hierarchy (SKU, Subclass, Class), this service also creates the Vendor product hierarchy (SKU, Vendor, Class) by:

  • creating a level member in the Vendor level for every vendor found in the data file.
  • associating every SKU to its Vendor.
  • associating every Vendor to a Class based on the Class of each SKU it sells. So, for instance, if a Vendor only sells SKUs that belong to the "Vegetables" class, it will only be associated with the "Vegetables" class.

Let's reconfigure our Ad Hoc Canvas so it allows us to change the level mappings between SKU and Vendor using a dropdown. We can achieve this by adding the SKU to Vendor measure to the view. We didn't have to define this measure, it was automatically generated for us by Modeler.

As you can see, the level mapping SKU to Vendor is Vendor-typed, giving us a dropdown of all level members of Vendor. Let's say, that for a given SKU, we only want to display dropdown choices for vendors that are associated with the same Class as the SKU.

Remember that for a dropdown filter, we need to write a CubiQL dropdown filter expression that, for a given position (CURRENT_POS), gives Modeler a list of vendors to display in the dropdown list.

Run Me

Let's use the CubiQL REPL first to take a look at the complete list of vendors:

> Product.Vendor.id

    Product : Vendor ┊      value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
    "Crispiest Inc." ┊     "Crispiest Inc."
       "Best Citrus" ┊        "Best Citrus"
   "Vegistatic Inc." ┊    "Vegistatic Inc."
"Fruit Masters Inc." ┊ "Fruit Masters Inc."
   "We love Veggies" ┊    "We love Veggies"

   Displayed all 5 result entries.

The value of CURRENT_POS will again be a SKU. For instance, use the following literal measure as if we are currently at the position "Blood Orange".

> {("sku-01")}:{Product.Sku}

 Product : Sku
┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Blood Orange"

   Displayed all 1 result entries.

Ok, so what happens when we use CURRENT_POS as our dropdown filter expression? Let's try that:

> :query
// Entering multi-line mode (ctrl-D to finish)
  let (
    CURRENT_POS = {("sku-01")}:{Product.Sku},
    expr = ( CURRENT_POS )
  ) in (
    dice Product.Vendor.id by expr
  )
// Exiting multi-line mode, now evaluating.

    Product : Vendor ┊      value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Fruit Masters Inc." ┊ "Fruit Masters Inc."

   Displayed all 1 result entries.

This is not really what we're looking for. Since we've now simply diced our list of vendors with the Vendor belonging to the selected SKU. We're actually interested in all vendors that belong to the same Class as the selected SKU. What we could do, is use a dice expression to find the Class of the selected position.

> :query
// Entering multi-line mode (ctrl-D to finish)
  let (
    CURRENT_POS = {("sku-01")}:{Product.Sku},
    expr = ( dice Product.Class.id by CURRENT_POS )
  ) in (
    dice Product.Vendor.id by expr
  )
// Exiting multi-line mode, now evaluating.

    Product : Vendor ┊      value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
    "Crispiest Inc." ┊     "Crispiest Inc."
       "Best Citrus" ┊        "Best Citrus"
"Fruit Masters Inc." ┊ "Fruit Masters Inc."

   Displayed all 3 result entries.

That looks more like it! The "Blood Orange" is associated with the class "Fruits". Let's also try sku-09 ("Radishes") belonging to the "Vegetables" class:

> :query
// Entering multi-line mode (ctrl-D to finish)
  let (
    CURRENT_POS = {("sku-09")}:{Product.Sku},
    expr = ( dice Product.Class.id by CURRENT_POS )
  ) in (
    dice Product.Vendor.id by expr
  )
// Exiting multi-line mode, now evaluating.

 Product : Vendor ┊   value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Vegistatic Inc." ┊ "Vegistatic Inc."
"We love Veggies" ┊ "We love Veggies"

   Displayed all 2 result entries.

Perfect!

Okay, let's configure this new dropdown filter in our application.

Modify Me

First, add the following logic to the file we created earlier: <TRAINING_HOME>/src/rules/tutorial3.logic.

^modeler_config:metamodel:cubiql:dropdown_filter_expr[filterName] = expr <-
  filterName = "VendorClassFilter",
  expr = """dice Product.Class.id by CURRENT_POS""".

Then, open up <TRAINING_HOME>/src/config/views/main/sheets/sku-maintenance-sheet.json and add the SKU to Vendor measure to SKU Maintenance view and apply the VendorClassFilter. The qualified name of the measure SKU to Vendor is Product:Sku:vendor.

Rebuild the application to install the dropdown filter expression and apply the changes to the view configuration.

Run Me

$ make

Now check the contents of the dropdown for SKU to Vendor. Do you notice that it is now restricted based on your current position because of our dropdown filter? For instance, for "Radishes" you should now only be able to select the vendors "Vegistatic Inc." and "We love Veggies".

As the drop filter on SKU to Vendor is only applied in the SKU Maintenance view, you can still see all vendors listed in the dropdown when looking at the measure via an Ad Hoc Canvas.

Congratulations, you have completed Tutorial 3!