Types and Formats

Measure Types

This section describes the various types that can be assigned to a measure. A measure can only have one type.

number

The sql parameter is required and can take any valid SQL expression that results in a number or integer. Type number is usually used, when performing arithmetic operations on measures. Learn more about Calculated Measures.

cube('Orders', {
  measures: {
    purchases_ratio: {
      sql: `${purchases} / ${count} * 100.0`,
      type: `number`,
      format: `percent`,
    },
  },
});

You can put any sql into number measure as long as it's an aggregate expression:

cube('Orders', {
  measures: {
    ratio: {
      sql: `sum(${CUBE}.amount) / count(*)`,
      type: `number`,
    },
  },
});

count

Performs a table count, similar to SQL’s COUNT function. However, unlike writing raw SQL, Cube.js will properly calculate counts even if your query’s joins will produce row multiplication. You do not need to include a sql parameter for this type.

drillMembers parameter is commonly used with type count. It allows users to click on the measure in the UI and inspect individual records that make up a count. Learn more about Drill Downs.

cube('Orders', {
  measures: {
    numberOfUsers: {
      type: `count`,
      // optional
      drillMembers: [id, name, email, company],
    },
  },
});

countDistinct

Calculates the number of distinct values in a given field. It makes use of SQL’s COUNT DISTINCT function.

The sql parameter is required and can take any valid SQL expression that results in a table column, or interpolated JavaScript expression.

cube('Orders', {
  measures: {
    uniqueUserCount: {
      sql: `user_id`,
      type: 'countDistinct',
    },
  },
});

countDistinctApprox

Calculates approximate number of distinct values in a given field. Unlike countDistinct measure type, countDistinctApprox is decomposable aggregate function or additive. This allows its usage in additive rollup pre-aggregations which are much more versatile than non-additive ones. It uses special SQL backend-dependent functions to estimate distinct counts, usually based on HyperLogLog or similar algorithms. Wherever possible Cube.js will use multi-stage HLL which significantly improves calculation of distinct counts at scale.

The sql parameter is required and can take any valid SQL expression.

cube('Orders', {
  measures: {
    uniqueUserCount: {
      sql: `user_id`,
      type: 'countDistinctApprox',
    },
  },
});

sum

Adds up the values in a given field. It is similar to SQL’s SUM function. However, unlike writing raw SQL, Cube.js will properly calculate sums even if your query’s joins will result in row duplication.

The sql parameter is required and can take any valid SQL expression that results in a numeric table column, or interpolated JavaScript expression. sql parameter should contain only expression to sum without actual aggregate function.

cube('Orders', {
  measures: {
    revenue: {
      sql: `${chargesAmount}`,
      type: `sum`,
    },
  },
});

cube('Orders', {
  measures: {
    revenue: {
      sql: `amount`,
      type: `sum`,
    },
  },
});

cube('Orders', {
  measures: {
    revenue: {
      sql: `fee * 0.1`,
      type: `sum`,
    },
  },
});

avg

Averages the values in a given field. It is similar to SQL’s AVG function. However, unlike writing raw SQL, Cube.js will properly calculate averages even if your query’s joins will result in row duplication.

The sql parameter for type: average measures can take any valid SQL expression that results in a numeric table column, or interpolated JavaScript expression.

cube('Orders', {
  measures: {
    avg_transaction: {
      sql: `${transaction_amount}`,
      type: `avg`,
    },
  },
});

min

Type of measure min is calculated as a minimum of values defined in sql.

cube('Orders', {
  measures: {
    date_first_purchase: {
      sql: `date_purchase`,
      type: `min`,
    },
  },
});

max

Type of measure max is calculated as a maximum of values defined in sql.

cube('Orders', {
  measures: {
    date_last_purchase: {
      sql: `date_purchase`,
      type: `max`,
    },
  },
});

runningTotal

Type of measure runningTotal is calculated as summation of values defined in sql. Use it to calculate cumulative measures.

cube('Orders', {
  measures: {
    total_subscriptions: {
      sql: `subscription_amount`,
      type: `runningTotal`,
    },
  },
});

Measure Formats

When creating a measure you can explicitly define the format you’d like to see as output.

percent

percent is used for formatting numbers with a percent symbol.

cube('Orders', {
  measures: {
    purchase_conversion: {
      sql: `${purchase}/${checkout}*100.0`,
      type: `number`,
      format: `percent`,
    },
  },
});

currency

currency is used for monetary values.

cube('Orders', {
  measures: {
    total_amount: {
      sql: `amount`,
      type: `runningTotal`,
      format: `currency`,
    },
  },
});

Dimension Types

This section describes the various types that can be assigned to a dimension. A dimension can only have one type.

time

In order to be able to create time series charts, Cube.js needs to identify time dimension which is a timestamp column in your database.

You can define several time dimensions in schemas and apply each when creating charts. Note that type of target column should be TIMESTAMP. Please use this guide if your datetime information is stored as a string.

cube('Orders', {
  dimensions: {
    completed_at: {
      sql: `completed_at`,
      type: `time`,
    },
  },
});

string

string is typically used with fields that contain letters or special characters. The sql parameter is required and can take any valid SQL expression.

The following model creates a field full_name by combining 2 fields: first_name and last_name:

cube('Orders', {
  dimensions: {
    full_name: {
      sql: `CONCAT(${first_name}, ' ', ${last_name})`,
      type: `string`,
    },
  },
});

number

number is typically used with fields that contain number or integer.

cube('Orders', {
  dimensions: {
    amount: {
      sql: `amount`,
      type: `number`,
    },
  },
});

boolean

boolean is used with fields that contain boolean data or data coercible to boolean. For example:

cube('Orders', {
  dimensions: {
    is_enabled: {
      sql: `is_enabled`,
      type: `boolean`,
    },
  },
});

geo

geo dimension is used to display data on the map. Unlike other dimension types it requires to set two fields: latitude and longitude.

cube('Orders', {
  dimensions: {
    location: {
      type: `geo`,
      latitude: {
        sql: `${CUBE}.latitude`,
      },
      longitude: {
        sql: `${CUBE}.longitude`,
      },
    },
  },
});

Dimension Formats

imageUrl

imageUrl is used for displaying images in table visualization. In this case sql parameter should contain full path to the image.

cube('Orders', {
  dimensions: {
    image: {
      sql: `CONCAT('https://img.example.com/id/', ${id})`,
      type: `string`,
      format: `imageUrl`,
    },
  },
});

id

id is used for IDs. It allows to eliminate applying of comma for 5+ digit numbers which is default for type number. The sql parameter is required and can take any valid SQL expression.

cube('Orders', {
  dimensions: {
    image: {
      sql: `id`,
      type: `number`,
      format: `id`,
    },
  },
});

link

link is used for creating hyperlinks. link parameter could be either String or Object. Use Object, when you want to give a specific label to link. See examples below for details.

The sql parameter is required and can take any valid SQL expression.

cube('Orders', {
  dimensions: {
    orderLink: {
      sql: `'http://myswebsite.com/orders/' || id`,
      type: `string`,
      format: `link`,
    },

    crmLink: {
      sql: `'https://na1.salesforce.com/' || id`,
      type: `string`,
      format: {
        label: `View in Salesforce`,
        type: `link`,
      },
    },
  },
});

currency

currency is used for monetary values.

cube('Orders', {
  dimensions: {
    amount: {
      sql: `amount`,
      type: `number`,
      format: `currency`,
    },
  },
});

percent

percent is used for formatting numbers with a percent symbol.

cube('Orders', {
  dimensions: {
    open_rate: {
      sql: `COALESCE(100.0 * ${uniq_open_count} / NULLIF(${delivered_count}, 0), 0)`,
      type: `number`,
      format: `percent`,
    },
  },
});