MicroStrategy的ROLAP工具和NCR的Teradata数据仓库的技术合作

导出表（Derived Tables）及NCR/MicroStrategy决策支持系统

综述:

MicroStrategy和NCR的Global Partners Engineering Support (GPES)队伍进行了MicroStrategy的ROLAP工具和NCR的Teradata数据仓库的技术合作。这种合作的目标是为了展示软件特性和SQL生成技术如何提高了Teradata系统和/或MicroStrategy工具的性能。Teradata V2R2和MicroStrategy 5.1实现了导出表(Derived Table)。导出表是在SQL语句中的FROM子句中使用SELECT CLAUSE构建出来的。NCR和MicroStrategy在1997年9月共同分析导出表的性能问题。结果表明使用导出表极大的提高了Teradata/MicroStrategy系统的性能（高达3倍）。另外，还分析了另一个版本的特性”volatile table”（1998年加入到Teradata V2R3中），实验表明此特性帮助系统提高了性能（高达2.5倍）。此次报告所用的数据仓库是1998年发布的Teradata V2R3版本。

前言

MicroStragey产品的一个独一无二的特点是尽量利用RDBMS提供商提供的优化手段，将一切必要的计算放在数据仓库端执行。因此，MicorStrategy的SQL引擎的逻辑和工作基础就是有效地利用临时表（temporary table）。执行复杂的OLAP计算需要临时存储空间，在关系数据库中，临时数据存储在临时表中。有些数据库支持真正的无日志、非永久表，但另一些不支持。如果使用真正的临时表，在创建时不会有明显的overhead。对那些不支持真正的临时表的平台而言，在表创建和删除时就会有slight overhead。这种overhead是因为要读/写系统目录。由于导出表并不访问系统目录，因此不支持真临时表的数据库使用导出表在效果上如同使用真临时表。

导出表由内嵌在FROM子句中的SELECT语句构成。对导出表的支持需要遵循ANSI-92 SQL。导出表的其他名称还有：表子查询(table subqueries)，嵌套查询(nested queries)，以及表值构建器(table value constructors，ANSI-92 SQL中的正规命名)。虽然不可能从MicroStrategy的多路SQL(multi-pass SQL)中去除所有的临时表，但很多的查询还是可以用导出表来重写的。使用导出表减少了回答复杂问题所需的SQL数目。解决多维度(multi-dimensional)、包含度量限定(contain metric qualification)、分层事实(split fact)或外联接(outer join)问题所需的多路SQL可以用导出表来构造。

NCR在Teradata V2R3中包含了”volatile table”。Volatile表在内存中创建，只在一次会话中保留。它们无日志纪录的，不能恢复，并且创建过程中在Teradata数据字典中没有记录条目。由于非常复杂的查询通常会需要一些临时表，导出表与Volatile表的联合使用将会进一步提高Teradata-MicroStrategy决策支持系统的性能。
NCR和MicroStrategy对导出表和Volatile表进行了联接测试。这篇文档会大体描述此次测试，并讨论其结果对生产决策支持系统的影响。

测试

测试在两台NCR 5100单节点服务器上执行，每台服务器有8颗Pentium 133 MHz处理器，2GB内存，128G硬盘。测试导出表和临时表的机器使用Teradata DBMS V2R2.00.02.10。由于当时V2R3还没有发布，为了测试Volatile表，NCR使用了加上Volatile表补丁的Teradata DBMS V2R2.00.01。每台机器上创建三个标准的TPC-D数据仓库，分别为1 GB, 3 GB和10 GB。在TPC-D仓库之上，创建了12张视图用于维护列名一致性(column name homogenneity)。TPC-D可选的时间表是从Jan. 1, 1991到Dec. 31, 1999。

报表

NCR/MicroStrategy用于测试导出表的MicroStrategy Agent项目中包含三种类型的报表：多维度量、度量限定和外联接。

任何需要不同级别聚合和度量筛选的计算都需要创建临时多维表。比如包含穿透(penetration)、占比(contribution)、百分占比、turnover, tell-through, EOH, BOH的报表。带多维临时表的报表可以用导出表改写为如下形式：
SELECT * FROM (SELECT col1 FROM T1) a1, (SELECT col2, col3 FROM T2) a2…
NCR/MicroStrategy创建的报表涵盖了2~13个维度表，另外还创建了访问不同仓库表的报表。

Concurrency并发

NCR/MicroStrategy使用MicroStrategy Agent创建了15张报表。每张报表以临时表报表、导出表报表、Volatile表报表方式运行。查寻范围从两张临时表到13张临时表，平均每张报表含9.7张临时表。MicroStrategy Agent为每张报表生成的SQL保存在自己的脚本文件中。总共45个脚本文件——15张报表每个有一个导出表SQL，一个临时表SQL，一个Volatile表SQL。

Teradata的BTEQ工具用来处理所有提交的SQL。一个UNIX登录可以提交多个并发BTEQ会话。也就是说，每个BTEQ会话代表一个用户，提交20个BETQ会话相当于20个并发用户。NCR/MicroStrategy分别模拟了5、10、15、30和45个并发用户测试。在所有测试中，每个用户执行15张报表中的3张。一旦用户完成了全部3张报表，此用户将不再提交任何报表。总体的分配原则是使得所有的报表在每次测试中执行的次数相同。

结果

测试结果如表1所示。导出表vs.临时表提高的百分比相对于不同的并发用户数计划保持常量，唯一的例外出现在10GB数据上。在1GB水平上大约提高200%，在3GB水平上大约提高60%。在10 GB水平上，性能增加的百分比随着并发用户数的增加而增加。

表格 1 测试结果

在表中

Ø TT = 临时表
Ø DT = 导出表
Ø VT = Volatile表
Ø DT vs. TT = (TT Time - DT Time)/(DT Time) * 100%
Ø VT vs. TT = (TT Time - VT Time)/(VT Time) * 100%

结论

对于特定的查询使用导出表技术会有200%的性能提高。另外，DBA也不再需要用批处理方式删除临时表，这降低了Teradata/MicroStrategy决策支持系统的管理成本。

NCR正在进一步的提高Teradata数据仓库导出表的功能和性能，NCR和MicroStrategy 在将来还会进一步配合以提高导出表的使用性能。
在Teradata V2R3版本中，将会正式支持Volatile表。参见《在NCR/MicroStrategy商务智能平台中实现Volatile表及排名运算》。
导出表只是临时表的一种选择而不是替代，通过MicroStrategy对导出表的增强大大提高了在Teradata系统上的性能。报表执行的更快，维护量降低，并使本已很高的扩展性提升到一个新的高度。

在NCR/MicroStrategy商务智能平台中实现Volatile表及排名运算

前言

成功的商务智能应用依赖于以下几个关键点：（1）存储和管理海量的详细数据，（2）分析这些数据，（3）将数据分发给客户。为实现这样的目标，数据仓库厂商与商务智能提供商应能很好的集成他们的产品，为客户提供无缝的整体解决方案。

MicroStrategy是世界领先的商务智能产品和服务提供商，利用MicroStrategy的产品很荣将NCR的Teradata数据库中的信息以主动或被动的方式分发出去，及时满足企业或其他用户群的需要。MicroStrategy的产品与Teradata数据仓库配合起来，能够使用户查询和分析最详细的、交易级的数据，将数据转化为商务智能。
NCR选择MicroStrategy作为其V2R3.02的Controlled Deployment Program的评估合作伙伴。评估的关键点之一是Volatile表的测试。通过这样的合作，NCR和MicroStrategy在其相应的产品中对Volatile表（也叫真临时表）的介绍达成一致，当Controlled Deployment Program完成时，MicroStrategy将NCR的V2R3.02 OLAP Assists Ranking特性。

在数据仓库上进行不同级别和维度的复杂分析需要多步计算过程，每步计算都需要一个存储中介。在关系型数据库中，需要创建表来存储这些中间结果。但是这些表只是在报表执行时才会用到，因此需要动态的创建并在不需要时注销。在Teradata V2R3中引入的Volatile表提供了完全的临时存储。Volatile表只会在一个会话中存在，随后就会自动注销。由于不需要为Volatile表维护日志或数据字典，Volatile表的使用明显的提高了性能。MicroStrategy通过支持Teradata的原生Ranking特性，充分利用Teradata数据库的并行处理能力。
下述的部分分别描述测试的方法、测试的结果和总结出的结论。

测试环境

我们使用了标准的硬件进行性能对比测试。在数据库服务器端，Teradata安装在Unix环境下，使用了4个节点，每节点的配置是4颗PIII5000MHZ的CPU，在MIcroStrategy应用服务器端，采用了一台4颗PII400MHZ的CPU的PC服务器。

相应的软件是Teradata V2R3.02, V2R2.01.3 Unix 3.02, MicroStrategy DSS Server V5.6 SP2, MicroStratgy Agent V5.6 SP1.1, MicroStrategy Administrator-Warehouse Monitor 5.0用于收集报表执行和SQL生成的统计信息。

测试数据库使用370GB标准的TPC数据模型，少量地增加了一些对照表和聚合表。MicroStrategy实验室在此数据模型基础上构建了整个多维模型。

测试

在此次测试中，提高性能的主要方法聚焦在两种不同的要素上：使用中间表处理和对计算值进行排名。这两个功能在商务智能应用中的使用频率非常高。

所有的测试都建构在TPC数据框架模型上，在后续章节中会详细描述两个测试案例。

临时表

临时表在复杂的多维分析中是必需的。为了测量使用了中间表的报表的执行时间，我们创建了50张报表，这些报表使用的中间表的数目从0到54。
在Teradata V2R3.02中，这些报表以3种不同的方式执行。第一种创建永久表，在报表执行时删除这些表。第二种也创建永久表，但这些中间表会留在数据库中。第三种使用Volatile表存储中间结果。在Teradata V2R2上的测试只涉及前两种，因为在这个版本中还没有支持Volatile表。

排名（Ranking）

第二项测试的功能是Teradata V2R3中引入的数据库原生的排名（Ranking）功能。Ranking功能常用来回答这样的问题“我的哪些客户在他们所在的地区贡献了超过5%的销售额”。
Ranking功能可以分为两类：（1）百分比排名（Rank Percent）（例如，10%到50%之间的值），（2）绝对排名（Absolute Rank）（例如，根据销售额对商店做排名）。这些只可以限定为Top5或Buttom5等。另外，这些功能还可以通过布尔运算符AND, OR, NOT等组合起来
实现组合排名。

下列报表集用来测试排名功能：
Ø 27张报表用于测试百分比排名
Ø 16张报表用于绝对排名
Ø 24张报表用于组合排名
所有的报表都运行在V2R4和V2R3.02上，由于V2R4没有原生的Ranking支持，在测试中，由MicroStrategy应用服务器完成Ranking功能。

测试结果

每个测试都是独立进行，所有的测试都是在数据库上执行的时间。为了避免任何不准确的结果，所有测量的执行时间都是指生成的SQL在数据库上的纯执行时间。SQL生成、数据转换和结果处理时间都未考虑在内。
临时表测试包含在V2R2上的两组测试和在V2R3上的三组测试。每张报表的执行时间如图1所示。为了区分不同的报表执行模式，’V2R2’和’V2R3.02’用于描述Teradata数据库的版本。不同的临时表创建模式：’PTD’表示使用永久表并在报表执行时删除；’PTND’表示永久表留在数据库中；’VT’表示使用Volatile表。

图表 1 报表的临时表执行次数

表1显示了每个执行周期的运行时间，表2显示了不同执行模式下带来的性能的提高。两次执行时间的性能提高的计算方式为：
性能提高百分比=（（time2*100）/time1）-100

表 2 50张临时表测试报表的总共执行时间

表 3 临时表性能提高

结果显示，使用了Volatile表的方式，相对于将永久表留在数据库中的模式，性能提高了至少35%。与删除永久表的模式相比，性能提高了最大到77%。

与临时表性能测试不同的是，Ranking性能测试是针对不同版本的数据库（V2R2 vs. V2R3）展开的。它考量的是数据仓库及别的Ranking性能。图2~4显示了三种不同的Ranking选项在Teradata V2R2跟V2R3之间的性能比较。

图表 2 百分比排名测试

图表 3 绝对排名测试

图表 4 组合排名测试

所有的图形结果显示，使用V2R3.02数据仓库的Ranking功能，报表执行时间都明显降低。表3和表4对此结果进行了汇总。

表 4 排名报表的执行时间

表 5 排名性能的提高

这样的测试结果对NCR/MicroStratgey客户如何充分使用最新的产品特性提供了很有价值的建议：

Ø 通过配置MicroStrategy环境来充分利用Teradata原生的Ranking功能，能有效的降低MicroStrategy应用服务器端的负载，从而更好的达到MicroStartegy服务器与Teradata数据库服务器之间的负载平衡，有利于消弭性能瓶颈。

Ø 在运行复杂报表需要创建中间表时，使用Volatile表能够明显的减少数据库的Overhead，提高性能。Volatile表的使用在提高性能的同时也减小了数据库管理成本。

结论

很明显，MiroStaregy和NCR的针对关键问题的性能测试结果满足了数据仓库的最本质的需求。通过MicroStrategy和Teradata对Volatile表和排名报表的无缝集成支持，用户获得了关键的性能支持。在典型的复杂分析中，使用Volatile表可获得35%到75%的性能提高，使用原生的OLAP Assist Ranking功能可获得10%到20%的性能提高。
通过MicroStrategy的SQL生成技术，可以最充分地利用Teradata数据仓库的卓越性能，为用户提供更具竞争力的商务智能解决方案。