I have a requirement to have data analysed based on a range. The best example would be: give me the number of people that were alive on a given period (time dimension).
I know how to deal with the "Born date".
I know how to deal with the "Death date".
I'm not sure how to deal with the "alive anytime during a given period". Since the Time Dimension can deal with days, weeks, months, quarters, years, etc.
The data that would satisfy a given period would be:
Other examples would be: orders that had an un-shipped state during a period, call centre calls that were opened (not closed) during a period, etc.
The analysis would be used by managers to identify periods in a given year were more (or less) staff would be required.
I didn't find a way to achieve what I wanted using SSAS. Instead, I created created a table (or view) with the data I needed for this cube:
I have 1 row for each day that the person was alive.
I used the same idea for dealing with similar problems: - Call Center: calls per day: calls that were open (at any point in time) per day. - Shipping: in-transit per day: orders that were in wait state in the warehouse and waiting to be shipped per day.