Jedox Calendar Extract

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

    • Jedox Calendar Extract

      I am trying to build a calendar based dimension, where the first level is a month, next level is week and the third level is the day within the week. Sounds quite simple. So, at Level 1, I have Months with the pattern MMM. Level 2, I have Weeks with the pattern 'Wk'w, and level 2, I have Days with the pattern d.

      Now comes the problem - I realize the problem too, but, need advice to fix it - the days of the month (atleast from 1 to 28) repeat every month. Hence, they might end up being duplicate elements. Jedox resolves it by suffixing the dates with month and year. We can also manually avoid it by changing the pattern to yyyyMMdd or similar.

      When I do the data preview after that, I get a message:

      "Avoiding multiple consolidation of node Wk14 by renaming to Wk14 04.1. Please verify your generation pattern!"

      When I go to Wk14, I notice that it spans between 29-Mar-2015 and 04-Apr-2015. In order to (again) avoid the element with multiple hierarchies, Jedox changes the name of the week.

      But, for my reporting, I need the standard weekly convention. Could you suggest an alternate way to handle this?

    • Hi Kiran,

      do you intend to mix months and weeks?

      from my point of view this is humanly not possible as week can overlap on a single month, meaning sum of weeks != month

      solution 1 would be to make it in two hierarchies (one for month, and one for weeks), other one would be to pack the number of the week in an extra dimension


      Post hoc, non est propter hoc
    • Hi laloune,

      You are absolutely correct that weeks can create major hurdle as they don't fit within a month, and definitely not within a year. However, in Production Planning scenarios, the planners always want to plan in terms of weeks, instead of dates and months. Hence the requirement.

      Though not the best process, I managed a work around, that works pretty fine in my current scenario. I needed a planning sheet for the years 2015 to 2019. Here is what I did

      1. Calendar extract with level 1 as years (2015 -2019) and level 2 as days
      2. Field Transform to generate the week number of each day and month for each day - month gets generated based on week number (this helps to fit a week in a month)
      3. Tree FH to have a hierarchy of year, month, week and day.

      The issue here is that I end up with a dimension with too many elements, but, it definitely helps in my requirement.

      I have attached a sample xml for the etl (change the .txt to .xml) for your reference. Any suggestions to improve this method are greatly appreciated.

    • Hi,

      I tested the "Production Planning.txt" sample integrator project you posted, as I am in a need for a similar function.

      The test worked well besides the fact that for year 2019 the week 49 goes under November, which it should not (Week 49 begins on Monday, December 2, 2019)!

      I am trying to find the cause of this. Maybe someone knows the answer?

    • FYI

      Solved the issue of the original file by implementing an additional function under field transform.

      Previously the month was taken based on week only (which caused discrepancies), but now (together with week) also year is taken into account as base for determining the month of the week.
      • Capture1.PNG

        (49.82 kB, downloaded 129 times, last: )
      • Capture2.PNG

        (47.66 kB, downloaded 119 times, last: )
      • Capture3.PNG

        (47.52 kB, downloaded 124 times, last: )