on 01-05-2012 6:47 AM
Hello Guys,
I have the following scenarion in BW.
1. Cumulative Key figure(KF) with Aggregarion as "Maximum", Exception aggregation as "Summation" on characteristics "B"
2. I have the following data at DSO1 where A and B are the key.
A B D E KF
A1 B1 D1 E1 100
3. I am multiplying the base data with anothe characteristics(C) value as below in DSO2.
A B C D E KF
A1 B1 C1 D1 E1 100
A1 B1 C2 D1 E1 100
4. The same data goes to Cube as follow.
A B C D E KF
A1 B1 C1 D1 E1 100
A1 B1 C2 D1 E1 100
5. The report output with characteristics A and KF in the view is as follow which is perfectly fine.
A KF
A1 100
6. But the key figure value doubles when an delta record comes to cube. Say D1 is changed to D2.
A B C D E KF
A1 B1 C1 D1 E1 100
A1 B1 C1 D1 E1 -100
A1 B1 C1 D2 E1 100
A1 B1 C2 D1 E1 100
A1 B1 C2 D1 E1 - 100
A1 B1 C2 D2 E1 100
7. The report output becomes as below, which is wrong.
A KF
A1 200
8. Ideally it should still show as below as the key figure is an exception aggregation with reference characteristics "B".
A KF
A1 100
I did som initial investigation and found that, since aggregation is "Maximum", the before and after image are not cancelled out. It takes the maximum of 100 and -100 values as 100.
Can you please provide your expert suggestion about how to overcome this issue?
Thanks & Regards,
Rajib
Hi,
If your initial investigation is true then the query output is correct.
You are quering on a single Charectristic but in the cube it has two records varying at D chaecteristic.
The OLAP when clculating will sum up and give you the result at the query level that is why you are seeing 200 value.
And at the load level it will be taken as a new record and not a changed record.
Please let me know what is your source system so that I can comment further.
Thanks,
Vishnu
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Vishnu,
My source system is SAP CRM.
As per my understansding, while fetching data from cube itself, it should cancel out the +/- values first, because, thats is how the deltas are maintained in cubes. The exception aggregation should be performed at the OLAP processor level.
I tried the compression technique as well. After the compression, the +/- key figure value does not calcel out, instead, it is saved as 100 against that record in the E fact table.
If this is the case, we will never be able to use a key figure with aggregation as "Maximum" and Exception Aggregation as "Summation" in a delta scenario.
Thanks & Regards,
Rajib
Hi,
I think you have to set the query property exception aggregation as Maximum to get what you desired.
Please look at the following link which may be helpful for you.
Thanks & Regards,
Vishnu
User | Count |
---|---|
91 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.