Microstrategy OLAP - Dynamic Aggregation

Dynamic Aggregation:

When you create a report, MSTR SQL engine writes a SQL in a report level and calculates all the metric values added in the report. After report creation and execution, you have two different options in all objects added in the report.

a) Remove from report
b) Remove from grid  - Dynamic aggregation

Remove From Report:

Right Click on any attribute and give "Remove from report" then MSTR SQL engine will rewrite the SQL in the new level and calculate all the values


Remove from Grid:

Right Click on any attribute and give "Remove from grid" (Still that object present in the report object and only removed from the grid) - In this case, No changes in the SQL. MSTR Analytical engine will calculate all metric aggregation based on the new level in the grid  - This is called DYNAMIC AGGREGATION  

In simple terms, MSTR analytical engine dynamically aggregates and calculates all metric values when you remove any attribute from the grid

How Dynamic aggregation works?

When you define any metric, in the subtotal\aggregation tab, we need to specify the aggregate function which will be used for dynamic aggregation by the analytical engine.

In case the dynamic aggregation function is set it as default then for simple formulas, analytical engine considers the same aggregate function used in the metric formula.

Challenges\Limitations with dynamic aggregation:

Dynamic aggregation works well only with simple aggregate function like Sum, Count, Max, Min etc.

We have some challenges with other aggregate functions.

Dynamic aggregation for Count: Dynamic aggregation for count should be Sum.

Dynamic aggregation for Avg:

We will not get accurate dynamic aggregation values for Avg function. because avg is depends on the number of records.

Workaround: In this case, we can use Sum()\Count() instead of Avg function and configure this as a smart metric. This will give correct values

Dynamic aggregation for Distinct Count:

As like Avg, we will not get correct values for Distinct count when we do dynamic aggregation. Instead of distinct it will just act like a count

Workaround:

1) Create an attribute based on the column where we need to do distinct count
2) Metric = Max(#1 attribute)
3) Create a Sub total function as Count(x) <Distinct = true>
4) Use this function as a dynamic aggregation function for the above created metric.
5) We should keep the above attribute (created in #1) in the report object -Must

Dynamic aggregation for Standard deviation stddev() or other analytic functions:

Dynamic aggregation won't support any of these functions. Only option available is the below

1) create an attribute on the primary key column\grain of the fact table
2) Add this attribute in the report object
3) In metric definition, define the metric as Max(column)
4) In dynamic aggregation, specify the actual function we want to use (Ex: Stddev)

Note: This is very costly solution but we don't have any other option available