title: design of fact table(s) for data warehouse
body: <p>how would you model this in a data warehouse:</p>
  
  <hr>
  
  <ol>
  <li><p>there are municipalities which are geographical areas, that exist in geographical hierarchies, such a province (i.e. state, e.g. Minnesota), region (e.g. MidWest).</p></li>
  <li><p>a performance evaluation is done on these municipalities, by calculating performance indicators such as "% of housing backlog completed", "% of budget spent", "% of budget allocated to infrastructure", "debtor coverage", etc.</p></li>
  <li><p>there are about 100 of these performance indicators.</p></li>
  <li><p>these indicators are grouped into "performance groups", which are themselves grouped into "key performance areas"</p></li>
  <li><p>calculations are applied to the performance indicators (the calculations vary based on certain factors such as municipality type, size, region, etc) to produce "performance scores".</p></li>
  <li><p>weightings are then applied to the scores to create "final weighted scores". (i.e. some indicators are weighted more than others when aggregated into the "key performance areas")</p></li>
  <li><p>there will be a time dimension (evaluations done yearly), but for now just the one data set.</p></li>
  </ol>
  
  <hr>
  
  <p>NB: users need to be able to easily query the data across any combination of indicators. i.e. someone might want to see: (i) the performance level of (ii) "debtor coverage" against (iii) "% budget spent" against (iv) "debtor days" at a (v) provincial level.</p>
  
  <p>I tried this by having "IndicatorType" as a dimension, and then having the [indicator / performance group / performance area] hierarchy in that table - but then i can't work out how to easily get multiple indicators on the same line, as it would need a fact table alias(?). So I thought of putting all 100 items as columns in a (very wide!) fact table - but then I would lose the [group/area] heirarchy on the indicators...?</p>
  
  <p>Any ideas?</p>
  
  <p>Thanks</p>