Data aggregation in cubes and reports
The Prognoz Platform’s aggregation tool is designed to aggregate several values by using various mathematical functions, such as sum, arithmetic mean, and so on. Report users often need to represent data aggregated in a particular way. Our flexible aggregation feature is available in many reporting tools, including cubes. Depending on user-defined settings, aggregation can be calculated by cubes or reports on the server or client side. Let’s explore in details how, where, and why aggregation types are configured.
Aggregation by cube fact, or server aggregation, enables you to aggregate data simply by retrieving it from cube sources on the server. Server aggregation is not always applicable. For example, if you select the ODBC data source as the fact table, server aggregation becomes unavailable. In this case, you can configure aggregation on the client side via the standard cube wizard.
Aggregation on the client side can be set in the standard cube wizard on the Fact Bindings page. You can select a desired aggregation method for each fact and specify for each source whether you want to perform aggregation at server or client. By default, data are aggregated at server.
We recommend to set aggregation by cube fact when one set of dimensions in a cube source includes several records that should be aggregated. Server aggregation allows for aggregating values by the specified method.
Aggregation by dimension level is set for hierarchical dictionaries where the value of a parent element is derived from the aggregated data of its child elements. You can choose whether to aggregate all child elements or only some of them.
Aggregation by dimension level is performed on the client side, which is why calculation time and speed depend on the performance of the client computer, the selected elements, and the data available in the data sources for that particular selection.
You can set up this aggregation method on the Aggregation page of the wizards for standard and automatic cubes. For each dimension, you can define any aggregation method for different facts.
Aggregation by fixed dimension enables you to combine data by selected elements in fixed dimensions. To set up this aggregation method in a report, click the Data button under the Source tab of the side panel. In a dashboard, click the Data button on the Data tab.
Presently, reports can aggregate data by elements of fixed dimensions according to the Sum, Average, Maximum, and Minimum methods. However, reports also allow for applying methods set for cube dimensions. Consequently, if you select the From Source Dimensions method, data for the fixed dimension will be aggregated according to settings that you have specified on the Aggregation page of the cube wizard.
In addition, Prognoz Platform 8 provides a new aggregation method for fixed dimensions, which is called From Source Facts. To use this method in your report, check the Aggregate Data by Dimensions with Full Selection option on the Dimensions page of the cube, and set fact aggregation on the Fact Bindings page. Aggregation will be performed on the server side according to the methods specified for cube facts, and it will be applied only to fixed dimensions with full selection.
With this option, you can also redefine aggregation methods that have been set for cube facts. For example, if you select the Sum method in the report, the server will aggregate data for all facts using the Sum method instead of the methods that were previously set in the cube.
Now I’d like to describe some features of combining aggregation by fixed dimensions and aggregation defined for cube facts. For illustration purposes, let me fill in the bottom elements of the cube with data by calendar dimension and employees. To make our example simple to understand, I’ve used 1.00 for the all the element values.
Let’s set aggregation by the Sum method for the Employees dimension.
Then we place the Employees dimension in the fixed area of an OLAP report and set aggregation by fixed dimensions based on the Sum method:
- If you check the Aggregate Selected Elements Only option in the aggregation settings and then select the parent together with its child elements, the value for the parent element will be doubled, because the parent element already has the value of the data aggregated from the cube. If you select the parent without its child elements, data from child elements will not be aggregated.
- If this option is unchecked and aggregation is made for the entire dimension, the value of the parent without its child elements will be obtained by aggregating the data of its child elements; while for a parent element selected together with its child elements, this value will be doubled.
As you select the From Source Dimensions method, there are two aggregation scenarios:
- If you check the Aggregate Selected Elements Only option, data will be aggregated only by selected elements. In addition, if you select only the parent, data from its child elements will not be aggregated.
- If you uncheck the Aggregate Selected Elements Only option and select multiple elements, the report will show data aggregated for the entire dimension regardless of your selection. If you select a single element, which is a parent, data will be aggregated from its child elements.
If you do not set aggregation by dimension, data will be aggregated for the first element in the selection.
Now you have a general idea about the platform’s data aggregation features. Thanks for reading!