我们有一个数据仓库，它有两个维度，Product 和 ProductLine。Product 只有一个条目，并且只有一个 ProductLine 的描述从transport更改为Transport。当从源数据创建星型模式时，是否应该将产品和产品线组合到一个产品维度？
+-------------+-------------+-------------+------------------------+------------+------------+
| Product | | | | | |
+-------------+-------------+-------------+------------------------+------------+------------+
| SurrogateID | BusinessKey | Description | ProductLineBusinessKey | ValidFrom | ValidTo |
+-------------+-------------+-------------+------------------------+------------+------------+
| 1 | 100 | Car | 10 | 01.01.1900 | 31.12.9999 |
+-------------+-------------+-------------+------------------------+------------+------------+
+-------------+-------------+-------------+------------+------------+
| ProductLine | | | | |
+-------------+-------------+-------------+------------+------------+
| SurrogateID | BusinessKey | Description | ValidFrom | ValidTo |
+-------------+-------------+-------------+------------+------------+
| 1 | 10 | transport | 01.01.1900 | 15.10.2016 |
+-------------+-------------+-------------+------------+------------+
| 2 | 10 | Transport | 16.10.2016 | 31.12.9999 |
+-------------+-------------+-------------+------------+------------+
A）包含产品和产品线的产品的一维表？
+-------------+--------------------+------------------------+-------------+-----------------+------------+------------+
| DimProduct | | | | | | |
+-------------+--------------------+------------------------+-------------+-----------------+------------+------------+
| SurrogateId | BusinessKeyProduct | BusinessKeyProducsLine | DescProduct | DescProductLine | ValidFrom | ValidTo |
+-------------+--------------------+------------------------+-------------+-----------------+------------+------------+
| 1 | 100 | 10 | Car | transport | 01.01.1900 | 15.10.2016 |
+-------------+--------------------+------------------------+-------------+-----------------+------------+------------+
| 2 | 100 | 10 | Car | Transport | 16.10.2016 | 31.12.9999 |
+-------------+--------------------+------------------------+-------------+-----------------+------------+------------+
+-----------------------+--------------------+----------------+
| FactTable | | |
+-----------------------+--------------------+----------------+
| SurrogateIdDimProduct | SurrogateIdDimDate | MeasureSalesPc |
+-----------------------+--------------------+----------------+
| 1 | 20161014 | 100 |
+-----------------------+--------------------+----------------+
| 2 | 20161016 | 150 |
+-----------------------+--------------------+----------------+
B) product 和 product line 的二维表？
+-------------+--------------------+-------------+------------+------------+
| DimProduct | | | | |
+-------------+--------------------+-------------+------------+------------+
| SurrogateId | BusinessKeyProduct | DescProduct | ValidFrom | ValidTo |
+-------------+--------------------+-------------+------------+------------+
| 1 | 100 | Car | 01.01.1900 | 31.12.9999 |
+-------------+--------------------+-------------+------------+------------+
+----------------+-------------+-------------+------------+------------+
| DimProductLine | | | | |
+----------------+-------------+-------------+------------+------------+
| SurrogateID | BusinessKey | Description | ValidFrom | ValidTo |
+----------------+-------------+-------------+------------+------------+
| 1 | 10 | transport | 01.01.1900 | 15.10.2016 |
+----------------+-------------+-------------+------------+------------+
| 2 | 10 | Transport | 16.10.2016 | 31.12.9999 |
+----------------+-------------+-------------+------------+------------+
+-----------------------+----------------+--------------------+----------------+
| FactTable | | | |
+-----------------------+----------------+--------------------+----------------+
| SurrogateIdDimProduct | DimProductLine | SurrogateIdDimDate | MeasureSalesPc |
+-----------------------+----------------+--------------------+----------------+
| 1 | 1 | 20161014 | 100 |
+-----------------------+----------------+--------------------+----------------+
| 1 | 2 | 20161016 | 150 |
+-----------------------+----------------+--------------------+----------------+
应如何设计适用于此场景的数据集市星型模式？显然，解决方案 A 的优点是只需要一个连接，而 B 导致产品维度中的条目较少。如果有很多产品，维度可能会快速增长。还有其他的考虑吗？什么是最佳实践？
对不起丑陋的“桌子”。
这两种方法都有话要说。我通常更喜欢在单个维度中处理这种情况，这样您就可以定义允许引擎计算位图索引的属性关系。
如果这样做，查询引擎不需要通过实际计算组合来计算 nonemptycrossjoin 并查看它们是否为空，而是可以使用该索引来查看哪些产品属于产品线并只计算那些产品的空性产品。
但是，如果您有多个度量值组，并且在某些度量值组上您只能链接到产品维度，而在其他一些度量值组上您只能链接产品线维度您可以选择具有单独的维度以避免链接到非粒度属性.