MicroStrategy ONE

Metrics that are not dynamically aggregated by default

The ability to aggregate data at a higher level in memory is useful for quick report interaction and analysis. However, due to their aggregation function, some metrics cannot be dynamically aggregated. For certain metric aggregation functions, recalculating the data at the higher level would yield erroneous or null values if using only the data in the report. The metrics would need to be re-executed against the data warehouse to return correct values.

Metrics that cannot be calculated correctly using a dynamic aggregation function have the default dynamic aggregation function set to none. Metrics have their default aggregation function set to none if they are defined with the following functions:

  • Average (Avg): Sum of input values divided by number of input values.
  • Count (Distinct=true): Number of distinct input values.
  • Geometric mean (Geomean): Square root of the product of input values.
  • Median: Middle value when input values are sorted.
  • Mode: Most frequently found input value.
  • Standard Deviation (Stdev): Statistical distribution of input values.
  • Variance (Var): Square of the standard deviation of input values.
  • Non-group functions or arithmetic operators: Metrics defined in this way are called compound metrics.

For information on supporting dynamic aggregation for compound metrics, see Dynamic aggregation for compound metrics. Dynamic aggregation is supported for a shortcut metric defined in a report, document, or Visual Insight quick dashboard created in MicroStrategy Web. Dynamic aggregation and subtotals are calculated correctly for these shortcut metrics, even if they contain the functions listed above. For instructions to create shortcut metrics, which are a type of derived metric, see the MicroStrategy Web Help.

When you use dynamic aggregation at a level the Analytical Engine considers erroneous for a metric defined with one or more of the functions listed above, null values, represented by dashes (--), are displayed on the report. For information on changing the display of null values, see Changing the display of null values.

This section provides the following information related to functions and metrics that do not support dynamic aggregation by default:

Example of exceptions to dynamic aggregation

This example uses the Dynamic Aggregation - Region - Employee report from the MicroStrategy Tutorial project. To look at only those metrics that do not have default dynamic aggregation functions, move the metrics Revenue, Max Revenue, and Min Revenue to the Report Objects pane. To use dynamic aggregation, the Employee attribute is also moved to the Report Objects pane. The report results are shown below.

By default, the Standard Deviation of Revenue and Count Distinct (Items Sold) metrics would return null values because they use the Standard Deviation and Count Distinct functions in their metric definitions, respectively. The default dynamic aggregation functions have instead been set to the functions used for their metric definitions.

Now compare these values to the values returned by executing against the data warehouse instead of using dynamic aggregation to calculate the values from the data within the report.

You can see that the Count Distinct (Items Sold) value is 360 for the Northeast region, which is far different from the 2,160 value returned for the Northeast region in the report that uses dynamic aggregation. The report above is able to query the data warehouse and show the distinct items sold by all employees in the Northeast region. For example, if Employee A, Employee B, and Employee C all sell one or more wrenches, the item is only counted as one distinct item for the Northeast region.

Dynamic aggregation uses the data available in the report. In this example, all the values for each employee in a given region are simply added together. When the calculation is performed, there is no way of relating which distinct items each employee sold. The calculation results in double-counting distinct items sold by two or more different employees. Rolling up data to a higher level for metrics defined with functions such as Standard Deviation and Average also perform erroneous calculations. For this reason, by default, metrics defined with certain functions return null values instead of erroneous results.

Returning correct metric values by accessing the data warehouse

If using dynamic aggregation in a report returns null or erroneous data, you can force the report to re-execute against the data warehouse instead of triggering dynamic aggregation.

For example, move the Employee attribute to the Report Objects pane in the Dynamic Aggregation - Region - Employee report in the MicroStrategy Tutorial project. If you have not changed how the report displays null values, you get the results shown below.

Notice that Employee is still a part of the Report Objects pane.

To execute the report against the data warehouse, right-click the Employee attribute and select Remove from Report. The report is re-executed against the data warehouse, and returns the data shown below.

The report is re-executed against the warehouse without using dynamic aggregation only if all the attributes in the Report objects pane are on the report.

Note that taking Employee off the report changes the data definition of the report, instead of the view definition. While you get the correct results, you are no longer taking advantage of dynamic aggregation to perform the calculations. The SQL must be regenerated and executed against the data warehouse to retrieve the results.

Estimating dynamic aggregation values with different aggregation functions

If you use a function that does not support dynamic aggregation, you can use a different function to estimate the values using dynamic aggregation. This tactic depends greatly on the function used and the amount of error that is allowable for your report results. For steps on how to change the default aggregation function, see Changing the default dynamic aggregation function.

The Dynamic Aggregation - Region - Employee from the MicroStrategy Tutorial project is used as the base report to illustrate this tactic. Move all the attributes and metrics to the Report Objects pane except for the attribute Region and the metric Standard Deviation of Revenue. When you run this report, null values are displayed for the metric because the metric's function does not support dynamic aggregation.

You can estimate these values by using the Average function as the dynamic aggregation function. This estimation is possible because the Standard Deviation and Average formulas are similar in this situation.

You can change the dynamic aggregation function in the Subtotals / Aggregation tab of the Metric Editor. The report below shows the results for the metric using the Average dynamic aggregation function on the left, compared to the Standard Deviation function on the right, run against the data warehouse.

Using the average function to perform the dynamic aggregation estimates the metric values to within $1,000 for all but two regions.

The accuracy of data returned when using a different dynamic aggregation function than the function used to define the metric depends on the similarity of the functions. For example, you cannot expect an accurate estimation of values if you use Sum as the dynamic aggregation function for a metric defined with the Standard Deviation function.

You can use any function as the dynamic aggregation function of a metric. However, be aware that not all functions are well suited for dynamic aggregation (see Metrics that are not dynamically aggregated by default).

You can also create your own subtotal to use as the dynamic aggregation function. You cannot directly use a function as the dynamic aggregation function of a metric, you must create a subtotal that uses the function in its definition. For steps to create a subtotal, refer to the Report Designer Help.

Dynamic aggregation for compound metrics

A compound metric is any metric defined by a formula based on arithmetic operators and non-group functions. Arithmetic operators are +, -, *, and /; non-group functions are OLAP and scalar functions such as RunningSum or Rank. The operators and functions can be applied to facts, attributes, or metrics.

This section covers compound metrics as they are used with dynamic aggregation. For complete information on compound metrics, see the Advanced Reporting Help.

For example, a metric defined with the formula Sum(Revenue) - Sum(Cost) is a compound metric because of the minus (-) operator. Another example of a compound metric is one defined with the formula RunningSum(Revenue) because of the RunningSum function.

By default, dynamic aggregation causes compound metrics to return null values. One reason for this is that the calculation of the formula is performed before it is dynamically aggregated, which can cause erroneous or null results depending on the formula (see Functions used in dynamic aggregation). Another possible reason is that when a metric has a complex formula for its definition, there is no way to assume the aggregation function that should be performed on the data. Rather than returning possibly erroneous results by default, you can decide how the metric handles dynamic aggregation.

For example, consider the Dynamic Aggregation report from the MicroStrategy Tutorial project, which contains the metrics Revenue, Cost, and Profit as well as the attributes Region and Employee. The report is shown below, with Employee off the report grid, but still in the Report Objects pane.

The Profit metric in this case is based on a profit fact stored in the data warehouse. Suppose that your data warehouse only has facts for revenue and cost, but you want to create a metric that calculates profit. One way you can achieve this is by creating a profit metric called Compound Profit that combines the two metrics Revenue and Cost. The metric can be defined as Sum(Revenue) - Sum(Cost).

You can also see a comparison of profit margins by including a Profit Margin metric on the report. The definition for Profit Margin is Sum(Profit) / Sum(Revenue). When you add these two metrics to the Dynamic Aggregation report, the report returns the results shown below.

With Employee in the Report Objects pane and not on the report grid, the metrics are dynamically aggregated to the regional level. The Profit metric is a simple sum of the Profit fact. This calculation can be aggregated from the Employee level to the Region level. By default, the metrics Compound Profit and Profit Margin do not use a dynamic aggregation function.

For Compound Profit to be dynamically aggregated correctly, you change the dynamic aggregation function to Sum. In this case the Sum function can be used to aggregate the data after the subtraction because the order of operations does not matter in a formula with only sum and subtract. For more information on changing the default dynamic aggregation function, see Changing the default dynamic aggregation function.

For Profit Margin, you cannot choose Sum as the dynamic aggregation function, because the definition of the metric includes a division. If Sum is chosen, the division is performed first and then these values are added together, which would use the formula Sum(Profit/Revenue). Recall that the definition of the metric is Sum(Profit) / Sum(Revenue), performing the sum aggregations first and then dividing the sums.

To return valid results in this case, you can calculate the subtraction after dynamic aggregation. You can achieve this functionality by defining the compound metric as a smart metric. After you define both of the compound metrics as smart metrics, the correct results are returned, as shown below.

You can define a metric as a smart metric using the procedure below. For an introduction to smart metrics, see Derived elements in Report Services documents with multiple datasets.

To define a metric as a smart metric

  1. In MicroStrategy Developer, right-click a metric and select Edit. The Metric Editor opens.
  2. On the Subtotals / Aggregation tab, select the Allow Smart Metric check box.
  3. Click Save and Close to save your changes and close the Metric Editor.

Changing the display of null values

You can change the default display of null values caused by incorrect aggregation for your reports.

To change the display of aggregation null values

  1. Open a report.
  2. Choose Data > Report Data Options. The Report Data Options dialog box opens.
  3. Expand the Display category, and then select Null Values. The Display - Null Values tab opens.
  4. Under Aggregation null values, clear Use Default.
  5. Replace "--" with the symbol you want to use for null values, for example, 00, null, blank, and so on.