Help required in migrating 3.5 query to BI 7.0

[Former Member]

Hello,

I have a query created in BW 3.5 which uses the property 'before aggregation' on a calc KF which is defined as constant (value 1). I have a characteristic "priority" in rows and month and year in columns above this calc KF. The way this worked, I believe, was that it was counting the number of records for each value of priority.

This property (before aggregation) is no longer present in BI 7.0 query designer. I realise that I need to implement this using the 'exception aggregation' property but I am struggling to do the same. I tried to find a characteristic in the cube which will be at a low granular level but it seems there is none. Is it not possible to implement this scenario in absence of such a characteristic (which can be used in exception aggregation)?

Please advice. Let me know in case more info is required.

[Former Member · Accepted solution]

Gurus / Experts / Mentors !!!

Someone please throw some light on this issue.

And dont worry... points in loads are assured

[Former Member · Accepted solution]

Hello,

you're right. you will have to use Aggregation with 'exception aggregation' .

In BI7 you just cann do ONE aggregation before calculating the formula. (SUM, AVG,...) and this aggregation cab just refered to ONE characteristic... whereas in 3.5 it was the more detail data.

for more info :

http://help.sap.com/saphelp_nw70/helpdata/en/82/f2dc37f0f12313e10000009b38f8cf/frameset.htm

http://help.sap.com/saphelp_nw70/helpdata/en/d2/02223c5f00612be10000000a11402f/frameset.htm

[Former Member · Accepted solution]

Hi,

Thanks for replying.

But I am not sure if I understood it fully. Does it mean there should be a characteristic called ONE in the dropdown for reference characteristics of exception aggregation? Because I did not find any such characteristic.

Please help.

Edited by: Mayank Gupta on Jan 21, 2008 7:58 PM

[Former Member · Accepted solution]

No,

it's mean that you can't use a reference (for you aggregation) composed by several Characteristic.

Nevertheless you can create differente keyfigures with the same Aggragation and just changed the referenced Characteristic.

[Former Member · Accepted solution]

Hi,

Thanks again. This part I know already - only one characteristic can be used in exception aggregation. So how do I realise the scenario that was working with the 3.5 query? I need to count, as I mentioned, no. of records for 'priority' - computation at record level in the cube.

Please suggest.

[Former Member · Accepted solution]

Like in 3.5, add a new fomula Keyfigure, with the constant 1, select the aggragation : Total and select "Priority" for the Exception aggregation.

If you haven't got the right result,it's because there is at least one characteristic at a low granular level in the cube.

So in BI7, you will have to display the characteristics which are missing to reach this low granular level you need.

There is no equivalent for "before aggragation" between BW 3.5 and BI 7. In BI 7, We have to use "Exception aggregation" with its strength and weakness.

[Former Member · Accepted solution]

Hello,

Thanks for the continued support. I have tried using 'Priority' in exception aggregation and it seems to work; But I am not able to understand this properly. I tried using various characteristics for exception aggregation and I got different results (number of unique values of that particular characteristic out of the records corresponding to one value of 'Priority').

Going by this way, when I use 'Priority' for exception aggregation, it seems that for each value of priority, it should display 1? But actually it displays the number of records present in cube for each value of priority.

Please explain this behaviour.

[Former Member · Accepted solution]

Hi,

There was some mistake in my testing. It is not working when I create the exception aggregation on 'Priority'. I am actually getting only 1s and 0s. So I am kind of back to square one.

Please help.

[Former Member · Accepted solution]

Any more opinions please?