Data mart settings

The settings of a data mart determine the shape of its rows: the data mart type, the merge type when several fact tables are involved, the aggregation level of each measure, and the filters that restrict the returned records.

Data mart type

The type is chosen when the data mart is created and can be changed later in the edit dialog, either from the list or from the constructor.

Type	Behaviour
With grouping	Grouping is performed on the fact and dimension columns, and for each such group the aggregated values of all selected measures are written into separate columns. Default type for new data marts
Without grouping	Data for all selected dimension, fact and measure columns is output in its original, non-aggregated form
With grouping and pivoting	Measures are listed in separate rows, dimensions are placed in separate columns, and measure values and their details from the RMD are output into their own shared columns

Notes:

With grouping is the only type compatible with the JOIN merge type. Selecting JOIN switches the type to With grouping, and changing the type away from With grouping switches the merge type back to UNION
Changing the type changes the generated query, so the view created for the data mart in the database has to be updated afterwards

Merge type

The merge type applies only to data marts built on two or more fact tables and defines how their data is combined: UNION merges rows vertically, JOIN merges them horizontally on common dimensions. The setting becomes available as soon as a second fact table appears on the canvas and is cleared when fewer than two remain. The full behaviour and the element restrictions of each mode are described on the Data marts from several fact tables page.

Aggregation levels for measures

For a measure in the data mart you can define its own aggregation level — the set of dimensions and facts by which its value is grouped — instead of the aggregation implied by the data mart type.

Configuring an aggregation level:

Choose Define aggregation level in the measure actions menu, or double-click the measure node. A dialog with a dedicated canvas opens
The dialog has a side panel listing the dimensions and facts available for aggregation (those of the measure's fact table) and a central canvas with the measure node
Levels are added by dragging an element from the panel onto the canvas. Each added element becomes a separate node connected to the measure by an animated line
A level is removed with the button on its node

How the level is interpreted:

Data mart type	Nothing selected	Some dimensions or facts selected
With grouping	Aggregation is performed over all dimensions and facts of the data mart	Aggregation is performed only over the selected dimensions and facts
Without grouping	No aggregation is performed	Aggregation is performed over the selected dimensions and facts
With grouping and pivoting	The aggregation settings are applied when the pivoted representation is built	The aggregation settings are applied when the pivoted representation is built

Notes:

Aggregation levels are available for aggregated measures only. For a non-aggregated measure the action is unavailable: "Aggregation levels for non-aggregated measures are not allowed"
Aggregation levels can be changed at any time; the change requires the data mart view in the database to be updated afterwards

Adding the same measure several times:

The same measure can be included in a data mart more than once with different aggregation levels. Each instance has its own node on the canvas and its own configuration
The system distinguishes the instances by name, based on the selected aggregation level, and shows how many times each measure has been added
If a measure is added several times with the same aggregation level, the redundant instances are removed automatically when the data mart is saved

Data mart filters

A filter restricts the records the data mart returns. It consists of the element it is bound to and a condition on that element's value, for example > 100, IN (1, 2, 3) or LIKE '%test%'. Filters are configured in the same way as verification filters for measures.

Configuring a filter:

Choose Configure filter in the actions menu of a dimension, a fact or a non-aggregated measure. The filter field is pre-selected from the element
Filters cannot be set on aggregated measures: "Filtering is not allowed for aggregated measures"
Only one filter can be set per data mart element. Configuring a filter again for an already filtered element overwrites the previous condition. A more complex restriction on a single element has to be expressed as one condition using AND and OR
The Show field in data mart checkbox in the filter dialog controls whether the element also appears as a column of the result: "If enabled, the field will be shown as a separate column in the data mart. Unselect to use the field only as a filter". A field excluded this way is marked on the canvas with an icon: "Not shown in the data mart. Used for filtering only"

Filter behaviour:

Filters are stored with the data mart in its project version and are independent between data marts
They are copied when a data mart is cloned and when a project version is copied
They are removed when the filtered element is removed from the data mart, and when the RMD element they are bound to is deleted
They are applied both during verification and when the query for the database view is generated
If a filter condition refers to a calculated element — a calculated measure, a derived fact or a derived dimension — its formula is expanded into an SQL expression automatically. Elements whose formulas contain aggregate functions cannot be used in a filter condition
Changing filters requires the data mart view in the database to be updated afterwards