Consolidations - Microstrategy

Consolidations enable you to group together and to pick specific attribute elements. Further, consolidations allow you to place this grouping of attribute elements on a template just like an attribute. The elements of the consolidation appear as rows on your report, and they can have arithmetic calculations.

For example, suppose you wanted to see each season as a separate row on a report, but Season does not exist as an attribute in your project. A consolidation allows you to group together the elements of the Month of Year attribute into various seasons and place them on the template. This consolidation will contain four consolidation elements, one for each season.

Summer consists of June + July + August, fall consists of September+ October + November, and so on. The consolidation is placed in the rows of your report with the desired metrics in the columns. Therefore, when a user runs the report, the metric values for June, July, and August are added together to yield the value for Summer. This occurs for each of the seasons.

Consolidation elements do not have to be based on a single attribute, as described in this example. You can use attributes at different levels, such as Region and Country, or unrelated attributes, such as Country and Year.

In general, consolidations provide two powerful functions that enhance your reporting needs. These two functions are

  • create a “virtual” attribute
  • perform row level math

Create a “virtual” attribute

In the above example of the Seasons consolidation, the four different season consolidation elements are made up by adding together the respective Months of Year that belong to the different seasons. The fact that you can add together attribute elements in groups means that you can aggregate data on a report at a level other than one of the predefined attributes. The effect appears as if you had a Seasons attribute in your data model, as shown below.

Create a “virtual” attribute

Of course, you can get the same effect if you change your data model, and actually add a Seasons attribute to your Time hierarchy. However, adding an attribute is generally a very complex task because you have to ensure that the proper lookup and relationship tables exist in the warehouse. Consolidations allow you to avoid changing the data model, although in a limited way.

This Seasons consolidation is built by adding together respective Months of Year in the different seasons. But you are not limited to just adding. In fact, you can perform any simple arithmetic operation while building your consolidation.

Perform row level math

Consolidations allow mathematical operations between elements or element groups. That is, you can perform arithmetic operations such as addition, multiplication, division, and subtraction. You can even use constants while specifying your elements.

This feature makes consolidations a powerful tool in reporting. It allows you to specify row level math for a report. That is, it allows you to have a row in a report that is specified by a mathematical operation.

Continuing with the Seasons example, the ratio of sales between different seasons can be calculated using row level math in a consolidation, as shown below:

Perform row level math

Spring/Fall is the consolidation element Spring divided by the consolidation element Fall. Similarly, Summer/Winter is the consolidation element Summer divided by the consolidation element Winter. The Seasons consolidation performs row level math that makes this report possible. Without consolidations, creating this analysis would be cumbersome.

Notice that elements are formatted as dollars ($) and percentages (%) in the same consolidation. You can format individual consolidation elements in the Consolidation Editor.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Microstrategy Topics