星型模式设计-一栏尺寸 [英] star schema design - one column dimensions

问题描述

我是数据仓库的新手，但我认为我的问题相对容易回答. 我建立了一个星型模式，其中包含一个尺寸表"product".该表具有列"PropertyName"和列"PropertyValue". 因此，维度看起来像这样:

I`m new to data warehousing, but I think my question can be relatively easy answered. I built a star schema, with a dimension table 'product'. This table has a column 'PropertyName' and a column 'PropertyValue'. The dimension therefore looks a little like this:

surrogate_key | natural_key (productID) | PropertyName | PropertyValue | ...
    1              5                          Size           20          ...
    2              5                          Color          red
    3              6                          Size           20
    4              6                          Material       wood

以此类推.

在我的事实表中，我总是使用尺寸的替代键.造成PropertyName和PropertyValue列的原因，我的自然键不再唯一/无法标识，因此事实表中的行太多了.

In my fact table I always use the surrogate keys of the dimensions. Cause of the PropertyName and PropertyValue columns my natural key isn`t unique / identifying anymore, so I get way too much rows in my fact table.

我现在的问题是，我应该如何处理属性列?最好将每个属性放到单独的维度中，例如维度大小，维度颜色等?我有大约30种不同的属性. 还是应该为事实表中的每个属性创建列? 还是将所有属性一维化?

My question now is, what should I do with the property columns? Would it be best, to put each property into separate dimensions, like dimension size, dimension color and so on? I got about 30 different properties. Or shall I create columns for each property in the fact table? Or make one dimension with all properties?

在此先感谢您的帮助.

推荐答案

您的尺寸表产品"应如下所示:

Your dimension table 'product' should look like this:

surrogate_key | natural_key (productID) | Color | Material | Size | ...
    1              5                      red     wood       20     ...
    2              6                      red     ...         

如果必须具有多个属性，请尝试将它们归入另一个维度.例如，如果您可以在另一种颜色或材料中使用相同的ID和相同的价格购买相同的产品，则颜色和材料可以是另一维的属性.您的事实表可以使用两个键来标识产品:product_id和colormaterial_id ...

If you have to many properties, try to group them in another dimension. For example Color and Material can be attributes of another dimension if you can have the same product with same id and same price in another color or material. Your fact table can identify product with two keys: product_id and colormaterial_id...

阅读建议: 数据仓库工具包，Ralph Kimball

这篇关于星型模式设计-一栏尺寸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！