I am trying to model a DW where I have many levels of geography (Neighborhood, District, City, State).
I have several demographic data that should be included in the model. These data include count of people living in that area, average monthly income, average age, and others. The lowest level I have data for is neighborhood, which means it can be grouped together in order to calculate the values for the upper levels.
The Geography is modeled in a single (denormalized) dimension, where each level gets its own column.
Now I have to fit the demographic data in the model. Should I put it in another, separate dimension, or should I put it in the Geography dimension? What about the aggregated levels?
Putting it all on the Geography dimension would leave me with a large number of columns:
- Id
- Neighborhood Name
- District Name
- City Name
- State Name
- Neighborhood Avg Monthly Income
- Neighborhood Avg working population age
- Neighborhood Number of people
- ...
- District Avg Monthly Income
- District Avg working population age
- District Number of people
- ...
- City Avg Monthly Income
- City Avg working population age
- City Number of people
- ...
Is this correct? This seems rather convoluted. I searched for alternative designs on several books (including Kimball's), but haven't been able to find anything satisfactory.
Are there better, proven, approved and reliable alternatives to this design?