KB19607: How to calculate an Average Subtotal at the original data level in MicroStrategy Engine

SYMPTOM:
 When Subtotal Averages are calculated based on already aggregated values the results might not be as expected. Smart Compound Metrics should be used in order to achieve the results in Subtotals that are calculated in the original data level. MicroStrategy Engine is working as designed with both type of calculations, whether the calculation is done to already aggregated or original data level values. This allows flexibility for the report developer to choose which metric design approach to use in order to produce the desired Subtotal values.

 

In Figure 1 the Subtotal value highlighted in red is based on non-smart metric and the one highlighted in green is a smart metric Subtotal.Unit Cost Average metric Subtotal calculation is based on the values that can be seen in the report template. Unit Cost Average metric Subtotal is calculated based on the values that are already aggregated on the Quarter level. Unit Cost Average (Smart) metric Subtotal is based on the average calculation in the original data level (Fact table level). Unit Cost Average (Smart) metric is based on the Unit Cost Sum and Unit Count metrics shown in the last two columns. The next sections describe how to design the metrics with detailed explanation on which type of formula is used with each of the metrics. The last section describes how the results are generated by the Analytical Engine.

 

Figure 1

ANALYTICAL ENGINE FORMULA:This section describes the underlying formulas that are producing the Subtotal values shown in Figure 1. The calculation formulas are followed by scenarios that are introducing the Metric design. The last section explains how the Analytical Engine is retrieving the data and calculating the Compound Metric and Subtotal results.

 

Formulas used in the Subtotal calculations:

 

METRIC DESIGN:Non-smart metric scenario:This scenario describes the metric details that the Subtotal calculations are based in the Unit Cost Average column in Figure 1. The Subtotal is calculated based on a metric that is based on Unit Cost Fact table. Smart metric functionality is not used in this example as seen in Figure 3. This means that the results calculated in the Subtotals are based on values that are already aggregated in Attribute level. Figures 2 and 3 are describing the Unit Cost Average metric.

 

Figure 2.

Smart Metric Scenario:This scenario describes the metric details of which the Subtotal calculations are based in the Unit Cost Average (Smart) column in Figure 1. The Subtotal is calculated based on a compound metric. The metrics Unit Cost Sum and Unit Count are used in the compound metric; they are the last two columns described in Figure 1. Unit Cost Sum is divided by Unit Count metric, this is producing the same row level values as the metric described in Figure 2 and Figure 3.

 

Smart metric functionality is used in this example as illustrated in Figure 5. This means that the Unit Cost Sum and Unit Count metric fact level Total values are used for the compound metric calculations. This means that the results calculated in the Subtotals are based on the original data level calculations. Figures 4 and 5 are describing the Unit Cost Average (Smart) metric. The metric values are shown in the second column in Figure 1.

 

Figure 4.

ANALYTICAL ENGINE CALCULATIONS:Non-smart metric SQL view:With Unit Cost Average the Subtotal calculation is based on Quarter Attribute level values calculated in database level (SQL level calculation). The Subtotal Average is carried out as a post calculation to the already aggregated Average values. In the SELECT clause it is seen that the Average calculation is aggregated in Quarter level. The Analytical engine calculation steps show that the Subtotal calculation is done after the data is already aggregated in Quarter level.

 

select a12. AS QUARTER_ID,
max(a13.) AS QUARTER_DESC,
avg(a11.) AS WJXBFS1
from a11,
a12,
a13
where a11. = a12. and
a12. = a13.
group by a12.

Smart metric SQL view:With Unit Cost Average (Smart) metric calculation it is apparent that the Unit Cost Sum metric and the Unit Count metric values are retrieved in the SELECT clause. This ensures that the original data level (fact level) values can be used for the Average calculations for the Subtotal and the Compound Metric. Analytical engine calculation steps show that first Unit Cost Sum and Unit Count metric values are used to calculate Unit Cost Average (Smart) metric in the original data level. Then the Subtotal Average is calculated based on the original data level values. After this the Unit Cost Average (Smart) compound metric values are aggregated to the report level.

 

select a12. AS QUARTER_ID,
max(a13.) AS QUARTER_DESC,
count(a11.) AS WJXBFS1,
sum(a11.) AS WJXBFS2
from a11,
a12,
a13
where a11. = a12. and
a12. = a13.
group by a12.

 

 

19607