在sqlserveranalysisservices中选择表格或多维建模.docx
- 文档编号:24991442
- 上传时间:2023-06-03
- 格式:DOCX
- 页数:27
- 大小:129.83KB
在sqlserveranalysisservices中选择表格或多维建模.docx
《在sqlserveranalysisservices中选择表格或多维建模.docx》由会员分享,可在线阅读,更多相关《在sqlserveranalysisservices中选择表格或多维建模.docx(27页珍藏版)》请在冰豆网上搜索。
在sqlserveranalysisservices中选择表格或多维建模
在SQLServer2012AnalysisServices中选择表格或多维建模体验
Microsoft商业智能技术文章
作者
HitachiConsulting:
LizVitt-作者
ScottCameron-作者
HilaryFeier-审校
Microsoft:
T.K.Anand-审校
AshviniSharma-审校
发布时间:
2012年5月
适用范围:
SQLServer2012AnalysisServices
摘要:
本白皮书提供的实用指南可帮助BI专业人员和决策者决定,您的下一个BI解决方案最合适采用SQLServer2012AnalysisServices表格建模,还是SQLServer2012AnalysisServices多维建模。
版权信息
本文档按“原样”提供。
本文档中的信息和表达的观点(包括URL和其他Internet网站引用)如有更改,恕不另行通知。
您应承担使用本文档所带来的风险。
本文档不向您提供对任何Microsoft产品中的任何知识产权的任何法律权利。
您可以出于内部参考目的复制和使用本文档。
©2012MicrosoftCorporation。
保留所有权利。
简介
数据建模是BI专业人员多年来一直不断实践的领域,它的目标只有一个:
将不同的数据整理到一个分析模型中,从而快速高效地支持业务的报告和分析需要。
这些年来,数据建模随着新技术和工具的推出而不断发展,而组织在如何以无缝和连贯的方式有效地混合建模范例方面面临着越来越多的挑战,不仅要能够满足各种分析需要,还要能够针对业务提供良好的分析体验。
在SQLServer2012版本中,Microsoft引入BI语义模型(BISM),不仅实现了建模的目标,而且还解决了组织所面临的挑战。
这个单一模型可以支持广泛的报告和分析功能,同时在后台混合两种AnalysisServices建模体验:
∙多维建模-这种建模方法随SQLServer7.0OLAP服务引入,并一直延续至SQLServer2012AnalysisServices。
它使BI专业人员能够使用传统的联机分析处理(OLAP)创建复杂的多维数据集。
∙表格建模-这种建模方法随PowerPivotforMicrosoftExcel2010引入,它为业务和数据分析人员提供了自助数据建模功能。
对于多年来一直在桌面效率工具(如Excel或MicrosoftAccess)中处理数据的许多用户而言,表格建模体验更易于接受。
在SQLServer2012中,表格建模已扩展为支持BI专业人员在AnalysisServices中创建表格模型,或将表格模型从PowerPivot导入到AnalysisServices中。
请注意,PowerPivot模型无法导入到AnalysisServices多维模型中。
本白皮书的目标是提供实用指南,以帮助您确定哪种SQLServer2012AnalysisServices建模体验(表格建模或多维建模)最符合您的下一个BI解决方案的需要。
本白皮书中的产品说明和建议基于2012年3月发布的SQLServer2012AnalysisServices。
随着AnalysisServices多维建模和表格建模在SQLServer的将来版本中不断改进,产品功能和建议可能发生变化。
BISM建模入门知识
在深入探讨多维建模与表格建模之间的详细差异之前,我们首先简要介绍一下SQLServer2012AnalysisServices提供的每种BISM建模体验的基础知识。
多维建模
多维建模的核心是基于关系数据库中包含的数据创建多维数据集(由度量值和维度组成)。
若要使用此范例,AnalysisServices服务器必须配置为在多维模式(默认设置)下运行。
在此模式下,OLAP引擎使用多维模型预聚合大量数据,以支持快速的查询响应时间。
OLAP引擎可以通过多维OLAP(MOLAP)存储将这些聚合存储在磁盘上,或通过关系OLAP(ROLAP)存储将它们存储在关系数据库中。
多维建模的主要特征包括:
∙丰富的数据模型:
SQLServer2012AnalysisServices的多维模型已是推出的第六个版本,它提供了广泛的功能,可根据企业数据仓库中常见的简单和复杂数据集为度量值和维度建模。
更复杂的数据集通常包括诸如多对多关系、父子层次结构和本地化等高级功能。
多维模型本身自带了这些功能。
∙复杂的分析:
多维模型还提供一种称为多维表达式(MDX)的高级计算和查询语言。
使用MDX,您可以创建复杂的业务逻辑和计算,它们可以在多维空间中的任何位置运行,以实现财务分配、时序计算或半累加性度量。
尽管全面的数据建模和复杂的分析是多维建模的重要优点,但常常随之带来一些负面影响:
开发周期较长以及不能快速适应不断变化的业务状况。
此外,多维体验通常需要很高的建模和MDX技能水平。
表格建模
表格建模是将数据整理到相关的表中。
如果您要使用表格建模,则必须将AnalysisServices配置为在表格模式下运行。
在表格模式中,可以使用xVelocity(以前称为Vertipaq)内存中引擎将表格数据加载到内存中以实现快速的查询响应,也可以使用DirectQuery将查询传递到源数据库以利用其查询处理功能。
表格建模的主要特征包括:
∙熟悉程度:
对于许多用户而言,他们日常处理的数据大多存储在关系数据库、Excel或Access表中,因此处理起表格数据来驾轻就熟。
另外,可以使用数据分析表达式(DAX)(一种被认为是Excel公式语言扩展的公式语言)来编写计算。
因此,生成表格模型所需的技能与生成多维模型所需的技能相比,前者更常用或更容易学会。
∙灵活性:
因为不需要将数据严格地整理到度量值和维度中,所以,与多维模型相比,表格建模可以加快开发周期,所需的前期数据准备工作较少,设计严格性也较低。
当需要根据不断变化的业务要求更新关系和计算时,这种数据体系结构还可以很方便地对数据建模做出相应的变更。
尽管熟悉程度和灵活性是表格建模的关键优点,但它也存在缺点。
例如,表格建模可能不适合于那些具有高度复杂的数据集或需要复杂业务逻辑的解决方案。
虽然DAX语言的用户通常可以通过创建DAX公式来提供表格模型中原本未提供的分析功能,但是,在这些情况下,使用多维建模本身自带的高级功能可能更适合且更高效。
BISM客户端分析工具
无论您选择多维建模还是表格建模,都请务必注意,您都可以使用能够生成MDX或DAX的客户端工具来查询模型。
例如,Excel和SQLServerReportingServices就是可以使用MDX生成查询的客户端工具,而PowerView是可使用DAX生成查询的客户端工具。
本指南有两种例外情况。
∙PowerView是一个可提供交互式数据浏览和可视化的工具,是用于MicrosoftSharePointServer2010EnterpriseEdition的SQLServer2012ReportingServices外接程序的一项功能。
如果您要使用PowerView或任何其他使用DAX查询BISM的分析客户端,则需要使用表格模型。
SQLServer的未来版本可能会提供使用DAX来查询多维模型的功能,这样,您就可以通过诸如PowerView之类的客户端工具来访问这些模型了。
∙已配置为使用DirectQuery的表格模型要求使用可生成DAX查询的客户端工具(如PowerView)。
SQLServer的未来版本可能会允许配置为使用DirectQuery的表格模型接受MDX查询。
数据模型
您的数据模型的特征是您选择建模体验时的核心注意事项。
数据关系
任何数据模型的基本要求都是要正确地表示该模型中的数据元素如何相互关联和连接,这非常类似于拼图游戏的各个拼图碎片。
表格模型和多维模型都要求您在源数据表之间定义关系。
数据建模中的常见关系是一对多关系、多对多关系和引用关系。
一对多关系
在一对多关系中,一个表中的一个记录与另一个表中的多个记录相关。
一对多关系的示例是一个客户具有多个销售订单。
表格数据模型和多维数据模型本身就可以处理一对多关系。
多对多关系
在多对多关系中,一个表中的许多记录与第二个表中的许多记录相关。
例如,单个客户与多个销售订单具有一对多关系;但每个客户可以分类为一个或多个客户概况(如体育爱好者、休闲游戏玩家和健身专家)。
按客户概况来分析订单就是一个多对多关系,其中可能会出现重复计数:
当汇总对应于各客户概况的订单来获得订单总数时,如果某位订购一辆自行车的客户既是体育爱好者又是健身专家,则此订单很容易被计入两次。
通常,多对多关系通过使用桥接表或中间表分解为两个一对多关系来进行管理,如图1中所示。
图1:
多对多示例
在多维模型中,通过标识桥接表,然后将该桥接表与模型中的其他表相关,可以直接在此数据模型中定义并生成多对多关系。
当聚合时,AnalysisServices将应用非重复汇总,以确保数据总计正确汇总,而不会错误地放大。
SQLServer2012AnalysisServices表格模型不支持多对多关系的定义。
但是,您可以使用DAX语言来创建处理多对多关系的公式。
引用关系
数据模型可能包含与多个实体相关的一组公共属性。
例如,与客户、供应商和商店相关的地理属性。
在多维建模中,您必须创建一个包含公共属性的维度,然后创建指向每个相关维度的引用维度关系。
在表格建模中,不需要创建引用关系,只需在包含公共属性的表与包含相关实体的表之间创建关系。
层次结构
层次结构将数据分类到树结构中,以便于进行深化分析。
标准层次结构
标准层次结构由来自源数据中各列的有序级别组成。
例如,产品层次结构可以将产品整理到子类别中,然后进一步整理到类别中。
在这种情况下,您将具有含三个级别的层次结构,其中每个级别都来自源数据的一个单独列。
表格模型和维度模型都支持简单的层次结构(例如此处介绍的产品层次结构)。
请注意,在多维模型中有一个创建属性关系的附加步骤,此步骤显式标识每个维度中各属性之间的一对多关系。
强烈建议定义属性关系,因为借助这些关系可以更高效地设计预先计算的聚合,并且MDX语义依赖于属性关系。
表格建模更简单,因为您不需要创建属性关系。
表格模型不预先计算聚合,并且DAX语义不依赖于标识属性之间的一对多关系,因此在表格建模中,没有与多维建模的属性关系等效的关系。
不规则层次结构
当层次结构树中缺少给定的数据元素时,将出现不规则的层次结构。
例如,如果没有为产品分配子类别,但分配了产品类别,则将出现不规则的产品层次结构。
在这些情况下,您可以选择在树中隐藏空隙(不显示空隙),以便于进行深化分析。
多维模型本身支持不规则的层次结构;但表格模型不支持此功能。
父子层次结构
父子层次结构提供了一种更复杂的层次结构设计。
父子层次结构中的分支并不全都具有相同的级别数。
例如,员工与经理之间的父子关系可能生成这样的层次结构:
一些经理只具有直接下属,而其他经理所具有的每个直接下属也有其自己的直接下属。
这种层次结构是通过在源数据表的两列之间创建关系来建模的,如图2所示。
父子源数据
父子层次结构树
图2-父子层次结构
通过多维模型本身具有的功能,您可以根据源数据中的关系来定义和生成父子层次结构。
在表格模型中,您可以利用DAX函数来创建在计算中导航并使用父子结构的公式。
有关如何在表格模型中使用父子层次结构的详细信息,请参阅了解DAX中父子层次结构的函数((v=sql.110).aspx)。
其他建模功能
除数据关系和层次结构外,还有其他建模功能可以帮助您选择最佳的建模体验:
∙透视:
通过透视,您可以定义数据模型的子集,以简化最终用户的浏览体验。
多维模型和表格模型中都提供了透视。
∙翻译:
借助于翻译,多维模型可以用计算机的本地化设置所指定的语言来显示维度、属性、度量值、计算成员以及其他对象名称和维度成员值。
启用此功能要求模型开发人员提供已翻译的对象名称,并引用源数据中包含已翻译的维度成员值的列。
表格模型不提供此功能。
∙操作:
通过操作,最终用户可以运行ReportingServices报表、导航到URL或根据发生操作的单元的上下文启动外部操作。
例如,通过使用操作,最终用户可以启动一个网页,该网页显示的公司产品目录会自动筛选为用户浏览过的一个或多个产品。
多维模型本身就支持操作,许多客户端工具(如Excel和ReportingServices)也允许用户执行操作。
在SQLServer2012中,不支持使用SQLServerDataTools在表格模型中创建操作。
∙钻取:
钻取使您能够导航到模型中存储的详细数据。
多维建模和表格建模都提供了钻取功能。
多维模型还可让您创建钻取操作,以便可以通过指定钻取操作返回的列以及启用此操作的多维数据集空间来自定义钻取体验。
∙写回:
写回是预算编制和预测应用程序通常所需的一项功能。
在这些情况下,业务用户通常需要执行“假设”分析,在分析中更改和更新模型中的数据值,然后发布以供他人查看。
多维模型本身支持数据写回功能。
在SQLServer2012中,表格模型不支持此功能。
业务逻辑
业务逻辑可以为任何数据模型增加巨大的价值,它借助于可增强数据的计算和业务规则,为最终用户提供分析功能。
表格建模和多维建模都提供了丰富的公式语言以实现业务逻辑。
多维模型利用MDX,而表格模型利用DAX。
在深入探讨每个范例的一些高级业务逻辑方案之前,需要首先对在多维建模和表格建模中如何使用行级转换、聚合值和计算来应用业务逻辑有一个基本的了解。
行级转换
您可能需要执行源数据中未提供的计算和数据转换。
例如,源数据可能具有“销售额”列和“每日汇率”列,但缺少换算为外币的销售额,或者源数据可能具有“员工姓氏”和“员工名字”,但缺少串联起来的“员工全名”。
请注意,在这些示例中,计算或数据操作必须在行级、未聚合的数据上发生。
在多维建模中,必须先对未聚合的数据执行行级转换,然后才能将数据加载到模型中;或者必须先执行行级转换,然后才能查询模型。
您可以在数据源系统中应用转换,或编写一个在AnalysisServices查询源数据库时应用的SQL表达式,以转换维度属性(如员工姓名)。
数值数据的行级转换可以通过使用SQL表达式在数据加载到AnalysisServices之前执行;或者,可以使用MDX表达式以及Scope语句将计算限制在行级范围内,从而应用行级转换。
如果在加载数据前应用转换,AnalysisServices可以预聚合数值。
如果使用Scope语句应用转换,则将在查询时发生聚合。
在表格建模中,行级转换是使用计算列创建的。
当您创建计算列时,您需要将该列添加到模型的特定表中,然后使用DAX公式来定义该列的值。
然后,针对该表中的每个记录计算此公式,并且将公式加载到内存中,就像模型中的任何其他列一样。
借助于这种灵活性,您可以基于您的具体分析要求直接在表格模型中增强您的数据,而且不再迫切需要对能够或者不能容纳您的更改的上游数据源进行及时调整。
计算列提供了一种非常方便的方法,使您能够创建和保留在对数据进行聚合之前必须在详细级别执行的计算。
尽管这种灵活性很强大,但请注意,计算列并不用于执行大量数据清理或数据转换(例如提取、转换和加载(ETL)过程)。
聚合值
在多维建模中,聚合值是通过使用度量值来创建的。
AnalysisServicesOLAP引擎使用聚合函数(如SUM、COUNT、MIN、MAX和DISTINCTCOUNT以及其他函数)来预聚合多维数据集的度量值。
在多维数据集处理过程中,每个度量值都可以在所有层次结构中自下而上进行聚合。
由于这种处理发生在最终用户进行分析之前,因此,预聚合的度量值可以为查询性能提供巨大优势。
当您在多维数据集中创建度量值时,多维数据集度量值与源数据中的数值列之间存在一对一关系。
因此,在多维建模中,当您需要对满足以下条件的数值数据元素执行自下而上的聚合时,需使用度量值:
(1)这些元素位于您的源数据的最低详细级别;
(2)这些元素要求使用其中一个本机多维数据集聚合函数进行汇总。
在表格建模中,您也可以使用度量值来创建聚合值。
可以通过选择一列,然后指定聚合函数(SUM、COUNT、DISTINCTCOUNT、MIN、MAX或AVERAGE),以创建度量值;也可以编写DAX表达式来指定要用来对度量值进行聚合的函数。
在表格建模中,行级数据存储在内存中,并在查询时计算聚合。
如下一部分所述,在表格建模中,度量值还可以用来应用计算。
这可以包含基于多个聚合列的计算。
计算
在多维建模中,使用MDX来创建计算。
MDX既是表达式语言,也是查询语言,它具有的函数本身就理解多维数据集的维度、层次结构、属性关系和度量值的设计。
因此,您可以创建简洁且强大的表达式,在多个数据上下文中应用业务逻辑。
您可以在多维数据集的计算脚本中创建并存储MDX计算,从而可以控制应用逻辑的顺序。
计算成员是最常见的MDX计算。
计算成员是在对数据执行预聚合后,在查询时进行计算的。
可以在任何维度中创建计算成员。
当在度量值维度中创建计算成员时,计算成员通常称为“计算度量值”。
计算成员可以非常简单,只包含基本的数学运算,如单位销售额(销售额/单位)或每个人的支出(支出/总人数);也可以非常复杂,需要应用特定的业务规则,如滚动三期平均销售额或年度利润。
例如,如果要计算当前时间段的销售额并将其表示为父时间段的百分比,可以使用以下MDX计算。
[Measures].[SalesAmount]/([Date].[Calendar].CurrentMember.Parent,[Measures].[SalesAmount])
除度量值维度之外,在其他维度中创建计算成员会向该维度中的某个属性添加一个值。
例如,如果您有一个包含颜色列表的维度属性,您可能希望添加一个计算成员PrimaryColors,用于汇总红、绿和蓝这三种颜色的值。
在表格建模中,创建度量值类似于在多维模型的度量值维度中创建一个计算成员。
在表格建模中,您无法向表中的列添加值,因此,表格建模不支持像在多维模型中的非度量值维度中创建计算成员那样的等效功能。
范围分配不仅比计算度量值更高级,而且功能也更强大。
正如前面的“行级转换”部分所述,您可以使用Scope语句来将计算限制在行级范围内。
但是,也可以使用Scope语句指定您要应用计算的任何多维数据集单元范围。
范围分配在查询之前进行编译,它使AnalysisServices能够在查询计算时提供优化的执行路径。
在给定强度的情况下,范围分配不仅能够执行多个计算度量值的工作,还能够更高效地完成工作。
例如,在预算编制解决方案中,您希望将东部区域下一年的预算指定为今年预算的90%,将西部区域的新预算指定为今年预算的75%,将南部区域的新预算指定为今年预算的105%,另外,将北部区域的新预算指定为与今年的预算相同。
在本示例中,不需要使用包含嵌套IF语句的一个复杂计算度量值,也不需要使用多个计算度量值将每个预算方案分隔开来,而是可以使用范围分配来有效地在区域级别应用这些比率,然后聚合数据总计。
例如,如果您希望使用每日汇率将销售额转换为外币,则可以使用以下MDX表达式:
Scope([Date].[Date]);
This=[Measures].[SalesAmount]*[Measures].[DailyFXRate];
EndScope;
在表格建模中,您可以使用DAX来创建计算。
如前面所述,在表格建模中,您可以通过创建计算列来应用行级计算,也可以在通过编写DAX表达式创建度量值的时候应用计算。
因为您显式使用DAX行级函数和聚合函数的组合,所以表格模型中的度量值是非常灵活的。
您可以应用行级函数,然后应用聚合函数,以便您的度量值在聚合之前应用计算;或者您可以先应用聚合函数,然后应用行级函数,以便您的度量值在聚合之后应用计算。
DAX公式可以在不同的数据上下文(而不仅仅是Excel工作表或数据透视表的当前视图)中,使用一组特殊的函数(称为FILTER函数)动态地计算公式。
在最广泛的意义上,这些函数对于AnalysisServices范围分配具有类似的作用,因为它们使您能够针对特定的行集来定义和执行计算。
例如,可以使用FILTER函数处理上述的预算编制示例。
业务逻辑方案
既然您已经很好地了解如何在MDX和DAX中创建和应用基本业务逻辑,那么可以考虑以下计算方案,以比较和对比表格建模体验与多维建模体验。
层次结构逻辑
如前所述,层次结构为业务用户提供了一种在数据分析过程中浅化和深化的方法。
在某些情况下,创建可以导航层次结构的计算非常有用。
例如,考虑一个产品维度,其中具有“产品类别”、“产品子类别”和“产品”。
对于层次结构中的每个级别,您要添加一个计算,以衡量每个级别的成员对父项的总销售额的贡献有多大。
考虑到此计算必须导航层次结构才能返回所需的值,所以称为“父项百分比”计算。
MDX和DAX都提供了相应的函数来处理整理到层次结构中的数据,并且可以创建类似父项百分比这样的计算;但MDX函数通常更简单且更易于使用。
例如,在MDX中,这是在产品维度中提供父项百分比的表达式。
[Measures].[SalesAmount]/
([Product].[ProductCategories].CurrentMember.Parent,[Measures].[SalesAmount])
要使用DAX创建相同的父项百分比计算,则需要以下更复杂的表达式。
IF(
ISFILTERED(Product[Product])
[Sales]/CALCULATE([Sales],ALL(Product[Product]))
IF(
ISFILTERED(Product[Subcategory])
[Sales]/CALCULATE([Sales],ALL(Product[Subcategory]))
1
)
)
自定义汇总
尽管统一的数据汇总在许多情况下均适用,但在其他一些情况下,您可能希望对数据汇总的方式进行更精细的控制。
这种情况的一个示例是财务模型:
您具有一个科目表(通常为父子格式),而每个科目都要求特定的汇总逻辑。
如下所示,“毛利”的计算方法是“净销售额”减去“销售成本总计”,而“运营利润”的计算方法是“毛利”减去“运营开支”。
多维模型不仅本身支持父子层次结构,而且它们还提供内置的帐户智能,这使您能够在帐户级别轻松地应用驱动数据汇总的一元运算符和MDX公式。
在表格模型中,父子或帐户智能不是内置的,但您可以使用计算列和度量值的组合来生成父子层次结构并应用自定义汇总,以生成您自己的解决方案。
半累加性度量值
一般来说,半累加性度量值是那些在所有维度(日期除外)中统一进行聚合的度量值。
半累加性度量值的示例包括期初余额和期末余额。
对于这些度量值,您要应用特殊逻辑,以便按时间段对数据进行正确
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sqlserveranalysisservices 选择 表格 多维 建模