IBM InfoSphere Warehouse（简称ISW）是一套集成的软件包，提供了一整套的工具（design studio，Cubing service等）， 满足企业的DB2数据库管理、数据处理转换、多维建模等需求，提供了完善的数据仓库生命周期管理的解决方案。随着国内数据仓库系统的不断发展，ISW在企业中的应用越来越广泛。
数据仓库和BI系统会随着业务量和企业规模的变化，面临各种压力和挑战。对于系统的调优就成为数据仓库系统生命周期中的重要部分。本文结合具体的业务场景和实践经验，介绍了通过ISW来进行数据仓库系统和OLAP模型调优的主要方法。
数据仓库的开发和模型的设计，往往和具体的业务背景紧密结合。在电力行业数据仓库的建设过程中，常要考虑到各个主题要满足的业务特点，才能设计出更好的业务模型，并满足用户的分析需求。
在本次开发过程中，我们以发电单位成本分析主题为例。在这个主题中，主要分析的是发电成本的构成、各个成本之间的相互影响。主要具有以下特点：
- 成本之间相互关联。例如固定成本就由水费、职工薪酬、材料费、检修费、折旧费和其他费用组成。因此固定成本就由这些基本费用汇聚的来，某个费用的异常，往往会导致汇聚费用的异常。这种逻辑往往是用户分析的业务重点，用户可以通过这种指标间的关联，直观的看到业务情况的好坏，并迅速定位业务异常点。在电力行业，这种成本之间的组成关系直接影响后面单位成本的计算。下图是各成本之间的关系图。
图1.成本之间的关联 - 大量的单位成本。在电力行业中的成本控制中，一类很重要的分析需要通过单位成本来完成。用户对成本的控制好坏，在成本总量上往往不够明显，单位成本值能够更好的衡量企业的成本控制效果。
- 同比、环比和平均值的计算。为了能够宏观上对业务情况进行分析，需要同时分析各个指标的同比值、环比值和平均值。在多维分析中，同比值和环比值需要在同一个维度中选择不同的时间来进行比较，平均值则需要通过一定的运算得到，因此相对来说更复杂，而且在上下钻取的过程中不能简单的进行汇聚得来。
根据前面提到的电力系统的业务特点，在多维建模的过程中，我们需要特别考虑这些特殊的指标的实现方式。一方面我们需要满足用户基本的分析需求，实现相关功能；另一方面，应该在满足功能的前提下，考虑性能方面的要求。在本主题中，我们对不同的指标只要做以下操作：
- 对于成本总和，除了一些基本的费用如水费、材料费等需要分析总额以外，其他的汇聚指标并不需要展现在用户面前。这些成本总和往往只是为了计算单位成本时的中间度量。因此并不需要将这一类的指标放在多维模型的事实表当中。这类指标在多维模型的设计时，上钻和下钻的过程中，只需要使用默认的sum就可以了。
- 对于单位成本指标，由于涉及到中间值，因此计算的公式相对复杂。以“上网电量影响发电单位成本变化”这个指标为例
上网电量影响发电单位成本变化=（本期水费+本期外购动力费+本期环境保护费+本期职工薪酬+本期材料费+本期检修费+本期折旧费+本期其他费用）*（1/本期上网电量-1/同期上网电量）
这类指标由于包含乘除法运算，因此必须在多维模型中定义复杂的MDX度量，同时在上下钻取的过程中，往往不能简单的sum来实现汇聚。每一个层次都需要重新计算。
- 对于同比、环比和平均值指标，需要一定时间范围内的汇总值除以时间范围，同2类指标相似，需要定义复杂的MDX度量，这类指标的计算公式相对简单，只是需要通过MDX函数找到特定的维度成员对应的度量值。这类指标的上下钻取，不能简单的通过sum汇聚，也需要在不同层次上的单独计算来实现。
OLAP数据仓库初始实现和性能分析
业务分析完后，接下来是数据仓库的实现。首先使用ISW Design Studio创建OLAP模型、Cube模型和Cube，然后将创建好的模型部署到ISW CubeServer，最后通过Alphablox展示OLAP分析结果。
初始建模时，出于实验对比的考虑，并未将所有业务指标包括在Cube模型中，只是选择了一部分比较常用的基本指标。对于由基本指标派生出来的其他指标，比如同期，同比等度量值，通过动态的MDX语句来计算，并在OLAP建模时创建相应的MDX计算度量。
从图2中可以看到，我们创建了一个名为FADDWCB的Cube，这个Cube包含两个维度：DIM_SHIJ和DIM_JIG以及多个度量。这些度量中有一个计算同期值的MDX计算度量TongQi，它的具体计算见下图3：
MDX中的ParallelPeriod和CurrentMember等函数可以很方便的用来计算某个基本度量的同期值。MDX计算度量虽然很灵活，但对性能却有一定影响，特别是在数据量比较大的时候。
OLAP建模完成后，使用AdminConsole创建CubeServer，然后将OLAP模型部署到CubeServer。接着，在事实表上有外键的列上建上索引：分别在列ORG_ID（外键连接到机构维表）和列TIME_ID（外键连接到时间维表）上建立索引。
建模和部署完成后，使用Alphablox进行OLAP多维分析。事实表中有200多万条数据；时间的跨度是20年，层次从年到半年，到季度，再到月，最后到最小粒度天；为了更好对比性能，机构只包含一个层次，250个成员。首先执行默认的MDX查询，即对默认的一个基本度量在所有维度上进行汇总，查询时间大概是10秒；接着加入由MDX计算度量计算的同期度量，然后在时间维上进行第一层下钻操作，花费了超过10多秒的时间，第二层下钻花了20多秒的时间。这样的性能是难以让人接受的，性能调优成了必然的要求。
初始实现的数据仓库存在着性能方面的问题，需要根据电力系统的业务和ISW的特点进行多层次的调优。
OLAP模型调优
在初始的数据仓库的实现中，出于实验和对灵活性的考虑，对于经常用的比如同期值这样的技术指标，使用了MDX计算度量来进行计算。然而，这样的OLAP模型是有相当的性能缺陷的：
- MDX语句需要经过CubeServer解析成SQL再由DB2执行，有很多额外的开销。
- 每次使用这样的指标是都需要动态计算，造成许多不必要的重复。
- 作为一种中间度量，同期值被许多其他度量用到，每次计算其他度量又要重新计算同期值。
由于初始OLAP模型的这些缺陷，我们有针对性地对OLAP模型进行相应调整：
- 避免在常用业务指标上使用MDX计算度量，只在一些定义可能会变化的不经常使用的探索性指标（比如一些预期值等）上使用MDX计算度量。
- 考虑电力系统的特点，许多业务指标的计算都需要先计算一些中间度量（比如同期值），而这些中间度量的值都是不变的，只需计算一次。因此，对每一个会派生出中间度量的基本指标，一律都通过ETL过程计算其中间度量并在事实表中增加相应的列保存。
- 将可以由基本度量和中间度量计算的度量都通过ETL计算出来并添加到事实表中。
调整后，我们先通过ETL过程计算出基本度量的同期值，然后再用基本度量和其同期值计算派生度量。以折旧费对发电成本变化影响这个度量为例（DWCB_ZJYX），这个度量可以由折旧费，同期折旧费和同期上网电量这三个度量计算出来，经过ETL过程，这三个度量都是已知的，于是折旧费影响这个派生度量很直接地被计算出来。见下图4：
需要注意的是：由于很多度量的计算涉及到除法，因而需要使用相应粒度的其他度量的度量（Measure）值进行计算，并且不向上聚集：”Function for all of the dimensions”需要选为None。（见下图5）。”Function for all of the dimensions”是度量进行向上汇总时作用于其上的函数，比如求和（SUM），最大最小值（MAX，MIN），平均值（AVG）等。当设成None时，向上汇总时不进行任何操作。
在初次设计实现中，我们在每张维表上建上了一般索引，在事实表上建了外键并在这些外键所在列上建上了一般索引，没有进行其它的DB2数据库的优化。这种配置的性能表现是不能让人满意的。
重新考虑在事实表上建的索引，初始时是两个，分别建在与事实表关联的外键上，但这两个索引并不是唯一（unique）的。由于时间跨度很长—20年，最低粒度到每日，同时机构成员也不少—250个，虽然有索引，但这两个索引的性能是很低的，大量的表扫描任然难以避免。现在考虑重建事实表上的索引，有两种可选的更好的做法：1).建立MDC块索引（Multi-Dimensional Clustering：多维集群）。或者2).建立unique索引。MDC块索引可以在物理上将在多个维上具有类似值的行聚集在一起放在磁盘上。这种聚集能为常见分析性查询提供高效的I/O，从而提高查询的性能。MDC一般用在列的cardinality比较低的情况下（当一列包含大量不同的值时，cardinality高，反之）。由于没有对机构进行分层，时间跨度长而粒度小，导致时间这一列上有大量不同的值，最终我们选择第二种方法：在两个有外键的列上建一个唯一（unique）索引（或者是主键），这样每个索引便能唯一指向一个数据行，从而避免了表扫描。最终的实验表明，这个调整成数量级地大幅度提高了OLAP查询的性能。
db2 "create unique index uix01 on facttable(time_id, org_id)" |
事实表中度量的数据类型一开始使用的是DECIMAL。跟FLOAT或者DOUBLE这些数据类型相比，DECIMAL性能是比较差的。由于事实表中有很多的计算度量，OLAP查询也会导致大量的聚集运算，使用DECIMAL累积起来的性能损失是很可观的。于是将事实表中的度量全部由DECIMAL调整为FLOAT。在随后的OLAP查询中，能很明显感觉得这个调整带来的性能提升。
使用物化查询表（MQT）来保存常用的聚集操作的结果是比较常用的OLAP调优手段。物化查询表（MQT）是一种以一次查询的结果为基础定义的表,可以显著提高查询的性能，尤其是提高复杂查询的性能。如果优化器确定查询或查询的一部分可以用一个MQT来解决，那么就会重写查询，以便利用MQT。这里的问题是由于OLAP模型的复杂性以及OLAP查询的多样性和常用聚集操作的粒度在不同的运用中有差异，需要首先根据运用的特点确定聚集操作的粒度，涉及的维度，以及相应的常用的查询语句，然后再基于这些查询语句创建创建相应的MQT。以下是具体步骤：
- 先在AdminConsole中将CubeServer的MDX Log和SQL Log打开。然后执行一些常用的MDX查询。
- 从CubeServer的日志中找到相应的MDX语句和对应的SQL语句。这些日志一般存放在$ISWINSTALLDIR/CubingServices/$CUBESERVER/Logs/目录中。
- 使用得到的SQL语句创建MQT构建脚本并执行。
- 在某些情况下，事实表外键上的查询优化可能会影响DB2优化器从而导致MQT不命中。这时可以关闭这些外键上的优化：
db2 alter table facttable alter foreign key FK_TIME disable query optimization alter foreign key FK_ORG disable query optimization |
- 执行常用查询验证MQT命中。
CubeServer层面可以进行以下一些方面的调优：
- 打开CubeServer的Data caching。Data caching用以缓存数据立方体（Cube）的数据。一旦某个粒度的聚集数据已经被查询过，这个值便被缓存在了CubeServer的内存中，下次查询同样的数据时就可以直接从CubeServer中取出而不需再访问数据库了。
- 打开CubeServer的Member Caching并确保使用的是静态缓存（static caching）。Member Caching用来缓存维度成员（Dimension members）的信息。它有两种模式：static模式和dynamic模式。使用static模式时，CubeServer会在Cube启动时将所有维度上的成员从数据库中查询出来并缓存在内存中；使用dynamic模式时，CubeServer会将维度成员缓存在用户自己指定的文件中。由于static模式是缓存在内存中，因而速度更快。
- 为了让CubeServer能更多地在内存中缓存数据，增大JVM的堆（Heap）大小。
经过多个层面的调优，OLAP查询性能有了很大的提高：所有层次的下钻操作几乎是立即完成；只包含默认度量的总聚集操作也几乎立即就能出结果；包含所有40多个度量和所有维度的总聚集查询操作4秒左右就能完成。整个OLAP查询性能提高了一个多数量级。调优的效果非常明显。
本文描述了使用InfoSphere Warehouse实现电力系统数据仓库并从多个层面进行调优的过程；列举了对性能有影响的因素和相应的调优手段。对数据仓库设计，实施和调优有一定的借鉴意义。
TAG: 数据 仓库
