Remove Aggregation Method

Remove Aggregation Method is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

The Remove Aggregation Method property determines whether to keep or remove aggregations in SQL queries executed from MicroStrategy. This VLDB property has the following options:

• Remove aggregation according to key of FROM clause (default): Aggregations are kept or removed based on the level of data created by joining all the tables included in the query. If the level of the information returned in the query (SELECT clause) is the same as the level determined by joining all required tables (FROM clause) then any unnecessary aggregations are removed. If these levels are different, then aggregations must be kept to ensure that correct data is returned. Determining whether aggregations are necessary after joining all relevant tables helps to provide valid SQL when the attribute data and the fact data are stored at different levels.

  For example, the report shown in the image below was created in the MicroStrategy Tutorial project.

To create this report, data must be joined from the tables LU_MONTH, LU_CUST_CITY, and CITY_MNTH_SLS. Since the attribute lookup tables combine to have a level of Customer City and Month, and the CITY_MNTH_SLS table has a level of Customer City and Month, normally this VLDB property would have no effect on the SQL. However, for the purposes of this example the LU_MONTH table was modified to include an extra attribute named Example, and it is not related to the Month attribute. Because of this additional unrelated attribute, while the report only displays Month and Customer City, the level of the data is Month, Customer City, and Example. If you use the other option (Remove aggregation according to key of fact tables) for this VLDB property, the following SQL is created:

The SQL statement above uses DISTINCT in the SELECT clause to return the Month data. However, since there is an additional attribute on the LU_MONTH table, the correct SQL to use includes aggregations on the data rather than using DISTINCT. Therefore, if you use this Remove aggregation according to key of FROM clause option for the VLDB property, the following SQL is created:

This SQL statement correctly uses aggregation functions and a GROUP BY clause to return the attribute data.

• Remove aggregation according to key of fact tables: Aggregations are kept or removed prior to determining the level of data created by joining all of the tables required for the query. This option can be used for backward compatibility, which can help to provide the expected data and SQL statements in scenarios that utilize features such as nested aggregation in metrics and custom groups.

Levels at Which You Can Set This

Database instance, report, and template