SSAS：基于雪花模型的维度设计

SSAS：基于雪花模型的维度设计2014-03-23 cnblogs BIWORK基于雪花模型的维度以下面的 Product 产品与产品子类别，产品类别为例。 DimProduct 表和 DimProductSubcategory 表有外键关系，而 DimProductSubcategory 表和 DimProductCategory 表存在外键关系。

测试的维度表与数据 -

USE BIWORK_SSISGOIF OBJECT_ID（"DimProduct"） IS NOT NULLDROP TABLE DimProduct GOIF OBJECT_ID（"DimProductSubcategory"） IS NOT NULLDROP TABLE DimProductSubcategory GOIF OBJECT_ID（"DimProductCategory"） IS NOT NULLDROP TABLE DimProductCategory GOSELECT ProductKey, ProductAlternateKey, ProductSubcategoryKey, EnglishProductName, StandardCost, Color, SafetyStockLevel, ListPrice, Class, Size, StartDate, EndDate, [Status], ProductAlternateKey + " （" + CONVERT （Char（10）, StartDate, 120） + "）" AS ProductIDINTO DimProductFROM AdventureWorksDW2012.dbo.DimProductSELECT ProductSubcategoryKey, ProductSubcategoryAlternateKey, EnglishProductSubcategoryName, ProductCategoryKey INTO DimProductSubcategoryFROM AdventureWorksDW2012.dbo.DimProductSubcategorySELECT ProductCategoryKey, ProductCategoryAlternateKey, EnglishProductCategoryNameINTO DimProductCategoryFROM AdventureWorksDW2012.dbo.DimProductCategoryALTER TABLE DimProductCategory ADD CONSTRAINT PK_CategoryKey PRIMARY KEY CLUSTERED（ProductCategoryKey）GOALTER TABLE DimProductSubcategory ADD CONSTRAINT PK_SubCategoryKey PRIMARY KEY CLUSTERED（ProductSubcategoryKey）GOALTER TABLE DimProduct ADD CONSTRAINT PK_Product PRIMARY KEY CLUSTERED（ProductKey）GOALTER TABLE DimProductADD CONSTRAINT FK_SubcategoryKey FOREIGN KEY（ProductSubcategoryKey） REFERENCES DimProductSubcategory（ProductSubcategoryKey）ALTER TABLE DimProductSubcategoryADD CONSTRAINT FK_CategoryKey FOREIGN KEY（ProductCategoryKey） REFERENCES DimProductCategory（ProductCategoryKey）SELECT * FROM DimProduct SELECT * FROM DimProductSubcategorySELECT * FROM DimProductCategory