How to relate two cubes in Hubviz

Link two cubes through a field that exists in both and bring the columns of one into the other. Example: the Sales cube has the business unit, and the Business units cube has each unit’s region and manager. After relating them, Sales gains region and manager, and you can build a chart of revenue by region.

When to use it

• When a piece of information lives in one cube but the analysis is in another: a customer, product, unit or salesperson register.
• When you want to group or filter a measure by data that doesn’t come in the main table.

If both pieces of data fit in the same source table, join them in the SQL query itself instead of creating a relationship.

Before you start

• Both cubes must be in the same project.
• The fields of each cube must be chosen and saved. Only fields checked on the Field Mapping tab show up to link. See Cube fields.
• Think about which cube is the main one. The relationship starts at the cube you are editing (the origin) and goes to the other (the destination). The destination works as a lookup table: for each value of the link field, it should have a single row.

Create the relationship

The relationship only counts when the three fields of the row are filled. To link through more than one field, click Add Relation and fill another row. The table shows 5 rows per page.

What changes in the cube

When you complete the row, Hubviz copies all the destination cube’s fields into the current cube, already checked. They show up on the Field Mapping tab, in the Select fields list, under Relationship Fields, and have the Relationships type.

• The friendly name of each copied field is the technical name of the source field plus the cube name in parentheses, such as regiao (Business units). You can change it in the mapping table.
• They become regular fields in measures: use them to group, filter and pick the value.
• If a destination field has the same technical name as an existing field, Hubviz renames the copy internally (it adds the cube name) and shows a notice that a rename happened. In the example, unidade exists in both cubes.

How the link works

For each row of the main cube, Hubviz looks in the destination cube for the row whose link field has the same value, and brings its fields over.

Rule	What it means
Text comparison	Values are compared as text, ignoring case. Retail and retail match. 00123 and 123 don’t.
First row wins	If the destination has several rows with the same value, only the first is used. That is why the destination should have one row per value.
No match	If the value doesn’t exist in the destination, or the field is empty, the destination fields stay empty on that row. The row stays in the cube.
No overwriting	Hubviz only fills a field that comes from the destination when the main cube has no value for it.

Remove a relationship

Click the trash icon on the row and save the cube. This removes the link and also the fields copied from that cube. If a measure or chart used one of those fields, it stops working: switch the field before deleting.

To change the link, delete the row and create another. Changing only one field of a row that already exists doesn’t update the saved link.

Example: region and manager in Aurora’s sales

The Sales cube has revenue by business unit. The Business units cube lists each unit with its region and manager:

unidade	regiao	gerente
Industry	Southeast	Marina Alves
Retail	South	Carlos Pinto
Services	Northeast	Joana Reis

Relate Sales’ Origin Cube Field “Business unit” to the “Business unit” field of the Business units cube. After saving, create a sum measure over Revenue grouped by regiao (Business units), or filter the measure by manager. The chart now shows revenue by region, with no change to the sales table.

Cautions

• Link through a code or identifier, not names, which change more easily. Both fields need to hold the same values, written the same way.
• Relationship fields don’t appear in the origin list or the destination list. So you can’t chain relationships across three cubes.
• The destination cube must have its fields chosen. If you change its fields later, open this cube and check the relationship fields.
• If the destination cube leaves the project, the link stops showing in the editor. Check the diagram after any change.
• Fields renamed because of a name conflict (like unidade in the example) deserve a check: open the Integrated Data tab and see whether the values came through.

Next steps

• Use the new fields in measures and filters.
• Do math across fields of both cubes with calculated fields.