We have a DB2 OLTP environment which, we are planning to remodel to an OLAP in MS SQL Server 2008.
In this, we have a scenario.
Our fact table has Reference_ID, Contact_ID and Assignment_ID.
Now, the relation is such that : Reference_ID to Contact_ID is 1--> N
Reference_ID to Assignment_ID is 1--> M
and there is no direct relation between Contact_ID and Assignment_ID. So, we are getting a Cartesian result of M * N in our fact table.
How to solve this scenario and still keep the structure as a Star?