Search code examples
sqldatabase-designdata-warehouseolap

Are these sex and demographic classifications part of the fact table or the dimension tables, with what other relevant fields?

My data warehouse attributes:

  • sex (true/false)
  • demographic classification (A, B, C, etc.)
  • place of birth
  • date of birth
  • weight (recorded daily) (a fact about other attributes)

My requirements are to be able to run OLAP queries to:

  • 'slice and dice'
  • 'drill up/down'
  • view from different perspectives

Apparently this is best implemented using dimension tables rather than normalized tables.

Natural/obvious dimensions

  • Date
  • Geographical location

have hierarchical attributes.

Fields

  • sex
  • demographic classification
  1. have no obvious hierarchical attributes which will aid aggregation - which suggests they should be in a fact table
  2. very rarely change - which suggests they should be in a dimension table

Maybe the heuristic is too crude.

I would like to aggregate and analyze the data by sex and demographic classification - e.g. answer questions like:

  • How do male and female weights compare across demographic classifications?
  • Which demographic classification (male AND female) shows the most increase in weight this quarter?

Are sex and demographic classification part of the fact table or dimension tables?

If dimension tables, what are other relevant fields?

The obvious schema:

CREATE TABLE sex_type (is_male int);
CREATE TABLE demographic_category (id int, name varchar(4));
Solution

  • Not sure why you feel that using RDBMS is poor man's solution, but hope this may help.

    weight_model_01.png

    Tables dimGeography and dimDemographic are so-called mini-dimensions; they allow for slicing based on demographic and geography without having to join dimUser, and also to capture user's current demographic and geography at the time of measurement.

    And by the way, when in DW world, verbose -- Gender = 'female', AgeGroup = '30-35', EducationLevel = 'university', etc.