Day Weighted YTD Custom Measure performance poor in BPC with HANA_MDX enabled

[Former Member]

BPC Gurus,

Looking for some help here to utilize Custom Measures in BPC to provide easier reporting for users at an ad hoc level.  I have tried this approach at my previous job and it failed to performance.  That system was EPM 10 on BW/DB2.

The system I am currently on is EPM 10 on BW/HANA.  The HANA DB I am using in DB 102.

When enabled the smaller custom measure logic for day weighted QTD flies .. ~ 7 seconds.  However a similar day weighted YTD calc that is more involved takes roughly 7 minutes.  ** This is being done in our Sandbox so timings have be taken lightly.

Interesting Note:  Running a report a the highest note for Entity and Account with HANA MDX on for Calc measures for QTD and YTD was 7 min.  I take off the SPRO Model Parameter HANA_MDX and the same report comes back in 42 second.

The Day Weighted YTD logic takes all periods into account and account type in each run.  I am not sure how to make this more efficient.  A consultant had initially created this for us and since this was all MDX was hoping the HANA engine run through this easier.

I know several of my peers have run into this hurdle and have not had success to this point.

Here is the code we utilized.  We are reaching to our time dimension for days in period.  Any help would be appreciated or if you can benefit from it.

Day Weighted YTD.txt.zip

[former_member186338 · Accepted solution]

Hi Vineet,

The formula is very complex (using something like SUMPRODUCT in Excel). May be you can use some precalculated values like MonthValue*NumberOfDaysInMonth in some special AUDITID?

Vadim

[Former Member · Accepted solution]

Vadim,

Thanks.  We currently do not have HANA_MDX turned on so we utilize local formulas and EPM formulas (hidden) to retrieve the # of days etc.  Solution works however I would like the HANA_MDX solution to work for all of course.  For the other custom measure for Day Weighted QTD its extremely fast although the MDX syntax is much less.

Thanks for the suggestion.

Vineet

[Former Member · Accepted solution]

Hi Vineet,

Have a look at this SAP note: 1958603

Andy

[Former Member · Accepted solution]

I did look at this note however I am still encountering other errors that SAP has taken back for evaluation with the HANA Team.  However in the scenario of syntax they provided a note if you did not want to change syntax.  [LevelXX] to [Level XX]. 

[Former Member · Accepted solution]

SAP Note 2164827 - Support the keyword [LEVEL00] in BPC member formula with HANA MDX enabled.

Also they showed that by limiting the spaces in large queries will work as a workaround.  They are working on that fix now.