Problem with result of CKF on a multiprovider

[Former Member]

hello, I have another question about infocubes and like everytime that I don´t know how to make something, I come here to see if someone could give a hand.

I got a multicube based on this two cubes:

CUBE1

C1 C2 C3 KF1

-

-

---

1 1 1 50

1 1 2 75

1 2 1 100

2 1 1 25

2 2 1 30

2 2 2 50

CUBE2

C1 C2 KF2

-

-

---

1 1 150

1 2 100

2 1 35

2 2 85

MULTICUBE (using constant selection for C3)

C1 C2 C3 KF1 KF2 CKF(KF1*KF2)

-

-

---

1 1 1 50 150 7500

1 1 2 75 150 11250

1 2 1 100 100 10000

2 1 1 25 35 875

2 2 1 30 85 2550

2 2 2 50 85 4250

And I calculate the result of CKF as sum, so it is: 36,425

The problem is that I change the drill down of characteristics in the query the result of CKF is different.

for example:

C1 kF1 KF2 CKF(KF1*KF2)

-

-

---

1 225 250 56250

2 105 120 12600

So the sum of CKF would be 68,850 instead of 36,425 that is the value that I would like to get, independently of characteristcs that I'd have in the drill down.

I thought that this could be made using calculate before aggregarion but if I select this option the result is 0.

Is there any way to perform this ?

Thanks in advance.

Sebastián.-

[Former Member · Accepted solution]

Hi Sebastian,

I had the similar problem (but I got wrong values instead of zeros) and it helped not to use calculate before aggregation (for all characteristic), but choose after aggregation and use before aggregation just for selected characteristics using replacement path variable -- atribut value -- Reference to Characteristic (Constant 1) .

See

<a href="http://help.sap.com/saphelp_bw31/helpdata/en/6f/56853c08c7aa11e10000000a11405a/frameset.htm">http://help.sap.com/saphelp_bw31/helpdata/en/6f/56853c08c7aa11e10000000a11405a/frameset.htm</a>

Maybe it'll help.

Pavel

[Former Member · Accepted solution]

Hi Pavel, thanks for writing.

I'm not being able to work out the solution you propose.

Could you please explain me a bit more what I'm supposed to do?

Just to clarify the idea, what I want to do Is that the CKF behaves as if it were in the fact table, so the result will be always the same independently of tha characteristics that are in the drill down.

Thanks,

Sebastián.-

[Former Member · Accepted solution]

Any aditional help gurus?

I am not able to solve this yet...

[Former Member · Accepted solution]

Hi,

Solution suggested by Pavel should work. Since your calculated key figure contains product of two values, when you go to the property window of that calculated key figure in the selection window, there is an option "Aggregation which contains time of calculation that has two options before aggregation and after aggregation", available at the end of the window. choosing before aggregation results in calculation for each record in cube and hence should display correct value in your case.

This info you can get from the link posted by Pavel.

Please let me know the result.

Madhav

[Former Member · Accepted solution]

Hi Madhav,

Thanks for you replay, but as I wrote in the orignal message I already tried this option and didn't worked.

Any other idea people?

[Former Member · Accepted solution]

Hi Sebastián,

I suppose you don't have to use all characteristic in the cube for drill-down. If so I'll try to write down some steps you need to follow.

1.Create or edit your CKF

2.For each characteristic you use in the query for drill-down, create formula variable (processing by "replacement path" --> choose char. --> Replace variable with "attribute value" and choose attribut "Characteristic value (constant 1)"

3. Your formula should look like this:

KF1KF2Formula_Variable1Formula_Variable2Formula_Variable3

4. In the properties choose after aggregation (CFK will be calculated after aggregation except for the characteristic existed as formula variable in your formula).

Hope it'll help

Regards

Pavel

[Former Member · Accepted solution]

Hello Pavel, there is still something I cannot understand.

This is what I did:

1 - For each characteristic I use in the query for drill-down, I created formula variable (processing by "replacement path" --> choose char. --> Replace variable with "attribute value" and choose attribut "Characteristic value (constant 1)"

2 I Created a formula that looks like this:

KF1KF2Formula_Variable1Formula_Variable2Formula_Variable3

and this doesn't work...

In your answer you mentioned a CKF, but I dont have one, should I create a CFK? How should I define it?

Thanks

Sebastián.-

[Former Member · Accepted solution]

Hi Sebastián,

you are right. You have to create calculated key figure, because in the formulas it doesn't work like this. So create the calculated key figure,(right click on the "key figure" on the right side of query designer (below "strucure") choose "new calculated key figure" and create there the same formula you've tried before. (You can use this CKF also in other queries)

Best regards

Pavel

[Former Member · Accepted solution]

Pavel I tryed all just the way you told me, but the result is still 0.

I think that the main problem is that C3 is not present in one of the cubes...

Does anyone has other suggestion?

Thanks again...

Sebastián.-

[Former Member · Accepted solution]

Quote =======================

hello, I have another question about infocubes and like everytime that I don´t know how to make something, I come here to see if someone could give a hand.

I got a multicube based on this two cubes:

CUBE1

C1 C2 C3 KF1

-

-

---

1 1 1 50

1 1 2 75

1 2 1 100

2 1 1 25

2 2 1 30

2 2 2 50

CUBE2

C1 C2 KF2

-

-

---

1 1 150

1 2 100

2 1 35

2 2 85

MULTICUBE (using constant selection for C3)

C1 C2 C3 KF1 KF2 CKF(KF1*KF2)

-

-

---

1 1 1 50 150 7500

1 1 2 75 150 11250

1 2 1 100 100 10000

2 1 1 25 35 875

2 2 1 30 85 2550

2 2 2 50 85 4250

And I calculate the result of CKF as sum, so it is: 36,425

The problem is that I change the drill down of characteristics in the query the result of CKF is different.

for example:

C1 kF1 KF2 CKF(KF1*KF2)

-

-

---

1 225 250 56250

2 105 120 12600

So the sum of CKF would be 68,850 instead of 36,425 that is the value that I would like to get, independently of characteristcs that I'd have in the drill down.

I thought that this could be made using calculate before aggregarion but if I select this option the result is 0.

Is there any way to perform this ?

End Quote =================================

I have the same issue.

The reason you cannot use formula variable with "Characteristic reference (constant 1)" is because KF2 is using a constant selection and thus is no longer a simply operand. So BW is considering the new calculated key figure CKF as "complex" and does aggregation prior to the calculation, no matter what you do. For the aggregation prior to calculation to work you have to use "simple" key figures.

Has anyone figured out a way to do this differently?

Message was edited by: Anton Karnaukhov

[Former Member]

Hi,

Can you tell me how you are using third characteristic in multiprovider which is not there in rest of cubes upon which multi provider is built. As far as i know every characteristic in multiprovider must match precisely one characteristic or navigation attribute in each InfoProvider involved.If there is no matching characteristic in cubes i am not sure how key figure values will be calculated.

thanks,

Madhav

[Former Member]

Madhav,

when you create a multiprovider you can select characteristics that are not in all data providers.

see: