Exceptions to dynamic aggregation - Microstrategy

The ability to roll up data in memory is useful for quick report interaction and analysis. However, not all metrics can be rolled up with an additional aggregation function. Instead, if the data is required at the higher level, it first must be recalculated from the detail data available only in the data warehouse.

For example, a count distinct tallies each different item only once, as opposed to a regular count, which adds up all the items. For example, if employee A sells four widgets and two gizmos, a count of items sold returns six. The count distinct of items sold is two. Employee B sells ten widgets and no gizmos, so his count is ten and count distinct is one. For example, to aggregate the data at a level higher than employee, remove Employee from the grid to display the data at the regional level. The counts are added together, for a total of sixteen. Adding the count distinct, however, would incorrectly return three. The only items sold were widgets and gizmos, so the proper answer is two. Note that the correct answer can only be obtained by accessing the lowest level of detail in the data warehouse.

To create a count distinct metric, use the Insert Function Wizard in the Metric Editor. This wizard displays the parameters of a function, so you can easily select Distinct. For more information, see the online help about the Insert Function Wizard.

For those metrics that can be rolled up, you can specify which function to use. On the Subtotals/Aggregation tab in the Metric Editor, change the Dynamic Aggregation Function from Default.

Dynamic aggregation exception example

Open the Dynamic Aggregation - Region – Employee report, which is displayed below.

Dynamic aggregation exception example

This report is a basic analysis of revenue at the employee level. It includes the revenue for each employee, and then the standard deviation, maximum, and minimum revenue calculated for each employee. The final metric is a count of distinct items sold by that employee. A count distinct means that each item is counted only once, as opposed to a regular count which adds up how many items the employee sold.

To roll this report up to the region level, move Employee from the grid to Report Objects. The results of the aggregation are shown in the next report sample.

Dynamic aggregation exception example

This report is saved as Dynamic Aggregation -Region.

Revenue can be rolled up, as the sum of all employees in the region. Standard deviation cannot be merely summed; it must be recalculated at the region level. The report does not contain this information, so dashes are displayed in the standard deviation column.

The minimum and maximum values can be calculated at the region level, because all the needed information is contained in the report. Count distinct cannot be rolled up, because duplicate items can exist between employees, and therefore a sum will not be valid.

If you need to calculate the values, you can completely remove Employee from the report, not just the grid. Simply right-click Employee in the Report Objects pane and select Remove from report. A warning appears, because the report must be regenerated to return the correct answers. Therefore, this action is no longer a function of the view definition, but instead, the data definition. The results are shown in the next report sample.

Dynamic Aggregation -Region.

This report is saved as Region.

The difference between the Dynamic Aggregation – Region report and the Region report is where the metric calculations are performed. In the dynamic aggregation report, the metrics are rolled up in memory, with the data available on the report. In the second report, everything is calculated in the data warehouse at the region level.

MicroStrategy can determine these values easily in both ways. When dynamic aggregation occurs, most values roll up correctly in memory. However, when exceptions occur, incorrect values are not displayed. You can easily recalculate the report to obtain the correct values by removing the attribute from the report entirely.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Microstrategy Topics