Forum Discussion
Modeling with different granularities
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.
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.
