微软BI 之SSAS 系列 - 关于父子维度的设计

2023-02-12 18:29:39

除了之前的几篇文章中出现的时间维度，雪花型维度的设计之外还有一种比较特殊的维度 - 父子维度。父子维度特殊就特殊在它包含了一种基于递归关系(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。