Fact table design - One-to-many
<p>I come from a relational SQL Server database background, and am trying to make the transition to a multi-dimensional model in Analysis Services.</p>

<p>I'm struggling with how to approach the following problem, which would be incredibly simple in the relational world.</p>

<p>I have 3 tables - Incident, IncidentOffender, and IncidentLoss. There may be none, one, or many IncidentOffenders and IncidentLosses to an Incident:</p>

<p><a href="https://i.stack.imgur.com/rI4sO.png" rel="nofollow"><img src="https://i.stack.imgur.com/rI4sO.png" alt="enter image description here"></a></p>

<p>How can I design my data warehouse such that I will be able to ask the cube, for example, "how much time did we spend dealing with incidents on which a bald offender stole baked beans?", as well as "what was the value of those beans?"?</p>

<p>Apologies if this sounds simple, but I've scoured the web and devoured various books, but still I cannot find a real-life example of anything like this, which seems like an everyday situation to me.</p>