aggregation of key figures

8
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com © 2011 SAP AG 1 Aggregation of Key Figures Applies to: SAP Net Weaver BW. For more information, visit the EDW homepage Summary Detailed description on how and When to Use Before or After Aggregation in Key Figures Author: Adlin Sundararaj Company: Accenture Services Pvt. Ltd. Created on: 19-April-11 Author Bio Adlin Sundararaj is working as SAP-BI Consultant in Accenture Services Private Ltd and has experience of working on implementation and maintenance projects.

Upload: nandeesh-kodimallaiah

Post on 24-Apr-2015

255 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: Aggregation of Key Figures

SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com

© 2011 SAP AG 1

Aggregation of Key Figures

Applies to:

SAP Net Weaver BW. For more information, visit the EDW homepage

Summary

Detailed description on how and When to Use Before or After Aggregation in Key Figures

Author: Adlin Sundararaj

Company: Accenture Services Pvt. Ltd.

Created on: 19-April-11

Author Bio

Adlin Sundararaj is working as SAP-BI Consultant in Accenture Services Private Ltd and has experience of working on implementation and maintenance projects.

Page 2: Aggregation of Key Figures

Aggregation of Key Figures

SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com

© 2011 SAP AG 2

Table of Contents

Introduction ......................................................................................................................................................... 3

Aggregation......................................................................................................................................................... 3

Features .......................................................................................................................................................... 3

Before / After Aggregation .............................................................................................................................. 3

How Before/After Aggregation Works ............................................................................................................. 4 After Aggregation ......................................................................................................................................................... 4

Before aggregation ...................................................................................................................................................... 4

Illustration with real-time example ................................................................................................................... 5

Limitations ....................................................................................................................................................... 6

Disadvantages ................................................................................................................................................ 6

Related Content .................................................................................................................................................. 7

Disclaimer and Liability Notice ............................................................................................................................ 8

Page 3: Aggregation of Key Figures

Aggregation of Key Figures

SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com

© 2011 SAP AG 3

Introduction

To provide flexibility with calculated query key figure values and meet the demands of a user environment, you might want to calculate values in queries using different types of aggregation.

BW uses the Before Aggregation and After Aggregation settings in query processing. They do not relate to aggregates that improve performance. The Before Aggregation setting calculates key figures differently than the After Aggregation setting in BEx Analyzer.

The document describes how they operate and where to find them in the system.

Aggregation

To calculate the value of key figures, the data from the InfoProvider has to be aggregated at the detail level for the query. It is possible that formulas also have to be calculated. In this case, several characteristics have to be aggregated. For each key figure, one selected characteristic can be aggregated with another rule.

Features

The OLAP engine in the BW proceeds as follows:

Standard aggregation is executed first. Possible types if aggregation are summation (SUM), minimum (MIN), and maximum (MAX). Minimum and maximum can be set, for example, for date key figures.

The aggregation of a selected characteristic takes place after the standard aggregation (exception aggregation). Possible exception aggregations available are average, counter, first value, last value, minimum, maximum, no aggregation, standard deviation, summation and variance. Cases where exception aggregation would be applied include, for example, storage non-cumulative that can not be totaled by time, or counters that count the number of characteristics for a particular characteristic.

Aggregation by currency and units is executed last. If two figures are aggregated unequally with different currencies or units, the system marks this with „*‟.

Before / After Aggregation

The BW 3.x system can perform two different types of calculated key figure aggregation:

Before Aggregation

After Aggregation.

For example, a query might display a total price variance report. This report would calculate differently if calculated before aggregation versus after aggregation.

When BW calculates the report before aggregation, it calculates each variance individually on each line and then aggregates them together to form a variance.

If BW calculates after aggregation, it adds the total price values and then determines the variance.

Depending on the formula, these two approaches can give widely different key figure results.

Developers can set the type of aggregation individually on each calculated key figure. However, the system initially sets all key figures to After Aggregation by default. Using the Before Aggregation setting, you can arrive at a calculation that you may otherwise not be able to perform. However, it may negatively affect your system‟s performance.

Page 4: Aggregation of Key Figures

Aggregation of Key Figures

SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com

© 2011 SAP AG 4

How Before/After Aggregation Works

After Aggregation

By default, the system performs an After Aggregation calculation.

The calculated key figure formula multiples value A by value B.

Using After Aggregation, the system adds up all the values for value A, then adds up all the values for value B, and multiplies these sums together.

In Table 1, the values yield 70 * 70 = 4900.

This adds all the value A entries multiplied by the total of the value B entries.

After Aggregation Values

Value A Value B A * B

10 10 100

20 20 400

20 10 200

20 30 600

70 70 4900

Before aggregation

Let‟s see how the result differs by changing the calculated key figure to provide the value Before Aggregation.

First, the system multiplies the value at the lowest level and then sums the resulting values. Next, the system adds these results to produce a Before Aggregation result of 1300.

Before Aggregation Values

Value A Value B A * B

10 10 100

20 20 400

20 10 200

20 30 600

1300

Page 5: Aggregation of Key Figures

Aggregation of Key Figures

SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com

© 2011 SAP AG 5

Illustration with real-time example

We have a query for Sales Order which has two Key Figures Quantity and Revenue.

Make two Calculated Key Figures one for „After Aggregation‟ and other for „Before Aggregation‟

Rclick on the CKF edit option

For „After Aggregation‟ CKF, go to the aggregation tab in the properties, you can see that be default the “Calculation After Aggregation” option is selected.

For „Before Aggregation‟ CKF, go to the aggregation tab in the properties, uncheck the “Calculation After Aggregation” checkbox.

Page 6: Aggregation of Key Figures

Aggregation of Key Figures

SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com

© 2011 SAP AG 6

When we see the Query output result in Query Analyzer,

We can find the different results for „After/Before Aggregation‟.

Limitations

You only can set the Before Aggregation option on Basis calculated key figures. In other words, the calculated key figure formula only can use this setting if the InfoCube stores each key figure in the calculation. You cannot use Before Aggregation if you are performing calculations using other calculated key figures in your formula. If you have calculated key figures in your formula, then the system grays out this option and does not allow you to choose it.

Disadvantages

There is a distinct disadvantage to setting calculated key figures to Before Aggregation. The performance of Adjust the Time of Calculation setting to specify before or after aggregation calculation is significantly slower than the After Aggregation calculated key figures because the system must perform multiple calculations for each value and then aggregate these values. Therefore, the system cannot use InfoCube aggregates. Thus, consider performance before setting this in an InfoCube with a large volume.

Page 7: Aggregation of Key Figures

Aggregation of Key Figures

SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com

© 2011 SAP AG 7

Related Content

http://forums.sdn.sap.com/thread.jspa?threadID=1590397

http://forums.sdn.sap.com/thread.jspa?threadID=965822

http://forums.sdn.sap.com/thread.jspa?threadID=808482

For more information, visit the EDW homepage

Page 8: Aggregation of Key Figures

Aggregation of Key Figures

SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com

© 2011 SAP AG 8

Disclaimer and Liability Notice

This document may discuss sample coding or other information that does not include SAP official interfaces and therefore is not supported by SAP. Changes made based on this information are not supported and can be overwritten during an upgrade.

SAP will not be held liable for any damages caused by using or misusing the information, code or methods suggested in this document, and anyone using these methods does so at his/her own risk.

SAP offers no guarantees and assumes no responsibility or liability of any type with respect to the content of this technical article or code sample, including any liability resulting from incompatibility between the content within this document and the materials and services offered by SAP. You agree that you will not hold, or seek to hold, SAP responsible or liable with respect to the content of this document.