SSAS：基於雪花模型的維度設計

基於雪花模型的維度以下面的 Product 產品與產品子類別，產品類別為例。 DimProduct 表和 DimProductSubcategory 表有外鍵關系，而 DimProductSubcategory 表和 DimProductCategory 表存在 外鍵關系。

測試的維度表與數據 -

USE BIWORK_SSIS
GO
    
IF OBJECT_ID('DimProduct') IS NOT NULL
DROP TABLE DimProduct 
GO
    
IF OBJECT_ID('DimProductSubcategory') IS NOT NULL
DROP TABLE DimProductSubcategory 
GO
    
IF OBJECT_ID('DimProductCategory') IS NOT NULL
DROP TABLE DimProductCategory 
GO
    
SELECT ProductKey,
       ProductAlternateKey,
       ProductSubcategoryKey,
       EnglishProductName,
       StandardCost,
       Color,
       SafetyStockLevel,
       ListPrice,
       Class,
       Size,
       StartDate,
       EndDate,
       [Status],
       ProductAlternateKey + ' (' + CONVERT (Char(10), StartDate, 120) + ')' AS ProductID
INTO DimProduct
FROM AdventureWorksDW2012.dbo.DimProduct
    
SELECT ProductSubcategoryKey,
       ProductSubcategoryAlternateKey,
       EnglishProductSubcategoryName,
       ProductCategoryKey 
INTO DimProductSubcategory
FROM AdventureWorksDW2012.dbo.DimProductSubcategory
    
SELECT ProductCategoryKey,
       ProductCategoryAlternateKey,
       EnglishProductCategoryName
INTO DimProductCategory
FROM AdventureWorksDW2012.dbo.DimProductCategory
    
ALTER TABLE DimProductCategory 
ADD CONSTRAINT PK_CategoryKey PRIMARY KEY CLUSTERED(ProductCategoryKey)
GO
    
ALTER TABLE DimProductSubcategory 
ADD CONSTRAINT PK_SubCategoryKey PRIMARY KEY CLUSTERED(ProductSubcategoryKey)
GO
    
ALTER TABLE DimProduct 
ADD CONSTRAINT PK_Product PRIMARY KEY CLUSTERED(ProductKey)
GO
    
ALTER TABLE DimProduct
ADD CONSTRAINT FK_SubcategoryKey FOREIGN KEY(ProductSubcategoryKey) REFERENCES 

DimProductSubcategory(ProductSubcategoryKey)
    
ALTER TABLE DimProductSubcategory
ADD CONSTRAINT FK_CategoryKey FOREIGN KEY(ProductCategoryKey) REFERENCES 

DimProductCategory(ProductCategoryKey)
    
SELECT * FROM DimProduct 
SELECT * FROM DimProductSubcategory
SELECT * FROM DimProductCategory