Table of Contents

• Everyday
  • The Cube Block
  • Dimensions
  • Measures
  • Joins
  • Segments
  • The CUBE Token
  • Filtering and Time Grains: Nothing to Write
  • A Complete Everyday Cube
• Drill Paths
• Advanced (Rarely Needed)
  • Changing What a Field Means
  • Wiring
  • Shown in the Result
  • DSL Only
  • Not Supported Yet
• What parse-dsl Returns
• Tips

A cube is a Groovy file: one cube { ... } block holding dimensions, measures, joins and segments. Most cubes use only the keys in Everyday. Everything below that section exists for a reason, but you can write, ship and use a cube without ever reading it.

Everyday

The Cube Block

cube {
  sql_table 'public.orders'              // where the rows come from
  title 'Orders'                         // what people see in the field picker
  description 'Customer orders'          // read by people and by the AI
 
  // dimension { ... }
  // measure   { ... }
  // join      { ... }
  // segment   { ... }
}

Either sql_table or sql is required — a cube needs rows from somewhere. The other cube-level keys (sql_alias, public_, access_filter, currency, meta, extends_) are advanced.

Dimensions

A dimension is something you group by or filter on: a column, or any SQL expression over one.

dimension { name 'Status';    title 'Order Status'; sql '${CUBE}.status';     type 'string' }
dimension { name 'OrderDate'; title 'Order Date';   sql '${CUBE}.order_date'; type 'time' }
dimension { name 'OrderID';   title 'Order';        sql '${CUBE}.id';         type 'number'; primary_key true }

The advanced dimension keys — case_, sub_query, latitude/longitude, order, filter_options, format, drill_members, public, meta — are further down.

Measures

A measure is a number the database adds up for you.

measure { name 'Orders';  title 'Orders';        type 'count' }
measure { name 'Revenue'; title 'Revenue';       sql '${CUBE}.amount'; type 'sum'; format 'currency' }
measure { name 'AvgOrder'; title 'Average Order'; sql '${CUBE}.amount'; type 'avg'; format 'currency' }

A measure type that is not in that list is an error: the field is shown as broken in the field picker and cannot be ticked, and the rest of the cube goes on working.

Joins

A join brings a second table in. Once it is declared, any dimension or measure may read its columns, and DataPallas adds the JOIN only to the queries that actually name it.

join {
  name 'customers'
  title 'Customers'
  sql '${CUBE}.customer_id = customers.id'
  relationship 'many_to_one'
}

Every join is a LEFT JOIN. A main-table row with no match is kept, with empty values for the joined columns — so "revenue by country" still shows the orders of customers who have no country, instead of quietly dropping them.

primary_key and double counting. A one_to_many join multiplies the main table's rows: an order with three lines arrives three times, and a plain SUM over an order-level column would count it three times. DataPallas handles this for you when the main table has a dimension with primary_key true: measures over main-table columns are computed once per key and then added up, so the total is right whichever fields are ticked. If the main table has no primary_key and a selection would double count, the query is refused with a message saying what to do — you never get a silently wrong number. So declare the primary key: it is what lets the two live side by side.

Segments

A segment is a named, reusable filter — a saved WHERE clause a viewer can tick.

segment {
  name 'Recent'
  title 'Last 30 Days'
  description 'Orders placed in the last 30 days'
  sql "${CUBE}.order_date >= CURRENT_DATE - INTERVAL '30 days'"
}

Several ticked segments are combined with AND. A segment costs nothing when it is not ticked: it is in the SQL only then.

The CUBE Token

${CUBE} stands for the cube's own table, with whatever alias the query needs:

sql '${CUBE}.order_date'

Write it in every sql that reads a main-table column, and the cube keeps working when its sql_table changes or when the same cube is read through a sub-query. A joined table is named by its join's name instead: customers.country.

Groovy has two kinds of string, and only one of them expands $:

• 'single quotes' — the text is taken as it is, so ${CUBE} reaches DataPallas. Use this.
• "double quotes" — Groovy expands ${...} itself, so ${CUBE} must be escaped as \${CUBE}. You need double quotes when the value contains a single quote, as in "${CUBE}.status = 'shipped'", where it is written "\${CUBE}.status = 'shipped'".

Filtering and Time Grains: Nothing to Write

Two things people expect to have to declare need no key at all:

• Every visible dimension can be filtered. Viewers add filters from the field picker and see them as chips. The control follows the type: a checkbox list for text (with a search box once the list is long), from–to boxes for a number with many distinct values, date pickers for time, and Any / Yes / No for boolean. Write filter_options only when the generated list is not the list you want.
• Every time dimension can be grouped by day, week, month, quarter or year, or left as it is. The viewer picks the grain next to the field.

A Complete Everyday Cube

cube {
  sql_table 'public.orders'
  title 'Orders'
  description 'Customer orders, with revenue by customer and country'
 
  join {
    name 'customers'
    title 'Customers'
    sql '${CUBE}.customer_id = customers.id'
    relationship 'many_to_one'
  }
 
  dimension { name 'OrderID';      title 'Order';        sql '${CUBE}.id';          type 'number'; primary_key true }
  dimension { name 'Status';       title 'Status';       sql '${CUBE}.status';      type 'string' }
  dimension { name 'OrderDate';    title 'Order Date';   sql '${CUBE}.order_date';  type 'time' }
  dimension { name 'CustomerName'; title 'Customer';     sql 'customers.company_name'; type 'string' }
  dimension { name 'Country';      title 'Country';      sql 'customers.country';   type 'string' }
 
  measure { name 'Orders';   title 'Orders';        type 'count' }
  measure { name 'Revenue';  title 'Revenue';       sql '${CUBE}.amount'; type 'sum'; format 'currency' }
  measure { name 'AvgOrder'; title 'Average Order'; sql '${CUBE}.amount'; type 'avg'; format 'currency' }
 
  segment { name 'Shipped'; title 'Shipped Orders'; sql "\${CUBE}.status = 'shipped'" }
}

Drill Paths

A hierarchy is a path from the general to the particular — country, then region, then city. The field picker shows it as a drill path, so a viewer can walk down it instead of hunting for the next field.

cube {
  sql_table 'public.customers'
 
  dimension { name 'Country'; title 'Country'; sql '${CUBE}.country'; type 'string' }
  dimension { name 'Region';  title 'Region';  sql '${CUBE}.region';  type 'string' }
  dimension { name 'City';    title 'City';    sql '${CUBE}.city';    type 'string' }
 
  hierarchy {
    name 'Geography'
    title 'Geography'
    levels 'Country', 'Region', 'City'
  }
}

Every name in levels must be a dimension of the same cube; one that is not is an error naming it. A dimension may appear in more than one hierarchy, and a cube may have as many as it needs.

Advanced (Rarely Needed)

Everything from here down is optional. Reach for it when the everyday keys cannot say what you mean.

Changing What a Field Means

Measure filters — a measure that counts only some rows. The condition applies to this measure alone, whatever else the query asks for, so "Won Deals" and "Lost Deals" can sit side by side on one row:

measure {
  name 'WonDeals'
  title 'Won Deals'
  type 'count'
  filters {
    filter sql: "\${CUBE}.stage = 'Closed Won'"
  }
}

case_ — a dimension whose value is a band, not a column. The first when that matches wins; else_ is the value for everything else. A case_ dimension needs no sql of its own:

dimension {
  name 'Grade'
  title 'Grade'
  case_ {
    when sql: '${CUBE}.score >= 90', label: "Dean's List"
    when sql: '${CUBE}.score >= 60', label: 'Passed'
    else_ label: 'Failed'
  }
}

type 'geo' — a point on a map. Instead of one sql, a geo dimension names its two coordinates. Either form works: a plain expression, or a block with sql in it.

dimension {
  name 'Location'
  title 'Location'
  type 'geo'
  latitude  '${CUBE}.latitude'
  longitude '${CUBE}.longitude'
}

A geo dimension with only one of the two is an error, and it cannot be given an order — a pair of coordinates has no order. Tick it with a measure and the result is drawn as a pin map.

sub_query true — a number from another table, on each row of this one. The dimension's sql is one reference of the form ${<join>.<measure>}, where the join is declared directly on this cube and the measure is count or a measure of a cube of that name in the same file:

dimension {
  name 'CoursesTaken'
  title 'Courses Taken'
  sql '${cube_demo.school_enrollments.count}'
  type 'number'
  sub_query true
}

It is generated as a correlated sub-select. ClickHouse does not run a correlated sub-query in a SELECT, so a cube that uses one answers on every other supported database and tells the viewer, in words, to pick another field or another connection there.

A calculated measure — type 'number'. Its sql is arithmetic over other measures of the same cube, each written as ${OtherMeasure}. Each reference becomes that measure's whole aggregate, so the result is a ratio of totals and not an average of ratios, and it is never in the GROUP BY:

measure {
  name 'MarginPct'
  title 'Margin %'
  sql '(${Revenue} - ${Cost}) / NULLIF(${Revenue}, 0)'
  type 'number'
  format 'percent'
}

Without any ${...}, the sql of a number measure is used exactly as written, as a complete aggregate expression. A reference to a measure that does not exist, and a circle of references, are both errors naming the measure.

The three analysis measures. Each is type 'number' whose sql is exactly one ${BaseMeasure}, and the base measure must be a sum, a count or a count_distinct — a share of an average means nothing:

measure { name 'RevenueShare';     title 'Revenue Share';   sql '${Revenue}'; type 'number'; share_of_total true;                            format 'percent' }
measure { name 'RevenueRunning';   title 'Running Revenue'; sql '${Revenue}'; type 'number'; rolling_window trailing: 'unbounded';           format 'currency' }
measure { name 'RevenueYTD';       title 'Revenue YTD';     sql '${Revenue}'; type 'number'; rolling_window type: 'to_date', granularity: 'year'; format 'currency' }
measure { name 'RevenuePriorYear'; title 'Prior Year';      sql '${Revenue}'; type 'number'; time_shift interval: '1 year';                  format 'currency' }

A share is taken over all the groups the filters leave in, not only the rows shown, and a running total starts at the first period the filters leave in — narrow the filter and the running total starts later. Any other rolling_window form (trailing: '3 month', leading, offset) is refused with a message saying which two forms exist.

Wiring

Several cubes in one file. A named block is a cube of its own; a file may hold as many as belong together, and the viewer picks one:

cube('customer-invoices') {
  sql_table 'cube_demo.erp_invoices'
  // ...
}
 
cube('customer-payments') {
  sql_table 'cube_demo.erp_payments'
  // ...
}

A dotted reference — ${something.member} — is only a sub_query dimension's, and anywhere else it is an error, because nothing resolves it. Cubes in one file are related by joining their tables, not by naming each other in ordinary sql.

sql_alias names the main table inside the generated SQL. Set it when the table's real name is long or awkward; it is what ${CUBE} expands to.

public_ false at cube level hides a cube from the pickers while leaving it readable by name — useful for a cube other cubes are built on. On a dimension, a measure or a segment the key is public, and because public is a Groovy keyword it is written in the map form of the block:

cube {
  sql_table 'public.orders'
  public_ false
  dimension(name: 'InternalCode', sql: '${CUBE}.internal_code', type: 'string', public: false)
}

A hidden member is in no picker, cannot be ticked and cannot be filtered on.

order 'asc' or order 'desc' on a dimension is the order its values come back in when nothing else is asked for — month names in calendar order rather than alphabetical. Any other value is an error.

parent on a join names the joined table this join hangs off, for a table that is two steps from the main one:

join { name 'order_lines'; sql '${CUBE}.order_id = order_lines.order_id';          relationship 'one_to_many' }
join { name 'products';    sql 'order_lines.product_id = products.product_id';     relationship 'many_to_one'; parent 'order_lines' }

DataPallas then adds order_lines whenever products is needed, in the right order.

filter_options '<SELECT …>' on a dimension replaces the list of values offered in its filter. Column 1 holds values of the dimension itself — the filter still compares the dimension's own sql with them — and column 2, if there is one, is only the label shown. It exists for four cases the generated list cannot handle:

• a faster list: read customers from Customers instead of SELECT DISTINCT over every order;
• a curated list: only the active ones, or a fixed set;
• an order: month names in calendar order;
• labels for codes: 0 and 1 shown as Active and Discontinued.

dimension {
  name 'MonthName'
  sql 'dim_time.month_name'
  type 'string'
  filter_options 'SELECT month_name FROM dim_time GROUP BY month, month_name ORDER BY month'
}

On a measure, filter_options is ignored with a warning: only dimensions have filter lists.

access_filter '<SQL condition>' at cube level is a row filter: one condition added to the WHERE of every SELECT over this cube — the query, its totals, a drill-through, and even the lists offered in the filters. It is written once per cube; a second one is an error, because the second would replace the first and quietly show rows the first one hid. Join several conditions with AND or OR yourself.

cube {
  sql_table 'cube_demo.crm_deals'
  access_filter "\${CUBE}.owner_login = \${dp_user_id} OR \${dp_user_role} = 'admin'"
}

The condition is ordinary SQL — ${CUBE}, the joined tables, brackets, sub-selects — plus the built-in variables that say who is asking. They are set by the server on every request and bound as values, never pasted into the SQL, so nothing a viewer types can change them:

A value nobody supplied is empty, not missing: a condition comparing with it matches no row, which is the safe direction, and is never an error. A share link and an embed token have no person behind them, so the person variables are empty there and the attributes are how the host application says who the viewer is.

Shown in the Result

format on a measure is currency, percent or number, and it decides how the number is shown everywhere the cube is read — the table, the chart axis, the tooltip and the single value. percent expects a fraction: 0.34 is shown as 34%. Anything else is a warning, and the number is shown as it comes back.

currency '<ISO code>' at cube level is the money the cube's amounts are in — EUR, GBP, JPY. The default is USD. A code that is not three letters is a warning. Write it once, and every measure with format 'currency' is shown in it.

drill_members on a measure lists the fields to show for "the rows behind this number". With it, the number is clickable and a click opens the rows in a modal; a measure without it cannot be drilled:

measure {
  name 'Revenue'
  sql '${CUBE}.amount'
  type 'sum'
  format 'currency'
  drill_members 'OrderID', 'OrderDate', 'CustomerName', 'Country'
}

Totals are not a DSL key. Any query may ask for them, and the server answers them by running the same question once more without the grouping — which is why a total is right even where adding up the column would be wrong, as for a distinct count, an average or a ratio. In a cube widget, the table shows them as a Total row.

DSL Only

meta takes a map and is carried through to whoever reads the model — your own tooling, a catalogue, an export. Nothing in DataPallas reads it, and a member that has one is reported as a warning saying exactly that, so nobody expects behaviour from it.

Not Supported Yet

extends_ is parsed and reported as a warning: cube inheritance is not implemented, and a cube that declares it is read as if it did not. Copy the keys you need instead.

What parse-dsl Returns

POST /api/cubes/parse-dsl turns DSL text into the model the field picker, the AI and your own tools read. Its JSON names are these:

Each entry of warnings is { cube, block, member, key, level, message }, and level is one of two:

• warning — the cube works. Something is ignored or was probably a typo: an unknown key (with the nearest known one suggested), an unknown dimension type or relationship, a format that is not one of the three, filter_options on a measure, a meta, an extends_, a currency code that is not three letters, a second primary_key.
• error — the generator refuses that member. It is shown as broken in the field picker and cannot be ticked, the message says what to fix, and the rest of the cube goes on working: one bad line never takes the whole cube off the page. A second access_filter is an error of the cube itself.

An unknown key is always a warning, never an error: a cube written for a newer version still loads.

Tips

• Write description on everything people and the AI will read. "Total Revenue" is fine; "Sum of completed orders' net amount in USD, after refunds" removes a whole class of wrong answers.
• Declare a primary_key. It is what lets a one_to_many join and a correct total coexist. Without one, the selections that would double count are refused rather than guessed.
• format shows, it does not convert. format 'currency' does not divide or multiply anything; make sure the sql already produces the value in the unit you mean. percent is the one rule to remember: it expects a fraction.
• Prefer a segment to a hand-written filter people would otherwise retype. It is in the SQL only when it is ticked.
• Name a table or a view in sql_table when you can. A sql sub-query works, but the schema is then opaque to the AI and to anyone reading the cube.
• Let the AI write the first draft. The "Hey AI, Help Me…" button in the cube editor drafts a cube against your live schema from a plain-English description, and the parser's warnings tell you the rest.