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?

Thanks,
Kiran

Though a round about way, I can define the elements manually. It is also a very long drawn process. A solution in ETL will help.

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.

Thanks,
Kiran