From Date and To Date ? 2

[Recce]

Good day,

I have a tabel with a few fields. One field is a "From Date" and the next field is a "To Date". There are other fields as well. Now, I have a user who needs to see or measure the data in the above table with a "From Date" and "To Date" functionality. I am not sure how to go about doing this because, if you filter the one field i.e. "From Date" then obviously there will be no data in the "To Date" field, when you work with a cube. So, does anyone have any suggestions of how I can do this ?

"We know nothing but, that what we know is not the truth..." - Me

 

[johnherman]

Dates in OLAP and DW are a funny thing. Nulls could have many meanings. For instance, you have a pricing file with price effective date and price expiration date. If the price is not scheduled to expire, what belongs in the price expiration date? In this case, null implies a date distant in the future. What about the effective date? If the effective date is not known, it would make sense to default it to some very early date.

Additionally, if you have another date, say, regarding price appeal date and that date has not happened and is not scheduled, what do you put there? In this case, it means Not Applicable.

Or you could have Date of OB/Gyn exam for male, or prostate exam for female - this is a date which can NEVER happen.

Therefore, I suggest you assign date surrogate keys for your dates representing (at least) these three scenarios. You may have additional scenarios in your business.

In summary:
Date which has not happened - implied distant future date
Date which has not happened - implied very early date.
Date which has not happened - event has not occurred yet.
Date which has not happened - N/A - can never occur.

It could be argued that a fully normalized database design might prevent these situations from occurring, but in DW/OLAP, we are in a Dimensional Model, for performance and other reasons.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[MDXer]

yet another great explination. Have a Star!

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Recce]

Hi johnherman

Thanks for this. Yes, what you say makes sense. Thanks for taking the time to aswer my Q

"We know nothing but, that what we know is not the truth..." - Me