SSAS：关于父子维度的设计

下面仍然是我们的测试表和数据，测试表包含了一个员工维度表和销售的事实表。

USE BIWORK_SSIS
GO
    
IF OBJECT_ID('FactResellerSales','U') IS NOT NULL
DROP TABLE FactResellerSales
GO 
    
IF OBJECT_ID('DimEmployee','U') IS NOT NULL
DROP TABLE DimEmployee
GO 
    
SELECT EmployeeKey,
       ParentEmployeeKey,
       EmployeeNationalIDAlternateKey,
       CASE WHEN ISNULL(MiddleName,'') = ''  THEN FirstName +' '+ LastName  
            ELSE FirstName +' '+ MiddleName +' '+LastName
       END AS FullName,
       Title 
INTO DimEmployee
FROM AdventureWorksDW2012.dbo.DimEmployee
    
SELECT ProductKey,
       OrderDateKey,
       EmployeeKey,
       SalesOrderLineNumber,
       SalesOrderNumber,
       UnitPrice,
       ProductStandardCost,
       SalesAmount
INTO FactResellerSales
FROM AdventureWorksDW2012.dbo.FactResellerSales
GO
    
ALTER TABLE DimEmployee 
ADD CONSTRAINT PK_EmployeeKey PRIMARY KEY CLUSTERED (EmployeeKey)
GO
    
ALTER TABLE DimEmployee
ADD CONSTRAINT FK_ParentEmployeeKey FOREIGN KEY(ParentEmployeeKey) REFERENCES DimEmployee

(EmployeeKey)
GO
    
ALTER TABLE FactResellerSales 
ADD CONSTRAINT PK_Reseller_OrderLineNumber_OrderNumber PRIMARY KEY CLUSTERED 

(SalesOrderLineNumber,SalesOrderNumber)
GO
    
ALTER TABLE FactResellerSales
ADD CONSTRAINT FK_EmployeeKey FOREIGN KEY(EmployeeKey) REFERENCES DimEmployee(EmployeeKey)
GO
    
SELECT * FROM DimEmployee
SELECT * FROM FactResellerSales

员工表中的 ParentEmployeeKey 指向了自身的主键 EmployeeKey，而 FactResellerSales 中的主键 是由 SalesOrderLineNumber 和 SalesOrderNumber 构成的一个复合主键，并且 EmployeeKey  指 向了 DimEmployee 的 EmployeeKey。

（编辑：应用网_阳江站长网）

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