design of fact table(s) for data warehouse

Question

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

Answer 1

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!

Answer 2