Moving series of If-Then-ElseIF logic from Web-I to BW

[SudhanshuSurma]

Dear BI Experts,

My BEx query is used as basis for a Universe. It has a free characteristic 0ACCOUNT activated with hierarchy display.

As a result, in the Universe the Account hierarchy levels are expanded automatically as sub-classes/objects [L01 Account Number] up to [L09 Account Number].

In the Web-I report we have a variable which tests the description/name of these P&L account hierarchy levels to decide the P&L Account Category under which an account should be grouped & displayed. Sample code is displayed below -

If (        [PCA Q9].[L07 Account Number] = "60001-Hardware - Purchases for Selling"

            Or [L07 Account Number] = "Hardware - Purchases for Selling"

) Then "Hardware"

This code is obviously a lengthy one with a series of IF-THEN-ELSEIF to determine the correct Category based on the texts of the various account hierarchy levels.

Client believes this could be a reason for poor performance of our report, hence are evaluating if this logic in the variable can be pushed down to the BW level.

Seek your expert BO advice for the following questions -

a) The variable logic, though long in length, is a simple IF-THEN-ELSEIF, can it really impact the report performance?

b) Can the If-else construct be replaced with an alternate construct like a Case/Switch etc. available in Web-I which is better from performance point of view?

Seek your expert BW advice for the following questions -

a) Can we expand the hierarchy levels of the 0ACCOUNT in the BEx query itself, so that each hierarchy level from L01 - L09 can be shown as an individual characteristic? In my opinion, it can only be attempted in the data-flow using FM/BAPIs.

b) Once the hierarchy levels are available as individual characteristics in the BEx query, how best do I write the same logic in the query, as used in the variable to stamp the correct Category for the account?

Any inputs will be greatly appreciated.

Regards,

Sudhanshu

[jyothirmayee_s · Accepted solution]

Hi,

Yes that might be one of reason to slow down the report performance.

You can try below formula to reduce no of lines of code.

= if(Match([PCA Q9].[L07 Account Number] ="*-Hardware*") ) then "Hardware"

Thanks,

Jothi