Let's say that in my database model I have three fact tables. These fact tables have same dimension tables (so called conformed dimensions). I know that I shouldn't connect directly fact tables (since direct connection can cause double-counting of some facts), but only through the dimension tables. What I am interested in is can I connect every fact with every dimension table without problems? I looked for an answer a lot and the opinions are divided. Some say there is no problem, the others say that because of this fact tables can associate with each other and circular references can occur; and that in these cases so called link table should be used. Is this link table really necessary or can this work without it?
I implemented the model (in MS SQL), and I'm sharing here my experience in case anyone is interested in this in future.
In the end I created five fact tables (model turned out to be more complex), they are all connected to all existing dimension tables (six of them) directly. I didn't use the link table.
This model is in usage for almost five months now and so far no problems appeared.