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:

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

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

Natural/obvious dimensions

have hierarchical attributes.

Fields

  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:

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.