Depreciation Calculation Data action is taking longer time than expectation.

[Arpit_Shah1]

Hello,

Depreciation Calculation Data action is taking longer time than expectation. Even if we are reducing the timeframe, reducing the scoping for which it is running still it is taking longer time than expectation.

This is Code which is taking longer time

MEMBERSET [d/GN_SS_DATE] = [d/GN_SS_VERSION].[p/Planning_From_Period] TO [d/GN_SS_VERSION].[p/Planning_To_Period]//-----10 Years of Time Frame
MEMBERSET [d/Measures] = ("AMOUNT", "DEPRECIATION_PER_MONTH")
MEMBERSET [d/GN_SS_DATASOURCE] = ("DS102", "DS103")
MEMBERSET [d/GN_SS_ASSET_CLASSIFIED] = "AC002"//For New Asset
MEMBERSET [d/GN_SS_INPUT_CURRENCY] = %Select_Currency%
MEMBERSET [d/GN_SS_ACCOUNTGROUP] = ("S000035", "MNG180")
MEMBERSET [d/GN_S4_COCODE] = BASEMEMBER([d/GN_S4_COCODE].[h/Hierarchy], "All_Company")
MEMBERSET [d/GN_S4_ASSET] = "#"
MEMBERSET [d/GN_S4_ASSETCLASS] = "#"
MEMBERSET [d/GN_S4_COSTCENTER] = %Division%
MEMBERSET [d/GN_S4_PROFITCENTER] = BASEMEMBER([d/GN_S4_PROFITCENTER].[h/PC_HIERARCHY], %Profit_Center%)
MEMBERSET [d/GN_SS_ASSET_SUB_CLASS] = %Asset_SubClass%

INTEGER @ASSET_LIFE
INTEGER @start_COUNT
INTEGER @count
DELETE([d/Measures] = "DEPRECIATION_PER_MONTH", [d/GN_SS_ACCOUNTGROUP] = "MNG180", [d/GN_SS_DATASOURCE] = "DS103", [d/GN_SS_ASSET_CLASSIFIED] = "AC002")

FOREACH.BOOKED [d/GN_SS_ASSET_SUB_CLASS]
@ASSET_LIFE = ATTRIBUTE([d/GN_SS_ASSET_SUB_CLASS].[p/Useful_Life_In_Month])
@count = 120 //-----for 10 Years
FOREACH.BOOKED [d/GN_SS_DATE]
FOR @start_COUNT = 1 TO @count
DATA([d/Measures] = "DEPRECIATION_PER_MONTH", [d/GN_SS_ACCOUNTGROUP] = "MNG180", [d/GN_SS_DATASOURCE] = "DS103", [d/GN_SS_DATE] = NEXT(@START_COUNT)) = RESULTLOOKUP([d/Measures] = "AMOUNT", [d/GN_SS_ACCOUNTGROUP] = "S000035", [d/GN_SS_DATASOURCE] = "DS102") / @ASSET_LIFE
ENDFOR
ENDFOR
ENDFOR

Is there any room for performance improvement for the above code.

Thanks and Regards,

Arpit

[AbdullahShaffi]

You can try this approach:
1. Input template to plan Asset Acquisition (This will also contain the period to start depreciation)

2. Input template to maintain useful life by Asset Class (You can avoid getting it as a parameter in Data action)

You can decide whether you want to calculate depreciation amount till end of the year or till asset useful life period.
This can be set in Memberset: Date dimension. Ideally for budgeting, yearly depreciation should be sufficient.

Sample code:

MEMBERSET [d/Date] = [d/Version].[p/StartPeriod] TO [d/Version].[p/EndPeriod]

VARIABLEMEMBER #YTD_ASSET_COST OF [d/Measures]

// Calculate YTD Asset value for the year in a temporary variable

FOREACH [d/Date]
	IF [d/Date].[p/FISCAL_PERIODDESC] = "P01" THEN
		DATA([d/Measures] = #YTD_ASSET_COST) = RESULTLOOKUP([d/Measures] = "AMOUNTINLC")
	ENDIF
	DATA([d/Measures] = #YTD_ASSET_COST) = RESULTLOOKUP([d/Measures] = #YTD_ASSET_COST, [d/Date] = PREVIOUS()) + RESULTLOOKUP([d/Measures] = "AMOUNTINLC")
ENDFOR

// Calculate Depreciation based on Useful life in P&L Account

DATA([d/Measures] = "AMOUNTINLC", [d/GLACCOUNT] = [d/GLACCOUNT].[p/DeprPLAcct]) =

RESULTLOOKUP([d/Measures] = #YTD_ASSET_COST) / RESULTLOOKUP([d/Measures] = "USEFULLIFE") / 12