Thanks Sam,
Yes it is a hierarchy that is true and yes the naming conventions are not good. The report has filters like : hierarchy_1 and Hierarchy_2. Depending on the top level you filter on (franchise). The levels in the hierarchy have different meanings. This is very confusing indeed and difficult to understand.
I am going to advise to change this in a more meaningful way so everybody can easily understand it. Just something that you haven’t gone into… It’s not necessary to repeat all of the levels for the tables that use the hierarchy right?
In my opinion you only need to connect the lowest level to the hierarchy item and all of the above levels will be automatically referred and can be used in rolled up totals en for drilling up-down purposes.
I would like to minimize the redundancy as must as possible which off course would be beneficial for the amount of memory used and will have a impact on the performance…
That is right isn’t Sam?
kind regards,
Sjaak Wooning