Login Try Free

Operational Data · Track 3 · Tutorial 2 of 7

Build a custom model

A custom model starts from nothing: you choose the sources and decide what each one feeds. Here, units sold from two companies meet the revenue in the profit and loss, so the group can read units, revenue and revenue per unit in one place.

You will needAn Administrator or Data Loading Admin of Your Group on the Premium edition or the trial, and the Units sold dataset in Company A and Company B from Consolidate datasets across companies.
By the endA Units and revenue model: units sold by product and entity, revenue from the Profit & Loss, and revenue per unit.

1Start a custom model

  1. Select Your Group, open Organisation Settings → Business Data Builder, and click New Model.
  2. On Custom, click Use this template.
  3. Enter Units and revenue as the Model name, add a Description if you like, and click Create model. The model page opens, with no sources yet.

A model name must not be used by another model or by an operational dataset of the organisation.

2Add the sources

  1. Click + Add source under Sources in model. Under Financial statements, choose Profit & Loss.
  2. Click + Add source again and, under Operational data, choose Units sold of Company A.
  3. Add Units sold of Company B the same way.
The Add a source dialog: Profit & Loss already marked Added under Financial statements, and the Units sold datasets of Company A and Company B under Operational data
Profit & Loss, already marked Added (1), and the Units sold datasets of Company A (2) and Company B (3).

Each company’s dataset is its own source, so the model reads both and adds them together. The sources wait on the page as unsaved changes; nothing is written until Save.

3Keep each entity apart

  1. In the Model cube, click Entity and tick Entity in model.
  2. Leave Scenario off. Each source is then read for one scenario, Actual.

4Link the units

  1. Choose Units sold of Company A on the left.
  2. In the Model cube, click + Add beside Metrics, choose Metric, and name it Units sold.
  3. Click Value in the Source cube, then the Units sold metric. A line joins them.
  4. Drag Product from the Source cube onto Drop here to add a dimension. A Product dimension is created from the dataset’s products, and linked to the field with Auto matching.
  5. Choose Units sold of Company B. Click Value, then Units sold; click Product, then the Product dimension, check the matches, and click Apply.
The model page with Units sold from Company A chosen: its Value measure linked to the Units sold metric, its Product field linked to the Product dimension with Auto matching, its scenario read as Actual only, and Entity on in the model
Value linked to the Units sold metric (1), Product linked to the Product dimension with Auto matching (2), the scenario read as Actual only (3), and Entity on (4).

If Company B names its products differently, see Match source values to dimension members.

5Link the revenue

  1. Choose Profit & Loss on the left.
  2. Add a metric named Revenue, then click the line that holds your revenue in the Source cube (for example, the revenue total), then Revenue.
  3. In the metric panel, set Format to Currency.

The profit and loss has no products, so Product is not filled by this source and its revenue sits on Unassigned. To put it in a member instead, click the Product dimension and choose one under Put all of Profit & Loss’s data in.

6Add the ratio

  1. Click + Add beside Metrics, choose Formula metric, and name it Revenue per unit.
  2. Click Edit formula, build Revenue / Units sold, and click Done.

A formula metric is worked out after the figures are totalled, so a quarter’s revenue per unit is the quarter’s revenue divided by the quarter’s units. See Choose how a model’s metrics total.

7Save and check

  1. Click Save. The whole model is written at once and starts loading its data.
  2. When the badge reads Up to date, click View.

Check

At Your Group, Units sold for a month is Company A’s plus Company B’s. Revenue per unit, read without Product on an axis, is Revenue divided by Units sold for the month.

8If something goes wrong

What you seeWhat to do
A source says Feeds no metric yetNothing reads it. Link one of its measures to a metric, or remove it.
This organisation already has a model called …Choose another name.
Leaving the page asks Leave without saving?There are unsaved changes. Stay and click Save, or Discard and leave.
Revenue per unit is blank for a productRevenue is not split by product; read the ratio without Product, or by entity.

Want more detail? See the documentation

Building a Business Data Builder Model
Next in Combine data with the Business Data Builder Match source values to dimension members All tutorials