Example of Setting Up Data Aggregation in Cubes and Reports

Aggregation is used to merge several values using various mathematical functions, for example, sum, arithmetic mean, and so on. Often, it is required to display data aggregated by some method in reports. Foresight Analytics Platform provides users with flexible aggregation setup in various reporting tools and in cubes. Depending on where aggregation is set up, it can be calculated on server or on client, in cubes or reports.

Aggregation by cube facts (server aggregation) allows for calculation during data extraction from cube sources on server. If ODBC data source is selected as a fact table, the standard cube wizard allows for setting up aggregation calculation on client for this data source.

This aggregation type is set up in the standard cube wizard on the Facts Binding page. One can select individual aggregation method for different facts, and specify where execute calculation - on server or on client - for each data source. Data is aggregated on server by default:

If there are several records for one dimension set in the data source, which require aggregation, set up aggregation by cube facts. If server aggregation is set up, these values will be aggregated by the specified method.

Aggregation by dimension levels is set up for hierarchical dictionaries, in which data of dictionary child elements, using some method, is aggregated and written to the parent element. The user can set up, that is, aggregate all child elements or only selected elements to the parent element.

Aggregation by levels is calculated on the client that is why calculation speed and time depend on technical characteristics of a client computer, on selection in cube dimensions and the amount of data in data sources by this selection.

This aggregation type is available for setup on the Aggregation page in standard and automatic cube wizards. One can set up various aggregation methods for different facts in each dimension:

Aggregation by fixed dimensions in platform reporting tools allows for merging data by selected elements in fixed dimensions. The setup is executed in reports on the Data tab of the Source group of tabs on the side panel:

Aggregation by fixed dimension elements calculated by reports is implemented only for the Sum, Average, Maximum, and Minimum methods. However, reports allow for aggregation in fixed dimensions using the methods that are set up in a cube for dimensions. If the From Source Dimensions method is selected, data by a fixed dimension is merged according to settings determined on the Aggregation page in the cube wizard.

One can also use the From Source Facts aggregation method for fixed dimensions, which becomes available in report if the Aggregate Data by Dimensions with Full Selection checkbox is selected in the cube on the Dimensions page, and aggregation is set for facts on the Facts Binding page. This aggregation is calculated on server according to the methods specified for cube facts and only for fixed dimensions with full selection. Besides, the checkbox allows for redetermining aggregation methods configured in the cube for facts. That is, if the checkbox is selected, and, for example, the Sum method is selected in the report, server aggregation for all facts will be calculated using the Sum method instead of the methods configured in the cube.

Consider the example of combined work of aggregation tools by fixed dimensions and aggregation configured in a cube. Illustrate these features using a simple example of a cube filled with data on the bottom most elements by calendar dimension. For visual clarity, all data is filled with ones:

Set up aggregation in the cube by the Years-Quarters dimension using the Sum method:

Move the Years-Quarters dimension to the fixed area in the report and set up aggregation by fixed dimensions in the report using the Sum method.

If the Aggregate Selected Elements Only checkbox is selected for cube dimension in aggregation settings, data will be doubled on parent element selection, that is, cube aggregation has already calculated in the parent element:

If the parent element is selected without child elements, child elements will not be aggregated because they are not selected:

If the Aggregate Selected Elements Only checkbox is deselected, the entire dimension is aggregated, if only the parent element is selected, its child element data will not be aggregated, while the parent element is selected with child elements, data will be doubled in the parent element.

If the From Source Dimensions aggregation method is selected in the report, and the Aggregate Selected Elements Only checkbox is selected, only selected elements will be aggregated. If only the parent element is selected without child elements, data will not be aggregated to it:

If the Aggregate Selected Elements Only checkbox is deselected, aggregation result by multiple selection will always be displayed by the entire dimension regardless of the selection:

If single selection is set for the parent element, data will be aggregated from its child elements.

If cube aggregation by dimension is not set up, values for the first selected element will be displayed.