Skip to content

Data marts from several fact tables

A data mart can be built on several fact tables at once. In that case the merge type decides how their data is combined, and it also restricts which elements the data mart may contain.

Choosing the merge type

The merge type is bound to the activated fact tables — the fact tables shown on the canvas of the data mart constructor.

  • As soon as a second fact table appears on the canvas, the Select merge type dialog opens: "You are adding multiple fact tables to the data mart. Please select how they should be merged. The selected merge type can be changed later in the data mart settings"
  • The dialog appears regardless of which elements have been added to the data mart — it is the number of activated fact tables that matters, not the composition
  • The merge type is set and can be changed for every data mart that has at least two fact tables on the canvas. For a data mart with one fact table or none, the setting is disabled and empty: "The merge type selection will become available once you add at least two fact tables to the data mart"
  • The merge type can also be changed later in the edit dialog, from the list or from the constructor
  • A cloned data mart inherits the merge type of its source and can be changed during or after cloning

UNION

UNION combines rows from the different fact tables vertically: "Measures from different fact tables will be displayed in separate rows of the data mart".

  • The result contains the union of the columns of all fact tables. Columns defined in the source fact table of a row are filled with values; columns that the source fact table does not have are filled with NULL
  • A service field fact is added automatically and holds the name of the fact table each row comes from
  • UNION is the mode offered by default and works with every data mart type

JOIN

JOIN combines the fact tables horizontally on their common dimensions: "Measures from different fact tables will be combined into the same data mart rows based on common dimensions".

  • The result contains one row per unique combination of dimension values, with the measures of all fact tables in separate columns
  • Measure values are calculated for every combination of dimensions. Where a value cannot be calculated for a combination, NULL is returned
  • No fact field is added

JOIN requires:

  • The data mart type With grouping. It is not compatible with Without grouping or With grouping and pivoting
  • No facts in the data mart composition
  • Dimensions that are linked to every activated fact table
  • Common measures are allowed — measures that are calculated over the data of all merged fact tables

Which elements are allowed

The allowed elements depend on the data mart type combined with the merge type. The UNION and JOIN columns apply only once two or more fact tables are activated; while fewer are activated no merge type is set and the restrictions of the merge type do not apply.

Element Without grouping / UNION With grouping / UNION With grouping / JOIN With grouping and pivoting / UNION
Fact ✓ ✗ ✗ ✗
Dimension linked to all fact tables ✓ ✓ ✓ ✓
Dimension not linked to all fact tables ✓ ✓ ✗ ✓
Dimension taken from a fact table ✓ ✓ ✗ ✓
Measure ✓ ✓ ✓ ✓
Common measure ✗ ✗ ✓ ✓

Notes:

  • Common measures can be added to a data mart of any type while fewer than two fact tables are activated. The restriction in the table above takes effect only after UNION has been chosen for two or more fact tables
  • Dimensions of a dimension group that is not linked to every activated fact table cannot be added in any configuration: their checkboxes in the selection panel are unavailable and the add button is not shown on the canvas. The group itself can still be placed on the canvas without its dimensions
  • Saving is not blocked by a disallowed element. Such elements are marked with a warning icon and the tooltip "This element cannot be used in the data mart of the current type", the data mart is reported as invalid in the list ("Data mart contains invalid elements"), and verification and view creation become unavailable until the composition is corrected

How elements from different fact tables are shown

  • An element that several fact tables have in common is shown once in the data mart node and linked to all of its source fact tables
  • Elements that differ between the fact tables are all included separately
  • Dimensions of a dimension group are kept in step across the activated fact tables — see the Selecting data mart elements page

Requirements and limitations

  • All elements of the data mart must resolve to a single database connection. Elements from several connections cause an error when the query is generated
  • Filters apply to all merged fact tables
  • Verification and database view management work exactly as they do for a single-fact-table data mart