微软BI 之SSAS 系列 - 基于雪花模型的维度设计

2023-08-14 09:14:34

基于雪花模型的维度以下面的 Product 产品与产品子类别，产品类别为例。 DimProduct 表和 DimProductSubcategory 表有外键关系，而 DimProductSubcategory 表和 DimProductCategory 表存在外键关系。

测试的维度表与数据 -

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

新建一个 SSAS 项目并创建数据源和数据源视图 -

新建一个 Dimension 指定使用 DimProduct 表，Key Column 为 ProductKey 列，Name Column 为 Product ID 列。每个属性都有两种特性：Key Column 来引用唯一确定属性成员的一列 (如果是复合主键的话可能是多列)，Name Column 来引用包含描述性标签的列，它是显示在报表上被浏览的文本。

由于 DimProduct 表是雪花型产品维度的一部分，因此相关联的表业也会被包括进来，向导默认为每个表的主键创建属性。

选择需要出现在维度中的维度属性，用户根据这些属性来查看相应的事实数据。

重命名属性，让这些属性名称看起来更符合人们的习惯。

下一步并指定维度名称为 Product ，就可以看到维度设计中维度以及它下面的属性，可以部署一下这个项目。

部署之后可以在 Browser 浏览器中查看属性层次结构，在维度中的每一个属性都是一个属性层次结构，它通常包含两个级别：一个是 ALL 级别，第二个是由 Category 属性值构成的级别。但是在这里看到的 Category ALL 级别下的成员都是数值，并不能明确表示它自身的含义。这是因为我们之前在创建这个属性的时候，只默认指定了 Category 的 Key Column 而没有指定 Category 的 Name Column，因此需要修改这些属性的 Name Column。

修改属性 Category 的 Name Column ，同样的还需要修改 Subcategory 。

修改完成之后重新部署一下，就可以看到对应的属性标签内容了。

接下来我们需要创建一些层次结构来方便用户从指定的角度来查看事实数据。维度中的层次结构一般分为：属性层次结构，用户自定义层次结构中的非自然层次结构和自定义层次结构中通过创建属性关系所识别的自然层次结构。

属性层次结构我们前面我们已经看到过了，即每一个属性都是一个属性层次结构。属性层次结构一般含有两层，第一层是 ALL 级别，第二层是自身数据形成的成员级别。

自定义层次结构的自然层次结构中，每一个子成员都只有一个父成员，并且级别可能多于两级。我们可以通过创建属性关系来建立自然层次结构，创建属性关系可以加快数据库的处理速度。由于提供了更快的数据访问并改善了查询执行计划，从而也提升了查询性能。

自定义的层次结构也包含自然层次结构，但是也可以不是自然层次结构，也就是说子类可能存在多个父类。

对于产品，产品子类，产品大类这几个属性来说，是符合自然层次结构的要求的。一个产品只属于一个产品子类，一个产品子类也只属于一个产品大类。

对于颜色和大小，一个颜色可以对应多个不同大小的尺码，不同大小的尺码也可以对应多个颜色。那么为颜色和大小创建自定义的层次结构，就是属于自定义层次结构中的非自然层次结构了。

创建 Category - Subcategory - Product 这样的层次结构，并创建 Color - Size 这样的层次结构。

在属性关系中查看各个属性是如何关联的，下图所示可以看到基本上所有的属性都是通过关键属性 Product ID 相关联的， Category 和 Product ID 是通过 Subcategory 间接关联的。那么在分析服务中，这种关联关系表示了分析服务可以通过聚合所有与某个 Category 相关的 Subcategory 的值来得到该 Category 的值。但是分析服务不能从 Product 中得到 Subcategory 的值，因为 Product 和 Subcategory 没有关系。

我们通过修改 Product 和 Subcategory 的属性关系实现了一种自定义的自然层次结构。但是像 Color 和 Size 这种本身在层次逻辑上就并不存在这种自然层次结构的特征，因此就不需要做出任何改变了。

部署并查看层次结构 - Product By Category -

Color by Size -

更多 BI 文章请参看 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server) 如果觉得这篇文章看了对您有帮助，请帮助推荐，以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。

码农公寓

相关文章