除了之前的幾篇文章中出現的時間維度,雪花型維度的設計之外還有一種比較特殊的維度 - 父子維 度。父子維度特殊就特殊在它包含了一種基於遞歸關系(Recursive Relationship)的引用結構, 在我的這篇文章中提到了如何基於父子層次結構來設計和制作 SSRS 報表,不過那個報表是基於數據倉 庫的。而現在我們要設計的是基於父子結構的維度,在此基礎之上我們也可以設計出基於 SSAS 數據庫 的 SSRS 報表。
下面仍然是我們的測試表和數據,測試表包含了一個員工維度表和銷售的事實表。
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。