Modeling with different granularities

[danrope]

I'm struggling to model some maintenance contract data. I have two tables from OLTP.

[Maintenance List] is a table containing estimated and redeemed time for each scheduled maintenance date. Each contract (job_id) can have numerous maintenance lists (list_id) on which we perform maintenance tasks at various points throughout the year (date_compl). Note the grain of this table is daily 

[Contract Rates] is a table containing contracts (job_id) and an historical account of revenue metrics. This data, however is stored at a yearly grain. The [renew_date] and [expiration_date] represent start and end dates for the period.

The most obvious relationship to establish here is on the contract (job_id) - however we also need to consider the DATES. The [Maintenance List].[compl_date] should fall BETWEEN [Contract Rates].[renew_date] AND [Contract Rates].[expiration_date]. But the tables being at daily and yearly granularities has me stumped on how to best approach this. Anyone with more experience able to give me some guidance as to how you would handle this?

[johnt75 · Accepted solution]

I would not create a relationship between these tables. Instead, I would create a new table with the unique values of job ID and company and link that to both tables in a one-to-many relationship.

Also create a date table, marked as a date table, and link that to the maintenance table.

Handling the values from contract rates would depend on the granularity you want to be able to report on. If you wanted to report at the month level then you could generate an entry for the 1st of each month between renewal and expiry date and divide the values by 12. You could then link the date table to this, and all your measure would be simple SUMs, AVERAGEs etc.

[danrope]

That makes perfect sense @johnt75 . I'm going to attempt to implement this before accepting as solution. Thanks for your help my man