title: Many to Many relationship between dimensions in a DW - The better modeling?
body: <p>I have this situation in front of me and I would like to ask for your opinion about modeling :</p>
  <ul>
  <li>An event can have several invited companies ( Accounts )</li>
  <li>A company ( Account) can of course attend several events</li>
  <li>An event has a start time and an end time and a total cost</li>
  </ul>
  <p>As you can see there's a Many to Many relationship ( Dim_Event ) and ( Dim_Accounts )
  My question is how to model this in a DW .</p>
  <p>What I have done:
  Inserting a &quot;Table Bridge&quot; called EVENT_GUEST_BRIDGE in which I put a key to Dim_Event and another key to Dim_Accounts .
  It's like a * - * table generated when defining a Many to Many relationship between two entities .</p>
  <p>Fact_EVENT the table is linked to the Dim_Event and is doubly linked to the dimension of time since an event has a start date and an end date.</p>
  <p><a href="https://i.stack.imgur.com/KeebZ.gif" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KeebZ.gif" alt="enter image description here" /></a></p>
  <p>To have for eg the countries from which companies participating in an Event I have to Write :</p>
  <pre><code>SELECT [FK_EVENT]
  ,[FK_DEBUT_EVENT]
  ,[FK_FIN_EVENT]
  ,[TotalPriceByEvent]
  ,C.Country
  FROM [DW_CRM].[dbo].[Fact_MARKETING_EVENT] A 
  inner join [DW_CRM].[dbo].[EVENT_GUEST_ACCOUNT_BRIDGE] B on (A.FK_EVENT = B.FK_Event_ID)
  inner join [DW_CRM].[dbo].[Dim_Accounts] C on (B.FK_Account_ID = C.Accounts_TechKey)
  </code></pre>
  <p>Would it be better if I have for example opt for a purely star design ?
  something like that :</p>
  <p><a href="https://i.stack.imgur.com/2vdgq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2vdgq.png" alt="enter image description here" /></a></p>
  <p>I wish I knew the + and - of each design!</p>
  <p>Thank you</p>