OBIEE: Dimension (Hierarchy) - SOLVED

Of course 12 minutes after I finished writing this up I figured it out...or at least think I figured it out.

I began to think of this in terms of SQL. How would I do this if I had to write this all out by hand.

I started from the top most Level, Year:

SELECT DISTINCT
  calendar_year, 
  calendar_year_id
FROM times
GROUP BY 
  calendar_year, 
  calendar_year_id
ORDER BY calendar_year DESC;

That returned the results I expected. Let's add quarter:

SELECT DISTINCT
  calendar_year, 
  calendar_year_id,
  calendar_quarter_id,
  calendar_quarter_desc
FROM times
GROUP BY 
  calendar_year, 
  calendar_year_id,
  calendar_quarter_id,
  calendar_quarter_desc
ORDER BY 
  calendar_year DESC, 
  calendar_quarter_desc DESC;

OK, good. How about month?

SELECT DISTINCT
  calendar_year, 
  calendar_year_id,
  calendar_quarter_id,
  calendar_quarter_desc,
  calendar_month_id,
  calendar_month_desc
FROM times
GROUP BY 
  calendar_year, 
  calendar_year_id,
  calendar_quarter_id,
  calendar_quarter_desc,
  calendar_month_id,
  calendar_month_desc
ORDER BY 
  calendar_year DESC, 
  calendar_quarter_desc DESC, 
  calendar_month_desc DESC;

That little exercise got me thinking...as you drill down, predicates are applied. After this first one, you would have:

SELECT DISTINCT
  calendar_year, 
  calendar_year_id,
  calendar_quarter_id,
  calendar_quarter_desc
FROM times
WHERE calendar_year_id = 1111
GROUP BY 
  calendar_year, 
  calendar_year_id,
  calendar_quarter_id,
  calendar_quarter_desc
ORDER BY 
  calendar_year DESC, 
  calendar_quarter_desc DESC;

So on and so forth as you drill down. This makes perfect sense. You don't need to define a level (Quarter for example) YYYYYQQ, because it already knows the year. Likewise, the further down you go, the more predicates are added. That will allow you, at the Day Level, to just display the Day of the Week or the Day Number of the Week...or some such nonsense.

I confirmed this by reviewing the physical SQL being submitted to the database.

Funny how stepping back for a second and taking the time to write down the problem enables you to see the problem more clearly.

I should do this more often. :)