How can I limit a dimension to obtain specific descendants of of a hierarchy?
I'd like to obtain all days for Year X.
I'd like to obtain all products for a specific Category or Subcategory.
Another example could be.
If you have
How can I determine all previous days (in that month) compared to the current day
Use Limit to modify dimension status as required.
It exists as LIMIT command and also as LIMIT function "LIMIT()".
Read Oracle OLAP DML Reference 11g Release 2 document for details/examples.
Find it via google or via oracle olap home page.
If you want the leaf-level members, then use BOTTOMDESCENDANTS function.
There are many ways of doing this. Lets say that your dimension name is TIME and its hierarchy name is TIMEHIER. Here are some examples:
(1). You can do it multiple commands: LIMIT TIME_HIERLIST to 'TIMEHIER'; LIMIT TIME TO 'YR2010'; LIMIT TIME TO BOTTOMDESCENDANTS';
(2). If you have one hierarchy: LIMIT TIME TO (limit(limit(time to 'YR2010') to bottomdescendants));
(3). If you have multiple hierarchies, then you can qualify it with hierarchy name: limit time to (limit(limit(time to 'YR2010') to bottomdescendants using relation TIME_PARENTREL qualify TIME_HIERLIST 'TIMEHIER'))