Login Try Free

Operational Data · Track 3 · Tutorial 3 of 7

Match source values to dimension members

Each company names things its own way: one says Widgets, another WGT Widgets. A model dimension has its own members, and each source’s values are matched onto them, so the same product adds up across companies.

You will needAn Administrator or Data Loading Admin of Your Group on the Premium edition or the trial, and the Units and revenue model from Build a custom model.
By the endCompany B’s coded products matched to the Product members, and Widgets and Gadgets grouped under Hardware.

1Open the link

  1. Open the Units and revenue model, and choose Units sold of Company B on the left.
  2. Click the line from Product to the Product dimension. The link panel opens beside the canvas.

Each of the field’s values is listed under Result with the member it lands on. Company B codes its products, such as WGT Widgets, so with plain Auto matching nothing matches and every value goes to Unassigned.

2Match on part of the text

  1. Leave Auto on, and open Match on part of the text.
  2. In Source item: from character, enter 5, so the code and the space are skipped.
  3. Watch the Result list: each value now matches the member with the same name.
The link panel for Company B’s Product field and the model’s Product dimension: Auto matching on the text from character 5, three values matched to members and Service plan with no match going to Unassigned
Auto (1), the source item compared from character 5 (2), the values matched to members (3), and a value with no match going to Unassigned (4).
SettingWhat it compares
Source item: from characterWhere the source value starts being compared.
Source item: lengthHow many characters of the source value are compared. Empty means to the end.
Model member: from characterWhere the member name starts being compared.
Model member: lengthHow many characters of the member name are compared.

Auto ignores capitals and spaces at either end, so widgets and Widgets match.

3Deal with what doesn’t match

SVC Service plan has no member yet. The choices:

ChoiceWhat happens
+ Create memberAdds a member named after the whole value. With part of the text compared, the new name has to fit the comparison too, so it is often simpler to add the member yourself.
Switch to manualMoves the link to Manual, starting from what Auto matched. Pick the member for each value, or click + New to add one.
Leave itThe value lands on Unassigned, so the totals still agree with the source.
  1. Click Apply to keep the matches so far.
  2. Click the Product dimension, type Service plan in + Member, and click Add.
  3. Click the line from Product again: compared from character 5, SVC Service plan now matches Service plan. Click Apply.

Check

The Product node shows Auto, and no Company B value sits on Unassigned.

4Group the members

  1. Click the Product dimension in the Model cube.
  2. Type Hardware in + Member and click Add.
  3. Beside Widgets and Gadgets, choose In Hardware.
The Product dimension card: Widgets and Gadgets placed in the Hardware member, Spares and Service plan at the top level, the Unassigned member, and the field for adding a member
The dimension’s name (1), members placed in Hardware (2), the Unassigned member (3), and the field for adding a member (4).

A parent member reads the figures of everything under it, so Hardware is Widgets plus Gadgets. Auto matches on member names, so renaming a member changes which values it takes.

5Save and check

  1. Click Save, and when the badge reads Up to date, click View.

Check

With Product on the rows, Hardware is the total of Widgets and Gadgets, and Company B’s figures sit beside Company A’s on the same members.

6If something goes wrong

What you seeWhat to do
Every value goes to UnassignedThe text doesn’t match. Set where the comparison starts, or switch to Manual.
Two values land on one member by mistakeSwitch to Manual and pick the right member for each.
A member can’t be placed in anotherA member can’t go in one of its own members.
This field has no values yetThe dataset has no rows for this field. Load its data first.

Want more detail? See the documentation

Building a Business Data Builder Model
Next in Combine data with the Business Data Builder Choose how a model’s metrics total All tutorials