title: modelling hierarchical data warehouse dimension
body: <p>I'm trying to model a dimension that is hierarchical and has an indeterminate amount of features at each level.  Here's an example on how the tables are structured in the data source</p>
  
  <pre><code>+-------------+              +-------------+                +-------------+     
  |Product      +--------------+SubCategory  +----------------+Category     |     
  +------+------+              +------+------+                +-------+-----+     
         |                            |                               |           
         |                            |                               |           
  +------+----------+         +-------+-------------+         +-------+----------+
  |Product Features |         |SubCategory Features |         |Category Features |
  +-----------------+         +---------------------+         +------------------+
  </code></pre>
  
  <p>There's a one to many relationship from Product -> SubCategory and from SubCategory -> Category.  Each of the Product/SubCategory/Category also reference their respective features table.  The number of features is however not fixed and could be 0.</p>
  
  <p>The fact tables I'm trying to build needs to be at the level of product for its grain, and without the features I could just make a dimension with each of these as columns like so:</p>
  
  <pre><code>+-----------------+
  |Dim_Product      |
  +-----------------+
  |Dim_Product_Id   |
  |Product          |
  |SubCategory      |
  |Category         |
  |...              |
  +-----------------+
  </code></pre>
  
  <p>But then all the features would be lost.</p>
  
  <p>Is it possible to keep the dimension at the product level and keep all the features from each hierarchy?  Or would it be necessary to make a bridge table containing all the combinations of all the features in the hierarchies?  Would I need to break the levels out into their own dimension (i.e. Dim_Product, Dim_SubCategory, Dim_Category) instead?  There is also fixed attributes for each heirarchy level, so can these be just flattened out and included as columns if a single dimension is a suitable option?</p>