HANA MDX - [%TIME%].[LEVEL03]

[former_member187113]

Hi,

I wonder if anyone can help out with some MDX here.

I am trying to get a BPC installation to work on HANA with BW 7.50.

I am battling to ENABLE_HANA_MDX for our (YTD) Consolidation Model.

Note 1958603 has an Word Attachment which requests the following: https://launchpad.support.sap.com/#/notes/1958603

In UJA_MAINTAIN_MEASURE_FORMULA (SE38)

Replace all occurences of:

"[%TIME%].[LEVEL00]” with "[%TIME%].[%TIMEYEARLEVEL%]",

"[%TIME%].[LEVEL01]” with "[%TIME%].[%TIMEQUARTERLEVEL%]",

"[%TIME%].[LEVEL02]” with "[%TIME%].[%TIMEBASELEVEL%]".

Looking at my TIME dimension, I see that I have a number of occurrences of LEVEL03. (This is in the non-HANA version).

Question: What would I replace this with for the HANA version?

The Time Dimension Hierarchy looks something like this:

2015.TOTAL

     2015.H1

          2015.Q1

               2015.OCT

               2015.NOV

               2015.DEC

          2015.Q2

               2016.JAN

And so forth ...

Below is the custom Periodic measure - it was implemented by SAP Support to improve performance.

Kind Regards

Nick

----

'IIF( [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",

-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL03])),

IIF( [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",

IIF( ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="OCT" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="H1"),

([MEASURES].[/CPMB/SDATA],

CLOSINGPERIOD([%TIME%].[LEVEL03])),

IIF( ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q2" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q3" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q4" ),

([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL03]))-([MEASURES].[/CPMB/SDATA],

CLOSINGPERIOD([%TIME%].[LEVEL03]).LAG(3)),

IIF( [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="H2",

([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL03]))-([MEASURES].[/CPMB/SDATA],

CLOSINGPERIOD([%TIME%].[LEVEL03]).LAG(6)),([MEASURES].[/CPMB/SDATA],

CLOSINGPERIOD([%TIME%].[LEVEL03]))-([MEASURES].[/CPMB/SDATA],

CLOSINGPERIOD([%TIME%].[LEVEL03]).LAG(1))))),

IIF( [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",

IIF( ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="OCT" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="H1" ),

-([MEASURES].[/CPMB/SDATA],

CLOSINGPERIOD([%TIME%].[LEVEL03])),

IIF( ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q2" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q3" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q4" ),

-([MEASURES].[/CPMB/SDATA],

CLOSINGPERIOD([%TIME%].[LEVEL03]))+([MEASURES].[/CPMB/SDATA],

CLOSINGPERIOD([%TIME%].[LEVEL03]).LAG(3)),

IIF( [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="H2",

-([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL03]))+([MEASURES].[/CPMB/SDATA],

CLOSINGPERIOD([%TIME%].[LEVEL03]).LAG(6)),

-([MEASURES].[/CPMB/SDATA],

CLOSINGPERIOD([%TIME%].[LEVEL03]))+([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL03]).LAG(1))))),

([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL03])))))' SOLVE_ORDER=3

[former_member186338]

Hi Nick,

You have non standard time hierarchy with one extra level - half year. What is the issue?

Vadim

[former_member187113]

Hi Vadim,

Thanks for the response.

Correct, the Time hiercharchy is non-standard.

Actually, there are a lot of issues.

• We've migrated a BPC 10.0 NW (on MS SQL) to 10.1 on HANA.
• The main issue is that nothing works, actually, mostly due to MDX issues and the fact I cannot activate ENABLE_HANA_MDX for the Model. By this I mean:

1. Reports won't work and give MDX issues irrespective of whether they refer to YTD or Periodic. The Model is YTD.
2. I cannot copy the Model using the Web Admin GUI.
3. Simple Account Member Formulas won't process.
4. I am not convinced the existing custom Measure (that I posted) is necessary, nor do I know how to replace the LEVEL03 as per the note.
5. The list goes on ...

What I have done is create a simple Model from EnviromentShell with similar Master Data to the Consolidation Model and that seems to work fine. Well, at least I can use an Input Sheet and a Report. ENABLE_HANA_MDX is enabled.

My next step is to try a UJBR of the Consolidation Model and see if I can restore it and then start pulling it apart to see where the issues might be. (It's going to take a bit of time).

I hope that makes some sense.

Kind Regards

Nick

[former_member186338]

Hi Nick,

You have to experiment with some test model with new TIME dimension... There are no guidelines how to properly configure half year level. I remember some posts with LEVEL property like:

YYYY.TOTAL - YEAR

YYYY.H1 - QUARTER

YYYY.Q1 - MONTH

YYYY.OCT - WEEK

Also please enter all other properties correctly: BASE_PERIOD, MONTHNUM, PERIOD, TIMEID, YEAR.

Vadim

[former_member187113]

Hi Vadim,

Thanks for the pointers. I'll scratch around a bit more and revert.

Kind Regards

Nick