Run allocation on condition

[paliwalamit9]

I want to run allocation logic on condition.

My allocation logic is as follows:

//  COPYING ABOVE RATE TO ALL HQs

*XDIM_MEMBERSET TIME = BAS(%TIME_SET%)

*XDIM_MEMBERSET CATEGORY=Actual

*XDIM_MEMBERSET ZVALUATION = RATE

*XDIM_MEMBERSET ACCOUNT = PL010

*XDIM_MEMBERSET RPTCURRENCY = <ALL>

*RUNALLOCATION //COPYING RATE FROM NO_HQ TO ALL HQ

  *FACTOR = 1

  *DIM ZHQ_CNTRY_CUST WHAT = NO_HQ; WHERE=BAS(ALL_HQ)

*ENDALLOCATION

*COMMIT

What above script does is just copies data from "NO_HQ" and replicates it on BAS(ALL_HQ)

But I want to copy the rates to BAS(ALL_HQ) on condition that member of ALL_HQ should have value in ZVALUATION=VALUE

Eg:

ZVALUATION	VALUE	RATE
ZHQ_CNTRY_CUST	(Data available from BW		Remark
NO_HQ		50	calculated by different logic script
HQ1	50000	50	Want to copy rate
HQ2	0		Don’t want to copy rate
HQ3	60000	50	Want to copy rate
HQ4	90000	50	Want to copy rate
HQ5	0		Don’t want to copy rate

[former_member186338 · Accepted solution]

Hi Amit,

Just some remark - strange idea to use dimension names like ZVALUATION and ZHQ_CNTRY_CUST - "Z" is not nesessary and confusing

Second - your table is unclear...

VALUE is a member of ZVALUATION dimension? Or?

Please describe members of ZVALUATION

Vadim

[paliwalamit9 · Accepted solution]

Thanks for the suggestion

Yes VALUE, RATE are members of ZVALUATION.

NO_HQ, HQ1, HQ2, etc are members of ZHQ_CNTRY_CUST

[former_member186338 · Accepted solution]

The rule you want to implement:

If VALUE <> 0 for HQx the copy RATE from NO_HQ to HQx

Then after RUNALLOCATION you may clear RATE with WHEN/ENDWHEN:

*XDIM_MEMBERSET TIME = BAS(%TIME_SET%)

*XDIM_MEMBERSET CATEGORY=Actual

*XDIM_MEMBERSET ZVALUATION = RATE

*XDIM_MEMBERSET ACCOUNT = PL010

*XDIM_MEMBERSET RPTCURRENCY = <ALL>

*RUNALLOCATION //COPYING RATE FROM NO_HQ TO ALL HQ

  *FACTOR = 1

  *DIM ZHQ_CNTRY_CUST WHAT = NO_HQ; WHERE=BAS(ALL_HQ)

*ENDALLOCATION

// have to repeat scope definition after *ENDALLOCATION!

*XDIM_MEMBERSET TIME = BAS(%TIME_SET%)

*XDIM_MEMBERSET CATEGORY=Actual

*XDIM_MEMBERSET ZVALUATION = RATE

*XDIM_MEMBERSET ACCOUNT = PL010

*XDIM_MEMBERSET RPTCURRENCY = <ALL>

*XDIM_MEMBERSET ZHQ_CNTRY_CUST = BAS(ALL_HQ)

*WHEN ZVALUATION

*IS *

*REC(EXPRESSION = [ZVALUATION].[VALUE]==0 ?  0 : %VALUE%)

*ENDWHEN

Vadim

P.S. And NEVER use COMMIT if you are not using MDX calculations!

[paliwalamit9 · Accepted solution]

Sir,

This works as I want.

Thanks a lot.

But this script is taking lot of time to execute.

Also, if I don't use commit after MDX satements, script uses the *XDIM_MEMBERSET from all script written above that logic.

eg:

If logic is

*XDIM_MEMBERSET CATEGORY=Actual

*WHEN

..........

*ENDWHEN

*XDIM_MEMBERSET ENTITY=1000

*WHEN

..........

*ENDWHEN

Then for second logic, it runs for CATEGORY=Actual and ENTITY=1000

[former_member186338 · Accepted solution]

1. Yes, WHEN/ENDWHEN loop is slower then *RUNALLOCATION but you have no other option - there is no conditional allocation...

2. The only effect of *COMMIT with WHEN/ENDWHEN calculations - is scope reset to original script scope. Data is written to database at the end of WHEN/ENDWHEN loop automatically.

*COMMIT is equivalent to

*XDIM_MEMBERSET TIME=%TIME_SET%

*XDIM_MEMBERSET ENTITY=%ENTITY_SET%

...

If you want to reset scope for some dimensions to original script scope - it's better to use *XDIM_MEMBERSET DIMNAME=%DIMNAME_SET% instead of *COMMIT for all dimensions.

And when I am talking about MDX calculations - I am talking about syntax like:

[P_ACCT].[#CE0661000] =[P_ACCT].[CE0004010]/[P_ACCT].[CE0652000]

For this type of calculations COMMIT actually write data to the cube. But MDX calculations are very slow!

Vadim

[paliwalamit9 · Accepted solution]

Actually I started using *COMMIT in script logic because using scope for all dimension using *XDIM_MEMBERSET DIMNAME=%DIMNAME_SET% (i.e using data manager prompt ) is not always possible and using *XDIM_MEMBERSET DIMNAME=<all> slows down the script tremendously.

[former_member186338 · Accepted solution]

Never have issues like this! Please show example ...

Vadim

[paliwalamit9 · Accepted solution]

Unfortunately that was on one of the previous projects and I don't have access to that now.

[Former Member]

Then something like:

*RUNALLOCATION //COPYING RATE FROM NO_HQ TO ALL HQ
*FACTOR = USING/USING
*DIM ZHQ_CNTRY_CUST	WHAT = NO_HQ;	WHERE=BAS(ALL_HQ)	USING= wherever is posted VALUE on this dimension
*DIM ZVALUATION	WHAT = XXX	WHERE=BAS(ALL_HQ)	USING= VALUE
*ENDALLOCATION
*COMMIT

[former_member186338]

Ok,

You mean something like:

*RUNALLOCATION //COPYING RATE FROM NO_HQ TO ALL HQ   

*FACTOR = USING/USING   

*DIM ZHQ_CNTRY_CUST; WHAT=NO_HQ; WHERE=BAS(ALL_HQ); USING=BAS(ALL_HQ)

*DIM ZVALUATION    WHAT=RATE; WHERE=RATE; USING=VALUE

*ENDALLOCATION

By the way, you don't need to use COMMIT after ENDALLOCATION...

[Former Member]

Yes like this!

I copied the commit from the original post...

By the way, when do we need COMMIT? Only after MDX statement, correct?

Also question I have about XDIM_MEMBERSET, do I need one before each runalloc?

Thanks!

[former_member186338]

"Only after MDX statement, correct?" - Yes! And it's better not to use MDX at all

"about XDIM_MEMBERSET" - you need to scope all dimensions that you don't use with *DIM statements in RUNALLOCATION. *DIM will override XDIM_MEMBERSET.

Vadim

[Former Member]

I never use MDX 🙂

Thanks Vadim for your expalnations.

Regards

Camille

[former_member186338]

Just tested this script - it works correctly even if zero is in second using (VALUE) - no record will be generated for zero!

[Former Member]

Works fine indeed.

Well ... was working fine up to a note SAP provided last week to improve the calculations and now all runallocation give wrong results... Anyway :-(((

[former_member186338]

Ups, can you show some test case? And the note number?

[Former Member]

Note is 2206128.

I was suspecting the issue to be with the CURRENCY (DEVISE_RPT for us), as I saw a case where data were not doubled when no currency conversion was run, and doubled when my quantity account (VVQ03) had been translated.

Also I added a 3rd currency in the dimension and results can now be trippled.

But I might be completly wrong ... Apart from the fact that support saw something going that way:

"From our side, we also found something in the HANA index alert log:

[18338]{430710}[93/-1] 2016-01-30 13:42:17.115590 e CalcEngine     

ceCalcScenarioModeler.cpp(00493) : The following errors occured:

Inconsistent calculation model (34011)

Details (Errors):

- CalculationNode (T_WHERE_NEW0) -> attributes -> calculatedAttribute

(DATA_WHERE) -> expression: Attribute 'lc' is missing in node

T_WHERE_NEW0.

This error seems relevant with the issue you reported, say the

restriction condition of DEVISE_RPT = 'LC' is missing, therefore all

the members will be read out from the master data table, say, the 'LC'

and 'EUR' data will be retrieved, which lead to the error result as

you reported."

Below what I sent to sap (can't attached the doc)

Source data:

01102521-0010016572	ZZOSSTEST	VVQ03	E_PM92	2015.03	00288	CODE_USAGE_NA	IMPORT_BW	REEL	LC	0,2500000
01102521-0010016572	ZZOSSTEST	VV010	E_PM92	2015.03	00288	CODE_USAGE_NA	IMPORT_BW	REEL	LC	27,7500000

01102521-0010016572

ZZOSSTEST

VVQ03

E_PM92

2016.01

00288

CODE_USAGE_NA

IMPORT_APS

BUDGETV0

LC

3,1736454

Run script.

What does the script do :

1) Copy BUDGET/ VVQ03 / E_PM92/all 2016 months (WHAT)

TO

BUDGET/QTE_REF/SITE_EXPEDITION_NA/ 2016.SAISIE (WHERE)

2) Copy REEL/ VV010 / E_PM92 / all months of 2015 (WHAT)

TO

BUDGET/CA_REF/SITE_EXPEDITION_NA/2016.SAISIE (WHERE)

3) Divide CA_REF / QTE_REF = PMV_REF on 2016.SAISIE

4) Reallocate 2016.SAISIE on all months of 2016 (2016.01, ..., 2016.12: same amount copied on all months)

Results I get are, as you can see lines are doubled ...

I suspect the problem to be with the CURRENCY dimension (here DEVISE_RPT).

I have added a third member to the dimension and now amounts are sometimes tripled. But I may be completly wrong...

The only thing I'm sure off is that was working without the note.

The thing is we need that note as without it scripts were failing and reporting also...!

01102521-0010016572	ZZOSSTEST	PMV_REF	SITE_EXPEDITION_NA	2016.SAISIE	00288	CODE_USAGE_NA	CALCUL	BUDGETV0	LC	111,0000000
01102521-0010016572	ZZOSSTEST	PMV_REF	SITE_EXPEDITION_NA	2016.SAISIE	00288	CODE_USAGE_NA	CALCUL	BUDGETV0	LC	111,0000000
01102521-0010016572	ZZOSSTEST	CA_REF	SITE_EXPEDITION_NA	2016.SAISIE	00288	CODE_USAGE_NA	CALCUL	BUDGETV0	LC	27,7500000
01102521-0010016572	ZZOSSTEST	QTE_REF	SITE_EXPEDITION_NA	2016.SAISIE	00288	CODE_USAGE_NA	CALCUL	BUDGETV0	LC	0,2500000
01102521-0010016572	ZZOSSTEST	QTE_REF	SITE_EXPEDITION_NA	2016.SAISIE	00288	CODE_USAGE_NA	CALCUL	BUDGETV0	LC	0,2500000

01102521-0010016572	ZZOSSTEST	VVQ03	E_PM92	2016.01	00288	CODE_USAGE_NA	IMPORT_APS	BUDGETV0	LC	3,1736454
01102521-0010016572	ZZOSSTEST	QTE_REF	SITE_EXPEDITION_NA	2016.01	00288	CODE_USAGE_NA	CALCUL	BUDGETV0	LC	0,2500000
01102521-0010016572	ZZOSSTEST	QTE_REF	SITE_EXPEDITION_NA	2016.01	00288	CODE_USAGE_NA	CALCUL	BUDGETV0	LC	0,2500000
01102521-0010016572	ZZOSSTEST	PMV_REF	SITE_EXPEDITION_NA	2016.01	00288	CODE_USAGE_NA	CALCUL	BUDGETV0	LC	111,0000000
01102521-0010016572	ZZOSSTEST	PMV_REF	SITE_EXPEDITION_NA	2016.01	00288	CODE_USAGE_NA	CALCUL	BUDGETV0	LC	111,0000000
01102521-0010016572	ZZOSSTEST	CA_REF	SITE_EXPEDITION_NA	2016.01	00288	CODE_USAGE_NA	CALCUL	BUDGETV0	LC	27,7500000
01102521-0010016572	ZZOSSTEST	CA_REF	SITE_EXPEDITION_NA	2016.01	00288	CODE_USAGE_NA	CALCUL	BUDGETV0	LC	27,7500000

Script:

*SELECT(%YEAR%, "[YEAR]","CATEGORIE","[ID] = 'BUDGETV0' ")

*SELECT(%CLT%,"ID","HCLIENT"," [CALC] = 'N'")

*SELECT(%PRD%,"ID","HPRODUIT"," [CALC] = 'N'")

// On calcule le PMV_REF

// Les mois choisis par l'utilisateur sont agrégés et postés sur ANNEE.SAISIE (2016.SAISIE) et CA_REF/QTE_REF

*XDIM_MEMBERSET CATEGORIE=REEL

*XDIM_MEMBERSET DEVISE_RPT=LC

*XDIM_MEMBERSET SITE_EXPEDITION=BAS(TOTAL_SITE_EXPEDITION)

*XDIM_MEMBERSET CODE_USAGE=<ALL>

*XDIM_MEMBERSET HCLIENT=<ALL>

*XDIM_MEMBERSET HPRODUIT=<ALL>

*XDIM_MEMBERSET NATURE=VVQ03,BAS(CA_BRUT_AP_R_FF)

*XDIM_MEMBERSET ORGANISATION=%ORGANISATION_SET%

*XDIM_MEMBERSET SOURCE_DONNEES=IMPORT_BW

*XDIM_MEMBERSET TEMPS=%TEMPS_SET%

*RUNALLOCATION

*FACTOR=1

*DIM NATURE                              WHAT=BAS(CA_BRUT_AP_R_FF);                      WHERE=CA_REF;

*DIM SITE_EXPEDITION              WHAT=BAS(TOTAL_SITE_EXPEDITION);            WHERE=SITE_EXPEDITION_NA;

*DIM SOURCE_DONNEES           WHAT=IMPORT_BW;                                          WHERE=CALCUL;

*DIM_NONAGGR HCLIENT           WHAT=%CLT%;                                                 WHERE=<<<;

*DIM_NONAGGR HPRODUIT        WHAT=%PRD%;                                                 WHERE=<<<;

*DIM CATEGORIE                        WHAT=REEL;                                                      WHERE=BUDGETV0;

*DIM TEMPS                              WHAT=BAS(%TEMPS_SET%);                              WHERE=%YEAR%.SAISIE;

*ENDALLOCATION

*RUNALLOCATION

*FACTOR=1

*DIM NATURE                                  WHAT=VVQ03;                                                    WHERE=QTE_REF;

*DIM SITE_EXPEDITION                  WHAT=BAS(TOTAL_SITE_EXPEDITION);            WHERE=SITE_EXPEDITION_NA;

*DIM SOURCE_DONNEES                WHAT=IMPORT_BW;                                        WHERE=CALCUL;

*DIM_NONAGGR HCLIENT                WHAT=%CLT%;                                               WHERE=<<<;

*DIM_NONAGGR HPRODUIT            WHAT=%PRD%;                                               WHERE=<<<;

*DIM CATEGORIE                            WHAT=REEL;                                                    WHERE=BUDGETV0;

*DIM TEMPS                                     WHAT=BAS(%TEMPS_SET%);                         WHERE=%YEAR%.SAISIE;

*ENDALLOCATION

// On calcule un PMV REF sur ANNEE.SAISIE en divisant CA_REF par QTE_REF

*XDIM_MEMBERSET CATEGORIE=BUDGETV0

*XDIM_MEMBERSET DEVISE_RPT=LC

*XDIM_MEMBERSET SITE_EXPEDITION=SITE_EXPEDITION_NA

*XDIM_MEMBERSET CODE_USAGE=<ALL>

*XDIM_MEMBERSET HCLIENT=<ALL>

*XDIM_MEMBERSET HPRODUIT=<ALL>

*XDIM_MEMBERSET NATURE=CA_REF,QTE_REF

*XDIM_MEMBERSET ORGANISATION=%ORGANISATION_SET%

*XDIM_MEMBERSET SOURCE_DONNEES=CALCUL

*XDIM_MEMBERSET TEMPS=%YEAR%.SAISIE

*RUNALLOCATION

*FACTOR=1/USING

*DIM NATURE                                WHAT=CA_REF;                                 WHERE=PMV_REF;               USING=QTE_REF

*DIM SITE_EXPEDITION                WHAT=SITE_EXPEDITION_NA;          WHERE=<<<;                         USING=<<<

*DIM SOURCE_DONNEES             WHAT=CALCUL;                                WHERE=<<<;                         USING=<<<

*DIM_NONAGGR HCLIENT             WHAT=%CLT%;                                 WHERE=<<<;                        USING=<<<

*DIM_NONAGGR HPRODUIT          WHAT=%PRD%;                                WHERE=<<<;                        USING=<<<

*DIM CATEGORIE                          WHAT=BUDGETV0;                             WHERE=<<<;                       USING=<<<

*DIM TEMPS                                  WHAT=%YEAR%.SAISIE;                   WHERE=<<<;                        USING=<<<

*ENDALLOCATION

//-----------------------------------------------------------------------------

// On recopie les montants de ANNEE.SAISIE sur chaque mois du budget

*XDIM_MEMBERSET CATEGORIE=BUDGETV0

*XDIM_MEMBERSET DEVISE_RPT=LC

*XDIM_MEMBERSET SITE_EXPEDITION=SITE_EXPEDITION_NA

*XDIM_MEMBERSET CODE_USAGE=<ALL>

*XDIM_MEMBERSET HCLIENT=<ALL>

*XDIM_MEMBERSET HPRODUIT=<ALL>

*XDIM_MEMBERSET NATURE=CA_REF,QTE_REF,PMV_REF

*XDIM_MEMBERSET ORGANISATION=%ORGANISATION_SET%

*XDIM_MEMBERSET SOURCE_DONNEES=CALCUL

*XDIM_MEMBERSET TEMPS=%YEAR%.SAISIE

*RUNALLOCATION

*FACTOR=1

*DIM TEMPS       WHAT=%YEAR%.SAISIE;    WHERE=BAS(%YEAR%.TOTAL);

*ENDALLOCATION

What do you think?

Best Regards

Camille

[former_member186338]

Sorry, but the sample is too complex! It's better to narrow down the issue...

[former_member186338]

P.S. By the way I see second RUNALLOCATION without scope set before it! *ENDALLOCATION resets the scope and the scope for second RUNALLOCATION will be incorrect!

Vadim

[former_member186338]

The script has to be like:

//The required scope

*XDIM_MEMBERSET DEVISE_RPT=LC

*XDIM_MEMBERSET CODE_USAGE=<ALL>

*XDIM_MEMBERSET ORGANISATION=%ORGANISATION_SET%

*RUNALLOCATION

...

*ENDALLOCATION

*XDIM_MEMBERSET DEVISE_RPT=LC

*XDIM_MEMBERSET CODE_USAGE=<ALL>

*XDIM_MEMBERSET ORGANISATION=%ORGANISATION_SET%

*RUNALLOCATION

...

*ENDALLOCATION

...

[Former Member]

Hi Vadim,

Thanks. As all my dimensions are specified in the Runalloc, so it should be fine, shouldn't be? Anyway I have modified them.

And they were working fine before the note.

Our issue is linked to the volume of our dimensions HCLIENT/HPRODUIT. Allocations are correct when run on small node of client/produit. When you run it for bigger region, the results are all messed up. Doubled, or even trippled (since I added back a new currency for test. is it link we don't know).

Before the note allocations were always fine, but scripts were running out of memory when run on big regions. Now ther run but bad results...!

Thanks

Camille

[former_member186338]

Hi Camille,

I think it's better to start a new discussion and provide a current version of your script.

Vadim

[Former Member]

Hi Vadim,

I would need to help if you don't mind.

I'm trying to move all my runalloc as *when/endwhen

The user must select the node of client/produit for which he wants the calculation to be done.

*XDIM_MEMBERSET HCLIENT=BAS(%HCLIENT_SET%)

*XDIM_MEMBERSET HPRODUIT=BAS(%HPRODUIT_SET%)

However this statement works ok when a small region is selected, and fails for higher node.

However *XDIM_MEMBERSET HCLIENT=<ALL> runs fine

Question:

Is there another way to restrict my calculation to the node selected while leave the xdim to <ALL> ?

Thanks

Camille

[former_member186338]

Sorry, but new discussion, please!

And detailed explanation!

Vadim

[Former Member]

Hi Amit,

Try that:

*FACTOR=USING/USING

*DIM ZHQ_CNTRY_CUST   WHAT=NO_HQ;     WHERE= WHERE=BAS(ALL_HQ); USING=ZVALUATION

I'm using it and it works.

Regards

Camille

[former_member186338]

Hi Camille,

In the original post ZVALUATION is a dimension name and it can't be used in USING condition for ZHQ_CNTRY_CUST dimension...

Vadim