Hi,

In our datawarehouse we have two tables representing the datetime dimension:
tblDate (DATEID, YEAR,WEEK,DATE) and
tblTime (TIMEID, HOUR)

What I would like to do is to create a Hierarchie based on a crossjoin of
both Tables (without using a view and a combined key)
YEAR-->WEEK-->DATE-->HOUR

Currently this can be archieved in the frontend (Pivottable) by simply
having a Date Hierarchie
~ Date (YEAR-->WEEK-->DATE) and dragging the Time Dimenstion (HOUR) next to
it.

Instead I would like to have a "real" Drilldown in one Hierarchie (SQL 2005)
Any help will be really appreciated.
Thanks,

Bjoern