Modifying how query items are aggregated (SAP BW)

When you import metadata, Framework Manager assigns values to the Usage and Regular Aggregate properties for query items and measures depending on the type of object that the query item or measure is in. The Usage property identifies the intended use for the data represented by the query item Usage property. The Regular Aggregate property identifies the type of aggregation that is applied to the query item or measure Regular aggregate property. Your users can override the values of the Regular Aggregate property. For semi-additive measures, you can specify additional aggregate rules by modifying the Aggregate Rules property Defining aggregate rules for semi-additive measures.

When modifying the Regular Aggregate property, you can select values that are not available through importing, such as average and maximum. You must understand what the data represents to know which aggregate value is required. For example, if you aggregate a part number, the only aggregate values that apply are count, count distinct, maximum, and minimum.

In general, use the Regular Aggregate property rather than adding summary functions to expressions. Use summary functions in expressions if the required summary is not supported by an aggregate property or if the complexity of the expression cannot be managed using the item properties.

To ensure consistent results in lists, crosstabs, and charts when counting distinct values, such as distinct customers, reference a business key that uniquely identifies the values rather than a string value. This approach prevents unintentional loss of rows where the same name is used by different customers.

The following example illustrates how to count distinct customers in a relational model:

1. Create a copy of the Customer Key query item in the Customer query subject, and name the query item Customer Count.
2. Set the Usage property for Customer Count to Fact.
3. Set the Regular Aggregate property for Customer Count to Count Distinct.
4. If formatting is required, set the Format Type to Number and specify format properties such as No. of Decimal Places and Thousands Separator as required.

   A query item with the expression of Count (distinct [Customer Key]) can behave unpredictably.

The following example illustrates how to count distinct customers in a dimensionally-modeled relational (DMR) model:

1. Follow the steps from the previous example to create the Customer Count query item for the Customer query subject.
2. Add the Customer Count query item from step 1 to a measure dimension.
3. The Regular Aggregate property and Format property settings are inherited.

   Alternatively, you can add the original Customer Key from the Customer query subject to the measure dimension. Then, set the Regular Aggregate property to Count Distinct, and set the formatting as required.