I work at an organization where data is entered as time-in and time-out for the individuals that we help. So, when an individuals starts service, there is a start date with 12/31/9999 as the end date until an actual end date is entered. I'll call this the base data table.
We have a time dimension where it goes by calendar days with years and months, etc.
How would we combine the two so that it would work in a DMR? I have tried to join the base data with the time dimension where [start date] <= [calendar day] <= [end date], but it is CRAZY slow.
Do I need to break out the base data into transaction records (one record per day the individual was in service) or is there another way around this?
Thoughts?
You are actually introducing a theta-join within the model AND you are using actual dates instead of related integer keys. Both will have serious performance effects.
Using integer values will be an improvement, but that will demand excessive changes. A first step is to :
1. Limit size of the calender table
2. Check possible index use (though the between join will always trigger full table scans I suspect.
3. Replace the dummy 12/31/9999 with a much more realistic date that represents an open service call.
Other ideas would depend on whether data is static (daily updated ) or real-time. In the first case consider additional ETL / indexed view