在数据仓库模型中容纳动态层次结构
我正在为公司(我正在工作的)核心 ERP 应用程序为特定客户构建一个数据仓库。
源数据库中与数据仓库中的层次结构相关的大部分数据都位于如下所示的列中:
但据我所知,传统上存储维度数据的模型如下:
我可以对数据进行透视和适合它们在上面显示的模型中。但当用户引入新的层次结构值时,问题就出现了。举例来说,用户将来决定定义一个名为产品子类别的新级别。然后我的整个数据仓库模型将崩溃,无法适应定义的新层次结构级别。
请让我知道克服这种情况的方法。
我希望我的回答足够清楚。如果需要更多详细信息,请告诉我。
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 overcome this situation.
I hope my answer is clear enough. Just let me know if further details are needed.
好吧,任何东西都不应该崩溃——ETL 应该像往常一样提取和加载数据。
以下是需要考虑的几个选项:
只需将新层次结构的一列再添加到
dimProduct
。尝试使用层次结构帮助表 .
考虑添加路径字符串属性 到
dimProduct
。Well, nothing should collapse -- the ETL should extract and load the data as always.
Here are a few options to consider:
Simply add one more column for the new hierarchy to the
dimProduct
.Try using hierarchy helper table.
Consider adding path string attribute to the
dimProduct
.