英文:
Should product hierarchy be added to 1 or multiple dim tables?
问题
我有以下产品层次结构：
类别
子类别
产品
在哪种情况下，将这3列放在名为"产品"的一张表中是有意义的，或者将它们拆分成3张表 - 每张表一个？
英文:
I have product hierarchy as follows:
Category
Sub category
Product
When does it make sense to have all these 3 columns in 1 table called Prooduct, or to make 3 tables - 1 for each?
答案1
得分: 1
它们应该都在一个单一的表中，因为它们在逻辑上是相关的。不仅如此，而且数据建模与OLTP相比有很大的区别。例如，常常会有一个包含几列可能看似无关的垃圾维度。建模是一个非常详细的主题，如果你想阅读相关材料，我可以推荐下面这本《Star Schema: The Complete Reference》。
https://www.amazon.co.uk/Schema-Complete-Reference-Christopher-Adamson/dp/0071744320
唯一有道理将它们放在单独的表中并“雪花化”它们的时间是为了利用PowerBI中的一个小技巧，在切片器或矩阵中抑制空白，如果你有一个不规则的层次结构，但这会带来更复杂的DAX编写的缺点。
英文:
They should all be in a single table as they're logically related. Not only that, but data modelling is very different compared to OLTP. For instance, it is not uncommon to have a junk dimension which contains several columns which can all appear unrelated. Modelling is a very detailed subject and if you're after reading material, I can recommend Star Schema: The Complete Reference below.
https://www.amazon.co.uk/Schema-Complete-Reference-Christopher-Adamson/dp/0071744320
The only time is makes sense to have these in separate tables and "snowflake" them is to take advantage of a little trick in PowerBI to suppress blanks in a slicer or matrix if you have a ragged hierarchy but this comes with drawbacks in having to write more complicated DAX down the line.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论