I have a composite model with two fact tables, Spend data and Contract data. Both have common Supplier Names.
Spend data shows all orders placed.
Contract data shows all contracts held (past & present).
The objective is to show the Amount (Spend data) by Contract Status (Contract data) based on the Subcategory (joining table).
The complexity is that the categorisation across these two databases are slightly different, so the joining table / shared dimension maps these categories together.
I have a sample file here and listed the expected result. Excluded from the sample are other elements of the composite model such as a Date table connecting both the Order date (Spend) and Expiry date (Contracts).
Is this the best model structure, or should this be a completely differently structure given the common Supplier Name also coming into play when filter by Supplier?
My only thought is that the common Supplier Names could be joined with a Supplier table, or have an inactive bidirectional many to many relationship between Spend and Contracts for the Supplier Names.
