Script logic needs to be fixed to work on data records with 0 value

[Former Member]

Hi,

The following script calculates the CTA based on the difference of the USD and the GC values.

USD values are the currency translated values(LC ->USD) in BPC.

GC values are coming from ECC into the "GC" groups dimension member.(We are uploading both LC, GC values from ECC into BPC)

The script works well when there is data in the LC and the GC members.

But when there is no data (amount 0.0)in the GC column in the upload file, those records are ignored by the system (in the WHEN stmnt) while calculating the difference, as a result of which the CTA amount is not coming correct.

The data can have either GC or LC blank, so even by swapping the GC with USD in the code did not help.

Can you suggest how to have the system filter the 0 values records too and calculate the CTA diff on them as well(USD - 0= USD value)?

I tried using WHEN_REF_DATA=MASTER_DATA,But the pacakge kept running for 12hrs before getting cancelled. (went into some sort of loop I guess)

*XDIM_MEMBERSET KT_GROUPS=GC

*XDIM_ADDMEMBERSET KT_GROUPS=USD

*XDIM_MEMBERSET KT_A_DATASRC=ECC_DATA

*XDIM_ADDMEMBERSET KT_A_DATASRC=CURR_TRANS

*XDIM_MAXMEMBERS KT_A_ACCOUNT=50

*XDIM_MEMBERSET KT_TRANSTYPE=F_TOT

*XDIM_ADDMEMBERSET KT_TRANSTYPE=F_CTA_C, F_100, F_120,F_CTA_H,F_CTA_C_PL

*WHEN KT_GROUPS

*IS "GC"

*WHEN KT_A_ACCOUNT.RATETYPE

*IS "AVG"

*WHEN KT_TRANSTYPE

*IS "F_120"

REC(EXPRESSION=(([KT_GROUPS].[USD])-%VALUE%)

-1),KT_A_ACCOUNT=312000,KT_TRANSTYPE=F_CTA_C,KT_GROUPS=GC,KT_A_DATASRC=CURR_TRANS)

*REC(EXPRESSION=(([KT_GROUPS].[USD])-%VALUE%),KT_TRANSTYPE=F_CTA_C,KT_GROUPS=GC,KT_A_DATASRC=CURR_TRANS)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*COMMIT

-

---

Need your help in fixing this script to make it more generic (to work even with 0.0 data records)

Thanks

Apeksha

[former_member200327 · Accepted solution]

Hi Apeksha,

I think WHEN_REF_DATA=MASTER_DATA is your best bet. Now the question is: why did it run for 12 hrs? Can you tell how many Dimensions in addition to shown 4 you have in that Application? How many members those remaining Dimensions have? Do you need all of them? How many records do you have in that Application?

You can try using MAXMEMBERS on your biggest Dimension.

Regards,

Gersh

[Former Member · Accepted solution]

Hi Gersh,

I'm not sure why it ran for 12hrs.

There are 10 dimensions in the consolidation application

1. ACCOUNT - This is the biggest one, has arnd 1900 members. Which is why I've already used MAXMEMBERS on it.

2. CONSUNIT - has arnd 70 members

3. PROFITCENTRE - Has arnd 580 members

4.FUNCTIONAL AREA - Has arnd 20 members

5. TRANSTYPE - has arnd 50 members (std ECC transtypes and few BPC related ones)

6. DATASRC - Has arnd 25 members

7. GROUPS (R-type) - Has all the reporting currencies, LC,GC,USD and consolidation grps as its members.

8. CATEGORY - Has 4 members

9. TIME

10. INTCO

My data records will have value for all these dimensions.

The script logic needs to filter data records first, based on the GROUPS = GC or USD; then as per RATETYPE property of ACCT (to get P&L accounts), and lastly based on the transactiontype = F_120

do you suggest any other way of writing this script(the WHEN, ENDWHEN construct)? or

how can I use the WHEN_REF_DATA=MASTER_DATA stmnt in the script to get the expected results?

Please suggest.

Thanks

Apeksha

[former_member200327 · Accepted solution]

Hi Apeksha,

I'd assume that Dimensions you listed here contain those KT_... Dimensions listed earlier. And sorry, I missed your MAXMEMBERS statement.

In your script, do you need to process all Years? Problem is that when you run that script with WHEN_REF_DATA = MASTER_DATA it creates Cartesian product of all dimensions that are not listed in MEMBERSET statements. In your case, just first 4 Dimensions will produce 1.5 BILLION records. Do you really need all those?

I'd suggest restricting most of the Dimensions to manageable number of members and check if this script produces desired results for those records. Then gradually increase number of records in some Dimensions until you reach needed data set. I doubt that you have even close to 1.5 billion records in your Application, so you have to think which members can which can not be in those combinations.

After you decrease that number to at least close to 1 million records we can start working on performance. Until then I don't think it's possible to process it in BPC or any other planning tool.

Regards,

Gersh