星型模式 - 一維或拆分

我們有一個數據倉庫，它有兩個維度，Product 和 ProductLine。產品只有一個條目，並且只有一個產品線，其描述從運輸更改為運輸。當使用源數據創建星型模式時，產品和產品線是否應該合併為一個產品維度？

+-------------+-------------+-------------+------------------------+------------+------------+
| 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) 產品產品和產品線的二維表？

+-------------+--------------------+-------------+------------+------------+
| 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 並查看它們是否為空，但可以使用該索引來查看哪些產品屬於產品線併計算這些產品的空缺產品。

但是，如果您有多個度量值組，並且在某些度量值組上，您只能連結到產品維度，而在某些其他度量值組上，您只能連結產品線維度，您可以選擇具有單獨的維度以避免連結到非粒度屬性.

引用自：https://dba.stackexchange.com/questions/147602