"Joining" snapshot to transaction by date range.

[dougcl]

Hi folks, we have a daily snapshot fact that we would like to join to a transaction fact. Each date in the snapshot fact will fit into at most one date range in the transaction fact. While this is easy to do in SQL, can the dimensional model in FM be set up to do this?

Thanks,
Doug

[dougcl]

Followup, I think this case is handled in the relationship editor.

Suppose the transaction fact table has three date columns:

1) date (joined to date_dim)
2) start_date (joined to role of date_dim)
3) end_date (joined to role of date_dim)

For the relationship in 1), instead of using the default fact.date = date_dim.date, use the ellipsis to create a custom relationship:
fact.start_date <= date_dim.date AND fact.end_date > date_dim.date

Now whenever a join is attempted to another fact table through the date dimension, a match will be found if the date from the other fact fits the range in the transaction fact.