I am trying to drill down to Year -> Quarter -> Month -> Week -> Day -> Hour -> Minute with two dims.
dim_Date: (Year -> Quarter -> Month -> Week -> Day)
dim_TimeofDate: (Hour -> Minute)
The fact has one column for the key from dim_date another key from dim_TimeofDate.
How to combine these two dim into one hierarchy to drill down?
Thanks.
This is a classic case of a tradeoff between performance and usability. The short answer is that no browser supports drilling down two dimensions as though they are one dimension. If you move to the time portion into the Date dimension you will end up with 1440 members per day (assuming you need all minutes per day) which will impact the cube performance but will the user drill down across the hierarchy. If you keep them seperate, performance won't be impacted but usability will.
I'd suggest you keep the way things are are let users know that they have to cross-join the two dimensions on the report to get the "drilldown" effect. Another option would be to implement a drill-down action which you enable on the Day level to show the fact data.
thank you so much for your reply.
i really running into the wall. :)
I am check your drill-down action solution on SOS in the SSAS book.
Thank you so much for the great book. The more I read it, the more often I smile; knowing it fixes so many of my problems.
Thanks for the kind words! I think drillthrough could be a good solution. Actually, keeping Date and Time dimensions seperate could be good for usability too as the users can put them on different axes, e.g. Date on columns and Time on rows.