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

Article

Evgeny Shvarov · Jul 27, 2016 1m read

#InterSystems IRIS BI (DeepSee) #Tips & Tricks

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

Peter Steiwer · Jul 28, 2016

Great tip Evgeny!

Because of this reason, I like to create a new hierarchy to contain my week level. This will allow me to keep a single time dimension that uses my date property as the source, but separating the hierarchies will allow my Year->Month->Day structure to be preserved.

2 0

Lexi Hayden · Jul 28, 2016

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.

4 0

Evgeny Shvarov · Jul 28, 2016

Lexi, you are right. I thought about Year->Month->Day hierarchy with Year->MonthYear-> DayMonthYear time functions for the levels of course.

And the "wrong" hierarchy, in this case, would be Year->MonthNumber-DayNumber because if you have more than one year in the facts you'll get several years for one January.

0 0