Time of Calculation "Before Aggregation" obsolete in NW2004S

[Former Member]

Hi all,

As mentioned in note 935903 the option Time of Calculation "Before Aggregation" is obsolete in NW2004S .

Can anyone tell me how to achieve a "Before Aggregation" in BEx in NW2004S?

The examply we would like to re-produce:

- Key figure A holding difference between 2 dates (number of days)

- Creation of CKF's with 'Before Aggregation':

-> (A>20) * Amount

-> (A>30) * Amount

-> (A>40) * Amount

-> ...

=> Calculation on cube level not possible as key users need to be able to define new CKF based upon A.

- Lowest level of detail in cube (document + item).

Thanks a lot for your feedback.

Kind Regards,

Kurt

[klaus_werner · Accepted solution]

Hi Kurt,

please read the documentation, http://help.sap.com/saphelp_nw2004s/helpdata/en/d2/02223c5f00612be10000000a11402f/frameset.htm.

Let me quote from there: "Calculating before aggregation results in poor performance because the database reads the data at the most detailed level and the formula is calculated for every record. In formula calculations, often the single record information for just one or two specific characteristics is required; the remaining data from the InfoProvider can be aggregated.

We recommend that you control the calculation level using an exception aggregation with the corresponding reference characteristic. If you require multiple exception aggregations or reference characteristics, you can nest formulas and calculated key figures in one another and specify an exception aggregation for each formula or calculated key figure."

Regards, Klaus

[Former Member · Accepted solution]

there's a fundamental flaw in that statement from SAP, then, because using exception aggregation doesn't solve the issue of calculating prior to aggregation.

If I want to calculate the difference between 2 dates for an order, and then aggregate an average for the entire plant, exception aggregation will only allow me to choose one date to represent all orders at a plant (max, min, etc.)

Even if performance takes a hit, I'd rather my data be correct. So now, end users have no ability to do this sort of analysis and the calculations have to be added in update rules?

[klaus_werner · Accepted solution]

Actually, you should be able to do what you would like to do because you can use exception aggregation in a nested way and you can use it on formula. With calculation before aggregation you might even get wrong results because of e.g. cancelled orders. If you have two Dates D1 and D2, then you can create a formula D1-D2, define an exception aggregation average with regards to characteristic order (and display it per plant).

Regards, Klaus

[Former Member]

Hi Gurus,

I have this message when i try to ejecute my query, can anyone help me to resolve this problem, i have the SAP 7.0 EHP 1, and i just have the upgrade from SAP 3.5.

I have a lot of CKF's in my queries and all of those use BEFORE AGGREGATION option, when i put in the propietries of my CKF the option after aggregation and then i check my query i have a message "time of calculation before aggregation is obsolete", and then when i put the option before aggregation in my CKF i have the message "CALLATE = 0 is not allowed".

I hope that someone have the same issue and can help me with this.

note: i'm using the BEX 3.5 and the BW 7.01 because the client wants that.

tnks

regards

Odiseo

[Former Member]

We are trying to do the exact same thing as above with the aging buckets. A unique record is the Order AND Item together. However, you can only pick one Characteristic for the Reference Characteristic. Picking either of those on their own returns incorrect results. I realize that if we had a field in our cube with Order and Item compounded together that would solve the problem. Outside of that, any ideas?

Thanks!

[Former Member]

Hi all. Just a follow up because I figured out the answer to my previous question.

If you want to have two Reference Characteristics that make a unique key:

1- Create your CKF with the first Characteristic (i.e. Item) selected in Reference Characteristic.

2- Create a second CKF with no formula in it except for the CKF from step 1. Set the Reference Characteristic in this one to the other Characteristic that forms a unique record (i.e. Order).

Hope this helps.

Edited by: Alan Weedman on Mar 19, 2010 5:43 PM

[Former Member]

Hmm...

Now tell me this - I have a specific requirement to provide aging buckets of orders. At the cube level, I have written the difference in days. The structure looks like this:

Order ....... Days Open ....... Qty.......Plant

123 .......... 25..................... 5...........A

456............40......................10.........A

780............15......................8...........A

743............20......................14.........B

In my query I want to show:

Plant........Qty < 30 days..........Qty > 30 days

A.............13...........................10

B.............14...........................0

Previously I would use a formula to calculate prior to aggregation that would look something like ('Days Open' < 30)*Qty.

In the case of plant A, if this calculation is run after aggregation, it will show that plant A has an aggregated total of 80 days open, so none of my Qty's will fall under the < 30 category.

What am I missing here?

(This is an oversimplified example, but I believe the logic remains the same. In reality, the difference in days is NOT stored at the cube level and cannot be because we use current date - document due date to determine the # of days)

[Former Member]

Hi,

APD with query as datasource is the best workaround here. Give your id . I can share a Document created with screen shots on the similar requirement.

With rgds,

Anil Kumar Sharma .P

[Former Member]

Please do share any documentation that you have.

azeckel@gmail.com

It seems like APD is a fairly intense workaround for something that you used to be able to do in the front end alone. Why remove the functionality. Even if performance is slow, I'd rather be able to do this simply as before.

[klaus_werner]

('Days Open' < 30)*Qty with exception aggregation SUM on order should do the job. Please try and let me know if it doesn't work.

Regards, Klaus

[Former Member]

thank you much for the helpful suggestions. I would assign some points, but alas, I hijacked this thread.

As far as what I decided to do: The message showing that before aggregation is obsolete is just a warning. I've tested it and it does still work. As our queries are scheduled to run overnight and publish, I think we'll just take the performance hit and use the old way.

Thanks again

[klaus_werner]

Let me just ask one thing, apart from the issue (I think I've mentioned) with cancellations, do you load delta information on existing orders and if so are you sure that your data is correct when you calculate before aggregation (that would happen on the deltas two that might be small even though the order as such is large).

Regards, Klaus

[Former Member]

Hi all,

Sorry for my late reply ...

Have been experimenting with exception aggregation for a while now and apparently it solves all my 'before aggregation' issues.

Thx a lot for your help.

Kurt

[Former Member]

I'm having the same issue. I don't want to create new key figures on the data target, but it sounds like i need to?