I need to display reports based on an SSAS cube which requires both date and time as input parameters.
I'd like to know what is the best practice while designing dimensions in such a scenario. Do i need to design a separate dimension for date ( quarter, year, month, week, day) and time ( hour, minute, second) ?
Or will having just the date dimension with the date key pointing to the full datetime in the fact table suffice?
I guess there is no correct answer to this question, but in my opinion you should design two dimensions - one for the dates and one for the seconds (with related attributes, of course). If you put it all in one dimension, your time dimension would consist of 60 x 60 x 24 x 365 = 31,536,000 members for each year (!!!), whereas if you make two separate dimensions, you have a date dimension with 365 members for each year and a time dimension with 86,400 members (constant). This will be much more performant and simple to work with in most cases.
No comments:
Post a Comment