Loading

Measures and Dimensions

ERP Analytics never runs hand-written queries. Every figure is a measure (what is counted, from which datastore, how it is added up) and every breakdown is a dimension (how it is grouped). Define each once and every report, dashboard card and scheduled e-mail built on it agrees; correct the definition and they all correct together.

Where to find it

Architect Panel → ERP - Trading & Analytics:

  • Measures — the figures reports can total
  • Dimensions — the groupings figures can be broken down by
  • Analytics — the console, which offers New measure and New grouping to anyone allowed to add them

Admin Panel → Reporting:

  • ERP Analytics — the console, where measures and groupings are chosen

The starter pack

A starter set of measures and dimensions ships with the platform, shared by every tenant. It covers revenue, cost of sales, gross profit and margin, operating expenses and profit, receivables and payables, collection and payment days, the balance sheet, cash, VAT analysis, purchase spend, inventory value, stock movements by value and quantity, hours logged, billable hours, billable utilisation and unbilled time. Each measure's description states its assumptions. A tenant measure with the same key replaces the shipped one for that tenant, so you can adjust a definition without editing the original.

Defining a measure

  1. Open Measures and add a row (or click New measure on the Explore tab).
  2. Enter a Measure Key (letters, digits and underscores, for example ops_scrap_value) and a Name people will recognise, and a Description saying what it means.
  3. Choose the Source Datastore the rows come from, for example Stock Movements.
  4. Choose the Aggregate: Total (SUM), Number of rows (COUNT), Number of different values (COUNT DISTINCT), Average (AVG), Lowest (MIN), Highest (MAX), or Calculated from other measures (formula).
  5. Enter the Value: a column, or a simple sum over columns such as qty * unit_cost. Columns may follow a linked field with a dot.
  6. Narrow the rows with Filter Column and Filter Value, or with Conditions for more than one condition.
  7. Set the Date Column that places each row in time. Leave it empty for a figure that is only true today, such as stock held now.
  8. Tick Is a Balance for money owed or stock held (shown as at the end of each period), and set Higher is Better so KPI cards colour a rise correctly.
  9. Choose the Format: Money, Number, Whole number, Percentage, Hours (the value is stored in minutes) or Days. Set a Row Limit, and leave Enabled ticked.

A Formula either post-processes an ordinary measure (for example value / 60) or, for a calculated measure, combines other measures by key, such as a margin percentage.

Defining a dimension

  1. Open Dimensions and add a row (or New grouping on Explore).
  2. Enter a Dimension Key and Name.
  3. Choose the Dimension Type: Flat list, Hierarchy, or Date by day, month, quarter or year.
  4. Enter the Key Column of the measure's datastore to group by (write @date to group by each measure's own date).
  5. If the key is a code, name Labels From, its Source Key Column and the Label Column to show. For a hierarchy, name the Parent Column.
  6. Set Applies To to one datastore, or leave it blank to offer the grouping to any measure whose datastore has the key column.

What goes wrong

  • A column error reads as a sentence, never a database error: the column does not exist, you cannot read it, or it is encrypted.
  • A measure gives the same figure for every period: it has no Date Column, so it is a figure for today.
  • Hours look sixty times too big: the value is in minutes but the format is not Hours.
  • A grouping is not offered: its Applies To names a different datastore, or the measure's datastore lacks the key column.

Worked example

A production manager wants scrap value by item. An architect copies the idea of the starter pack's stock measures: measure ops_scrap_value, Source Datastore Stock Movements, Total (SUM) of the movement value, a condition that the movement type is scrap, Date Column the movement date, Money, Higher is Better off. With the existing Item and Month dimensions it appears on Explore immediately, and the production manager saves "Scrap by item, last 90 days" as a report.

Recommendations

  • Put the definition in the measure, never in each report.
  • Name measures for the business and explain them in the description.
  • Override, do not edit, a shipped measure by saving a tenant measure with the same key.
  • Add measures on demand; unused ones still confuse the person choosing.