title: Database warehouse design: fact tables and dimension tables
body: <p>I am building a poor man's data warehouse using a RDBMS. I have identified the key 'attributes' to be recorded as:</p>
  
  <ul>
  <li>sex (true/false)</li>
  <li>demographic classification (A, B, C etc)</li>
  <li>place of birth</li>
  <li>date of birth</li>
  <li>weight (recorded daily):  The fact that is being recorded</li>
  </ul>
  
  <p>My requirements are to be able to run 'OLAP' queries that allow me to:</p>
  
  <ul>
  <li>'slice and dice'</li>
  <li>'drill up/down' the data and </li>
  <li>generally, be able to view the data from different perspectives</li>
  </ul>
  
  <p>After reading up on this topic area, the general consensus seems to be that this is best implemented using dimension tables rather than normalized tables.</p>
  
  <p>Assuming that this assertion is true (i.e. the solution is best implemented using fact and dimension tables), I would like to seek some help in the design of these tables.</p>
  
  <p>'Natural' (or obvious) dimensions are:</p>
  
  <ul>
  <li>Date dimension</li>
  <li>Geographical location</li>
  </ul>
  
  <p>Which have hierarchical attributes. However, I am struggling with how to model the following fields:</p>
  
  <ul>
  <li>sex (true/false)</li>
  <li>demographic classification (A, B, C etc)</li>
  </ul>
  
  <p>The reason I am struggling with these fields is that:</p>
  
  <ol>
  <li>They have no obvious hierarchical attributes which will aid aggregation (AFAIA) - which suggest they should be in a fact table</li>
  <li>They are mostly static or very rarely change - which suggests they should be in a dimension table.</li>
  </ol>
  
  <p>Maybe the heuristic I am using above is too crude?</p>
  
  <p>I will give some examples on the type of analysis I would like to carryout on the data warehouse - hopefully that will clarify things further. </p>
  
  <p>I would like to aggregate and analyze the data by sex and demographic classification - e.g. answer questions like:</p>
  
  <ul>
  <li>How does male and female weights compare across different demographic classifications?</li>
  <li>Which demographic classification (male AND female), show the most increase in weight this quarter.</li>
  </ul>
  
  <p>etc.</p>
  
  <p>Can anyone clarify whether sex and demographic classification are part of the fact table, or whether they are (as I suspect) dimension tables.?</p>
  
  <p>Also assuming they are dimension tables, could someone elaborate on the table structures (i.e. the fields)?</p>
  
  <p>The 'obvious' schema:</p>
  
  <pre><code>CREATE TABLE sex_type (is_male int);
  CREATE TABLE demographic_category (id int, name varchar(4));
  </code></pre>
  
  <p>may not be the correct one.</p>