Project REAL分析服务技术探讨（4）

核心提示：在下面的五个章节中，我们比较和对比了五种使用SQL Server 2005分析服务来模型化实体的方法，Project REAL分析服务技术探讨（4），我们发现，没有一种简单的方法能够实现所有环境中的关系型模型化，只要这个Tables行集(不管它是怎么被构建的)在格式上是一致的，程序包的余下部分就能顺利的被重用，我们发现

在下面的五个章节中，我们比较和对比了五种使用SQL Server 2005分析服务来模型化实体的方法。我们发现，没有一种简单的方法能够实现所有环境中的关系型模型化。我们发现最好根据运行时可用的存储、所允许的计算来选择使用的方法，从而实现更有弹性的厂商分析模型。

方法＃1- 创建单独的真实维度

我们实现的第一种方法是创建了五个真实维度（每个都对应一种厂商类型），并从Item维度表中加载。这种技术很直接，并且很容易实现。然而，它存在一些缺点。首先是它在Cube中带来了一些额外的复杂性。终端用户不得不多使用增加的五个维度。并且在维度存储上也增加了五倍以上（因为每个维度完全不依赖于其它的维度）。最终要的缺点是这种技术不可能实现交叉的厂商分析。例如，考虑“Abrams, Harry N., Inc.”这个厂商。因为Abrams有五个成员，因此无法通过厂商直接的来分离，除非你假定，Abrams在五个维度中都有相同的拼写，并且确保在所有五个维度中都选中这个相同的切片。

方法＃2 – 使用属性层次来代替物理维度

这个方法相比于前面的方法，使得货品和厂商之间的关系更加密切。在这种方法中，我们去掉了第一种方法中使用的物理维度，而是在Item维度上添加了五个属性或者用户定义的层次。这五个新的Item层次是：Item.Return Vendor，Item.Purchase Vendor，Item.Original Purchase Vendor，Item.Source Vendor和Item.DC Vendor。使用这种方法，有以下几个优点。首先，因为五个属性是从一个属性键下构建的，因此只需要一个存储位置。第二，因为我们将五个维度转换成五个属性层次（或者用户定义层次），因此这降低了最终Cube的复杂度(维度)。对于一些终端用户，这点很重要，这能使得他们在Cube之间导航更加简单。然而，这种方法也不允许覆盖多个厂商的分析。

方法＃3 – 为Vendor和Vendor类型创建多对多的维度

第三个方法是在三个维度（Item， Vendor和 Vendor Type.）之间创建一个多对多的维度，这是一个不常用的方法，这会带来大量的成本。Item维度并没有任何变化。Vendor维度需要在五中类型的厂商上将所有可能的厂商合并（并去除重复的项）。最后，Vendor Type已经有五个成员，分别为Return、Purchase、Original Purchase、Source和DC服务。一旦维度就绪后，就创建一个度量组，用来表示每个Item和它的五中类型。度量组看上去的效果如图21所示。

图21：一个在Item、Vendor、Vendor Type维度表之间多对多的维度

如同你看到的一样，这个度量组确实非常大。在完整的维度中，我们会有超过六百万个项货品，每项有五种类型，以及约4万个厂商。这意味着，在这个多对多的度量（在Cube设计中被称为Item Vendor）组中，我们会有超过3千万项记录。

这个方法的主要缺点是为了完成对厂商的分析，你必须构建一个在正被分析的分割表数据之间的百万对百万级别的交叉连接和一个多对多的度量组（也包含几百万条记录）。这意味着，在第二种方法中只需要几秒的查询，在这种方法中会慢上许多（例如几十妙）。但是，这种方法也带来了巨大的好处。现在，你可以用一个独立的成员表示一个成员。这能够让成员更容易被区分，并能够直接的比较。如图22所示。

图22：通过Vendor Type查找一个Vendor的销售情况

这在其它技术中是不可能实现的，除非你手工的把五个切片设置成相同的值。

方法＃4 – 使用一个引用或者扮演角色的维度

第四种技术将创建一个单独的Cube维度，被称为Vendor，就像多对多的这种方法一样，它有一个单独的成员，值都是来自于Item维度表中每个被联合的厂商。然后再将这个维度添加到Cube（每次都对应一个角色），作为一个引用或者扮演角色的维度。第一次，Vendor维度被称为Return Vendor；第二个被称为Purchase Vendor；以下类似。虽然这个方法还是具有交叉厂商分析的缺点，但是这种方法只需最小的存储并且实现很直接。

如果你使用这种技术，需要意识到一点，和多对多方法一样，实际的计算是在运行时完成的。在适当的分割表数据和扮演角色维度之间存在一个连接，然后数据就形成了。尽管这种方法存在和多对多一样的性能问题，但这种方法不需要额外的分析能力。

方法＃5 – 使用引用或者扮演角色维度并实物化

第五种用来模型化厂商的方法是用第四种方法创建扮演角色维度，但当你指定引用维度的时候，将类型设置成“materialized”。当这个设置生效的时候，会为每个角色产生一个维度的独立拷贝。这能够提高在维度内存成本上的性能（因为不用在运行的时候连接）。

关于厂商模型化的技术总结。

总体上，这五种厂商模型化技术如下所示：

1.实现五个真实的维度，每个都对应一个类型。这增加了维度的数量，但这种方法速度很快，并且集合能够被预先计算。

2.在Item维度中实现五个属性层次。这减少了维度的复杂性以及厂商和Item维度之间的关联（这相比于第一种方法，更加清晰）。方法＃1和方法＃2一个共同的缺点在于交叉厂商的分析，因为，无论它是什么角色，那里没有一个成员，能够代表一个唯一的维度。

3.实现了一个在Item、Vendor和Vendor Type之间的多对多维度。这种发放速度更慢一些，但是概念上却更加清晰。它允许你无需改变元数据就能添加厂商角色的数目。这使得结构更有伸缩性，并且允许这种交叉厂商的分析。因为这种多对多度量组需要大量的查询时间，因此这是一种速度最慢的方法。

4.使用一个单独的Vendor维度，实现五个引用（或角色扮演）维度。这很容易完成，并且比头两个方法概念更加清晰。由于这种方法是在运行的时候完成的，因此这种方法的性能会受一点影响。并且，没有可以预先计算的真实集合。

5.实现方法＃4，并且将角色扮演维度再实物化。这实现起来和方法＃4一样容易，但每个维度成员都需要创建来形成。这意味着能够设计集合，并实现预先计算。由于没有单独的成员来表示一个唯一的厂商（无论它是什么角色），和＃1、＃2一样，方法＃4和方法＃5都有交叉厂商共有的缺点，然而由于已经存在基础的对象，方法＃4和＃5相比于方法＃1和＃2，更加直接。所有你要做的，只是在添加第六、第七个引用或者扮演角色维度。Vendor维度已经准备就绪。但所有的四种方法都需要改变元数据，这将影响到你现有的报表和MDX查询。只有方法＃3允许你在无需修改元素据的情况下，添加新的厂商类型。

在Project REAL中，我们实现的是方法＃2和＃3。我们捆绑了这两种方法，以至于终端用户能够能够权衡是更好的考虑性能还是更好的考虑分析功能，从而选择更适当的方法。

半可加减的存货度量

在Store和DC存活数据中有一个出名的问题。一般，当我们处理像销售总数和销售数量这种多维度的数据时，度量都时可加减的。为了得到WA地区的销售总数，你所要做的就是把所有城市的所有仓库的销售总数加起来。我们把这种计算叫做可加减的(Additive)。SQL Server 2000分析服务支持四种可加减的操作，分别是Sum, Count, Min和Max。这样，平均值就能通过Sum/Count来实现。

例如，图23中Sale Amt 列中的数据代表了Kirkland,WA仓库在某个时段的销售总数。Sum就是为计算Sales Amt的集合函数。

图23：Kirkland仓库销售和存货数据

注意，每周的销售总数是从每天的总数计算获得，每月的总数是从每周的总数上计算获得。因此我们称总销售是一个可加减的度量。这种方法对On-Hand Qty度量适用么？显然不行。库存的数量、订单数量看上去是一些半可加性的度量。这些需要汇总的值都是基于某个时间段的一个时间点，而不是基于总数，也不是基于最后的值（或者是一些其它基于半可加性的计算）。在这个例子中，在周六统计库存的详细信息。如果在月初，Kirkland仓库书本的数量是10000，库存需要在每周六的时候都保存相同的数量（10000）直到月底（我们假定每周中每售出一本书，都会在周五得到补充），那么我们在月底的时候，存货是40000（总数）还是10000（最后的存活）呢？

为了解决这个问题，我们使用了SQL Server 2005分析服务中一个新的半加性集合函数。由于我们已经具备时间维度，我们所需要做的仅仅是把集合函数从Sum改成LastNonEmpty，这样就能实现我们预期的功能。

由于SQL Server 2000分析服务仅支持可加减的度量，原来在Project REAL设计的复杂计算都必须手工的来完成。现在在SQL Server 2005分析服务中，已经支持半可加性的操作。我们所要做的只是改变集合函数。如图24所示。

图24：为度量指定集合函数，可以设置成可加性的(左图)或者为半可加性的(右图)

最佳实践：如果你使用半可加性的度量，请确认Cube包含的维度不超过一个。

使用半可加性的度量，有一些约束。首先，你必须有一个被标识为Time类型的维度用来使用半可加性度量。如图25所示。当它运行的时候，时间智能向导(Time Intelligence Wizard)将会合适的设置属性和维度类型属性。如果你通过标准的维度向导(Dimension Wizard)创建你自己的时间维度，你必须再手工的设置适当的类型属性。

LastNonEmpty函数不能计算所有类型的维度。它只能完成基于时间维度的计算。而且，在Cube中智能包含一个时间维度。如果在你的cube中包含多个时间维度，系统会找到第一个符合要求的去执行。这虽然不是一个必要的、优秀的、可预言的选择，但事实上，它就是这么做的。

结论

这份白皮书提供了一些关于SQL Server 2005分析服务(Analysis Services)的技术性讨论，主要是关于分析服务的设计和Rroject Real项目中的一些实际应用。我们回顾了许多很好的实践和资料，这些都是在Project REAL项目中积累下来的，并提供了各种不同类型对象的信息，例如数据源、数据源视图、维度、层次、属性、度量组和部分。

若要回顾用来同步关系型分割表模式和分析服务(Analysis Services )度量组分割表模式的SQL Server 2005集成服务（Integration Services）程序包，请参见附录A。

附录A：自动的分割表创建

Project REAL设计应用了大量的分割。产品系统中有超过220个极大的分割表。范例数据使用了125个以上的分割表，每个表中都只有几万条数据。完整的产品系统在每个分割表中大概会有1.8亿-2.0亿条记录。因为如此多的分割表，每当我们生成一个新的模式时，我们都需要创建一个新的分割表。

因此，就如同谚语所说，“当过程变的基本一致的时候，程序员就需要编写程序了”。

这个附录详细描述了一个SQL Server 2005 Integration Services中的BuildASPartition程序包，这个程序包是为在SQL Server 2005分析服务数据库中自动构建分析服务度量组分割表中而创建的。这个程序包能够同步关系型分割表模式和分析服务(Analysis Services )度量组分割表模式。它循环不断的检查关系型数据库，查找每周生成的实际分割表（通过使用一个表名称转换）。如果找到一个关系型表，它会检查在分析服务度量组中是否存在这个分割表（使用同样的表名称转换）。如果不存在，它会构建一个XMLA脚本，并执行这个脚本，最后生成一个分割表。

图26描述了这个程序包的结构。

图27描述了它们的变量以及默认值

图27：BuildASPartition程序包中的变量列表

下表列举了每个变量并解释了如何使用。

变量	角色
Mask	一个用来标识实际表分割表的TSQL LIKE片断。按周的分割表可以的值可以是‘vTbl_Fact_%_WE_%’。 注意：LIKE子句中的％符合是一个代表任意字符的通配符，例如： vTbl_Fact_Store_Sales_WE_2004_11_27 vTbl_Fact_Store_Inventory_WE_2004_07_10 vTbl_Fact_DC_Inventory_WE_2005_01_08
Table	包含一个表记录，作为“ForEach Partition”任务中 Loop的循环变量，遍历一个包含表的行集。
Tables	一个行集，包含了RDBMS连接的数据库中匹配了Mask变量的数据表。
XMLA_Script	一个由Partition Already Present?任务生成的XML脚本
IsNotPresent	一个由Partition Already Present?任务设置的布尔值。如果分割表已经存在，这个值是True，然后执行DDL任务；如果是False，则ForEach Partition检查另外一个表，重复这个过程。

程序包包含以下四个任务。

◆Get Partition List (一个Execute SQL任务)

这个任务将使用一个Mask变量，将它作为Transact-SQL LIKE 模式匹配，去查询在RDBMS数据库连接中定义的视图。执行的输出结果(行集)将绑定到Tables变量上。图28表示了如何在程序包编辑器中配置Get Partition List任务。

图28：Get Partition List任务属性

◆ForEach Partition (一个For Each Loop 任务)

这个任务将遍历所有的表，每遍历到行集中一个新的元素时，它将把表的名称赋给Table变量。图29表示如何在程序包编辑器中配置ForEach任务。

图29：ForEach任务属性

Partition Already There? (一个脚本任务)

这个脚本会由两个输出。当分割表不存在的时候，它设置IsNotPresent布尔值变量，并且将构造一个XMLA脚本，用来创建一个分割表（保存到XMLA Script变量中）。

关于这个脚本，需要注意以下几个有趣的地方：

◆它展示了如何引用AMO，并使用它来判断一个分析服务数据库中是否存在一个分割表。

◆它展示了如阿从一个连接从提取服务器和数据库库名称（因此，根据这个连接，这些名称能在后来的脚本中创建AMO命令）。

图30展示了在程序包编辑器中如何配置一个Partition Already There?任务。

图30：Partition Already There? 任务属性

Create Partition (一个Analysis Services Execute DDL任务)

这个任务将执行一个XMLA脚本，这个脚本是由Partition Already There?任务构建的。图31展示了在程序包编辑器中如何配置一个Create Partition任务。

图31：Create Partition任务属性

从上面的描述可以看出，在Partition Already There?任务和Create Partition任务之间存在一个优先约束。这个约束会确保只有在IsNotPresent布尔变量被Partition Already There? 任务中的脚本设置成True的时候才执行Excute DDL任务。为了查看这个优先约束，在程序包编辑器中双击这两个任务间的箭头。图32展示了在程序包编辑器中如何配置优先约束。

图32：优先约束配置

程序包使用如下两种连接对象。

◆AS 数据库

这个连接对象包括SQL Server 2005 分析服务数据库，在数据库库中如果找不到度量组分割表，系统会选中这个分割表，并创建它们。

◆RDBMS 数据库

这个连接对象包含了SQL Server 2005关系型数据库，也就是查找每周实际表的地方。

最后，我们想在评论以下集合的设计。在你运行完程序包后，你将看到那里并没有为了集合设计而设计的集合。一次为多个分割表创建集合确实很简单。我们已经实现一次创建几百个分割表。

首先，在Cube窗口中选择多个分割表，只需要按住Ctrl或者Shift，然后再选择即可。鼠标右键单击，运行Design Storage Wizard用来创建集合。向导可以在所有选中的分割表中定义集合。把这个操作应用于Store Inventory、Store Sales、DC Inventory各一遍，就完成工作了。

注意：这个程序包假定关系型数据库分割表模式是基于每周的数据的，并且是根据表名称来编码的，很容易根据这个例子来修改程序包使其符合其它模式的要求。简单的修改Get Partition List任务(或者将它扩展成一系列任务)，然后基于不同的模式构建一个Tables行集。只要这个Tables行集(不管它是怎么被构建的)在格式上是一致的，程序包的余下部分就能顺利的被重用。

下面的源代码就是Partition Already There?脚本的一个范例。

>' Microsoft Data Transformation Services (DTS) Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
' NOTE:
' In order to get AMO to be imported you must copy the following
' file from:
'　C:Program FilesMicrosoft SQL Server90SDKAssemblies
'　　　　　　　　　　　　　　　　　　　 Microsoft.AnalysisServices.dll
' to the folder
'　Microsoft.NETFrameworkv2.0.
' And then you must add a reference to the assembly in the project
Imports Microsoft.AnalysisServices
Public Class ScriptMain
Public Sub Main()
' Get Server and Database name from DTS connection object
Dim oConn As ConnectionManager
oConn = Dts.Connections("AS database")
Dim sServer As String = _
CStr(oConn.Properties("ServerName").GetValue(oConn))
Dim sDatabase As String = _
CStr(oConn.Properties("InitialCatalog").GetValue(oConn))
' By convention, we know that the database, data source and
' cube are the same name
Dim sDataSource As String = sDatabase
Dim sCube As String = sDatabase
Dim oTable As Variable = Dts.Variables("Table")
Dim sTable As String = CStr(oTable.Value)
Dim sPartition As String = GetPartition(sTable)
Dim sMeasureGroup As String = GetMeasureGroup(sTable)
' We have all of the information about the partition -- use AMO
' to see if it is present. Save in a variable for later reference
Dts.Variables("IsNotPresent").Value = _
Not IsPartitionThere(sServer, sDatabase, sCube, _
sMeasureGroup, sPartition)
' Generate and save the XMLA script (will be executed in a
' Execute Analysis Services DDL task later on). Save in a
' variable for later reference
Dim sXMLA As String = GenerateXMLAScript(sDatabase, sDataSource, _
sCube, sMeasureGroup, sPartition, sTable)
Dts.Variables("XMLA_Script").Value = sXMLA
MsgBox(sXMLA, MsgBoxStyle.OkOnly, "XMLA Script")
Dts.TaskResult = Dts.Results.Success
End Sub
Private Function GetMeasureGroup(ByVal sMG As String) As String
' All tables are in the format: vTbl_Fact__WE_YYYY_MM_DD
'　 e.g. vTbl_Fact_Store_Sales_WE_2003_12_27
' Measure group names (from this) are: , e.g. Store Sales
'　 and the name may have embedded undorscores (_) which need to
'　 be replaced with spaces
Dim i_WE_location As Integer = InStr(sMG, "_WE_")
Dim i_FACT_location As Integer = Len("vTbl_Fact_")
sMG = Left(sMG, i_WE_location - 1)
sMG = Right(sMG, (Len(sMG) - i_FACT_location))
sMG = Replace(sMG, "_", " ")
Return sMG
End Function
Private Function GetPartition(ByVal sPart As String) As String
' All tables are in the format: vTbl_Fact__WE_YYYY_MM_DD
'　 e.g. vTbl_Fact_Store_Sales_WE_2003_12_27
' Partition names (from this) are:　WE YYYY MM DD,
'　 e.g. Store Sales WE 2003 12 27
'　 and the name may have embedded undorscores (_) which need to
'　 be replaced with spaces
Dim i_FACT_location As Integer = Len("vTbl_Fact_")
sPart = Right(sPart, (Len(sPart) - i_FACT_location))
sPart = Replace(sPart, "_", " ")
Return sPart
End Function
Public Function GenerateXMLAScript(ByVal sDatabase As String, _
ByVal sDataSource As String, ByVal sCube As String, _
ByVal sMeasureGroup As String, ByVal sPartition As String, _
ByVal sTable As String) As String
Dim sX As String
sX = ""
'
' XMLA script is missing the following clauses:
' 1) annotations (not needed)
' 2) physical storage, e.g. proactive caching settings, etc.
'　　　　Note: not needed since default values used
' 3) linkage to aggregation designs (TBD) -- we need a management
'　　　　utility to control aggregation usage
'
' Note: because of quoting rules conflict between VB.NET and
'　　　 XMLA scripts, all double-quotes (") are replaced with
'　　　 uparrows (^)
'
sX = sX & "
sX = sX & "analysisservices/2003/engine^>" & vbCrLf
sX = sX & "　　" & vbCrLf
sX = sX & "　　　　" & sDatabase & _
"" & vbCrLf
sX = sX & "　　　　" & sCube & "" & vbCrLf
sX = sX & "　　　　" & sMeasureGroup & _
"" & vbCrLf
sX = sX & "　　" & vbCrLf
sX = sX & "　　" & vbCrLf
sX = sX & "　　　　
sX = sX & "2001/XMLSchema^ xmlns:xsi=^http://www.w3.org/2001/"
sX = sX & "XMLSchema-instance^>" & vbCrLf
sX = sX & "　　　　　　" & sPartition & "" & vbCrLf
sX = sX & "　　　　　　" & sPartition & "" & vbCrLf
sX = sX & "　　　　　　" & vbCrLf
sX = sX & "　　　　　　　　" & sDataSource & _
"" & vbCrLf
sX = sX & "　　　　　　　　dbo" & _
vbCrLf
sX = sX & "　　　　　　　　" & sTable & _
"" & vbCrLf
sX = sX & "　　　　　　" & vbCrLf
sX = sX & "　　　　" & vbCrLf
sX = sX & "　　" & vbCrLf
sX = sX & "" & vbCrLf
sX = sX & " " & vbCrLf
' replace all up-arrows with double-quotes
sX = Replace(sX, "^", """")
Return sX
End Function
Public Function IsPartitionThere(ByVal sServer As String, _
ByVal sDatabase As String, ByVal sCube As String, _
ByVal sMeasureGroup As String, _
ByVal sPartition As String) As Boolean
' By default, we will assume that it isn't there
' Only if we get all of the way to the end and everything is
' found, will we set it true
Dim bIsPartitionThere As Boolean = False
Dim oServer As New Microsoft.AnalysisServices.Server
' connect to the server and start scanning down the
' object hierarchy
oServer.Connect(sServer)
Dim oDB As Database = oServer.Databases.FindByName(sDatabase)
If oDB Is Nothing Then
MsgBox("Did not find expected database: " & sDatabase, _
MsgBoxStyle.OkOnly, "Error looking for partition")
GoTo Done
Else
Dim oCube As Cube = oDB.Cubes.FindByName(sCube)
If oCube Is Nothing Then
MsgBox("Did not find expected cube: " & sCube, _
MsgBoxStyle.OkOnly, "Error looking for partition")
GoTo Done
Else
Dim oMG As MeasureGroup = _
oCube.MeasureGroups.FindByName(sMeasureGroup)
If oMG Is Nothing Then
MsgBox("Did not find expected Measure Group: " & _
sMeasureGroup, _
MsgBoxStyle.OkOnly, _
"Error looking for partition")
GoTo Done
Else
'-- This is the real test -- to see if the partition
'　 is really there
Dim oPart As Partition = _
oMG.Partitions.FindByName(sPartition)
If Not oPart Is Nothing Then
' This is the only place to set the value to TRUE
bIsPartitionThere = True
End If
End If
End If
End If
Done:
oServer.Disconnect() ' disconnect from the server -- we are done
NoConnection:
Return bIsPartitionThere
End Function
End Class