首页 > BI/数据库 > Analysis Services:你应该使用多对多维度吗?

Analysis Services:你应该使用多对多维度吗?

BI/数据库 , ,

如果你是一个初学者,请注意,这篇文章并不是告诉你不要在Analysis Services中使用多对多(m:n)维度。事实上,这是一个非常棒的功能,它允许你将多个维度成员和多条事实数据之间建立关联关系。在很多商业领域,从零售行业到会计行业到医疗行业,多对多关系被大量使用着。但有时在处理多对多关系时,如果遇到大规模数据量,可能会遇到数据查询性能问题以及数据精准性问题。这篇技术文章就是为了说明多对多关系背后的工作原理和适合的商业应用场景,以及应用多对多关系可能带来的数据精准性问题。

相关背景

我在我之前的博文“How to handle many-to-many relationships in Yukon?”中提到的应用多对多关系的一个例子是地理维度。但现在我们将使用Adventure Works DW OLAP这个示例(相关代码在Codeplex的SQL Server 2005 SP2 Sample Databases上可以下载到)。

打开AdventureWorks数据库,在AdventureWorks数据立方体的“维度使用”标签卡下,我们可以看到[Sales Reason]维度通过[Sales Reasons]中间度量组关联到了[Internet Sales]度量组(图中没有显示)上。[Sales Reasons]旁边放了一个数学上用于表示“无穷”的图标,说明这个关系和常规维度(就像[Source Currency]维度和[Source Currency Code]度量组的关系)不一样,这是一个多对多关系。

在BIDS中[Sales Reason]和[Internet Sales]的多对多关系图

单击[Sales Reasons]度量组,会弹出一个设计维度-度量组关联的对话框(见下图),在这里,你会注意到,[Sales Reason]和[Internet Sales]这两者之间的关系是多对多关系,并且,连接[Sales Reason]维度和[Internet Sales]度量组的中间度量组是[Sales Reasons]。

在BIDS中设计多对多关系

以下这张图说明了在这个场景中使用多对多维度的目的。我们希望把多个销售成功因素(例如价格很低,展示效果不错,或者其他因素)和一笔销售订单关联起来。

销售因素和销售情况之间的维度-度量组关系图

举个例子,John购买了一个高清电视机(对应事实表Internet Sales的一条记录),是因为最近正在大减价(销售成功因素:价格)并且超级联赛杯快要开始了(销售成功因素:其他)。

应用场景示例

我们回过头来看看[Adventure Works]的Sql以及Olap数据库的设计,这样可以更好地从技术上去理解多对多关系。在之前我们看到的[Adventure Works DW]Olap数据库中包含了[Internet Sales]和[Sales Reasons]度量组,以及[Sales Reason]维度。在AdventureWorks的Sql数据库中,这几个对象分别对应着FactInternetSales、FactInternetSalesReasons以及DimSalesReason表。

继续我们上面的例子,为了简单起见,我们只关注两条数据(SalesOrderNumber = 'SO69868', SalesOrderLineNumber = 2) 和(SalesOrderNumber = 'SO75088', SalesOrderLineNumber = 3)。

Sql查询为:

select SalesOrderNumber, SalesOrderLineNumber, SalesAmount
from FactInternetSales
where (SalesOrderNumber = 'SO69868' and SalesOrderLineNumber = 2) or
(SalesOrderNumber = 'SO75088' and SalesOrderLineNumber = 3)

结果集:

两笔订单交易数据

当你开始考虑FactInternetSales和FactInternetSalesReasons的多对多关系,需要将SalesReason表也加进来,你需要执行以下Sql查询:

select a.SalesOrderNumber, a.SalesOrderLineNumber, a.SalesAmount,
c.SalesReasonKey, c.SalesReasonName, c.SalesReasonReasonType
from FactInternetSales a
inner join FactInternetSalesReason b
on b.SalesOrderNumber = a.SalesOrderNumber
and b.SalesOrderLineNumber = a.SalesOrderLineNumber
inner join DimSalesReason c
on c.SalesReasonKey = b.SalesReasonKey
where (a.SalesOrderNumber = 'SO69868' and a.SalesOrderLineNumber = 2) or
(a.SalesOrderNumber = 'SO75088' and a.SalesOrderLineNumber = 3)

新的结果集如下:

image

从这两张表中你也可以看出,通过中间表FactInternetSalesReasons,你可以将多条销售成功因素记录关联到同一笔销售订单上。例如上述的SO75088订单,金额为$34.99,关联着“价格”和“其他”两条销售成功因素记录。

多对多维度的优势

这种通过中间表关联的优势在于,你可以将多个维度成员和一个事实表记录关联到一起。当你想要汇总这些值的时候,优势就体现出来了。例如,当你使用[Sales Reason]维度去查询[Internet Sales]度量组的时候,可以得到以下结果:

select {[Measures].[Internet Sales Amount]} on columns,
{[Sales Reason].[Sales Reason].members} on rows
from [Adventure Works]

不同销售成功因素对于销售的影响

上述结果集里头,由于[Price]和[Others]这两个销售成功因素都是SO75088这个订单成功的因素,因此其销售额都包含了SO75088订单的金额。而更不错的是,你现在有了一个[All Sales Reasons]的成员,用来表示总的销售金额$29,358,677.22。

你如果直接查询[Measures].[Internet Sales Amount]指标,得到的结果也是一样的。

select {[Measures].[Internet Sales Amount]} on columns
from [Adventure Works]

数据精准性问题

使用多对多维度的一个潜在问题就是数据重复计算,特别是当用户没能很好理解多对多背后原理的话,更有可能出现这种情况。例如,我们执行以下MDX查询:

select {[Measures].[Internet Sales Amount]} on columns,
{[Sales Reason].[Sales Reason].children} on rows
from [Adventure Works]

得到以下结果:

image

得到这样的数据之后,用户可能会直接使用上述的数据相加来计算总的销售金额,从而得到以下结果:

image

注意,这里的总和$30,856,531.52和前面提到的[All Sales Reasons]成员的汇总值$29,358,677.22很不一样,相差了$1,497,854.30,如果你从事金融方面工作的话,这个差别应该是足够明显了。

问题的复杂之处在于,当你应用多对多维度的时候,你无从知道那些成员会被重复计算,因为任何一单销售都有可能和任何一个销售成功因素关联。例如用户有可能直接把[Other]和[Price]成员的销售额相加,得到因为这两个原因而成交的订单销售额情况$11,224,325.90。但是这样的结果是错误的,因为有一些销售订单同时包含了这两个销售成功因素。因此总和应该是小于$11,224,325.90的。

有别的解决办法吗?

“是否应该使用多对多维度”,你主要需要考虑两个方面的问题:性能和数据精准性。

性能

Analysis Services 性能指南中提到,有时候应用多对多是出于性能的考虑,其中一个情形就是对Distinct Count类型的度量进行泛化。使用多对多维度可以让你通过Analysis Services的sum、count、max、min等运算符实现和Distinct Count一样的逻辑。为了计算Distinct Count或者Sum,Analysis Services存储引擎需要直接对最小粒度的数据做转换。这是因为,当一个查询涉及到多对多维度的时候,聚合结果是在查询期间通过目标度量组和中间度量组在属性级别上完成的。查询的过程相对比较消耗CPU和内存。

多对多的性能考虑主要包括以下几个方面:

  • 因为查询涉及到多对多维度,会在目标度量分组和中间度量分组之间生成一个Join连接,因此降低中间度量组的表大小(通常小于1百万行数据)可以提高查询性能。细节参考:Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques 白皮书。
  • 目标度量组和中间度量组之间的连接是哈希连接,因此完成此项操作非常消耗内存。
  • 和Distinct Count不同,多对多关系不能被预聚合(虽然通常对于Distinct Count来说,要设计通用的预聚合也不是一件容易的事)。因此,涉及到多对多维度的查询无法使用预聚合或者聚合缓存,除非直接命中才有效。不过在几种特殊情形下多对多关系还是有可能被预聚合的,详细信息参考:Analysis Services Many-to-Many Dimensions: Query Performance Optimization 技术白皮书。
  • 因为多对多无法被预聚合,很多MDX计算例如VisualTotals,subselects和Create Subcube都会遇到问题。

数据精准性

正如你在“数据精准性问题”这一节中看到的,在应用多对多关系的场景中你可能会得不到期望的值(例如重复计算)。所以,即便这是一项非常强大的功能,如果用户没有认识到背后的多对多关系,他可能会得到错误的结果。

分析

你可以参考Marco Russo写的Many-to-many维度建模,里头提及了很多基于多对多维度关系之上建模的解决方案。对于数据仓库以及商业智能问题来说,“是否应该使用多对多维度”这个问题,不存在一个绝对的答案。重要的是,你在实际应用之前,首先需要理解多对多背后的含义。

在 [Sales Reason]这个示例中,有多种情况可以判断是否应该使用多对多维度:

你想要多对多关系,并且经常会使用Sales Reason维度去剖析数据。

如果你经常需要使用Sales Reason维度去剖析数据,那么你其实并不需要一个中间度量组。在这个场景中,你可以将[FactInternetSales]和[FactInternetSalesReason]表合成一张新的事实表,然后基于这张表上建立新的数据立方体。因为你经常需要使用sales reason维度去剖析数据,当你使用[Price]成员去剖析数据的时候,以下高亮的行会被选中:

image

如果你选择了[Other]成员来剖析的话,以下高亮的行会被选中:

image

如果你想要获取汇总值,例如总销售额,你可以基于FactInternetSales表建立新的数据立方体,这样就不需要通过中间度量分组。

image

你实际上并不需要多对多关系

在我们这个场景中,实际上根本并不需要多对多关系。想想看,事实上,我们只需要规定,每笔销售订单只允许有一个销售成功因素即可。

image

image

另外一种处理办法是将此订单的销售额分摊到不同的销售成功因素上。

image

结论

注意,以上说的这些并不是要告诉你,不要去使用多对多维度。这是一项非常棒的功能,它让用户能够实现他们想要的效果——将多个维度成员关联到同一个事实表记录上,同时在计算总和的时候又不会出现重复计算的问题。但需要注意,使用多对多维度对于汇总值的影响(例如,将所有单元格的值汇总起来并不总是等于实际的总值)还有它对于其他维度的影响。同时,还需要考虑性能方面的问题,虽然在Analysis Services Many-to-Many Dimensions: Query Performance Optimization 技术白皮书中已经对如何优化多对多的查询性能做出了指导。在处理多对多问题上还有其他的解决方案,所以请确保你已经考虑过这些解决方案,并且已经理解了多对多的潜在问题。

——Kevin Yang

本博客遵循CC协议2.5,即署名-非商业性使用-相同方式共享
写作很辛苦,转载请注明作者以及原文链接~
如果你喜欢我的文章,你可以订阅我的博客:-D点击订阅我的文章

  1. | #1

    复杂的一塌糊涂

  2. | #2

    同上,不适合菜鸟

    • Kevin Yang
      | #3

      面对的人群不同吧,感觉自己虽然做的是BI方面的东东,但是BI方面的文章写得最少,真惭愧

  3. | #4

    看不懂...55555555555555

  4. | #6

    的克有点高难度哈...我是说相对于我来说哦.

  5. | #7

    好有难度哟...不过马马虎虎看懂了...

  1. 暂时没有trackbacks.