Drill down in cubes

I am new to using Microsoft SSAS. I have a Date dimension with hierarchy - Year -Month-Day ; and Time Dimension with
hierarchy - Hour Minute-Sec . Our reports requirement is that the user should be able to drill down from year - sec . Is this possible to define a hierarchy here containing attributes from both date and time dimensions?

Its not possible to make a hierarchy in SSAS that spans 2 dimensions I'm afraid.

It is possible to make a dimension from multiple tables, if you wish, although that isn't best practice.

While it would work for you to make the one big hierarchy you describe, the approach I would advise is a dimension called Date (containing Year->Quarter->Month->Day) and then a seperate dimension called Time or Time of Day. This is a standard Kimabll practice as far as I'm aware, discussed in chapter 2 (pg 41) of the Data Warehouse Toolkit.

I agree with jryans approach.

Most reporting tools will let you nest hierarchies from multiple dimensions within each other to achieve the business requirement you described. However performance varies greatly, depending on the MDX generated.