Accommodating Dynamic Hierarchies in a Data Warehouse Model

I am building a data warehouse for the company's (which I am working for) core ERP application, for a particular client.

Most of the data in the source database, which is related to hierarchies in the data warehouse are in columns as shown below:

But traditionally the model to store dimension data according to my knowledge is as:

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 Product Sub Category. Then my entire data warehouse model will collapse without a way to accommodate the new hierarchy level defined.

Do let me know a way to overc开发者_StackOverflow社区ome this situation.

I hope my answer is clear enough. Just let me know if further details are needed.

Well, nothing should collapse -- the ETL should extract and load the data as always.

Here are a few options to consider:

1. Simply add one more column for the new hierarchy to the dimProduct.

2. Try using hierarchy helper table.

3. Consider adding path string attribute to the dimProduct.