## Wednesday, March 31, 2010

### 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_idFROM timesGROUP BY   calendar_year,   calendar_year_idORDER 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_descFROM timesGROUP BY   calendar_year,   calendar_year_id,  calendar_quarter_id,  calendar_quarter_descORDER 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_descFROM timesGROUP BY   calendar_year,   calendar_year_id,  calendar_quarter_id,  calendar_quarter_desc,  calendar_month_id,  calendar_month_descORDER 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_descFROM timesWHERE calendar_year_id = 1111GROUP BY   calendar_year,   calendar_year_id,  calendar_quarter_id,  calendar_quarter_descORDER 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. :)