MDX Script Logic -> How create a select between two FOR

[I816327]

Hi Gurus,

I got perfomance problem becouse my secuence of FOR are including all members. I need to restrict the members including in the second FOR. (BPC 7.5 NW, SP05)

Please look at my scenarios:

// Current Scenario -

---

*SELECT (%ALLCECO%, "[ID]", CENTRODECOSTO, [DISTRIB] = "D")

*XDIM_MEMBERSET CENTRODECOSTO = %ALLCECO%

*SELECT (%ALLCECOREC%, "[ID]", CECOINT, [OHEAD3] = "VO")

*XDIM_MEMBERSET CECOINT = %ALLCECOREC%

*FOR %MES% = %MESES%

• FOR %CECO_EMI% = %ALLCECO%

• FOR %CECO_REC% = %RR%

• NEXT

• NEXT

*NEXT

// I need to do the next..

*SELECT (%ALLCECO%, "[ID]", CENTRODECOSTO, [DISTRIB] = "D")

*XDIM_MEMBERSET CENTRODECOSTO = %ALLCECO%

*FOR %MES% = %MESES%

• FOR %CECO_EMI% = %ALLCECO%

• SELECT (%RR%,"[ID]", CECOINT, [DISTR] = %CECO_EMI%)

*

• FOR %CECO_REC% = %RR%

• NEXT

• NEXT

*NEXT

But I can not do the last one. I can not use a variable %Ceco_Emi%. Someone knows how can do it?

Thanks a lot

Regards

Leticia

[Former Member]

Hi Laeticia,

Try to use XDIM_MAXMEMBERS

*XDIM_MAXMEMBERS = {max number of members}

*XDIM_MAXMEMBERS Entity = 50

Above instruction, if entities to process exceed the limit of 50 members, the logic

will break the query into multiple queries of no more than 50 entities each.

You will gain performance.

Theo

[I816327]

Hi Gersh,

First that all thanks for answer me.

The unique thing that I need to do is restrict the second variable (%RR%) in relation with the value whose comming from the first FOR (%Ceco_emi%).

-

---

The model has:

COSTCENTER -> %CECO_EMI%

CECOINT - > %CECO_REC% , has a propertie call DISTR where has dependence with COSTCENTER.

-

---

How is it work?, like this

If I replace... this..

*SELECT (%ALLCECO%, "ID", CENTRODECOSTO, DISTRIB = "D")

*XDIM_MEMBERSET CENTRODECOSTO = %ALLCECO%

*SELECT (%ALLCECOREC%, "ID", CECOINT, OHEAD3 = "VO")

*XDIM_MEMBERSET CECOINT = %ALLCECOREC%

by this..

XDIM_MEMBERSET CENTRODECOSTO = "CDX0001"

*SELECT (%ALLCECOREC%, "ID", CECOINT, DISTR = "CDX0001")

*XDIM_MEMBERSET CECOINT = %ALLCECOREC%

it is working perfectly, but I have 40 values like CDX0001, so I can not put fixed.

For this reason I need to use something like this (use a select in the middle):

FOR %MES% = %MESES%

• FOR %CECO_EMI% = %ALLCECO%

• SELECT (%RR%,"ID", CECOINT, DISTR = %CECO_EMI%)

*

• FOR %CECO_REC% = %RR%

• NEXT

• NEXT

*NEXT

Also, I tried with this but I got a dump.

FOR %MES% = %MESES%

• FOR %CECO_EMI% = %ALLCECO%

• FOR %CECO_REC% = %RR%

• When %CECO_REC%.DISTR

• IS %CECO_EMI%

// logic using allocation

*

• ENDWHEN

• NEXT

• NEXT

*NEXT

Do you have any advice?

Thanks

Leticia

[Former Member]

Haven't tried myself but give a try with *XDIM_FILTER between the for loops.

Another option is to try *SUB() / *ENDSUB

Edited by: ShilpaK on Mar 2, 2011 8:58 PM

[former_member200327]

Hi Leticia,

It looks like you want to make a transitive selection and BPC doesn't support it just by using *SELECT. Transitive selection can be done for 2 levels (like you need), but implementation depens on what key word you are using it for. For *WHEN it's different than for *RUNALLOCATION and for *LOOKUP, etc.

So, it looks like you want to use it in *WHEN. In that case I'd suggest the following construction:

*SELECT (%ALLCECO%, "ID", CENTRODECOSTO, DISTRIB = "D")
*XDIM_MEMBERSET CENTRODECOSTO = %ALLCECO%

*WHEN CECOINT.DISTR 
*IS %ALLCECO%
*REC(...)

*ENDWHEN

Your code shows 3 levels of FOR/NEXT. I don't think it's possible to derive third level with current script capabilities.

What's a litle surprising is that in commenst you said 'logic using allocation'. That doesn't belong inside WHEN/ENDWHEN; only *REC can be inside that statements. If you have *RUNALLOCATION then you should use same idea of restricting Properties directly in RUNALLOCATION.

Hope this helps,

Gersh

[former_member200327]

HI Leticia,

What are yu trying to do in this script? *SELECT inside FORNEXT loop is not going to work becuse you generating *SELECT statements that define same varaible %RR% multiple times. First of all, this is against BPC script syntax. Second, what values do you want %RR% to hold?

Regards,

Gersh