In SSAS it is possible to create many-to-many relationships by creating an intermediate fact table. Problem is what to do if the intermediate fact table is also a slowly changing dimension?
I have a regular fact table called FactSales which has a foreign key to Clients called ClientID. DimClients is a slowly changing dimension.
My company's employees can have many to many relationships with clients as they can be involved in various roles with the client.
So we have a DimEmployee with employees (also SCD) and a link table between clients called RelClientsEmployees. This of course is a perfect candidate to be the required intermediate fact table allowing me to link Employees to clients to FactSales.
Problem is that RelClientsEmployees is also slowly changing as over time roles to the same clients can switch employees.
As the intermediate fact table is not a dimension I can't indicate through the attribute type properties the SCD fields (SCDStatus, SCDEndDate etc.)
Without this however the results of the cube are incorrect, how do I solve this?
I have attached an image with the table layout.
Would be grateful for any help?