Skip to Content
avatar image
Former Member

MDX LAG in Member Formula

Hi.

Using BPC NW 10.1 Standard on NW with HANA. A Periodic application used for planning.

ENABLE_HANA_MDX is not turned on.

In our Test BW environment. The following Member Formula:

([TIME].CURRENTMEMBER.LAG(1),[M_COSI])

M_COSI is an Account member.

Returns the following output:

I suspect that for formula in 2016.004 is looking back at 2016.Q1 for some reason. It does not behave like this in our Development environment.

Our time dimension looks pretty ordinary:

I am completely stuck. What am I missing?

Thank you for any help.

Mike

results.png (7.8 kB)
time-dimension.png (37.3 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • avatar image
    Former Member
    May 22, 2017 at 09:59 PM

    Note: 1907423 has helped us fix up the issue when ENABLE_HANA_MDX is turned on. What I noticed in the text was the following:


    "1. If the names of dimension members at the same level in time type dimension are in different orders when sorted in alphabetical order and sorted by year & base period, the reports may display wrong data due to wrong closing period calculation."

    Interestingly this infers that YEAR and BASE_PERIOD play a role in the order of time dimension members once HANA MDX is turned on!!

    So we recast Time Master-Data to ensure that the concatenation of YEAR and BASE_PERIOD ensured that Months were in date order first, then quarters in date order and then years - when sorted by YEAR and BASE_PERIOD

    So the BASE_PERIOD of MONTH is now 001,002,003 to 012 etc of Quarter 101,102,103,104 and Year 201, 202 etc etc This fixed up the problem with HANA turned on immediately.


    Documentation on the use of BASE_PERIOD is somewhat opaque - It appears to be used internally in Consolidation situations?? So I am not clear on wider impact if anyone is in a consol situation???

    Our issue with HANA MDX turned off remains. We will try to recreate this in EnvironmentShell as you suggest.

    Add comment
    10|10000 characters needed characters exceeded

    • That's why I asked you to play with ENVIRONMENTSHELL copy.

      The standard TIME dimension is configured with BASE_PERIOD like:

      MONTHS: 001...012

      QUARTERS: 003,006,009,012

      YEAR: 012

      In line with:

      If the names of dimension members at the same level in time type dimension are in different orders when sorted in alphabetical order and sorted by year & base period, the reports may display wrong data due to wrong closing period calculation.

  • May 21, 2017 at 08:21 AM

    You have strange member 2016.TO having empty PERIOD property AND HAVING PARENT - CALENDAR - unsupported!

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Vadim. This has made improvements, but not fixed the issue.

      I have documented where we have got to below.

      Regards

      Mike

  • avatar image
    Former Member
    May 21, 2017 at 10:47 PM

    Thank you Vadim - the CALENDAR member caused my issue.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 22, 2017 at 12:08 AM

    It is a lot better but i called out fixed too early. There is distinctly different behaviour now depending on wether ENABLE_HANA_MDX is on or off.

    When OFF - it looks like it treats LAG as LEAD and then has a problem with the first month in each Quarter.

    When ON it is almost perfect, but then has this strange issue around 2019.003, 2019.004, 2019.007. The amounts in periods 3 and 4 are overstated and the amount in period 7 is understated by exactly the amount overstated in periods 3 and 4 - eg it comes back to balance???

    The following is dummy data, but explains our symptoms.

    We have tried refreshing different slices of data and it looks like those periods in 2019 are always wrong and the pattern is the same.

    Checking the Time Dimension for that area:

    Nothing stands out to me here - The 'Calendar' total has been removed.

    In addition we have run UJHANA_REFRESH_VIR_CUBE on the source Model.

    Thanks again for any help

    Add comment
    10|10000 characters needed characters exceeded