design of fact table(s) for data warehouse

how would you model this in a data warehouse:

---

1. 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).

2. 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.

3. there are about 100 of these performance indicators.

4. these indicators are grouped into "performance groups", which are themselves grouped into "key performance areas"

5. 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".

6. 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")

7. there will be a time dimension (evaluations done yearly), but for now just the one data set.

---

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.

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...?

Any ideas?

Thanks

From stackoverflow

• This is a very involved question but I took the time to go through some of your points and came up with this model (should be a good start for you).

  Dimensions:

  DIM_MUNICIPALITIES:

  Fields = {MUNICIPAL_KEY, COUNTRY, REGION, STATE_PROV, CITY?, SIZE_SCORE}

  Hierarchy = {COUNTRY <-- REGION <-- STATE_PROV <-- CITY?}

  DIM_INDICATORS:

  Fields = {INDICATOR_KEY, PERFORMANCE_AREA, PERFORMANCE_GROUP, PERFORMANCE_INDICATOR}

  Hierarchy = {PERFORMANCE_AREA <-- PERFORMANCE_GROUP <-- PERFORMANCE_INDICATOR}

  DIM_DATE:

  Fields = {DATE_KEY, CALENDAR_DATE (SQL datetime), YEAR, MONTH, WEEK, DAY...}

  Hierarchy = {YEAR <-- MONTH <-- WEEK <-- DAY <-- DATE_KEY}

  Then in your fact table (say MYFACT) you would do something like the following:

  FACT_MYFACT:

  Fields = {MYFACT_KEY, DATE_KEY, MUNICIPAL_KEY, INDICATOR_KEY, PERFORMANCE_SCORE, BUDGET, ETC....}

  The fact table could have all these Measure columns (BUDGET, ETC) or you could do them in Calculated members, it all depends on how you want to make the accessible.

  Hope this helps you get a good start!

  : thanks for your post. however, i'm confused: if the indicator-level dimension exists, then there is no need for multiple measure columns in the fact table, as they are the same thing. it's really about the design benefits of having a 100-wide column fact table, versus a single numeric measure column and a "measure type" dimension (in this case, the indicator dimension). with a wide table, i can easily pull out multiple columns alongside each other, but i lose the PI/PG/KPA hierarchy. with the indicator dimension, i lose the reporting flexibility. or is there another way?

  : more: i'd think 3 fact tables: - performance indicator - performance score - final weighted score (the calculations are done in the load, i.e. scoring rules & weightings are applied then, NOT in the d/w) so: if i have 100 columns in the "performance indicator" fact table, i have 100 measures. now it's easy to report on 15 different measures. if the measures are in a DIM, then i only have 1 measure object, and need to a filter to get the right one, and aliases to get multiple? and when reporting from excel, this is not possible? so go wide & lose the PI/PG/KPA heirarchy?

  ajdams : I didn't mean put the measures in the DIM, I was uncertain by what you meant by size score (if that';s what you are referring to). I must have misinterpreted what you meant by indicator. In the Indicator dimension I would store the fields that describe and denote a certain indicator and then the actual measure of that value in the FACT.

• 

  KenFar : This looks about right: by putting the IndicatorKey in the fact table and having just one generic IndicatorValue you've got a bit of a key-value pair approach. This isn't very useful for reporting, but it is a practical way to handle the inevitability of your Indicators changing over time. Based on this data you could then publish this data to a flattened table with different measures as dedicated columns. This secondary table would be easier to change - and perhaps you don't need each individual measure - maybe just the much more static performance area & group numbers.