Using dimension properties in LOOKUP

[Former Member]

Hi,

I'm trying to use dimension properties in a LOOKUP, but when I execute the script the log tells me that COMPANY, SALESORG, PRODUCT and TRANSCURRENCY is not defined and therefore create a lookup on all members.

THe usage of properties in LOOKUP is described in the documentation, but somehow it doesn't seem to work for me. Can anyone help me?

*LOOKUP ACTIVITYPLAN

*DIM FCIC:ACCOUNT= "FULLCOST"

*DIM FCIC:PARTNER="I_NONE"

*DIM FCIC:COMPANY=PARTNER.ENTITY

*DIM FCIC:SALESORG=PARTNER.ENTITY

*DIM FCIC:PRODUCT=PRODUCT.PRODREF

*DIM FCIC:TRANSCURRENCY=PARTNER.CURRENCY

*DIM COUNTRY = "CO_NONE"

*DIM DATASOURCE = "INPUT"

*DIM PROFITCENTER = "YB99"

*DIM RPTCURRENCY = "LC"

*ENDLOOKUP

Regards,

Lars

Edited by: Lars Johansen on Nov 4, 2011 10:41 AM

Edited by: Lars Johansen on Nov 4, 2011 10:41 AM

[Former Member · Accepted solution]

i don't know whether you can use properties in LOOKUP.

If it was not then you can use the SELECT statement & get the member based on your property and then pass on that member to LOOKUP.

[Former Member]

Hi,

I did try that and then the process "ran forver" and I had to cancel the process.

/Lars

[former_member200327]

This approach is not a 'silver bullet' - performance depends on how many members yuo have in %P_COMP% and %P_CURR%.

Also number of your *REC records will multiply. This is unavoidable, but to protect program from executing all of them put them inside WHEN/ENDWHEN conditions.

[Former Member]

Hi Gersh,

I'm getting closer. I have now tried to use the FOR/NEXT logic on 1 property and it works. I know want to include another. In this case it seems as if the LOOKUP works fine, but the WHEN/REC/ENDWHEN is not. It doesn't recognize my LOOKUP(%LOOP_CU%%LOOP_CO%).

Any suggestions have to solve this problem?

*LOOKUP ACTIVITYPLAN
*FOR %LOOP_CO% = %P_COMP%
    *FOR %LOOP_CU% = %P_CURR%
        *DIM %LOOP_CU%%LOOP_CO%:TRANSCURRENCY=%LOOP_CU%
        *DIM %LOOP_CU%%LOOP_CO%:ACCOUNT= "FULLCOST"
        *DIM %LOOP_CU%%LOOP_CO%:PARTNER="I_NONE"
        *DIM %LOOP_CU%%LOOP_CO%:COMPANY=%LOOP_CO%
        *DIM %LOOP_CU%%LOOP_CO%:SALESORG=%LOOP_CO%
        *DIM %LOOP_CU%%LOOP_CO%:PRODUCT=PRODUCT.PRODREF
    *NEXT
*NEXT
*ENDLOOKUP

*WHEN ACCOUNT
   *IS "QPURC"
               *FOR %LOOP_CU%=%P_CURR%
                   *REC(EXPRESSION=(%VALUE%*LOOKUP(%LOOP_CU%%LOOP_CO%)),ACCOUNT="529996")
                   *END
               *NEXT
*ENDWHEN

[former_member200327]

Hi Lars,

I think you forgot FOR %LOOP_CO% = %P_COMP%/NEXT around your *REC.

Regards,

Gersh

[Former Member]

Hi Gersh,

It's not working Actually my LOOKUP is even bigger tha shown above, but I just included the part that's troubling me.

I define 5 LOOKUP variables in the script, and in the script I have 6 WHEN/REC/ENDWHEN (all with reference to LOOKUP variables).

In the log I can the LOOKUP is executed everytime I run a WHEN/REC/ENDWHEN and because the LOOKUP will not accept my dimension.property in the LOOKUP it is executing on all members 6 times = very time consuming.

I have tried a lot of options but haven't found the silverbullet - please hand it to me:-)

Thanks,

Lars

[former_member200327]

Hi Lars,

I wonder how do you see in the log that LOOKUP is executed every time you refer to it? As far as I saw each LOOKUP definition brings data from that Application just once into internal table (that's why properties that are inefficient); than when you use it in REC it reads that internal table without going to the source Application back. So, performance of your RECs depend of how big those internal tables are. So, when you refer to LOOKUP 6 times it accesses that internal table 6 times multiplied by the number of records that use that LOOKUP.

My be there are way to change your script to improve performance, but it's hard to give a general recommendation.

As for the silver bullet, did you have a chance to look at my BAdI RUNLOGIC described in How-To guides?

Regards,

Gersh

[former_member1242110]

the systax of lookup is like this

*LOOKUP ACTIVITYPLAN

*DIM FCIC:ACCOUNT= "FULLCOST"

*DIM FCIC:PARTNER="I_NONE"

*DIM FCIC:COMPANY=PARTNER.ENTITY

*DIM FCIC:SALESORG=PARTNER.ENTITY

*DIM FCIC:PRODUCT=PRODUCT.PRODREF

*DIM FCIC:TRANSCURRENCY=PARTNER.CURRENCY

*DIM FCIC:COUNTRY = "CO_NONE"

*DIM FCIC:DATASOURCE = "INPUT"

*DIM FCIC:PROFITCENTER = "YB99"

*DIM FCIC:RPTCURRENCY = "LC"

*ENDLOOKUP

you missed FCIC for last four values

and you have to use LOOKUP(FCIC) in the code ehere you have to use in logic.

Thanks,

Rajesh

[former_member200327]

No, you don't need those IDs (FCIC) if you have just one line for a Dimension; this means that in your initial script you don't need them either. You can can use LOOKUP() in REC.

Unfortunately I don't think it will affect your performance.

[former_member200327]

Hi Lars,

There is one way to replace properties in LOOKUP with members, but it works well if you have not many different values of those properties. For example, if you have

*DIM COMPANY=PARTNER.ENTITY

you can replace it with

*LOOP %COMP% = %VARIABLE THAT CONTAINS ALL NEEDED MEMBERS OF PARTNER.ENTITY%
*DIM %COMP%: COMPANY = %COMP%
*NEXT

In that case BPC will bring only needed records from looked up Application that could speed up data transfer and search. Your problem is that you have too many different properties; in that case you'd need combinations of those members.

So, my suggestion is, analyze which set of properties has least number of members comparing to total number of members in that Dimension and try to convert those. Also keep in mind, how much smaller that selection will be comparing to leaving that Dimension unrestricted.

Just another thought.

Gersh

[Former Member]

Hi,

I'm trying to build the LOOKUP using FOR/NEXT as described, but since I have 3 loops the code keep on running for ever.

I have figures out it's just warnings and the LOOKUP works as intended, but since I have many product, the LOOKUP runs for a long time and even though I thought I only ran in the beginning of the script, it runs every time I have e new WHEN/REC/ENDWHEN meaning I the script take a long time to run.

/Lars

[former_member200327]

I don't see why would you need those loops. Your scrip should work with the original LOOKUP. It will bring that data from ACTIVITYPLAN just once and then access it every time you have LOOKUP(...) in your REC.

Please let me know if this doesn't work.

[former_member200327]

Hi Lars,

This message is just a warning. Reason is, LOOKUP is trying to bring all relevant data from ACTIVITYPLAN Application, but because you are using properties it doesn't know what records to bring for those Dimensions. So, those warnings mean that when data is brought from ACTIVITYPLAN COUNTRY will be restricted by "CO_NONE", DATASOURCE by "INPUT", PROFITCENTER = "YB99" and RPTCURRENCY by "LC". All other Dimensions won't be restricted.

So, it affects speed and memory.

Hope this helps,

Gersh

[Former Member]

Hi,

I have tried this solution, but my problem is that I do not have a single value to in the SELECT statement. For each transaction I have a different PARTNER and I need create a LOOKUP on the PARTNER.

Could the solution be to create a FOR/NEXT on the LOOKUP and WHEN/REC/ENDWHEN?

/Lars

[Former Member]

Ys, u can create FOR/NEXT.

Check the below sample code:

*SELECT(%CUR%, "[ID]", RPTCURRENCY, "[REPORTING]=Y")

*LOOKUP RATE

*DIM CATEGORY="ACTUAL"

*DIM R_ACCT="AVG"

*DIM R_ENTITY="GLOBAL"

*DIM TIME="2006.AUG"

*DIM MEASURES="PERIODIC"

*FOR %LOOP_CUR%=%CUR%

*DIM LOOKRATE:INPUTCURRENCY="%LOOP_CUR%"

*NEXT

*ENDLOOKUP

u2026

*WHEN P_ACCT

*IS "CE0004010"

*FOR %LOOP_CUR%=%CUR%

*REC(EXPRESSION=%VALUE%/LOOKUP(LOOKRATE), RPTCURRENCY=%LOOP_CUR%)

*NEXT

*ENDWHEN