title: Accommodating Dynamic Hierarchies in a Data Warehouse Model
body: <p>I am building a data warehouse for the company's (which I am working for) core ERP application, for a particular client.</p>
  
  <p>Most of the data in the source database, which is related to hierarchies in the data warehouse are in columns as shown below:<img src="https://i.stack.imgur.com/VvHzL.jpg" alt="Source ProductDescription table"></p>
  
  <p>But traditionally the model to store dimension data according to my knowledge is as:<img src="https://i.stack.imgur.com/FpKTz.jpg" alt="data warehouse DimProduct table"></p>
  
  <p>I could pivot the data and fit them in the model shown above. But the issue comes when a user introduces a new hierarchy value. Say for instance the user in the future decides to define a new level called <strong>Product Sub Category</strong>. Then my entire data warehouse model will collapse without a way to accommodate the new hierarchy level defined.</p>
  
  <p>Do let me know a way to overcome this situation.</p>
  
  <p>I hope my answer is clear enough. Just let me know if further details are needed.</p>