在sqlserveranalysisservices中选择表格或多维建模

在 SQL Server 2012 Analysis Services 中选择表格或多维建模体验
Microsoft 商业智能技术文章
作者
Hitachi Consulting：
Liz Vitt －作者
Scott Cameron －作者
Hilary Feier －审校
Microsoft：
T.K.Anand －审校
Ashvini Sharma －审校
发布时间：2012 年 5 月
适用范围：SQL Server 2012 Analysis Services
摘要：本白皮书提供的实用指南可帮助 BI 专业人员和决策者决定，您的下一个 BI 解决方案最合适采用 SQL Server 2012 Analysis Services 表格建模，还是 SQL Server 2012 Analysis Services 多维建模。

1 / 21
版权信息
本文档按“原样”提供。

本文档中的信息和表达的观点（包括 URL 和其他 Internet 网站引用）如有更改，恕不另行通知。

您应承担使用本文档所带来的风险。

本文档不向您提供对任何 Microsoft 产品中的任何知识产权的任何法律权利。

您可以出于内部参考目的复制和使用本文档。

© 2012 Microsoft Corporation。

保留所有权利。

目录
简介 (3)
BISM 建模入门知识 (3)
多维建模 (3)
表格建模 (4)
BISM 客户端分析工具 (4)
数据模型 (6)
数据关系 (6)
一对多关系 (6)
多对多关系 (6)
引用关系 (7)
层次结构 (7)
标准层次结构 (7)
不规则层次结构 (7)
父子层次结构 (7)
其他建模功能 (8)
业务逻辑 (8)
行级转换 (8)
聚合值 (9)
计算 (9)
业务逻辑方案 (10)
层次结构逻辑 (10)
自定义汇总 (11)
半累加性度量值 (11)
时间智能 (12)
KPI (12)
货币换算 (12)
命名集 (12)
数据访问和存储 (13)
性能和可伸缩性 (13)
多维模型 (13)
表格模型 (14)
可编程性 (15)
安全性 (15)
行级/属性级安全性 (15)
动态安全性 (16)
单元级和高级安全性 (16)
总结 (17)
有关详细信息 (21)
简介
数据建模是 BI 专业人员多年来一直不断实践的领域，它的目标只有一个：将不同的数据整理到一个分析模型中，从而快速高效地支持业务的报告和分析需要。

这些年来，数据建模随着新技术和工具的推出而不断发展，而组织在如何以无缝和连贯的方式有效地混合建模范例方面面临着越来越多的挑战，不仅要能够满足各种分析需要，还要能够针对业务提供良好的分析体验。

在 SQL Server 2012 版本中，Microsoft 引入 BI 语义模型 (BISM)，不仅实现了建模的目标，而且还解决了组织所面临的挑战。

这个单一模型可以支持广泛的报告和分析功能，同时在后台混合两种 Analysis Services 建模体验：
多维建模－这种建模方法随 SQL Server 7.0 OLAP 服务引入，并一直延续至 SQL Server 2012 Analysis Services。

它使 BI 专业人员能够使用传统的联机分析处理
(OLAP) 创建复杂的多维数据集。

表格建模－这种建模方法随 PowerPivot for Microsoft Excel 2010 引入，它为业务和数据分析人员提供了自助数据建模功能。

对于多年来一直在桌面效率工具（如Excel 或 Microsoft Access）中处理数据的许多用户而言，表格建模体验更易于接受。

在 SQL Server 2012 中，表格建模已扩展为支持 BI 专业人员在 Analysis
Services 中创建表格模型，或将表格模型从 PowerPivot 导入到 Analysis Services 中。

请注意，PowerPivot 模型无法导入到 Analysis Services 多维模型中。

本白皮书的目标是提供实用指南，以帮助您确定哪种 SQL Server 2012 Analysis Services 建模体验（表格建模或多维建模）最符合您的下一个 BI 解决方案的需要。

本白皮书中的产品说明和建议基于 2012 年 3 月发布的 SQL Server 2012 Analysis Services。

随着Analysis Services 多维建模和表格建模在 SQL Server 的将来版本中不断改进，产品功能和建议可能发生变化。

BISM 建模入门知识
在深入探讨多维建模与表格建模之间的详细差异之前，我们首先简要介绍一下 SQL Server 2012 Analysis Services 提供的每种 BISM 建模体验的基础知识。

多维建模
多维建模的核心是基于关系数据库中包含的数据创建多维数据集（由度量值和维度组成）。

若要使用此范例，Analysis Services 服务器必须配置为在多维模式（默认设置）下运行。

在此模式下，OLAP 引擎使用多维模型预聚合大量数据，以支持快速的查询响应时间。

OLAP 引擎可以通过多维 OLAP (MOLAP) 存储将这些聚合存储在磁盘上，或通过关系 OLAP (ROLAP) 存储将它们存储在关系数据库中。

多维建模的主要特征包括：
丰富的数据模型：SQL Server 2012 Analysis Services 的多维模型已是推出的第六个版本，它提供了广泛的功能，可根据企业数据仓库中常见的简单和复杂数据集为度量值和维度建模。

更复杂的数据集通常包括诸如多对多关系、父子层次结构和本地化等高级功能。

多维模型本身自带了这些功能。

复杂的分析：多维模型还提供一种称为多维表达式 (MDX) 的高级计算和查询语言。

使用 MDX，您可以创建复杂的业务逻辑和计算，它们可以在多维空间中的任何位置运行，以实现财务分配、时序计算或半累加性度量。

尽管全面的数据建模和复杂的分析是多维建模的重要优点，但常常随之带来一些负面影响：开发周期较长以及不能快速适应不断变化的业务状况。

此外，多维体验通常需要很高的建模和 MDX 技能水平。

表格建模
表格建模是将数据整理到相关的表中。

如果您要使用表格建模，则必须将 Analysis Services 配置为在表格模式下运行。

在表格模式中，可以使用 xVelocity（以前称为Vertipaq）内存中引擎将表格数据加载到内存中以实现快速的查询响应，也可以使用DirectQuery 将查询传递到源数据库以利用其查询处理功能。

表格建模的主要特征包括：
熟悉程度：对于许多用户而言，他们日常处理的数据大多存储在关系数据库、Excel 或 Access 表中，因此处理起表格数据来驾轻就熟。

另外，可以使用数据分析表达式(DAX)（一种被认为是 Excel 公式语言扩展的公式语言）来编写计算。

因此，生成表格模型所需的技能与生成多维模型所需的技能相比，前者更常用或更容易学会。

灵活性：因为不需要将数据严格地整理到度量值和维度中，所以，与多维模型相比，表格建模可以加快开发周期，所需的前期数据准备工作较少，设计严格性也较低。

当需要根据不断变化的业务要求更新关系和计算时，这种数据体系结构还可以很方便地对数据建模做出相应的变更。

尽管熟悉程度和灵活性是表格建模的关键优点，但它也存在缺点。

例如，表格建模可能不适合于那些具有高度复杂的数据集或需要复杂业务逻辑的解决方案。

虽然 DAX 语言的用户通常可以通过创建 DAX 公式来提供表格模型中原本未提供的分析功能，但是，在这些情况下，使用多维建模本身自带的高级功能可能更适合且更高效。

BISM 客户端分析工具
无论您选择多维建模还是表格建模，都请务必注意，您都可以使用能够生成 MDX 或DAX 的客户端工具来查询模型。

例如，Excel 和 SQL Server Reporting Services 就是可以使用 MDX 生成查询的客户端工具，而 Power View 是可使用 DAX 生成查询的客户端工具。

本指南有两种例外情况。

Power View 是一个可提供交互式数据浏览和可视化的工具，是用于 Microsoft
SharePoint Server 2010 Enterprise Edition 的 SQL Server 2012 Reporting Services 外接程序的一项功能。

如果您要使用 Power View 或任何其他使用 DAX 查询
BISM 的分析客户端，则需要使用表格模型。

SQL Server 的未来版本可能会提供
使用 DAX 来查询多维模型的功能，这样，您就可以通过诸如 Power View 之类的客户端工具来访问这些模型了。

已配置为使用 DirectQuery 的表格模型要求使用可生成 DAX 查询的客户端工具
（如 Power View）。

SQL Server 的未来版本可能会允许配置为使用 DirectQuery
的表格模型接受 MDX 查询。

数据模型
您的数据模型的特征是您选择建模体验时的核心注意事项。

数据关系
任何数据模型的基本要求都是要正确地表示该模型中的数据元素如何相互关联和连接，这非常类似于拼图游戏的各个拼图碎片。

表格模型和多维模型都要求您在源数据表之间定义关系。

数据建模中的常见关系是一对多关系、多对多关系和引用关系。

一对多关系
在一对多关系中，一个表中的一个记录与另一个表中的多个记录相关。

一对多关系的示例是一个客户具有多个销售订单。

表格数据模型和多维数据模型本身就可以处理一对多关系。

多对多关系
在多对多关系中，一个表中的许多记录与第二个表中的许多记录相关。

例如，单个客户与多个销售订单具有一对多关系；但每个客户可以分类为一个或多个客户概况（如体育爱好者、休闲游戏玩家和健身专家）。

按客户概况来分析订单就是一个多对多关系，其中可能会出现重复计数：当汇总对应于各客户概况的订单来获得订单总数时，如果某位订购一辆自行车的客户既是体育爱好者又是健身专家，则此订单很容易被计入两次。

通常，多对多关系通过使用桥接表或中间表分解为两个一对多关系来进行管理，如图 1 中所示。

图 1
：多对多示例
在多维模型中，通过标识桥接表，然后将该桥接表与模型中的其他表相关，可以直接在此数据模型中定义并生成多对多关系。

当聚合时，Analysis Services
将应用非重复汇总，以确保数据总计正确汇总，而不会错误地放大。

SQL Server 2012 Analysis Services 表格模型不支持多对多关系的定义。

但是，您可以使用 DAX 语言来创建处理多对多关系的公式。

桥接/中间表
（用于指定客户概况）
客户表 销售订单表
引用关系
数据模型可能包含与多个实体相关的一组公共属性。

例如，与客户、供应商和商店相关的地理属性。

在多维建模中，您必须创建一个包含公共属性的维度，然后创建指向每个相关维度的引用维度关系。

在表格建模中，不需要创建引用关系，只需在包含公共属性的表与包含相关实体的表之间创建关系。

层次结构
层次结构将数据分类到树结构中，以便于进行深化分析。

标准层次结构
标准层次结构由来自源数据中各列的有序级别组成。

例如，产品层次结构可以将产品整理到子类别中，然后进一步整理到类别中。

在这种情况下，您将具有含三个级别的层次结构，其中每个级别都来自源数据的一个单独列。

表格模型和维度模型都支持简单的层次结构（例如此处介绍的产品层次结构）。

请注意，在多维模型中有一个创建属性关系的附加步骤，此步骤显式标识每个维度中各属性之间的一对多关系。

强烈建议定义属性关系，因为借助这些关系可以更高效地设计预先计算的聚合，并且 MDX 语义依赖于属性关系。

表格建模更简单，因为您不需要创建属性关系。

表格模型不预先计算聚合，并且 DAX 语义不依赖于标识属性之间的一对多关系，因此在表格建模中，没有与多维建模的属性关系等效的关系。

不规则层次结构
当层次结构树中缺少给定的数据元素时，将出现不规则的层次结构。

例如，如果没有为产品分配子类别，但分配了产品类别，则将出现不规则的产品层次结构。

在这些情况下，您可以选择在树中隐藏空隙（不显示空隙），以便于进行深化分析。

多维模型本身支持不规则的层次结构；但表格模型不支持此功能。

父子层次结构
父子层次结构提供了一种更复杂的层次结构设计。

父子层次结构中的分支并不全都具有相同的级别数。

例如，员工与经理之间的父子关系可能生成这样的层次结构：一些经理只具有直接下属，而其他经理所具有的每个直接下属也有其自己的直接下属。

这种层次结构是通过在源数据表的两列之间创建关系来建模的，如图 2 所示。

图 2 －父子层次结构
通过多维模型本身具有的功能，您可以根据源数据中的关系来定义和生成父子层次结构。

在表格模型中，您可以利用 DAX 函数来创建在计算中导航并使用父子结构的公式。

有关如何在表格模型中使用父子层次结构的详细信息，请参阅了解 DAX 中父子层次结构的函数 ((v=sql.110).aspx)。

父子源数据 父子层次结构树
Employee Manager Ken J. Sánchez
Ken J. Sánchez Brian S. Welcker
Ken J. Sánchez Amy E. Alberts
Brian S. Welcker Jae B. Pak
Amy E. Alberts David M. Bradley
Ken J. Sanchez Kevin F. Brown David M. Bradley
员工
经理
其他建模功能
除数据关系和层次结构外，还有其他建模功能可以帮助您选择最佳的建模体验：透视：通过透视，您可以定义数据模型的子集，以简化最终用户的浏览体验。

多
维模型和表格模型中都提供了透视。

翻译：借助于翻译，多维模型可以用计算机的本地化设置所指定的语言来显示维
度、属性、度量值、计算成员以及其他对象名称和维度成员值。

启用此功能要求
模型开发人员提供已翻译的对象名称，并引用源数据中包含已翻译的维度成员值
的列。

表格模型不提供此功能。

操作：通过操作，最终用户可以运行 Reporting Services 报表、导航到 URL 或根
据发生操作的单元的上下文启动外部操作。

例如，通过使用操作，最终用户可以
启动一个网页，该网页显示的公司产品目录会自动筛选为用户浏览过的一个或多
个产品。

多维模型本身就支持操作，许多客户端工具（如 Excel 和 Reporting
Services）也允许用户执行操作。

在 SQL Server 2012 中，不支持使用 SQL Server Data Tools 在表格模型中创建操作。

钻取：钻取使您能够导航到模型中存储的详细数据。

多维建模和表格建模都提供
了钻取功能。

多维模型还可让您创建钻取操作，以便可以通过指定钻取操作返回
的列以及启用此操作的多维数据集空间来自定义钻取体验。

写回：写回是预算编制和预测应用程序通常所需的一项功能。

在这些情况下，业
务用户通常需要执行“假设”分析，在分析中更改和更新模型中的数据值，然后发
布以供他人查看。

多维模型本身支持数据写回功能。

在 SQL Server 2012 中，表
格模型不支持此功能。

业务逻辑
业务逻辑可以为任何数据模型增加巨大的价值，它借助于可增强数据的计算和业务规则，为最终用户提供分析功能。

表格建模和多维建模都提供了丰富的公式语言以实现业务逻辑。

多维模型利用 MDX，而表格模型利用 DAX。

在深入探讨每个范例的一些高级业务逻辑方案之前，需要首先对在
多维建模和表格建模中如何使用行级转换、聚合值和计算来应用业务逻辑有一个基本的
了解。

行级转换
您可能需要执行源数据中未提供的计算和数据转换。

例如，源数据可能具有“销售额”列
和“每日汇率”列，但缺少换算为外币的销售额，或者源数据可能具有“员工姓氏”和“员工
名字”，但缺少串联起来的“员工全名”。

请注意，在这些示例中，计算或数据操作必须在
行级、未聚合的数据上发生。

在多维建模中，必须先对未聚合的数据执行行级转换，然后才能将数据加载到模型中；
或者必须先执行行级转换，然后才能查询模型。

您可以在数据源系统中应用转换，或编
写一个在 Analysis Services 查询源数据库时应用的 SQL 表达式，以转换维度属性（如员
工姓名）。

数值数据的行级转换可以通过使用 SQL 表达式在数据加载到 Analysis Services 之前执行；或者，可以使用 MDX 表达式以及 Scope 语句将计算限制在行级范
围内，从而应用行级转换。

如果在加载数据前应用转换，Analysis Services 可以预聚合
数值。

如果使用 Scope 语句应用转换，则将在查询时发生聚合。

在表格建模中，行级转换是使用计算列创建的。

当您创建计算列时，您需要将该列添加
到模型的特定表中，然后使用 DAX 公式来定义该列的值。

然后，针对该表中的每个记录
计算此公式，并且将公式加载到内存中，就像模型中的任何其他列一样。

借助于这种灵
活性，您可以基于您的具体分析要求直接在表格模型中增强您的数据，而且不再迫切需
要对能够或者不能容纳您的更改的上游数据源进行及时调整。

计算列提供了一种非常方
便的方法，使您能够创建和保留在对数据进行聚合之前必须在详细级别执行的计算。

尽
管这种灵活性很强大，但请注意，计算列并不用于执行大量数据清理或数据转换（例如
提取、转换和加载 (ETL) 过程）。

聚合值
在多维建模中，聚合值是通过使用度量值来创建的。

Analysis Services OLAP 引擎使用聚
合函数（如 SUM、COUNT、MIN、MAX 和 DISTINCT COUNT 以及其他函数）来预聚合
多维数据集的度量值。

在多维数据集处理过程中，每个度量值都可以在所有层次结构中
自下而上进行聚合。

由于这种处理发生在最终用户进行分析之前，因此，预聚合的度量
值可以为查询性能提供巨大优势。

当您在多维数据集中创建度量值时，多维数据集度量值与源数据中的数值列之间存在一
对一关系。

因此，在多维建模中，当您需要对满足以下条件的数值数据元素执行自下而
上的聚合时，需使用度量值：(1) 这些元素位于您的源数据的最低详细级别；(2) 这些元
素要求使用其中一个本机多维数据集聚合函数进行汇总。

在表格建模中，您也可以使用度量值来创建聚合值。

可以通过选择一列，然后指定聚合
函数（SUM、COUNT、DISTINCT COUNT、MIN、MAX 或AVERAGE），以创建度量值；也可以编写 DAX 表达式来指定要用来对度量值进行聚合的函数。

在表格建模中，行级数据存储在内存中，并在查询时计算聚合。

如下一部分所述，在表格建模中，度量值还可
以用来应用计算。

这可以包含基于多个聚合列的计算。

计算
在多维建模中，使用 MDX 来创建计算。

MDX 既是表达式语言，也是查询语言，它具有
的函数本身就理解多维数据集的维度、层次结构、属性关系和度量值的设计。

因此，您
可以创建简洁且强大的表达式，在多个数据上下文中应用业务逻辑。

您可以在多维数据
集的计算脚本中创建并存储 MDX 计算，从而可以控制应用逻辑的顺序。

计算成员是最常见的 MDX 计算。

计算成员是在对数据执行预聚合后，在查询时进行计
算的。

可以在任何维度中创建计算成员。

当在度量值维度中创建计算成员时，计算成员
通常称为“计算度量值”。

计算成员可以非常简单，只包含基本的数学运算，如单位销售
额（销售额/单位）或每个人的支出（支出/总人数）；也可以非常复杂，需要应用特定
的业务规则，如滚动三期平均销售额或年度利润。

例如，如果要计算当前时间段的销售
额并将其表示为父时间段的百分比，可以使用以下 MDX 计算。

[Measures].[Sales Amount] /
([Date].[Calendar].CurrentMember.Parent,[Measures].[Sales Amount])
除度量值维度之外，在其他维度中创建计算成员会向该维度中的某个属性添加一个值。

例如，如果您有一个包含颜色列表的维度属性，您可能希望添加一个计算成员 Primary Colors，用于汇总红、绿和蓝这三种颜色的值。

在表格建模中，创建度量值类似于在多
维模型的度量值维度中创建一个计算成员。

在表格建模中，您无法向表中的列添加值，
因此，表格建模不支持像在多维模型中的非度量值维度中创建计算成员那样的等效功能。

范围分配不仅比计算度量值更高级，而且功能也更强大。

正如前面的“行级转换”部分所述，您可以使用 Scope 语句来将计算限制在行级范围内。

但是，也可以使用 Scope 语句
指定您要应用计算的任何多维数据集单元范围。

范围分配在查询之前进行编译，它使
Analysis Services 能够在查询计算时提供优化的执行路径。

在给定强度的情况下，范围
分配不仅能够执行多个计算度量值的工作，还能够更高效地完成工作。

例如，在预算编
制解决方案中，您希望将东部区域下一年的预算指定为今年预算的 90%，将西部区域的
新预算指定为今年预算的 75%，将南部区域的新预算指定为今年预算的 105%，另外，
将北部区域的新预算指定为与今年的预算相同。

在本示例中，不需要使用包含嵌套 IF 语
句的一个复杂计算度量值，也不需要使用多个计算度量值将每个预算方案分隔开来，而
是可以使用范围分配来有效地在区域级别应用这些比率，然后聚合数据总计。

例如，如
果您希望使用每日汇率将销售额转换为外币，则可以使用以下 MDX 表达式：
Scope([Date].[Date]);
This = [Measures].[Sales Amount] * [Measures].[Daily FX Rate];
End Scope;
在表格建模中，您可以使用 DAX 来创建计算。

如前面所述，在表格建模中，您可以通过创建计算列来应用行级计算，也可以在通过编写 DAX 表达式创建度量值的时候应用计算。

因为您显式使用 DAX 行级函数和聚合函数的组合，所以表格模型中的度量值是非常灵活的。

您可以应用行级函数，然后应用聚合函数，以便您的度量值在聚合之前应用计算；
或者您可以先应用聚合函数，然后应用行级函数，以便您的度量值在聚合之后应用计算。

DAX 公式可以在不同的数据上下文（而不仅仅是Excel 工作表或数据透视表的当前视图）中，使用一组特殊的函数（称为 FILTER 函数）动态地计算公式。

在最广泛的意义上，这
些函数对于 Analysis Services 范围分配具有类似的作用，因为它们使您能够针对特定的
行集来定义和执行计算。

例如，可以使用 FILTER 函数处理上述的预算编制示例。

业务逻辑方案
既然您已经很好地了解如何在 MDX 和 DAX 中创建和应用基本业务逻辑，那么可以考虑
以下计算方案，以比较和对比表格建模体验与多维建模体验。

层次结构逻辑
如前所述，层次结构为业务用户提供了一种在数据分析过程中浅化和深化的方法。

在某
些情况下，创建可以导航层次结构的计算非常有用。

例如，考虑一个产品维度，其中具
有“产品类别”、“产品子类别”和“产品”。

对于层次结构中的每个级别，您要添加一个计算，以衡量每个级别的成员对父项的总销售额的贡献有多大。

考虑到此计算必须导航层次结
构才能返回所需的值，所以称为“父项百分比”计算。

MDX 和 DAX 都提供了相应的函数来处理整理到层次结构中的数据，并且可以创建类似
父项百分比这样的计算；但 MDX 函数通常更简单且更易于使用。

例如，在 MDX 中，这是在产品维度中提供父项百分比的表达式。

[Measures].[Sales Amount] /
([Product].[Product Categories].CurrentMember.Parent, [Measures].[Sales
Amount])
要使用 DAX 创建相同的父项百分比计算，则需要以下更复杂的表达式。

IF(
ISFILTERED(Product[Product])
,[Sales]/CALCULATE([Sales],ALL(Product[Product]))
,IF(
ISFILTERED(Product[Subcategory])
,[Sales]/CALCULATE([Sales],ALL(Product[Subcategory]))
,1
)
)
自定义汇总
尽管统一的数据汇总在许多情况下均适用，但在其他一些情况下，您可能希望对数据汇
总的方式进行更精细的控制。

这种情况的一个示例是财务模型：您具有一个科目表（通
常为父子格式），而每个科目都要求特定的汇总逻辑。

如下所示，“毛利”的计算方法是“净销售额”减去“销售成本总计”，而“运营利润”的计算方法是“毛利”减去“运营开支”。

多维模型不仅本身支持父子层次结构，而且它们还提供内置的帐户智能，这使您能够在
帐户级别轻松地应用驱动数据汇总的一元运算符和 MDX 公式。

在表格模型中，父子或帐户智能不是内置的，但您可以使用计算列和度量值的组合来生
成父子层次结构并应用自定义汇总，以生成您自己的解决方案。

半累加性度量值
一般来说，半累加性度量值是那些在所有维度（日期除外）中统一进行聚合的度量值。

半累加性度量值的示例包括期初余额和期末余额。

对于这些度量值，您要应用特殊逻辑，以便按时间段对数据进行正确汇总。

毕竟，3 月份的库存现有量余额不是 3 月份中所有
日期的现有量总和。

此外，此余额还应正确地适用于所有日期属性（如季度和年份）。

例如，第 1 季度的库存现有量余额应与在 3 月 31 日报告的余额相同（假设 3 月 31 日是第 1 季度的最后一天）。