Consolidation allows you to group attributes to define a virtual attribute, which does not exist in the project schema. This virtual element could be placed on a report just like an attribute and is calculated on the Intelligence Server side, causing no load on the database. The consolidation element does not have to be based on a single attribute. Also, we cannot group metrics using consolidations. The two main uses of consolidation are:
- To create a virtual attribute
- To perform row-level math
Attribute elements define the consolidation. They can be any of the following:
- Elements from the same attribute, such as month-January and February
- Elements from different attributes in the same hierarchy, such as month and quarter from the time hierarchy
- Elements from different attributes in different hierarchies, such as month from the time hierarchy, and region from the territory hierarchy
- Elements from the same consolidation
- Elements from different consolidations within the project
Browse the folder in which to create new consolidation; the best practice is to create the consolidation under the public objects and consolidation folder:
- Select File | New | Consolidation. The consolidation editor is displayed.
- Under consolidation definition, create the consolidation element by double-clicking an attribute and then dragging its element onto the editor section from the data explorer section.
- Give a name to the group element and then define the logic in the editor:
- Click Save and Close, and type the name of a consolidation.
Using Arithmetic operator and element from the same attribute
Let's say your database contains data based on months or quarter, but you want a report to display data on a semester basis. You can create a consolidation with elements representing each semester. Basically, you will create a semester as a virtual attribute without changing your data model. In the following image, we have explained the usage of an arithmetic operator and an element from the same attribute by creating a consolidation and a report:
Using logical operator and element from a different attribute
Let's say you want a report showing bikes ordered and shipped in semester one and semester two, 2005. You can use a consolidation such as the following:
Here, you have used a Logical operator (AND) to consolidate data from different attributes, namely, Product and Date. The report generated based on this consolidation:
Row-level math
Consolidation allows us to perform a row-level mathematical operation, which makes it a powerful tool for reporting. We can perform addition, subtraction, and so on, between the elements or element groups. Continuing with the bike example, the difference between the first semester and second semester shipping order quantity is calculated using row-level math in a consolidation, as shown here:
Report
Similarly, we can have a ratio between the two semesters' sales amount and can format elements differently within the same consolidation, as shown here:
Consolidation
Report
To format each element individually, we right-click on an element within a consolidation, as shown, and then select Formatting properties:
MicroStrategy generates a query; the SQL engine generates the SELECT and then the consolidation and the calculation are performed on the Intelligence Server/analytical engine using three-tier mode if available. If not, the developer will perform those calculations.
Following is the SQL view of the report with a consolidation that shows the calculation performed by analytical engine:
See the following for a comparison:
- Consolidation use attributes are a grouping of attribute elements, whereas a custom group is based on filters
- Consolidation allows a user to perform row-level math, which is not possible in a custom group
- Consolidation is faster than custom groups as it may need just one pass for all the elements of the consolidation
- Consolidation can be used to create other consolidations, whereas that's not possible with custom groups
- Consolidation calculates data at the analytical engine level, whereas a custom group calculates at the warehouse-level
- Hierarchical display in consolidation is fixed at the element level only, whereas in a custom group it is flexible and expandable