· Jul 27, 2016 1m read

Tips & Tricks. Be aware of Month->Week hierarchy in DeepSee

Hi, Community!

You know, that when we build hierarchies in DeepSee dimension all the members of lower level should be the part of one member of the higher level.

If not you'll get some empty results in MDX queries with this hierarchy.

With Time dimensions the obvious valid hierarchy is Year->Month->Day, cause every Month consists of one Year and every day consists of one month. 

But be aware of using Year->Month->Week->Day hierarchy. In this case, there are some weeks in the year (most of them), which consist of two months. As a result, you can get unpredicatable empty results in your MDX queries with such a dimension.

Discussion (3)1
Log in or sign up to continue

Year->Month->Day is actually not a valid DeepSee hierarchy. Consider this: Which year does "January" belong to? Which month does the 17th day belong to? These questions have no answer.

But Year->MonthYear->DayMonthYear *is* a valid DeepSee hierarchy. In this hierarchy, the second level has members such as 2016-January, which does belong to a specific year.

I find it useful to bear in mind two points:
1) A member is a set of records.
2) DeepSee hierarchies are parent-child hierarchies. That is, all records that belong to a given member also belong to the parent of that member.