Dimension Property Comparison

[Former Member]

Hello Experts

I am new to BPC and I am trying to do a comparison using dimension property, but this is performance intensive. Was wondering if there is a work around.

*XDIM_MEMBERSET COMPANY = 207

*XDIM_MEMBERSET DATASRC = %DATASRC_SET%

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET ACCOUNTF = %ACCOUNTF_SET%

*SELECT(%DIM_CENTER%,"[ID]",CENTER,"[PROFIT_COST]='C' AND [REL_PC]<>''")

*SELECT(%DIM_CENTER_PC%,"[REL_PC]",CENTER,"[PROFIT_COST]='C' AND [REL_PC]<>''")

*XDIM_MEMBERSET CENTER = %DIM_CENTER%

*WHEN DATASRC

*IS "CPUNIT_DRV"

*FOR %LC2% = %DIM_CENTER_PC%

*WHEN CENTER.REL_PC

*IS %LC2%

*REC(EXPRESSION=( %VALUE% * ([ACCOUNTF].[UNITS],[CENTER].[%LC2%],[DATASRC].[TOTDATASRC])),DATASRC=TEST)

*ENDWHEN

*NEXT

*ENDWHEN

*COMMIT

Thanks

Nikil

[Former Member]

Hi Nikil,

The performance depends on how many members are passing the loop there. Another thing is if you have complex dimension formula, it usually cause a big difference in performance compare to simple +-x/ formulas only. There are lot of factors that may be contributing to poor performance so the first step is to identify first if the logic below or other factors is causing the issue. Try to run the logic below without running default formulas, running it on single member, many members, running it after dimension formulas(if there's any) are removed so you can pinpoint the real cause of intensive performance.

Hope this helps,

Marvin

[Former Member]

From my analysis the performance issue is because of the FOR loop. For every record processed the FOR loop executes 500 times.

Is there a way to call dimension property in an expression. If I could this then it could solve my problem.

I tried a few things - but none of them seem to work.

Example of what I tried

*WHEN DATASRC

*IS "CPUNIT_DRV"

*REC(EXPRESSION=( %VALUE% * ([ACCOUNTF].[UNITS],[CENTER].PROPERTIES("REL_PC"),[DATASRC].[TOTDATASRC])),DATASRC=TEST)

*ENDWHEN

[former_member200327]

Hi Nikil,

You already figured out that property is causing you performance issues.

I can see 2 things that can affect your script performance:

1. If it's true that %DIM_CENTER_PC% has 500 members, because than BPC will run 'select' 500 times.

2. Using a tuple expression like (ACCOUNTF.UNITS,CENTER.%LC2%,DATASRC.TOTDATASRC) in REC because for each record BPC will have to find total of all records with those conditions.

This script looks a little strange to me. Could you please share what you want to accomplish (not how you want to do it) and probably we'll be able to streamline the script or suggest model changes that would mane your script more efficient.

Regards,

Gersh

[Former Member]

Center dimension has members that belong to both cost and profit centers.

There is a property on center dimension called Profit_Cost which helps differentiate between cost and profit center. If Profit_Cost property value is 'C' it is cost center, if the value is 'P', its the profit center.

Most cost centers have a related profit center.There is a more property on center dimension called REL_PC. This property holds the related profit center id.

What I trying to do is iterate through the cost centers, multiple the value in DataSrc "CPUNIT_DRV" with the value in the related profit center's datasrc "TOTDATASRC and AccountF is UNITS

E..g if my cost center ID is: C30115 and related profit center ID is: P_R30101, I am trying to do is:

[CENTER].[C30115],[DATASRC].[TEST] = ([CENTER].[C30115],[DATASRC].[CPUNIT_DRV]) * ([CENTER].[P_R30101],[DATASRC].[TOTDATASRC],[ACCOUNTF].[UNITS])

Since I do not have an easy way of using dimension property in an expression - for each cost center I am iterating through all the profit centers to figure out which one of the profit center is related to the cost center.

Thanks

Nikil