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

测试的维度表与数据 -

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

（编辑：PHP编程网 - 襄阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!