SUM Exception Aggregation and delegated measures

[former_member190952]

Hi Colleagues,

I have a WebI 4.0 SP4 report connecting via BICS to a BW 7.3 BEx query that suffers from the already widely discussed #UNAVAILABLE behavior.  In my scenario, I'm showing undelivered net value for sales orders.  Because the net price for a material can fluctuate (in particular over time), the only way to calculate this accurately is to use SUM Exception Aggregation with reference to Sales Document and Item.  However, because I'm using Exception Aggregation, the WebI report is treating this measure as delegated and when I attempt to define any variables in the WebI that are based on dimensions and include them in my output results, I face the #UNAVAILABLE behavior.

My thought is that this measure can be safely used as non-delegated because the exception aggregation is done, the final results can be re-aggregated in any way that doesn't go below the document and item level.  Is there any way to override the default for this?  The other alternatives I've considered:

• Making the calculations in WebI -- IMO, more difficult.  Also requires returning much more data.
• Providing a new Infocube with data aggregated at the Document and Item level and the measures already calculated -- ends up being a one-off solution only for this one report, which is not desireable.
• Including / hiding the dimensions that underly any variables I create in my output results -- gives me multiple lines in my output when I instead want aggregated results

I haven't seen this particular situation fully answered anywhere, but if there's already a thread that I missed that has a complete answer, please point it out to me.

[former_member184594 · Accepted solution]

I think BICS connection for now only supports Standard Aggregation type and only Key Figures with SUM. SAP needs to improve this definitely.

If you want to use the key figure you defined, instead of using BICS connection, you can try creating an OLAP universe through Universe Design Tool and set the aggregation to SUM. I think this is the only solution you got for now.

[former_member190952 · Accepted solution]

I think you are right, but I'll see if anybody else has other ideas.  I also considered creating the OLAP Universe, but I don't think this can leverage a BEx query, which is important to us because we use some virtual characteristics as well.

Ideally, it would be good if the selection between delegated and SUM was more intelligent, but I'd be more than happy with a manual override capability.

[former_member188911 · Accepted solution]

Hi,

Zahid is right, at the moment in BICS we have a forced delegation whenever the aggregation is different from SUM. 

Please note that even if in your scenario the level of complexity is low this doesn't mean that can be applicable to all situations and this prevents any different behavior right now.

If a hierarchy is involved you may try to use this workaround:

- Create a variable which containing the Hierarchy with a qualification Measure
In your table use this variable and the also directly the same Hierarchy use by the variable
  - Make the hierarchy as not visible -  In this way the axis of the table will be drive by the Hierarchy and the variable will be not part of  the axis and display value
- Use your current expression in your hierarchy and in the body of the table use something like :
[MySmartMeasure] In ([Yourhierarchy])
In this way if your current axis cardinality match the cardinality of the dimension [Yourhierarchy] which drive the smart measure will be evaluate in the context [Yourhierarchy] and display the value.

Best regards,

Simone

[former_member242283 · Accepted solution]

Hi Simone,

  I have the same situation with Chris , and your reply really give me some inspirations.

But your workaround seems not working in my scenario, maybe i am not fully understanding yours.

Like ' Create a variable which containing the hierarchy with a qualification measure" ,does this mean create a variable with qualification as measure, and drag the hierarchy into formular area ?

both the variable and the hierarchy should be included in the table except hide the hierarchy ?

In my scenario,  I use  multiple tree list of the input controls to select from product hierarchy,  in the table, I will display calquarter/region/no.of systems. 

no.of systems is exception aggregation with reference to system number. However,  this can be sum aggregated in the webi report, for I only include calquarter/region/product hierarchy/no.of systems in the query. the result I want is no.of systems of some products in a certain quarter and a certain region.  that is to say,  no.of systems of  products which I select from the input controls should be add up.

actually I tried define several variables , but none of them works.

e.g : sum([no.of systems])

         Sum([Num of Systems])ForAll([detail product]) -------------([detail product] is the variable equal to product hierarchy)

        [Num of  Systems]In([Cal. year / month - Key];[region])

         NoFilter(Sum([Num of Systems];Descendants([Product Hierarchy];1;Self_After)))

Can you give me some advise ?

or any other ideas from anybody would be appreciated.

Thanks,

Amon

[former_member188911 · Accepted solution]

Hi Amon,

sorry for the late reply.

With Exception Aggregation things are a bit different, because the Exception Aggregation is a calculation made at the level of the OLAP Engine.

Once it is passed to WebI the WebI calculation Engine will compute the values with the functions it knows (Sum, Average) and will do it locally with its own engine.

Therefore you may have either #UNAVAILABLE or in the worst scenario, incorrect results.

Thanks

Simone

[Former Member]

Chris ,

#UNAVAILABLE ,#TOREFRESH Error occurs due to Database Delegate nature of the Key Figure hence if you are using OLAP universes then change the Database Delegate to Sum or any other aggregate function in Properties and if in case you are using BICS Connection then you have to create variables Iin webi will contain Sum as aggregate function and also if the report contains certain objects as Input controls then that object has to be specified explicitly as a part of " IN " Function .

For Eg : 

If webi report contains KF as 'Sales Qty' and Input control as 'Plant' , 'Branch' then the new variable will be =Sum([Sales Qty] IN( [plant];[Branch])).

Hope this will resolve your issue.

Regards

Jeetan Jagtap