PALO.ESIBLING() doesn't work

    This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

    • PALO.ESIBLING() doesn't work

      Hi,

      want to fill an element "previous period" (IST_VP).
      Dimension time has several levels (year/quarter/month), so previous period for 2012 is supposed to be 2011, for Q1 2012 it is Q4 2011, for Nov 2012 it is Oct 2012 an so on.

      Have created a rule, that works fine with EPREV-function:
      ['IST_VP'] = PALO.DATA("EF_MIS","ABS",!'KSEG',!'KUNDE',!'PRODUKT',!'PC',"IST",!'WERTART',PALO.EPREV("EF_MIS","ZEIT_W",!'ZEIT_W'))
      But EPREV is not appropiate, because it doesn't stay on the same level.

      Therefore I repclaced EPREV by ESIBLING, wich causes an error.
      ['IST_VP'] =
      PALO.DATA("EF_MIS","ABS",!'KSEG',!'KUNDE',!'PRODUKT',!'PC',"IST",!'WERTART',PALO.ESIBLING("EF_MIS","ZEIT_W",!'ZEIT_W',-1))
      This works on ALEA.

      There was already another post concerning problems with ESIBLING, but without response.


      Any help or hint is appreciated.

      With regards
      Matze

      The post was edited 1 time, last by MatzeR ().

    • I'd personally sort the dimension to have continuous sequences of elements that are from the same timeline.
      e.g.
      Jan, Feb, Mar,...Dec, Q1,Q2,Q3,Q4,Year
      each element has it's unique position within the dimension, it can be changed in Modeler/Dimension editor. Select element and press icon with arrow Move Element Up/Down in the middle
      I'm not sure if there is macro functionality for this or if it is supported by ETL.
      Once you have timeline sorted this way you can use PALO.PREV or PALO.OFFSET or short version of it like

      ['IST_VP'] = ['IST', ZEIT_W:offset(-1)]
      you must also define rule for first element of the timeline
      ['IST_VP','ZEIT_W':'0'] = ...
      to stop the recursion when first element of the timeline is reached

      the short version using [ dimension:offset(relative_offset)] is supported in 4.0 - not sure about older versions
    • I did not yet understand what kind of sorting you mean.

      In !ZEIT_W weeks are N-Elements. There are three hierarchies.
      Any kind of sorting wouldn't be a problem, we build all dimension on SQL Server.


      #1
      Year
      --Quartal
      ----Month
      ------Weeka = N-Element

      #2
      Year
      --Week
      ----Weeka

      #3
      Year
      --MonthYTD
      ----Month (1..n, from #1)
      ------Weeka


      With regards
      Matze
    • If you look at the dimension in modeller/dimension editor - you can see elements in that order on the left side. The initial order is the same as the elements were created, but it can be changed later using the icon with arrow up or down. This order defines "position" of element in dimension and is used in PALO.OFFSET(relative to current), PALO.EPREV(current-1), PALO.ENEXT(current+1), PALO.EINDEX (returned), PALO.ENAME(absolute position), PALO.EFIRST(position=0) functions

      The post was edited 1 time, last by jjunek ().

    • Sorry, it took me while to recognize that in PALO there is as difference between flat and hierarchy-looking to dimension.

      I tried to affect the order in wich elements are created during ETL-Process:
      Within a field "Level" on my SQL-Source I made an order by in the relational exctract " ...order by [Level],[Parent Name]".

      In extract-"Preview Data" there was the intended order.
      Neither in dimension. The order there seemed still quite arbitrary.

      What else could I try to affect the order elements are created in?

      With regards
      Matze
    • Sorted would look like:
      Year
      ------Q1
      ------Q2
      ------Q3
      ------Q4


      -------Q1
      -----------Jan
      -----------Feb
      -----------Mar

      in flat view
      Jan
      Feb
      Mar
      ..
      ..
      ..
      Q1
      Q2
      Q3
      Q4
      Year

      i.e elements should be in an predictable order within level
      In the modeler(excel addin modeler for CE) you can change the position of members or in ETL load job like you mentioned - order by.
      ===========================
      In same level you have multiple hierarchy members then have to ensure that first one finishes and then other,

      How is your Time dimension defined , do you have seperate Period & Year dimension ?

      PALO.ESIBLING will simply give the relative position of sibling member specified by offset you are mentioning.
      It depends on element order defined within dimension.

      For first elements within list, like you can control additional settings with IF clause if Jan then Previous Year's Dec; example
      jedox.com/community/palo-forum…5544&highlight=#post15544


      Best regards,
      Noel.
    • Hello Noel,

      thank you for reply. I think I understud what is meant with the needed order.

      It is no option for us to modify dimensions in the modeller.
      Either we make it within SQL (and Designer-tool) / ETL or not at all.

      As I wrote before, I have tried to bring elements in this order during ETL-Process.
      This attempt failed, it doesn't work with "order by..." in relational extract.

      Might there be another approach?


      With regards
      Matze
    • Very interesting .. your Time dimension :)

      Sadly I think the only way to do previous logic would be with complex IF, ELSE conditions with some MID, CONCATINATION, STR & VALUE functions,
      This would be also better because it would be a solid logic than relative position by offset, prev or sibling .

      I see the last incomplete week is splitted between 2 years, do this what the business desires or in most cases I see they prefer to delay the year start till last week of previous year closes - This you might want to discuss with them.

      Your if would be based on hierarchy by concatinating first 2 characters of element you would get either G1, G2, G3, G4.
      by concatinating 7 th position you will know if its M or W and based on numbers you would play the previous logic.

      If you need more help - do let me know the rule requirement a bit more detail and I can help you with creating this calculation

      Best regards,
      Noel.
    • Hi,

      reason why I offered the solution based on position is performance, especially when rules on base cells. Evaluation of IF on millions of cells can take some time, your time.
      Anyway if you are not planning to use rules on base cells, the solution proposed by Noel will work.

      Other option would be to load dimension in two steps.
      First to build dimension hierarchy you want to use for PALO.SIBLING rule iteration.
      Second step would add elements and relations for secondary hierarchies.
      PALO.ESIBLING would then use the first parent from first hierarchy.

      Jiri
    • I agree with Jiri, you will need to sort your data by levels and for each of your multiple hierarchy and then combine them.
      If using jedox etl to do it - Multiple Tree View and one Tree Join.
      The Tree View filter statement can accept regex when using like operator - Suppose you want only G120XXMXX elements then you can use expression like G1....M

      Only caution should be I see in your hierarchy if 2010 has incomplete Week say 53 you will carry it to 2011 as 2011W53 as first week,either complete all your weeks at end of the year and start next year with delay or give it special name like 2011W053 which is better for sorting and give proper alias name . I don't know if there could be a case where 2011 indeed has 2011W53 as last week of 2011 year then the hierarchy will become invalid. In any case this will need to be handled.


      Best regards,
      Noel.
    • Thanks for your replies.

      I would strongly prefer the solution with loading in two steps.
      Unfortunately I did not yet understand how to seperate steps.

      I wished to have previous element on each level, so I don't know wich elements to exclude in the first step.

      With regards
      Matze
    • Ordering the dimension elements level by level and sort each level .
      Idea is not to collide same level of different hierarchy to avoid confusion on previous sibling.
      Thats why its preferred to do loading for single hierarchy at a time.
      But its totally up to you - Idea is that you can manage properly level orders.

      example suppose at same level if hierarchy G1 & G2 collide, if its sorted like below its fine but what if sorting will not handle properly.
      also your rule will need to handle start points of each level. i.e handle previous element of first element so that it should not go to other hierarchy.

      G12010Q3
      G12010Q4
      G12011Q1
      G22010Q1
      G22010Q2
      G22010Q3
      G22010Q4

      Hope its clear!

      Best regards,
      Noel.
    • I got it:

      1.Step
      Relational Extract: Select distinct Childelement, Parentelement='' from ... order by level, orderposition
      Load: Only Elements load, no cosolidation, no attributes load

      2. Step
      Relational Extrag: "Normal" Select
      Load: Update all

      By this way I get the right order in every parallel hierarchy.


      Thank you all for your advice. :thumbup:


      With regards
      Matze