fwd 深入探讨数据仓库建模与ETL的实践技巧
更重要的是，在聚合时，数值型字段的匹配和比较，JOIN效率高，便于聚合。同时，代理键对缓慢变化维度有着重要的意义，在原数据主键相同的情况下，它起到了对新数据与历史数据的标识作用
扩展的星型模型
在数据仓库的数据库设计中,星型模型是一种基本的数据模式。星型模式是一种多维的数据关系,它由一个事实表(ＦａｃｔＴａｂｌｅ)和一组维表(ＤｉｍｅｎｓｉｏｎＴａｂｌｅ)组成。每个维表都有一个维作为主键,所有这些维则组合成事实表的主键,换言之,事实表主键的每个元素都是维表的外键。事实表的非主属性称为事实(Ｆａｃｔ),它们一般都是数值或其他可以进行计算的数据;而维大都是文字、时间等类型的数据。如图2所示为扩展的星型模型:
采用这种扩展的星型模型,多层分维结构减少了一级分维表的内容,避免一级分维表中出现大量的重复数据,使得复杂的数据模式保持简洁清晰。
深入探讨数据仓库建模与ETL的实践技巧
( 2008/8/5 10:56 )
本文关键字:
深入探讨了搭建数据仓库过程中应当遵循的方法和原则，更多内容请参考下文：
一、数据仓库的架构
数据仓库（Data Warehouse \ DW）是为了便于多维分析和多角度展现而将数据按特定的模式进行存储所建立起来的关系型数据库，它的数据基于OLTP源系统。数据仓库中的数据是细节的、集成的、面向主题的，以OLAP系统的分析需求为目的。
数据仓库的架构模型包括了星型架构（图二：pic2.bmp）与雪花型架构（图三：pic3.bmp）两种模式。如图所示，星型架构的中间为事实表，四周为维度表，类似星星；而相比较而言，雪花型架构的中间为事实表，两边的维度表可以再有其关联子表，从而表达了清晰的维度层次关系。
从OLAP系统的分析需求和ETL的处理效率两方面来考虑：星型结构聚合快，分析效率高；而雪花型结构明确，便于与OLTP系统交互。因此，在实际项目中，我们将综合运用星型架构与雪花型架构来设计数据仓库。
那么，下面我们就来看一看，构建企业级数据仓库的流程。
二、构建企业级数据仓库五步法
（一）、确定主题
即确定数据分析或前端展现的主题。例如：我们希望分析某年某月某一地区的啤酒销售情况，这就是一个主题。主题要体现出某一方面的各分析角度（维度）和统计数值型数据（量度）之间的关系，确定主题时要综合考虑。
我们可以形象的将一个主题想象为一颗星星：统计数值型数据（量度）存在于星星中间的事实表；分析角度（维度）是星星的各个角；我们将通过维度的组合，来考察量度。那么，“某年某月某一地区的啤酒销售情况”这样一个主题，就要求我们通过时间和地区两个维度的组合，来考察销售情况这个量度。从而，不同的主题来源于数据仓库中的不同子集，我们可以称之为数据集市。数据集市体现了数据仓库某一方面的信息，多个数据集市构成了数据仓库。
（二）、确定量度
在确定了主题以后，我们将考虑要分析的技术指标，诸如年销售额之类。它们一般为数值型数据。我们或者将该数据汇总，或者将该数据取次数、独立次数或取最大最小值等，这样的数据称为量度。
量度是要统计的指标，必须事先选择恰当，基于不同的量度可以进行复杂关键性能指标（KPI）等的设计和计算。
（三）、确定事实数据粒度
在确定了量度之后，我们要考虑到该量度的汇总情况和不同维度下量度的聚合情况。考虑到量度的聚合程度不同，我们将采用“最小粒度原则”，即将量度的粒度设置到最小。
例如：假设目前的数据最小记录到秒，即数据库中记录了每一秒的交易额。那么，如果我们可以确认，在将来的分析需求中，时间只需要精确到天就可以的话，我们就可以在ETL处理过程中，按天来汇总数据，此时，数据仓库中量度的粒度就是“天”；反过来，如果我们不能确认将来的分析需求在时间上是否需要精确到秒，那么，我们就需要遵循“最小粒度原则”，在数据仓库的事实表中保留每一秒的数据，以便日后对“秒”进行分析。
在采用“最小粒度原则”的同时，我们不必担心海量数据所带来的汇总分析效率问题，因为在后续建立多维分析模型（CUBE）的时候，我们会对数据提前进行汇总，从而保障产生分析结果的效率。关于建立多维分析模型（CUBE）的相关问题，我们将在下期栏目中予以阐述。
（四）、确定维度
维度是指分析的各个角度。例如我们希望按照时间，或者按照地区，或者按照产品进行分析，那么这里的时间、地区、产品就是相应的维度。基于不同的维度，我们可以看到各量度的汇总情况，也可以基于所有的维度进行交叉分析。
这里我们首先要确定维度的层次（Hierarchy）和级别（Level）（图四：pic4.bmp）。如图所示，我们在时间维度上，按照“年-季度-月”形成了一个层次，其中“年”、“季度”、“月”成为了这个层次的3个级别；同理，当我们建立产品维度时，我们可以将“产品大类-产品子类-产品”划为一个层次，其中包含“产品大类”、“产品子类”、“产品”三个级别。
那么，我们分析中所用到的这些维度，在数据仓库中的存在形式是怎样的呢？
我们可以将3个级别设置成一张数据表中的3个字段，比如时间维度；我们也可以使用三张表，分别保存产品大类、产品子类、产品三部分数据，比如产品维度。（图五：pic5.bmp）
另外，值得一提的是，我们在建立维度表时要充分使用代理键。代理键是数值型的ID号码（例如图六中每张表的第一个字段），它唯一标识了每一维度成员。更重要的是，在聚合时，数值型字段的匹配和比较，JOIN效率高，便于聚合。同时，代理键对缓慢变化维度有着重要的意义，在原数据主键相同的情况下，它起到了对新数据与历史数据的标识作用。
在此，我们不妨谈一谈维度表随时间变化的问题，这是我们经常会遇到的情况，我们称其为缓慢变化维度。
比如我们增加了新的产品，或者产品的ID号码修改了，或者产品增加了一个新的属性，此时，维度表就会被修改或者增加新的记录行。这样，我们在ETL的过程中，就要考虑到缓慢变化维度的处理。对于缓慢变化维度，有三种情况：
1、缓慢变化维度第一种类型：
历史数据需要修改。这种情况下，我们使用UPDATE方法来修改维度表中的数据。例如：产品的ID号码为123，后来发现ID号码错了，需要改写成456，那么，我们就在ETL处理时，直接修改维度表中原来的ID号码为456。
2、缓慢变化维度第二种类型：
历史数据保留，新增数据也要保留。这时，要将原数据更新，将新数据插入，我们使用UPDATE / INSERT。比如：某一员工2005年在A部门，2006年时他调到了B部门。那么在统计2005年的数据时就应该将该员工定位到A部门；而在统计2006年数据时就应该定位到B部门，然后再有新的数据插入时，将按照新部门（B部门）进行处理，这样我们的做法是将该维度成员列表加入标识列，将历史的数据标识为“过期”，将目前的数据标识为“当前的”。另一种方法是将该维度打上时间戳，即将历史数据生效的时间段作为它的一个属性，在与原始表匹配生成事实表时将按照时间段进行关联，这种方法的好处是该维度成员生效时间明确。
3、缓慢变化维度第三种类型：
新增数据维度成员改变了属性。例如：某一维度成员新加入了一列，该列在历史数据中不能基于它浏览，而在目前数据和将来数据中可以按照它浏览，那么此时我们需要改变维度表属性，即加入新的字段列。那么，我们将使用存储过程或程序生成新的维度属性，在后续的数据中将基于新的属性进行查看
五）、创建事实表
在确定好事实数据和维度后，我们将考虑加载事实表。
在公司的大量数据堆积如山时，我们想看看里面究竟是什么，结果发现里面是一笔笔生产记录，一笔笔交易记录… 那么这些记录是我们将要建立的事实表的原始数据，即关于某一主题的事实记录表。
我们的做法是将原始表与维度表进行关联，生成事实表（图六：pic6.bmp）。注意在关联时有为空的数据时（数据源脏），需要使用外连接，连接后我们将各维度的代理键取出放于事实表中，事实表除了各维度代理键外，还有各量度数据，这将来自原始表，事实表中将存在维度代理键和各量度，而不应该存在描述性信息，即符合“瘦高原则”，即要求事实表数据条数尽量多（粒度最小），而描述性信息尽量少。
如果考虑到扩展，可以将事实表加一唯一标识列，以为了以后扩展将该事实作为雪花型维度，不过不需要时一般建议不用这样做。
事实数据表是数据仓库的核心，需要精心维护，在JOIN后将得到事实数据表，一般记录条数都比较大，我们需要为其设置复合主键和索引，以实现数据的完整性和基于数据仓库的查询性能优化。事实数据表与维度表一起放于数据仓库中，如果前端需要连接数据仓库进行查询，我们还需要建立一些相关的中间汇总表或物化视图，以方便查询。
三、什么是ETL
在数据仓库的构建中，ETL贯穿于项目始终，它是整个数据仓库的生命线，包括了数据清洗、整合、转换、加载等各个过程。如果说数据仓库是一座大厦，那么ETL就是大厦的根基。ETL抽取整合数据的好坏直接影响到最终的结果展现。所以ETL在整个数据仓库项目中起着十分关键的作用，必须摆到十分重要的位置。
ETL是数据抽取（Extract）、转换（Transform）、加载（Load ）的简写，它是指：将OLTP系统中的数据抽取出来，并将不同数据源的数据进行转换和整合，得出一致性的数据，然后加载到数据仓库中。例如：下图就向我们展示了ETL的数据转换效果。
那么，在这一转换过程中，我们就完成了对数据格式的更正、对数据字段的合并、以及新增指标的计算三项操作。类似地，我们也可以根据其他需求，完善数据仓库中的数据。
简而言之，通过ETL，我们可以基于源系统中的数据来生成数据仓库。ETL为我们搭建了OLTP系统和OLAP系统之间的桥梁。
五、项目实践技巧
（一）、准备区的运用
在构建数据仓库时，如果数据源位于一台服务器上，数据仓库在另一台服务器端，考虑到数据源Server端访问频繁，并且数据量大，需要不断更新，所以可以建立准备区数据库（图八：pic8.bmp）。先将数据抽取到准备区中，然后基于准备区中的数据进行处理，这样处理的好处是防止了在原OLTP系统中频繁访问，进行数据运算或排序等操作。
例如我们可以按照天将数据抽取到准备区中，基于数据准备区，我们将进行数据的转换、整合、将不同数据源的数据进行一致性处理。数据准备区中将存在原始抽取表、转换中间表和临时表以及ETL日志表等。
（二）、时间戳的运用
时间维度对于某一事实主题来说十分重要，因为不同的时间有不同的统计数据信息，那么按照时间记录的信息将发挥很重要的作用。在ETL中，时间戳有其特殊的作用，在上面提到的缓慢变化维度中，我们可以使用时间戳标识维度成员；在记录数据库和数据仓库的操作时，我们也将使用时间戳标识信息。例如：在进行数据抽取时，我们将按照时间戳对OLTP系统中的数据进行抽取，比如在午夜0：00取前一天的数据，我们将按照OLTP系统中的时间戳取GETDATE到GETDATE减一天，这样得到前一天数据。
（三）、日志表的运用
在对数据进行处理时，难免会发生数据处理错误，产生出错信息，那么我们如何获得出错信息并及时修正呢? 方法是我们使用一张或多张Log日志表，将出错信息记录下来，在日志表中我们将记录每次抽取的条数、处理成功的条数、处理失败的条数、处理失败的数据、处理时间等等。这样，当数据发生错误时，我们很容易发现问题所在，然后对出错的数据进行修正或重新处理。
（四）、使用调度
在对数据仓库进行增量更新时必须使用调度（图九：pic9.bmp），即对事实数据表进行增量更新处理。在使用调度前要考虑到事实数据量，确定需要多长时间更新一次。比如希望按天进行查看，那么我们最好按天进行抽取，如果数据量不大，可以按照月或半年对数据进行更新。如果有缓慢变化维度情况，调度时需要考虑到维度表更新情况，在更新事实数据表之前要先更新维度表。
调度是数据仓库的关键环节，要考虑缜密。在ETL的流程搭建好后，要定期对其运行，所以调度是执行ETL流程的关键步骤。每一次调度除了写入Log日志表的数据处理信息外，还要使用发送Email或报警服务等，这样也方便的技术人员对ETL流程的把握，增强了安全性和数据处理的准确性。
五、总结
构建企业级数据仓库需要简单的五步，掌握了这五步的方法，我们可以构建一个强大的数据仓库。然而，每一步都有很深的内容需要研究与挖掘，尤其在实际项目中，我们要综合考虑。例如：如果数据源的脏数据很多，在搭建数据仓库之前我们首先要进行数据清洗，以剔除掉不需要的信息和脏数据。
ETL是OLTP系统和OLAP系统之间的桥梁，是数据从源系统流入数据仓库的通道。在数据仓库的项目实施中，它关系到整个项目的数据质量，所以马虎不得，必须将其摆到重要位置，将数据仓库这一大厦的根基筑牢！
***********
数据仓库系统中，一个很重要的目的就是保留数据的历史变化信息。而变化数据捕获（Change Data Capture，CDC）就是为这个目的而产生的一项技术。变化数据捕获常用的方法有：1）文件或者表的全扫描对比，2）DBMS日志获取，3）在源系统中增加触发器获取，4）基于源系统的时间戳获取，5）基于复制技术的获取，6）DBMS提供的变化数据捕获方法等。其中，由DBMS提供变化数据捕获的方法是大势所趋，即具体的捕获过程由DBMS来完成。
QQ08.net
创新性应用 数据建模经验谈
2007-09-27 20:38:45 作者： 来源：互联网 文字大小：【大】【中】【小】
笔者从98年进入数据库及数据仓库领域工作至今已经有近八年的时间，对数据建模工作接触的比较多，创新性不敢谈，本文只是将工作中的经验总结出来，供大家一同探讨和指正。 提起数据建模来，有一点是首先 ...
笔者从98年进入数据库及数据仓库领域工作至今已经有近八年的时间，对数据建模工作接触的比较多，创新性不敢谈，本文只是将工作中的经验总结出来，供大家一同探讨和指正。
提起数据建模来，有一点是首先要强调的，数据建模师和DBA有着较大的不同，对数据建模师来说，对业务的深刻理解是第一位的，不同的建模方法和技巧是为业务需求来服务的。而本文则暂时抛开业务不谈，主要关注于建模方法和技巧的经验总结。
从目前的数据库及数据仓库建模方法来说，主要分为四类。
第一类是大家最为熟悉的关系数据库的三范式建模，通常我们将三范式建模方法用于建立各种操作型数据库系统。
第二类是Inmon提倡的三范式数据仓库建模，它和操作型数据库系统的三范式建模在侧重点上有些不同。Inmon的数据仓库建模方法分为三层，第一层是实体关系层，也即企业的业务数据模型层，在这一层上和企业的操作型数据库系统建模方法是相同的；第二层是数据项集层，在这一层的建模方法根据数据的产生频率及访问频率等因素与企业的操作型数据库系统的建模方法产生了不同；第三层物理层是第二层的具体实现。
第三类是Kimball提倡的数据仓库的维度建模，我们一般也称之为星型结构建模，有时也加入一些雪花模型在里面。维度建模是一种面向用户需求的、容易理解的、访问效率高的建模方法，也是笔者比较喜欢的一种建模方式。
第四类是更为灵活的一种建模方式，通常用于后台的数据准备区，建模的方式不拘一格，以能满足需要为目的，建好的表不对用户提供接口，多为临时表。
下面简单谈谈第四类建模方法的一些的经验。
数据准备区有一个最大的特点，就是不会直接面对用户，所以对数据准备区中的表进行操作的人只有ETL工程师。ETL工程师可以自己来决定表中数据的范围和数据的生命周期。下面举两个例子：
1）数据范围小的临时表
当需要整合或清洗的数据量过大时，我们可以建立同样结构的临时表，在临时表中只保留我们需要处理的部分数据。这样，不论是更新还是对表中某些项的计算都会效率提高很多。处理好的数据发送入准备加载到数据仓库中的表中，最后一次性加载入数据仓库。
2）带有冗余字段的临时表
由于数据准备区中的表只有自己使用，所以建立冗余字段可以起到很好的作用而不用承担风险。
举例来说，笔者在项目中曾遇到这样的需求，客户表{客户ID，客户净扣值}，债项表{债项ID，客户ID，债项余额，债项净扣值}，即客户和债项是一对多的关系。其中，客户净扣值和债项余额已知，需要计算债项净扣值。计算的规则是按债项余额的比例分配客户的净扣值。这时，我们可以给两个表增加几个冗余字段，如客户表{客户ID，客户净扣值，客户余额}，债项表{债项ID，客户ID，债项余额，债项净扣值，客户余额，客户净扣值}。这样通过三条SQL就可以直接完成整个计算过程。将债项余额汇总到客户余额，将客户余额和客户净扣值冗余到债项表中，在债项表中通过（债项余额×客户净扣值/客户余额）公式即可直接计算处债项净扣值。
另外还有很多大家可以发挥的建表方式，如不需要主键的临时表等等。总结来说，正因为数据准备区是不对用户提供接口的，所以我们一定要利用好这一点，以给我们的数据处理工作带来最大的便利为目的来进行数据准备区的表设计。
行业借鉴经验：
数据仓库架构经验谈
对于数据仓库的架构方法，不同的架构师有不同的原则和方法，笔者在这里来总结一下当前常采用的架构方式及其优缺点。这些架构方式不限于某个行业，可以供各个行业借鉴使用。
首先需要说明的一点是，目前在数据仓库领域比较一致的意见是在数据仓库中需要保留企业范围内一致的原子层数据。而独立的数据集市架构（Independent data marts）没有企业范围内一致的数据，很可能会导致信息孤岛的产生，除非在很小的企业内或只针对固定主题，否则不建议建立这样的架构方式。联邦式的数据仓库架构（Federated Data Warehouse Architecture）不管是在地域上的联邦还是功能上的联邦都需要先在不同平台上建立各自的数据仓库，再通过参考（reference）数据来实现整合，而这样很容易造成整合的不彻底，除非联邦式的数据仓库架构也采用Kimball的总线架构（Bus Architecture）中类似的功能，即在数据准备区保留一致性维度（Conformed Table）并不断更新它。所以，这两种架构方式不在讨论范围之内。下面主要讨论剩下的三种架构方式。
1）三范式（3NF）的原子层＋数据集市
这样的数据仓库架构最大的倡导者就是数据仓库之父Inmon，而他的企业信息工厂（Corporate Information System）就是典型的代表。这样的架构也称之为企业数据仓库（Enterprise Data Warehouse，EDW）。企业信息工厂的实现方式是，首先进行全企业的数据整合，建立企业信息模型，即EDW。对于各种分析需求再建立相应的数据集市或者探索仓库，其数据来源于EDW。三范式的原子层给建立OLAP带来一定的复杂性，但是对于建立更复杂的应用，如挖掘仓库、探索仓库提供了更好的支持。这类架构的建设周期比较长，相应的成本也比较高。
2）星型结构（Star Schema）的原子层＋HOLAP
星型结构最大的倡导者是Kimall，他的总线架构是该类架构的典型代表。总线架构实现方式是，首先在数据准备区中建立一致性维度、建立一致性事实的计算方法；其次在一致性维度、一致性事实的基础上逐步建立数据集市。每次增加数据集市，都会在数据准备区整合一致性维度，并将整合好的一致性维度同步更新到所有的数据集市。这样，建立的所有数据集市合在一起就是一个整合好的数据仓库。正是因为总线架构这个可以逐步建立的特点，它的开发周期比其他架构方式的开发周期要短，相应的成本也要低。在星型结构的原子层上可以直接建立聚集，也可以建立HOLAP。笔者比较倾向于Kimball的星型结构的原子层架构，在这种架构中的经验也比较多。
3）三范式（3NF）的原子层＋ROLAP
这样的数据仓库架构也称为集中式架构（Centralized Architecture），思路是在三范式的原子层上直接建立ROLAP，做的比较出色的就是MicroStrategy。在三范式的原子层上定义ROLAP比在星型结构的原子层上定义ROLAP要复杂很多。采用这种架构需要在定义ROLAP是多下些功夫，而且ROLAP的元数据不一定是通用的格式，所以对ROLAP做展现很可能会受到工具的局限。这类架构和第一类很相似，只是少了原子层上的数据集市。
总结来说，这三种数据仓库的架构方式都是不错的选择。对于需要见效快、成本低的项目可以考虑采用第二种总线架构，对于资金充足并有成熟业务数据模型的企业可以考虑采用第一种架构或第三种架构。
应用难点技巧：
变化数据捕获经验谈
在数据仓库系统中，一个很重要的目的就是保留数据的历史变化信息。而变化数据捕获（Change Data Capture，CDC）就是为这个目的而产生的一项技术。变化数据捕获常用的方法有：1）文件或者表的全扫描对比，2）DBMS日志获取，3）在源系统中增加触发器获取，4）基于源系统的时间戳获取，5）基于复制技术的获取，6）DBMS提供的变化数据捕获方法等。其中，由DBMS提供变化数据捕获的方法是大势所趋，即具体的捕获过程由DBMS来完成。
像银行、电信等很多行业的操作记录生成后就不会改变，只有像客户、产品等信息会随时间发生缓慢的变化，所以通常的变化数据捕获是针对维度表而言的。Kimball对缓慢变化维的分析及应对策略基本上可以处理维度表的各种变化。
而对于一些零售行业，像合同表中的合同金额类似的数值在录入后是有可能会发生改变的，也就是说事实表的数据也有可能发生变化。通常对于事实表数据的修改属于勘误的范畴，可以采用类似缓慢变化维TYPE 1的处理方式直接更新事实表。笔者不太赞同对事实表的变化采用快照的方式插入一条新的事实勘误记录，这样会给后续的展现、分析程序带来太多的麻烦。
接下来要讨论的是笔者曾经遇到的一个颇为棘手的事实表数据改变的问题，该事实表的主键随表中某些数据的变化发生改变。以其中的一个合同表为例，该合同表的主键是由“供货单位编号”＋“合同号”生成的智能主键，当其中的“供货单位编号”和“合同号”中任何一个发生变化时，该合同表的主键都会发生变化，给变化数据捕获带来了很大的麻烦。
项目中，笔者的处理方式是采用触发器的办法来实现变化数据捕获。具体的实现方式是：
1）建立一个新表作为保存捕获的数据表使用，其中字段有“原主键”、“修改后主键”、及其他需要的字段，称为“合同捕获表”。
2）在原合同表Delete和Update时分别建立触发器，当删除操作发生时，建在Delete上的触发器会插入一条记录到“合同捕获表”，其中“修改后主键”字段为空，表示该记录是删除的记录；当发生更新时，将“原主键”、“修改后主键”及其他需要记录的字段都保存入“合同捕获表”中，表示该记录被修改过，如果“原主键”和“修改后主键”不同，则表示主键被修改，如果相同，则表示主键没有被修改。
3）由于操作系统中的主键通常会成为数据仓库中事实表的退化维度，可能仍会起主键的作用。所以在数据加载时，需要分情况判断“合同捕获表”的数据来决定是否更新事实表中的退化维度。
可以说，这样的基于触发器的变化数据捕获方法并不是一个很好的选择。首先这需要对源系统有较大的权限；其次，触发器会给源系统的性能带来很大的影响。所以除非是没有别的选择，否则不建议采用这种方法。
而对于这样的情况，我们在建立操作型数据库系统时完全可以避免。下面是对操作型数据库系统建立者的几点建议：1）操作型系统的主键不要建立成智能型的，至少不要建立成会变化的。2）操作型系统的表中需要加入操作人和操作时间字段，或者直接加入时间戳。3）操作型系统中操作型数据最好不要直接在原值上修改，可以采用“冲红”的方式加入新的记录。这样后续建立数据仓库时就不需要考虑事实表数据的变化问题。
最后，期待各大数据库管理系统的厂商能尽快在DBMS层提供功能强大、简单好用的变化数据捕获功能，目前Oracle已经有了这个功能。毕竟技术方面复杂的事情留给厂商做是一个趋势，而我们做应用的则更关注于业务
即在数据准备区保留一致性维度（Conformed Table）并不断更新它。所以，
Dimension Design Best Practices
Good dimension design is the most important aspect of a well designed Analysis Services OLAP database. Although the wizards in Analysis Services do much of the work to get you started, it is important to review the design that is created by the wizard and ensure that the attributes, relationships, and hierarchies correctly reflect the data and match the needs of your end-users.
Do create attribute relationships wherever they exist in the data
Attribute relationships are an important part of dimension design. They help the server optimize storage of data, define referential integrity rules within the dimension, control the presence of member properties, and determine how MDX restrictions on one hierarchy affect the values in another hierarchy. For these reasons, it is important to spend some time defining attribute relationships that accurately reflect relationships in the data.
Avoid creating attributes that will not be used
Attributes add to the complexity and storage requirements of a dimension, and the number of attributes in a dimension can significantly affect performance. This is especially of attributes which have AttributeHierachyEnabled set to True. Although SQL Server 2005 Analysis Services can support many attributes in a dimension, having more attributes than are actually used decreases performance unnecessarily and can make the end-user experience more difficult.
It is usually not necessary to create an attribute for every column in a table. Even though the wizards do this by default in Analysis Services 2005, a better design approach is to start with the attributes you know you'll need, and later add more attributes. Adding attributes as you discover they are needed is generally better a better practice than adding everything and then removing attributes.
Do not create hierarchies where an attribute of a lower level contains fewer members than an attribute of the level above
A hierarchy such as this is frequently an indication that your levels are in the incorrect order: for example, [City] above [State]. It might also indicate that the key columns of the lower level are missing a column: for example, [Year] above [Quarter Number] instead of [Year] above [Quarter with Year]. Either of these situations will lead to confusion for end-users trying to use and understand the cube.
Do not include more than one non-aggregatable attribute per dimension
Because there is no All member, each non-aggregatable attribute will always have some non-all member selected, even if not specified in a query. Therefore, if you include multiple non-aggregatable attributes in a dimension, the selected attributes will conflict and produce unexpected numbers.
For example, in a time dimension it might not make sense to sum the members of [Calendar Year] or [Fiscal Year], but if both are made non-aggregatable, whenever a user asks for data for a specific [Calendar Year] it will be filtered by the default [Fiscal Year] unless they also specify the [Fiscal Year]. Worse, because [Calendar Year] and [Fiscal Year] do not align but overlap, it is difficult to obtain the full data for either a [Calendar Year] or a [Fiscal Year] because the one is filtered by the other.
Do use key columns that completely and correctly define the uniqueness of the members in an attribute
Usually a single key column is sufficient, but sometimes multiple key columns are necessary to uniquely identify members of an attribute. For example, it is common in time dimensions to have a [Month] attribute include both [Year] and [Month Name] as key columns. This is known as a composite key and identifies January of 1997 as being a different member than January of 1998. When you use [Month] in a time hierarchy that also contains [Year], this distinction between January of 1997 and January of 1998 is important.
It may also make sense to have a separate [Month of Year] attribute that has only [Month Name] as the key. This [Month of Year] attribute contains a single January member that spans all years, which can be useful for comparing seasonal data. However, this attribute should not be used in a hierarchy together with [Year] because there is no relationship between [Month of Year] and [Year].
Similar distinctions between [Quarter] and [Quarter of Year], [Semester] and [Semester of Year], and so on should also be made by setting appropriate key columns.
Do perform Process Index after doing a Process Update if the dimension contains flexible AttributeRelationships or a parent-child hierarchy
An aggregation is considered flexible if any attribute included in the aggregation is related, either directly or indirectly, to the key of its dimension through an AttributeRelationship with RelationshipType set to Flexible. Aggregations that include parent-child hierarchies are also considered flexible.
When a dimension is processed by using the Process Update option, any flexible aggregations that the dimension participates in might be dropped, depending on the contents of the new dimension data. These aggregations are not rebuilt by default, so Process Index must then be explicitly performed to rebuild them.
Do use numeric keys for attributes that contain many members (>1 million)
Using a numeric key column instead of a string key column or a composite key will improve the performance of attributes that contain many members. This best practice is based on the same concept as using surrogate keys in relational tables for more efficient indexing. You can specify the numeric surrogate column as the key column and still use a string column as the name column so that the attribute members appear the same to end-users. As a guideline, if the attribute has more than one million members, you should consider using a numeric key.
Do not create redundant attribute relationships
Do not create attribute relationships that are transitively implied by other attribute relationships. The alternative paths created by these redundant attribute relationships can cause problems for the server and are of no benefit to the dimension. For example, if the relationships A->B, B->C, and A->C have been created, A->C is redundant and should be removed.
Do include the key columns of snowflake tables joined to nullable foreign keys as attributes that have NullProcessing set to UnknownMember
If tables that are used in a dimension are joined on a foreign key column that might contain nulls, it is important that you include in your design an attribute whose key column is the corresponding key in the lookup table. Without such an attribute, the OLAP server would have to issue a query to join the two tables during dimension processing. This makes processing slower; moreover, the default join that is created by the OLAP server would exclude any rows that contain nulls in the foreign key column. It is important to set the NullProcessing option on the key column of this attribute to UnknownMember. The reason is that, by default, nulls are converted to zeros or blanks when the engine processes attributes. This can be dangerous when you are processing a nullable foreign key. Conversion of a null to zero at best produces an error; in the worst case, the zero may be a legitimate value in the lookup table, thereby producing incorrect results.
To handle nullable foreign keys correctly, you must also set UnknownMember to Visible on the dimension. The Cube Wizard and Dimension Wizard currently set this property automatically; however, the Dimension Wizard lets you manually de-select the key attribute of snowflake tables. You must not deselect the key column if the corresponding foreign key is nullable.
If you do not want to browse the attribute that contains the lookup table key column, you can set AttributeHierarchyVisible to False. However, AttributeHierarchyEnabled must be set to True because it is necessary that all other attributes in the lookup table be directly or indirectly related to the lookup key attribute in order to avoid the automatic creation of new joins during dimension processing.
Do set the RelationshipType property appropriately on AttributeRelationships based on whether the relationships between individual members change over time
The relationships between members of some attributes, such as dates in a given month or the gender of a customer, are not expected to change. Other relationships, such as salespeople in a given region or the marital status of a customer, are more prone to change over time. You should set RelationshipType to Flexible for those relationships that are expected to change and set RelationshipType to Rigid for relationships that are not expected to change.
When you set RelationshipType appropriately, the server can optimize the processing of changes and re-building of aggregations.
By default, the user interface always sets RelationshipType to Flexible.
Avoid using ErrorConfigurations with KeyDuplicate set to IgnoreError on dimensions
When KeyDuplicate is set to IgnoreError, it can be difficult to detect problems with incorrect key columns, incorrectly defined AttributeRelationships, and data consistency issues. Instead of using the IgnoreError option, in most cases it is better to correct your design and clean the data. The IgnoreError option may be useful in prototypes where correctness is less of a concern. Be aware that the default value for KeyDuplicate is IgnoreError. Therefore, it is important to change this value after prototyping is complete to ensure data consistency.
Do define explicit default members for non-aggregatable attributes
By default, the All member is used as the default member for aggregatable attributes. This default works very well for aggregatable attributes, but non-aggregatable attributes have no obvious choice for the server to use as a default member, therefore a member will be selected arbitrarily. This arbitrarily selected member is then selected whenever the attribute is not explicitly included in an MDX query. To avoid this, it is important to explicitly set a default value for each non-aggregatable attribute.
Default members can be explicitly set either on the DimensionAttribute or in the cube script.
Avoid creating user-defined hierarchies that do not have attribute relationships relating each level to the level above
Having attribute relationships between every level in a hierarchy makes the hierarchy strong and enables significant server optimizations.
Avoid creating diamond-shaped attribute relationships
A Diamond-shaped relationship refers to a chain of attribute relationships that splits and rejoins but contains no redundant relationships. For example, Day->Month->Year and Day->Quarter->Year have the same start and end points, but do not have any common relationships. The presence of multiple paths can create some ambiguity on the server. If preserving the multiple paths is important, it is strongly recommended that you resolve the ambiguity by creating user hierarchies that contain all the paths.
Consider setting AttributeHierarchyEnabled to False on attributes that have cardinality that closely matches the key attribute
When an attribute contains roughly one value for each distinct value of the key attribute, it usually means that the attribute contains only alternative identification information or secondary details. Such attributes are usually not interesting to pivot or group by. For example, the Social Security number or telephone number may be interesting properties to view, but there is very little value in being able to pivot and group based on SSN or telephone. Setting AttributeHierarchyEnabled to False on such attributes will reduce the complexity of the dimension for end-users and improve its performance.
If you want to be able to browse such attributes, you can set AttributeHierarchyEnabled to True; however, you should consider setting AttributeHierarchyOptimized to NotOptimized and setting GroupingBehavior to DiscourageGrouping. By setting these properties, you can improve performance and indicate to the users that the attribute is not very useful for grouping.
Consider setting AttributeHierarchyVisible to False on the key attribute of parent-child dimensions
Because the members of the key attribute are also contained in the parent-child hierarchy in a more organized manner, it is usually unnecessary and confusing to the end-user to expose the flat list of members contained in the key attribute.
Avoid setting UnknownMember=Hidden
When you suppress unknown members, the effect is to hide relational integrity issues; moreover, because hidden members might contain data, results might appear not to add up. Therefore, we recommend that you avoid use of this setting except in prototype applications.
Do use MOLAP storage mode for dimensions with outline calculations (custom rollups, semi-additive measures, and unary operators)
Dimensions that contain custom rollups or unary operators will perform significantly better using MOLAP storage. The following dimension types will also benefit from using MOLAP storage: an Account dimension in a measure group that contains measures aggregated using ByAccount; the first time dimension in a measure group that contains other semi-additive measures.
Do use a 64 bit server if you have dimensions with more than 10 million members
If a dimension contains more than 10 million members, using an x64 or an IA-64-based server is recommended for better performance.
Do set the OrderBy property for time attributes and other attributes whose natural ordering is not alphabetical
By default, the server orders attribute members alphabetically, by name. This ordering is especially undesirable for time attributes. To obtain the desired ordering, use the OrderBy and OrderByAttributes properties and explicitly specify how you want the members ordered. For time-based attributes, there is frequently a date or numeric key column that can be used to obtain the correct chronological ordering.
Do expose a DateTime MemberValue for date attributes
Some clients, such as Excel, will take advantage of the MemberValue property of date members and use the DateTime value that is exposed. When Excel recognizes the value as DateTime, Excel can treat the value as a date type and apply date functions to the value, as well as provide better formatting and filtering. If the key column is a single DateTime column and the name column has not been set, this MemberValue is automatically derived from the key column and no action is necessary. However, in other cases, you can ensure that the MemberValue is DateTime by explicitly specifying the ValueColumn property of the attribute.
Do set AttributeHierarchyEnabled to False, specify a ValueColumn and specify the MimeType of the ValueColumn on attributes that contain images
Because there is no value in browsing the member names of an attribute that contains an image, you should disable browsing by setting AttributeHierarchyEnabled to False. To help clients recognize and display the member property of the attribute as an image, specify the ValueColumn property of the attribute and then set MimeType to an appropriate image type.
Avoid setting IsAggregatable to False on any attribute other than the parent attribute in a parent-child dimension
Non-aggregatable attributes have non-all default members. These default members affect the result of queries whenever the attributes are not explicitly included. Because parent-child hierarchies generally represent the most interesting exploration path in dimensions that contain them, it is best to avoid having non-aggregatable attributes other than the parent attribute.
Do set dimension and attribute Type properties correctly for Time, Account, and Geography dimensions
For time dimensions, it is important to set the dimension and attribute types correctly so that time-related MDX functions and the time intelligence of the Business Intelligence Wizard can work correctly. For Account dimensions, it is similarly important to set appropriate account types when using measures with the aggregate function ByAccount. Geography types are not used by the server, but provide information for client applications.
A common mistake is to set the Type property on a dimension but not on an attribute, or vice-versa. Another common mistake when configuring time dimensions is to confuse the different time attribute types, such as [Month] and [Month of Year].
Consider creating user-defined hierarchies whenever you have a chain of related attributes in a dimension
Chains of related attributes usually represent an interesting navigation path for end-users, and defining hierarchies for these will also provide performance benefits.
Do include all desired attributes of a logical business entity in a single dimension instead of splitting them up over several dimensions
In Analysis Services 2000, each hierarchy was in reality a separate dimension and attributes such as gender and age would also be separate dimensions. In Analysis Services 2005, a dimension can and should contain the complete information about a logical business entity, including multiple hierarchies and many attributes. This does not mean that every piece of information available must be included in the dimension, but rather that any desired information should be included in one dimension instead of split over many dimensions.
There are two exceptions to this guideline:
1.
A dimension can only contain one parent-child hierarchy.
2.
To model multiple joins to a lookup table within a dimension's schema, you must create a separate dimension based on the lookup table and then use this as a referenced dimension.
Do not combine unrelated business entities into a single dimension
Combining attributes of independent business entities, such as customer and product or warehouse and time, into a single dimension will not only create a confusing model, but also reduce query performance because auto-exist will be applied across attributes within the dimension.
Another way to state this rule is that the values of the key attribute of a dimension should uniquely identify a single business entity and not a combination of entities. Generally this means having a single column key for the key attribute.
Do set NullProcessing to UnknownMember on each attribute that has nulls and is used to join to a referenced dimension
By default, nulls are converted to zeros or blanks when the engine processes attributes. This can be dangerous when processing a nullable foreign key, because if a null is converted to zero when zero is a legitimate value in the reference dimension, the join on the values can produce incorrect results. At best, conversion to zero will produce an error.
To prevent these errors, you must also set UnknownMember to Visible on the referenced dimension.
The Cube Wizard in SQL Server 2005 Analysis Services handles both settings automatically, except when dealing with existing dimensions where UnknownMember is not set to Visible.
Do set NullKeyConvertToUnknown to IgnoreError on the ErrorConfiguration on any measure groups that contain a dimension referenced through a nullable column
By default, nulls are converted to zeros or blanks when the engine processes granularity attributes. This can be dangerous when you are processing a nullable foreign key, because if a null value is converted to zero and zero is a legitimate value in the dimension, the join can produce incorrect results. At best, the conversion will produce errors.
To prevent conversion of nulls, you must also set UnknownMember to Visible on the dimension.
The Cube Wizard in SQL Server 2005 Analysis Services handles these settings automatically, except when dealing with existing dimensions where UnknownMember is not set to Visible.
Consider setting AttributeHierarchyVisible to False for attributes included in user-defined hierarchies
It is usually not necessary to expose an attribute in its own single level hierarchy when that attribute is included in a user-defined hierarchy. This duplication only complicates the end-user experience without providing additional value.
One common case in which it is appropriate to present two views of an attribute is in time dimensions. The ability to browse by [Month] and the ability to browse by [Month-Quarter-Year] are both very valuable. However, these two month attributes are actually separate attributes. The first contains only the month value such as “January” while the second contains the month and the year such as “January 1998”.
Do not use proactive caching settings that put dimensions into ROLAP mode
For performance reasons, we strongly discourage the use of dimension proactive caching settings that may put the dimension in ROLAP mode. To ensure that a dimension with proactive caching enabled will never enter ROLAP mode, you should set the OnlineMode property to OnCacheComplete. You can also prevent use of ROLAP mode by deselecting the Bring online immediately check box in the Storage Options dialog box.
Avoid making an attribute non-aggregatable unless it is at the end of the longest chain of attribute relationships in the dimension
Non-aggregatable attributes have non-all default members that affect the result of queries in which values for those attributes are not explicitly specified. Therefore, you should avoid making an attribute non-aggregatable unless that attribute is regularly used. Because the longest chain of attributes generally represents the most interesting exploration path for users, it is best to avoid having non-aggregatable attributes in other, less interesting chains.
Consider creating at least one user-defined hierarchy in each dimension that does not contain a parent-child hierarchy
Most (but not all) dimensions contain some hierarchical structure to the data which is worth exposing in the cube. Frequently the Cube Wizard or Dimension Wizard will not detect this hierarchy. In these cases, you should define a hierarchy manually.
Do set the InstanceSelection property on attributes to help clients determine the best way to display attributes for member selection
If there are too many members to display in a single list, the client user interface can use other methods, such as filtered lists, to display the members. By setting the InstanceSelection property, you provide a hint to client applications to suggest how a list of items should be displayed, based on the expected number of items in the list.
代理键应该是人造的
In a temporal database, it is necessary to distinguish between the surrogate key and the primary key. Typically, every row would have both a primary key and a surrogate key. The primary key identifies the unique row in the database, the surrogate key identifies the unique entity in the modelled world; these two keys are not the same. For example, table Staff may contain two rows for "John Smith", one row when he was employed between 1990 and 1999, another row when he was employed between 2001 and 2006. The surrogate key is identical (non-unique) in both rows however the primary key will be unique.
微软总部Microsoft Marketing数据分析项目等
笔者在实际工作中，有幸接触到海量的数据处理问题，对其进行处理是一项艰巨而复杂的任务。原因有以下几个方面：
一、数据量过大，数据中什么情况都可能存在。如果说有10条数据，那么大不了每条去逐一检查，人为处理，如果有上百条数据，也可以考虑，如果数据上到千万级别，甚至过亿，那不是手工能解决的了，必须通过工具或者程序进行处理，尤其海量的数据中，什么情况都可能存在，例如，数据中某处格式出了问题，尤其在程序处理时，前面还能正常处理，突然到了某个地方问题出现了，程序终止了。
二、软硬件要求高，系统资源占用率高。对海量的数据进行处理，除了好的方法，最重要的就是合理使用工具，合理分配系统资源。一般情况，如果处理的数据过TB级，小型机是要考虑的，普通的机子如果有好的方法可以考虑，不过也必须加大CPU和内存，就象面对着千军万马，光有勇气没有一兵一卒是很难取胜的。
三、要求很高的处理方法和技巧。这也是本文的写作目的所在，好的处理方法是一位工程师长期工作经验的积累，也是个人的经验的总结。没有通用的处理方法，但有通用的原理和规则。
那么处理海量数据有哪些经验和技巧呢，我把我所知道的罗列一下，以供大家参考：
一、选用优秀的数据库工具
现在的数据库工具厂家比较多，对海量数据的处理对所使用的数据库工具要求比较高，一般使用Oracle或者DB2，微软公司最近发布的SQL Server 2005性能也不错。另外在BI领域：数据库，数据仓库，多维数据库，数据挖掘等相关工具也要进行选择，象好的ETL工具和好的OLAP工具都十分必要，例如Informatic，Eassbase等。笔者在实际数据分析项目中，对每天6000万条的日志数据进行处理，使用SQL Server 2000需要花费6小时，而使用SQL Server 2005则只需要花费3小时。
二、编写优良的程序代码
处理数据离不开优秀的程序代码，尤其在进行复杂数据处理时，必须使用程序。好的程序代码对数据的处理至关重要，这不仅仅是数据处理准确度的问题，更是数据处理效率的问题。良好的程序代码应该包含好的算法，包含好的处理流程，包含好的效率，包含好的异常处理机制等。
三、对海量数据进行分区操作
对海量数据进行分区操作十分必要，例如针对按年份存取的数据，我们可以按年进行分区，不同的数据库有不同的分区方式，不过处理机制大体相同。例如SQL Server的数据库分区是将不同的数据存于不同的文件组下，而不同的文件组存于不同的磁盘分区下，这样将数据分散开，减小磁盘I/O，减小了系统负荷，而且还可以将日志，索引等放于不同的分区下。
四、建立广泛的索引
对海量的数据处理，对大表建立索引是必行的，建立索引要考虑到具体情况，例如针对大表的分组、排序等字段，都要建立相应索引，一般还可以建立复合索引，对经常插入的表则建立索引时要小心，笔者在处理数据时，曾经在一个ETL流程中，当插入表时，首先删除索引，然后插入完毕，建立索引，并实施聚合操作，聚合完成后，再次插入前还是删除索引，所以索引要用到好的时机，索引的填充因子和聚集、非聚集索引都要考虑。
五、建立缓存机制
当数据量增加时，一般的处理工具都要考虑到缓存问题。缓存大小设置的好差也关系到数据处理的成败，例如，笔者在处理2亿条数据聚合操作时，缓存设置为100000条/Buffer，这对于这个级别的数据量是可行的。
六、加大虚拟内存
如果系统资源有限，内存提示不足，则可以靠增加虚拟内存来解决。笔者在实际项目中曾经遇到针对18亿条的数据进行处理，内存为1GB，1个P4 2.4G的CPU，对这么大的数据量进行聚合操作是有问题的，提示内存不足，那么采用了加大虚拟内存的方法来解决，在6块磁盘分区上分别建立了6个4096M的磁盘分区，用于虚拟内存，这样虚拟的内存则增加为 4096*6 + 1024 = 25600 M，解决了数据处理中的内存不足问题。
七、分批处理
海量数据处理难因为数据量大，那么解决海量数据处理难的问题其中一个技巧是减少数据量。可以对海量数据分批处理，然后处理后的数据再进行合并操作，这样逐个击破，有利于小数据量的处理，不至于面对大数据量带来的问题，不过这种方法也要因时因势进行，如果不允许拆分数据，还需要另想办法。不过一般的数据按天、按月、按年等存储的，都可以采用先分后合的方法，对数据进行分开处理。
八、使用临时表和中间表
数据量增加时，处理中要考虑提前汇总。这样做的目的是化整为零，大表变小表，分块处理完成后，再利用一定的规则进行合并，处理过程中的临时表的使用和中间结果的保存都非常重要，如果对于超海量的数据，大表处理不了，只能拆分为多个小表。如果处理过程中需要多步汇总操作，可按汇总步骤一步步来，不要一条语句完成，一口气吃掉一个胖子。
九、优化查询SQL语句
在对海量数据进行查询处理过程中，查询的SQL语句的性能对查询效率的影响是非常大的，编写高效优良的SQL脚本和存储过程是数据库工作人员的职责，也是检验数据库工作人员水平的一个标准，在对SQL语句的编写过程中，例如减少关联，少用或不用游标，设计好高效的数据库表结构等都十分必要。笔者在工作中试着对1亿行的数据使用游标，运行3个小时没有出结果，这是一定要改用程序处理了。
十、使用文本格式进行处理
对一般的数据处理可以使用数据库，如果对复杂的数据处理，必须借助程序，那么在程序操作数据库和程序操作文本之间选择，是一定要选择程序操作文本的，原因为：程序操作文本速度快；对文本进行处理不容易出错；文本的存储不受限制等。例如一般的海量的网络日志都是文本格式或者csv格式（文本格式），对它进行处理牵扯到数据清洗，是要利用程序进行处理的，而不建议导入数据库再做清洗。
十一、 定制强大的清洗规则和出错处理机制
海量数据中存在着不一致性，极有可能出现某处的瑕疵。例如，同样的数据中的时间字段，有的可能为非标准的时间，出现的原因可能为应用程序的错误，系统的错误等，这是在进行数据处理时，必须制定强大的数据清洗规则和出错处理机制。
十二、 建立视图或者物化视图
视图中的数据来源于基表，对海量数据的处理，可以将数据按一定的规则分散到各个基表中，查询或处理过程中可以基于视图进行，这样分散了磁盘I/O，正如10根绳子吊着一根柱子和一根吊着一根柱子的区别。
十三、 避免使用32位机子（极端情况）
目前的计算机很多都是32位的，那么编写的程序对内存的需要便受限制，而很多的海量数据处理是必须大量消耗内存的，这便要求更好性能的机子，其中对位数的限制也十分重要。
十四、 考虑操作系统问题
海量数据处理过程中，除了对数据库，处理程序等要求比较高以外，对操作系统的要求也放到了重要的位置，一般是必须使用服务器的，而且对系统的安全性和稳定性等要求也比较高。尤其对操作系统自身的缓存机制，临时空间的处理等问题都需要综合考虑。
十五、 使用数据仓库和多维数据库存储
数据量加大是一定要考虑OLAP的，传统的报表可能5、6个小时出来结果，而基于Cube的查询可能只需要几分钟，因此处理海量数据的利器是OLAP多维分析，即建立数据仓库，建立多维数据集，基于多维数据集进行报表展现和数据挖掘等。
十六、 使用采样数据，进行数据挖掘
基于海量数据的数据挖掘正在逐步兴起，面对着超海量的数据，一般的挖掘软件或算法往往采用数据抽样的方式进行处理，这样的误差不会很高，大大提高了处理效率和处理的成功率。一般采样时要注意数据的完整性和，防止过大的偏差。笔者曾经对1亿2千万行的表数据进行采样，抽取出400万行，经测试软件测试处理的误差为千分之五，客户可以接受。
还有一些方法，需要在不同的情况和场合下运用，例如使用代理键等操作，这样的好处是加快了聚合时间，因为对数值型的聚合比对字符型的聚合快得多。类似的情况需要针对不同的需求进行处理。
海量数据是发展趋势，对数据分析和挖掘也越来越重要，从海量数据中提取有用信息重要而紧迫，这便要求处理要准确，精度要高，而且处理时间要短，得到有价值信息要快，所以，对海量数据的研究很有前途，也很值得进行广泛深入的研究
创新性应用 数据建模经验谈
2007-09-27 20:38:45 作者： 来源：互联网 文字大小：【大】【中】【小】
笔者从98年进入数据库及数据仓库领域工作至今已经有近八年的时间，对数据建模工作接触的比较多，创新性不敢谈，本文只是将工作中的经验总结出来，供大家一同探讨和指正。 提起数据建模来，有一点是首先 ...
笔者从98年进入数据库及数据仓库领域工作至今已经有近八年的时间，对数据建模工作接触的比较多，创新性不敢谈，本文只是将工作中的经验总结出来，供大家一同探讨和指正。
提起数据建模来，有一点是首先要强调的，数据建模师和DBA有着较大的不同，对数据建模师来说，对业务的深刻理解是第一位的，不同的建模方法和技巧是为业务需求来服务的。而本文则暂时抛开业务不谈，主要关注于建模方法和技巧的经验总结。
从目前的数据库及数据仓库建模方法来说，主要分为四类。
第一类是大家最为熟悉的关系数据库的三范式建模，通常我们将三范式建模方法用于建立各种操作型数据库系统。
第二类是Inmon提倡的三范式数据仓库建模，它和操作型数据库系统的三范式建模在侧重点上有些不同。Inmon的数据仓库建模方法分为三层，第一层是实体关系层，也即企业的业务数据模型层，在这一层上和企业的操作型数据库系统建模方法是相同的；第二层是数据项集层，在这一层的建模方法根据数据的产生频率及访问频率等因素与企业的操作型数据库系统的建模方法产生了不同；第三层物理层是第二层的具体实现。
第三类是Kimball提倡的数据仓库的维度建模，我们一般也称之为星型结构建模，有时也加入一些雪花模型在里面。维度建模是一种面向用户需求的、容易理解的、访问效率高的建模方法，也是笔者比较喜欢的一种建模方式。
第四类是更为灵活的一种建模方式，通常用于后台的数据准备区，建模的方式不拘一格，以能满足需要为目的，建好的表不对用户提供接口，多为临时表。
下面简单谈谈第四类建模方法的一些的经验。
数据准备区有一个最大的特点，就是不会直接面对用户，所以对数据准备区中的表进行操作的人只有ETL工程师。ETL工程师可以自己来决定表中数据的范围和数据的生命周期。下面举两个例子：
1）数据范围小的临时表
当需要整合或清洗的数据量过大时，我们可以建立同样结构的临时表，在临时表中只保留我们需要处理的部分数据。这样，不论是更新还是对表中某些项的计算都会效率提高很多。处理好的数据发送入准备加载到数据仓库中的表中，最后一次性加载入数据仓库。
2）带有冗余字段的临时表
由于数据准备区中的表只有自己使用，所以建立冗余字段可以起到很好的作用而不用承担风险。
举例来说，笔者在项目中曾遇到这样的需求，客户表{客户ID，客户净扣值}，债项表{债项ID，客户ID，债项余额，债项净扣值}，即客户和债项是一对多的关系。其中，客户净扣值和债项余额已知，需要计算债项净扣值。计算的规则是按债项余额的比例分配客户的净扣值。这时，我们可以给两个表增加几个冗余字段，如客户表{客户ID，客户净扣值，客户余额}，债项表{债项ID，客户ID，债项余额，债项净扣值，客户余额，客户净扣值}。这样通过三条SQL就可以直接完成整个计算过程。将债项余额汇总到客户余额，将客户余额和客户净扣值冗余到债项表中，在债项表中通过（债项余额×客户净扣值/客户余额）公式即可直接计算处债项净扣值。
另外还有很多大家可以发挥的建表方式，如不需要主键的临时表等等。总结来说，正因为数据准备区是不对用户提供接口的，所以我们一定要利用好这一点，以给我们的数据处理工作带来最大的便利为目的来进行数据准备区的表设计。
行业借鉴经验：
数据仓库架构经验谈
对于数据仓库的架构方法，不同的架构师有不同的原则和方法，笔者在这里来总结一下当前常采用的架构方式及其优缺点。这些架构方式不限于某个行业，可以供各个行业借鉴使用。
首先需要说明的一点是，目前在数据仓库领域比较一致的意见是在数据仓库中需要保留企业范围内一致的原子层数据。而独立的数据集市架构（Independent data marts）没有企业范围内一致的数据，很可能会导致信息孤岛的产生，除非在很小的企业内或只针对固定主题，否则不建议建立这样的架构方式。联邦式的数据仓库架构（Federated Data Warehouse Architecture）不管是在地域上的联邦还是功能上的联邦都需要先在不同平台上建立各自的数据仓库，再通过参考（reference）数据来实现整合，而这样很容易造成整合的不彻底，除非联邦式的数据仓库架构也采用Kimball的总线架构（Bus Architecture）中类似的功能，即在数据准备区保留一致性维度（Conformed Table）并不断更新它。所以，这两种架构方式不在讨论范围之内。下面主要讨论剩下的三种架构方式。
1）三范式（3NF）的原子层＋数据集市
这样的数据仓库架构最大的倡导者就是数据仓库之父Inmon，而他的企业信息工厂（Corporate Information System）就是典型的代表。这样的架构也称之为企业数据仓库（Enterprise Data Warehouse，EDW）。企业信息工厂的实现方式是，首先进行全企业的数据整合，建立企业信息模型，即EDW。对于各种分析需求再建立相应的数据集市或者探索仓库，其数据来源于EDW。三范式的原子层给建立OLAP带来一定的复杂性，但是对于建立更复杂的应用，如挖掘仓库、探索仓库提供了更好的支持。这类架构的建设周期比较长，相应的成本也比较高。
2）星型结构（Star Schema）的原子层＋HOLAP
星型结构最大的倡导者是Kimall，他的总线架构是该类架构的典型代表。总线架构实现方式是，首先在数据准备区中建立一致性维度、建立一致性事实的计算方法；其次在一致性维度、一致性事实的基础上逐步建立数据集市。每次增加数据集市，都会在数据准备区整合一致性维度，并将整合好的一致性维度同步更新到所有的数据集市。这样，建立的所有数据集市合在一起就是一个整合好的数据仓库。正是因为总线架构这个可以逐步建立的特点，它的开发周期比其他架构方式的开发周期要短，相应的成本也要低。在星型结构的原子层上可以直接建立聚集，也可以建立HOLAP。笔者比较倾向于Kimball的星型结构的原子层架构，在这种架构中的经验也比较多。
3）三范式（3NF）的原子层＋ROLAP
这样的数据仓库架构也称为集中式架构（Centralized Architecture），思路是在三范式的原子层上直接建立ROLAP，做的比较出色的就是MicroStrategy。在三范式的原子层上定义ROLAP比在星型结构的原子层上定义ROLAP要复杂很多。采用这种架构需要在定义ROLAP是多下些功夫，而且ROLAP的元数据不一定是通用的格式，所以对ROLAP做展现很可能会受到工具的局限。这类架构和第一类很相似，只是少了原子层上的数据集市。
总结来说，这三种数据仓库的架构方式都是不错的选择。对于需要见效快、成本低的项目可以考虑采用第二种总线架构，对于资金充足并有成熟业务数据模型的企业可以考虑采用第一种架构或第三种架构。
应用难点技巧：
变化数据捕获经验谈
在数据仓库系统中，一个很重要的目的就是保留数据的历史变化信息。而变化数据捕获（Change Data Capture，CDC）就是为这个目的而产生的一项技术。变化数据捕获常用的方法有：1）文件或者表的全扫描对比，2）DBMS日志获取，3）在源系统中增加触发器获取，4）基于源系统的时间戳获取，5）基于复制技术的获取，6）DBMS提供的变化数据捕获方法等。其中，由DBMS提供变化数据捕获的方法是大势所趋，即具体的捕获过程由DBMS来完成。
像银行、电信等很多行业的操作记录生成后就不会改变，只有像客户、产品等信息会随时间发生缓慢的变化，所以通常的变化数据捕获是针对维度表而言的。Kimball对缓慢变化维的分析及应对策略基本上可以处理维度表的各种变化。
而对于一些零售行业，像合同表中的合同金额类似的数值在录入后是有可能会发生改变的，也就是说事实表的数据也有可能发生变化。通常对于事实表数据的修改属于勘误的范畴，可以采用类似缓慢变化维TYPE 1的处理方式直接更新事实表。笔者不太赞同对事实表的变化采用快照的方式插入一条新的事实勘误记录，这样会给后续的展现、分析程序带来太多的麻烦。
接下来要讨论的是笔者曾经遇到的一个颇为棘手的事实表数据改变的问题，该事实表的主键随表中某些数据的变化发生改变。以其中的一个合同表为例，该合同表的主键是由“供货单位编号”＋“合同号”生成的智能主键，当其中的“供货单位编号”和“合同号”中任何一个发生变化时，该合同表的主键都会发生变化，给变化数据捕获带来了很大的麻烦。
项目中，笔者的处理方式是采用触发器的办法来实现变化数据捕获。具体的实现方式是：
1）建立一个新表作为保存捕获的数据表使用，其中字段有“原主键”、“修改后主键”、及其他需要的字段，称为“合同捕获表”。
2）在原合同表Delete和Update时分别建立触发器，当删除操作发生时，建在Delete上的触发器会插入一条记录到“合同捕获表”，其中“修改后主键”字段为空，表示该记录是删除的记录；当发生更新时，将“原主键”、“修改后主键”及其他需要记录的字段都保存入“合同捕获表”中，表示该记录被修改过，如果“原主键”和“修改后主键”不同，则表示主键被修改，如果相同，则表示主键没有被修改。
3）由于操作系统中的主键通常会成为数据仓库中事实表的退化维度，可能仍会起主键的作用。所以在数据加载时，需要分情况判断“合同捕获表”的数据来决定是否更新事实表中的退化维度。
可以说，这样的基于触发器的变化数据捕获方法并不是一个很好的选择。首先这需要对源系统有较大的权限；其次，触发器会给源系统的性能带来很大的影响。所以除非是没有别的选择，否则不建议采用这种方法。
而对于这样的情况，我们在建立操作型数据库系统时完全可以避免。下面是对操作型数据库系统建立者的几点建议：1）操作型系统的主键不要建立成智能型的，至少不要建立成会变化的。2）操作型系统的表中需要加入操作人和操作时间字段，或者直接加入时间戳。3）操作型系统中操作型数据最好不要直接在原值上修改，可以采用“冲红”的方式加入新的记录。这样后续建立数据仓库时就不需要考虑事实表数据的变化问题。
最后，期待各大数据库管理系统的厂商能尽快在DBMS层提供功能强大、简单好用的变化数据捕获功能，目前Oracle已经有了这个功能。毕竟技术方面复杂的事情留给厂商做是一个趋势，而我们做应用的则更关注于业务。
扩展的星型模型
在数据仓库的数据库设计中,星型模型是一种基本的数据模式。星型模式是一种多维的数据关系,它由一个事实表(ＦａｃｔＴａｂｌｅ)和一组维表(ＤｉｍｅｎｓｉｏｎＴａｂｌｅ)组成。每个维表都有一个维作为主键,所有这些维则组合成事实表的主键,换言之,事实表主键的每个元素都是维表的外键。事实表的非主属性称为事实(Ｆａｃｔ),它们一般都是数值或其他可以进行计算的数据;而维大都是文字、时间等类型的数据。如图2所示为扩展的星型模型:
采用这种扩展的星型模型,多层分维结构减少了一级分维表的内容,避免一级分维表中出现大量的重复数据,使得复杂的数据模式保持简洁清晰。
深入探讨数据仓库建模与ETL的实践技巧
( 2008/8/5 10:56 )
本文关键字:
深入探讨了搭建数据仓库过程中应当遵循的方法和原则，更多内容请参考下文：
一、数据仓库的架构
数据仓库（Data Warehouse \ DW）是为了便于多维分析和多角度展现而将数据按特定的模式进行存储所建立起来的关系型数据库，它的数据基于OLTP源系统。数据仓库中的数据是细节的、集成的、面向主题的，以OLAP系统的分析需求为目的。
数据仓库的架构模型包括了星型架构（图二：pic2.bmp）与雪花型架构（图三：pic3.bmp）两种模式。如图所示，星型架构的中间为事实表，四周为维度表，类似星星；而相比较而言，雪花型架构的中间为事实表，两边的维度表可以再有其关联子表，从而表达了清晰的维度层次关系。
从OLAP系统的分析需求和ETL的处理效率两方面来考虑：星型结构聚合快，分析效率高；而雪花型结构明确，便于与OLTP系统交互。因此，在实际项目中，我们将综合运用星型架构与雪花型架构来设计数据仓库。
那么，下面我们就来看一看，构建企业级数据仓库的流程。
二、构建企业级数据仓库五步法
（一）、确定主题
即确定数据分析或前端展现的主题。例如：我们希望分析某年某月某一地区的啤酒销售情况，这就是一个主题。主题要体现出某一方面的各分析角度（维度）和统计数值型数据（量度）之间的关系，确定主题时要综合考虑。
我们可以形象的将一个主题想象为一颗星星：统计数值型数据（量度）存在于星星中间的事实表；分析角度（维度）是星星的各个角；我们将通过维度的组合，来考察量度。那么，“某年某月某一地区的啤酒销售情况”这样一个主题，就要求我们通过时间和地区两个维度的组合，来考察销售情况这个量度。从而，不同的主题来源于数据仓库中的不同子集，我们可以称之为数据集市。数据集市体现了数据仓库某一方面的信息，多个数据集市构成了数据仓库。
（二）、确定量度
在确定了主题以后，我们将考虑要分析的技术指标，诸如年销售额之类。它们一般为数值型数据。我们或者将该数据汇总，或者将该数据取次数、独立次数或取最大最小值等，这样的数据称为量度。
量度是要统计的指标，必须事先选择恰当，基于不同的量度可以进行复杂关键性能指标（KPI）等的设计和计算。
（三）、确定事实数据粒度
在确定了量度之后，我们要考虑到该量度的汇总情况和不同维度下量度的聚合情况。考虑到量度的聚合程度不同，我们将采用“最小粒度原则”，即将量度的粒度设置到最小。
例如：假设目前的数据最小记录到秒，即数据库中记录了每一秒的交易额。那么，如果我们可以确认，在将来的分析需求中，时间只需要精确到天就可以的话，我们就可以在ETL处理过程中，按天来汇总数据，此时，数据仓库中量度的粒度就是“天”；反过来，如果我们不能确认将来的分析需求在时间上是否需要精确到秒，那么，我们就需要遵循“最小粒度原则”，在数据仓库的事实表中保留每一秒的数据，以便日后对“秒”进行分析。
在采用“最小粒度原则”的同时，我们不必担心海量数据所带来的汇总分析效率问题，因为在后续建立多维分析模型（CUBE）的时候，我们会对数据提前进行汇总，从而保障产生分析结果的效率。关于建立多维分析模型（CUBE）的相关问题，我们将在下期栏目中予以阐述。
（四）、确定维度
维度是指分析的各个角度。例如我们希望按照时间，或者按照地区，或者按照产品进行分析，那么这里的时间、地区、产品就是相应的维度。基于不同的维度，我们可以看到各量度的汇总情况，也可以基于所有的维度进行交叉分析。
这里我们首先要确定维度的层次（Hierarchy）和级别（Level）（图四：pic4.bmp）。如图所示，我们在时间维度上，按照“年-季度-月”形成了一个层次，其中“年”、“季度”、“月”成为了这个层次的3个级别；同理，当我们建立产品维度时，我们可以将“产品大类-产品子类-产品”划为一个层次，其中包含“产品大类”、“产品子类”、“产品”三个级别。
那么，我们分析中所用到的这些维度，在数据仓库中的存在形式是怎样的呢？
我们可以将3个级别设置成一张数据表中的3个字段，比如时间维度；我们也可以使用三张表，分别保存产品大类、产品子类、产品三部分数据，比如产品维度。（图五：pic5.bmp）
另外，值得一提的是，我们在建立维度表时要充分使用代理键。代理键是数值型的ID号码（例如图六中每张表的第一个字段），它唯一标识了每一维度成员。更重要的是，在聚合时，数值型字段的匹配和比较，JOIN效率高，便于聚合。同时，代理键对缓慢变化维度有着重要的意义，在原数据主键相同的情况下，它起到了对新数据与历史数据的标识作用。
在此，我们不妨谈一谈维度表随时间变化的问题，这是我们经常会遇到的情况，我们称其为缓慢变化维度。
比如我们增加了新的产品，或者产品的ID号码修改了，或者产品增加了一个新的属性，此时，维度表就会被修改或者增加新的记录行。这样，我们在ETL的过程中，就要考虑到缓慢变化维度的处理。对于缓慢变化维度，有三种情况：
1、缓慢变化维度第一种类型：
历史数据需要修改。这种情况下，我们使用UPDATE方法来修改维度表中的数据。例如：产品的ID号码为123，后来发现ID号码错了，需要改写成456，那么，我们就在ETL处理时，直接修改维度表中原来的ID号码为456。
2、缓慢变化维度第二种类型：
历史数据保留，新增数据也要保留。这时，要将原数据更新，将新数据插入，我们使用UPDATE / INSERT。比如：某一员工2005年在A部门，2006年时他调到了B部门。那么在统计2005年的数据时就应该将该员工定位到A部门；而在统计2006年数据时就应该定位到B部门，然后再有新的数据插入时，将按照新部门（B部门）进行处理，这样我们的做法是将该维度成员列表加入标识列，将历史的数据标识为“过期”，将目前的数据标识为“当前的”。另一种方法是将该维度打上时间戳，即将历史数据生效的时间段作为它的一个属性，在与原始表匹配生成事实表时将按照时间段进行关联，这种方法的好处是该维度成员生效时间明确。
3、缓慢变化维度第三种类型：
新增数据维度成员改变了属性。例如：某一维度成员新加入了一列，该列在历史数据中不能基于它浏览，而在目前数据和将来数据中可以按照它浏览，那么此时我们需要改变维度表属性，即加入新的字段列。那么，我们将使用存储过程或程序生成新的维度属性，在后续的数据中将基于新的属性进行查看
五）、创建事实表
在确定好事实数据和维度后，我们将考虑加载事实表。
在公司的大量数据堆积如山时，我们想看看里面究竟是什么，结果发现里面是一笔笔生产记录，一笔笔交易记录… 那么这些记录是我们将要建立的事实表的原始数据，即关于某一主题的事实记录表。
我们的做法是将原始表与维度表进行关联，生成事实表（图六：pic6.bmp）。注意在关联时有为空的数据时（数据源脏），需要使用外连接，连接后我们将各维度的代理键取出放于事实表中，事实表除了各维度代理键外，还有各量度数据，这将来自原始表，事实表中将存在维度代理键和各量度，而不应该存在描述性信息，即符合“瘦高原则”，即要求事实表数据条数尽量多（粒度最小），而描述性信息尽量少。
如果考虑到扩展，可以将事实表加一唯一标识列，以为了以后扩展将该事实作为雪花型维度，不过不需要时一般建议不用这样做。
事实数据表是数据仓库的核心，需要精心维护，在JOIN后将得到事实数据表，一般记录条数都比较大，我们需要为其设置复合主键和索引，以实现数据的完整性和基于数据仓库的查询性能优化。事实数据表与维度表一起放于数据仓库中，如果前端需要连接数据仓库进行查询，我们还需要建立一些相关的中间汇总表或物化视图，以方便查询。
三、什么是ETL
在数据仓库的构建中，ETL贯穿于项目始终，它是整个数据仓库的生命线，包括了数据清洗、整合、转换、加载等各个过程。如果说数据仓库是一座大厦，那么ETL就是大厦的根基。ETL抽取整合数据的好坏直接影响到最终的结果展现。所以ETL在整个数据仓库项目中起着十分关键的作用，必须摆到十分重要的位置。
ETL是数据抽取（Extract）、转换（Transform）、加载（Load ）的简写，它是指：将OLTP系统中的数据抽取出来，并将不同数据源的数据进行转换和整合，得出一致性的数据，然后加载到数据仓库中。例如：下图就向我们展示了ETL的数据转换效果。
那么，在这一转换过程中，我们就完成了对数据格式的更正、对数据字段的合并、以及新增指标的计算三项操作。类似地，我们也可以根据其他需求，完善数据仓库中的数据。
简而言之，通过ETL，我们可以基于源系统中的数据来生成数据仓库。ETL为我们搭建了OLTP系统和OLAP系统之间的桥梁。
五、项目实践技巧
（一）、准备区的运用
在构建数据仓库时，如果数据源位于一台服务器上，数据仓库在另一台服务器端，考虑到数据源Server端访问频繁，并且数据量大，需要不断更新，所以可以建立准备区数据库（图八：pic8.bmp）。先将数据抽取到准备区中，然后基于准备区中的数据进行处理，这样处理的好处是防止了在原OLTP系统中频繁访问，进行数据运算或排序等操作。
例如我们可以按照天将数据抽取到准备区中，基于数据准备区，我们将进行数据的转换、整合、将不同数据源的数据进行一致性处理。数据准备区中将存在原始抽取表、转换中间表和临时表以及ETL日志表等。
（二）、时间戳的运用
时间维度对于某一事实主题来说十分重要，因为不同的时间有不同的统计数据信息，那么按照时间记录的信息将发挥很重要的作用。在ETL中，时间戳有其特殊的作用，在上面提到的缓慢变化维度中，我们可以使用时间戳标识维度成员；在记录数据库和数据仓库的操作时，我们也将使用时间戳标识信息。例如：在进行数据抽取时，我们将按照时间戳对OLTP系统中的数据进行抽取，比如在午夜0：00取前一天的数据，我们将按照OLTP系统中的时间戳取GETDATE到GETDATE减一天，这样得到前一天数据。
（三）、日志表的运用
在对数据进行处理时，难免会发生数据处理错误，产生出错信息，那么我们如何获得出错信息并及时修正呢? 方法是我们使用一张或多张Log日志表，将出错信息记录下来，在日志表中我们将记录每次抽取的条数、处理成功的条数、处理失败的条数、处理失败的数据、处理时间等等。这样，当数据发生错误时，我们很容易发现问题所在，然后对出错的数据进行修正或重新处理。
（四）、使用调度
在对数据仓库进行增量更新时必须使用调度（图九：pic9.bmp），即对事实数据表进行增量更新处理。在使用调度前要考虑到事实数据量，确定需要多长时间更新一次。比如希望按天进行查看，那么我们最好按天进行抽取，如果数据量不大，可以按照月或半年对数据进行更新。如果有缓慢变化维度情况，调度时需要考虑到维度表更新情况，在更新事实数据表之前要先更新维度表。
调度是数据仓库的关键环节，要考虑缜密。在ETL的流程搭建好后，要定期对其运行，所以调度是执行ETL流程的关键步骤。每一次调度除了写入Log日志表的数据处理信息外，还要使用发送Email或报警服务等，这样也方便的技术人员对ETL流程的把握，增强了安全性和数据处理的准确性。
五、总结
构建企业级数据仓库需要简单的五步，掌握了这五步的方法，我们可以构建一个强大的数据仓库。然而，每一步都有很深的内容需要研究与挖掘，尤其在实际项目中，我们要综合考虑。例如：如果数据源的脏数据很多，在搭建数据仓库之前我们首先要进行数据清洗，以剔除掉不需要的信息和脏数据。
ETL是OLTP系统和OLAP系统之间的桥梁，是数据从源系统流入数据仓库的通道。在数据仓库的项目实施中，它关系到整个项目的数据质量，所以马虎不得，必须将其摆到重要位置，将数据仓库这一大厦的根基筑牢！
***********
数据仓库系统中，一个很重要的目的就是保留数据的历史变化信息。而变化数据捕获（Change Data Capture，CDC）就是为这个目的而产生的一项技术。变化数据捕获常用的方法有：1）文件或者表的全扫描对比，2）DBMS日志获取，3）在源系统中增加触发器获取，4）基于源系统的时间戳获取，5）基于复制技术的获取，6）DBMS提供的变化数据捕获方法等。其中，由DBMS提供变化数据捕获的方法是大势所趋，即具体的捕获过程由DBMS来完成。
QQ08.net
创新性应用 数据建模经验谈
2007-09-27 20:38:45 作者： 来源：互联网 文字大小：【大】【中】【小】
笔者从98年进入数据库及数据仓库领域工作至今已经有近八年的时间，对数据建模工作接触的比较多，创新性不敢谈，本文只是将工作中的经验总结出来，供大家一同探讨和指正。 提起数据建模来，有一点是首先 ...
笔者从98年进入数据库及数据仓库领域工作至今已经有近八年的时间，对数据建模工作接触的比较多，创新性不敢谈，本文只是将工作中的经验总结出来，供大家一同探讨和指正。
提起数据建模来，有一点是首先要强调的，数据建模师和DBA有着较大的不同，对数据建模师来说，对业务的深刻理解是第一位的，不同的建模方法和技巧是为业务需求来服务的。而本文则暂时抛开业务不谈，主要关注于建模方法和技巧的经验总结。
从目前的数据库及数据仓库建模方法来说，主要分为四类。
第一类是大家最为熟悉的关系数据库的三范式建模，通常我们将三范式建模方法用于建立各种操作型数据库系统。
第二类是Inmon提倡的三范式数据仓库建模，它和操作型数据库系统的三范式建模在侧重点上有些不同。Inmon的数据仓库建模方法分为三层，第一层是实体关系层，也即企业的业务数据模型层，在这一层上和企业的操作型数据库系统建模方法是相同的；第二层是数据项集层，在这一层的建模方法根据数据的产生频率及访问频率等因素与企业的操作型数据库系统的建模方法产生了不同；第三层物理层是第二层的具体实现。
第三类是Kimball提倡的数据仓库的维度建模，我们一般也称之为星型结构建模，有时也加入一些雪花模型在里面。维度建模是一种面向用户需求的、容易理解的、访问效率高的建模方法，也是笔者比较喜欢的一种建模方式。
第四类是更为灵活的一种建模方式，通常用于后台的数据准备区，建模的方式不拘一格，以能满足需要为目的，建好的表不对用户提供接口，多为临时表。
下面简单谈谈第四类建模方法的一些的经验。
数据准备区有一个最大的特点，就是不会直接面对用户，所以对数据准备区中的表进行操作的人只有ETL工程师。ETL工程师可以自己来决定表中数据的范围和数据的生命周期。下面举两个例子：
1）数据范围小的临时表
当需要整合或清洗的数据量过大时，我们可以建立同样结构的临时表，在临时表中只保留我们需要处理的部分数据。这样，不论是更新还是对表中某些项的计算都会效率提高很多。处理好的数据发送入准备加载到数据仓库中的表中，最后一次性加载入数据仓库。
2）带有冗余字段的临时表
由于数据准备区中的表只有自己使用，所以建立冗余字段可以起到很好的作用而不用承担风险。
举例来说，笔者在项目中曾遇到这样的需求，客户表{客户ID，客户净扣值}，债项表{债项ID，客户ID，债项余额，债项净扣值}，即客户和债项是一对多的关系。其中，客户净扣值和债项余额已知，需要计算债项净扣值。计算的规则是按债项余额的比例分配客户的净扣值。这时，我们可以给两个表增加几个冗余字段，如客户表{客户ID，客户净扣值，客户余额}，债项表{债项ID，客户ID，债项余额，债项净扣值，客户余额，客户净扣值}。这样通过三条SQL就可以直接完成整个计算过程。将债项余额汇总到客户余额，将客户余额和客户净扣值冗余到债项表中，在债项表中通过（债项余额×客户净扣值/客户余额）公式即可直接计算处债项净扣值。
另外还有很多大家可以发挥的建表方式，如不需要主键的临时表等等。总结来说，正因为数据准备区是不对用户提供接口的，所以我们一定要利用好这一点，以给我们的数据处理工作带来最大的便利为目的来进行数据准备区的表设计。
行业借鉴经验：
数据仓库架构经验谈
对于数据仓库的架构方法，不同的架构师有不同的原则和方法，笔者在这里来总结一下当前常采用的架构方式及其优缺点。这些架构方式不限于某个行业，可以供各个行业借鉴使用。
首先需要说明的一点是，目前在数据仓库领域比较一致的意见是在数据仓库中需要保留企业范围内一致的原子层数据。而独立的数据集市架构（Independent data marts）没有企业范围内一致的数据，很可能会导致信息孤岛的产生，除非在很小的企业内或只针对固定主题，否则不建议建立这样的架构方式。联邦式的数据仓库架构（Federated Data Warehouse Architecture）不管是在地域上的联邦还是功能上的联邦都需要先在不同平台上建立各自的数据仓库，再通过参考（reference）数据来实现整合，而这样很容易造成整合的不彻底，除非联邦式的数据仓库架构也采用Kimball的总线架构（Bus Architecture）中类似的功能，即在数据准备区保留一致性维度（Conformed Table）并不断更新它。所以，这两种架构方式不在讨论范围之内。下面主要讨论剩下的三种架构方式。
1）三范式（3NF）的原子层＋数据集市
这样的数据仓库架构最大的倡导者就是数据仓库之父Inmon，而他的企业信息工厂（Corporate Information System）就是典型的代表。这样的架构也称之为企业数据仓库（Enterprise Data Warehouse，EDW）。企业信息工厂的实现方式是，首先进行全企业的数据整合，建立企业信息模型，即EDW。对于各种分析需求再建立相应的数据集市或者探索仓库，其数据来源于EDW。三范式的原子层给建立OLAP带来一定的复杂性，但是对于建立更复杂的应用，如挖掘仓库、探索仓库提供了更好的支持。这类架构的建设周期比较长，相应的成本也比较高。
2）星型结构（Star Schema）的原子层＋HOLAP
星型结构最大的倡导者是Kimall，他的总线架构是该类架构的典型代表。总线架构实现方式是，首先在数据准备区中建立一致性维度、建立一致性事实的计算方法；其次在一致性维度、一致性事实的基础上逐步建立数据集市。每次增加数据集市，都会在数据准备区整合一致性维度，并将整合好的一致性维度同步更新到所有的数据集市。这样，建立的所有数据集市合在一起就是一个整合好的数据仓库。正是因为总线架构这个可以逐步建立的特点，它的开发周期比其他架构方式的开发周期要短，相应的成本也要低。在星型结构的原子层上可以直接建立聚集，也可以建立HOLAP。笔者比较倾向于Kimball的星型结构的原子层架构，在这种架构中的经验也比较多。
3）三范式（3NF）的原子层＋ROLAP
这样的数据仓库架构也称为集中式架构（Centralized Architecture），思路是在三范式的原子层上直接建立ROLAP，做的比较出色的就是MicroStrategy。在三范式的原子层上定义ROLAP比在星型结构的原子层上定义ROLAP要复杂很多。采用这种架构需要在定义ROLAP是多下些功夫，而且ROLAP的元数据不一定是通用的格式，所以对ROLAP做展现很可能会受到工具的局限。这类架构和第一类很相似，只是少了原子层上的数据集市。
总结来说，这三种数据仓库的架构方式都是不错的选择。对于需要见效快、成本低的项目可以考虑采用第二种总线架构，对于资金充足并有成熟业务数据模型的企业可以考虑采用第一种架构或第三种架构。
应用难点技巧：
变化数据捕获经验谈
在数据仓库系统中，一个很重要的目的就是保留数据的历史变化信息。而变化数据捕获（Change Data Capture，CDC）就是为这个目的而产生的一项技术。变化数据捕获常用的方法有：1）文件或者表的全扫描对比，2）DBMS日志获取，3）在源系统中增加触发器获取，4）基于源系统的时间戳获取，5）基于复制技术的获取，6）DBMS提供的变化数据捕获方法等。其中，由DBMS提供变化数据捕获的方法是大势所趋，即具体的捕获过程由DBMS来完成。
像银行、电信等很多行业的操作记录生成后就不会改变，只有像客户、产品等信息会随时间发生缓慢的变化，所以通常的变化数据捕获是针对维度表而言的。Kimball对缓慢变化维的分析及应对策略基本上可以处理维度表的各种变化。
而对于一些零售行业，像合同表中的合同金额类似的数值在录入后是有可能会发生改变的，也就是说事实表的数据也有可能发生变化。通常对于事实表数据的修改属于勘误的范畴，可以采用类似缓慢变化维TYPE 1的处理方式直接更新事实表。笔者不太赞同对事实表的变化采用快照的方式插入一条新的事实勘误记录，这样会给后续的展现、分析程序带来太多的麻烦。
接下来要讨论的是笔者曾经遇到的一个颇为棘手的事实表数据改变的问题，该事实表的主键随表中某些数据的变化发生改变。以其中的一个合同表为例，该合同表的主键是由“供货单位编号”＋“合同号”生成的智能主键，当其中的“供货单位编号”和“合同号”中任何一个发生变化时，该合同表的主键都会发生变化，给变化数据捕获带来了很大的麻烦。
项目中，笔者的处理方式是采用触发器的办法来实现变化数据捕获。具体的实现方式是：
1）建立一个新表作为保存捕获的数据表使用，其中字段有“原主键”、“修改后主键”、及其他需要的字段，称为“合同捕获表”。
2）在原合同表Delete和Update时分别建立触发器，当删除操作发生时，建在Delete上的触发器会插入一条记录到“合同捕获表”，其中“修改后主键”字段为空，表示该记录是删除的记录；当发生更新时，将“原主键”、“修改后主键”及其他需要记录的字段都保存入“合同捕获表”中，表示该记录被修改过，如果“原主键”和“修改后主键”不同，则表示主键被修改，如果相同，则表示主键没有被修改。
3）由于操作系统中的主键通常会成为数据仓库中事实表的退化维度，可能仍会起主键的作用。所以在数据加载时，需要分情况判断“合同捕获表”的数据来决定是否更新事实表中的退化维度。
可以说，这样的基于触发器的变化数据捕获方法并不是一个很好的选择。首先这需要对源系统有较大的权限；其次，触发器会给源系统的性能带来很大的影响。所以除非是没有别的选择，否则不建议采用这种方法。
而对于这样的情况，我们在建立操作型数据库系统时完全可以避免。下面是对操作型数据库系统建立者的几点建议：1）操作型系统的主键不要建立成智能型的，至少不要建立成会变化的。2）操作型系统的表中需要加入操作人和操作时间字段，或者直接加入时间戳。3）操作型系统中操作型数据最好不要直接在原值上修改，可以采用“冲红”的方式加入新的记录。这样后续建立数据仓库时就不需要考虑事实表数据的变化问题。
最后，期待各大数据库管理系统的厂商能尽快在DBMS层提供功能强大、简单好用的变化数据捕获功能，目前Oracle已经有了这个功能。毕竟技术方面复杂的事情留给厂商做是一个趋势，而我们做应用的则更关注于业务
即在数据准备区保留一致性维度（Conformed Table）并不断更新它。所以，
Dimension Design Best Practices
Good dimension design is the most important aspect of a well designed Analysis Services OLAP database. Although the wizards in Analysis Services do much of the work to get you started, it is important to review the design that is created by the wizard and ensure that the attributes, relationships, and hierarchies correctly reflect the data and match the needs of your end-users.
Do create attribute relationships wherever they exist in the data
Attribute relationships are an important part of dimension design. They help the server optimize storage of data, define referential integrity rules within the dimension, control the presence of member properties, and determine how MDX restrictions on one hierarchy affect the values in another hierarchy. For these reasons, it is important to spend some time defining attribute relationships that accurately reflect relationships in the data.
Avoid creating attributes that will not be used
Attributes add to the complexity and storage requirements of a dimension, and the number of attributes in a dimension can significantly affect performance. This is especially of attributes which have AttributeHierachyEnabled set to True. Although SQL Server 2005 Analysis Services can support many attributes in a dimension, having more attributes than are actually used decreases performance unnecessarily and can make the end-user experience more difficult.
It is usually not necessary to create an attribute for every column in a table. Even though the wizards do this by default in Analysis Services 2005, a better design approach is to start with the attributes you know you'll need, and later add more attributes. Adding attributes as you discover they are needed is generally better a better practice than adding everything and then removing attributes.
Do not create hierarchies where an attribute of a lower level contains fewer members than an attribute of the level above
A hierarchy such as this is frequently an indication that your levels are in the incorrect order: for example, [City] above [State]. It might also indicate that the key columns of the lower level are missing a column: for example, [Year] above [Quarter Number] instead of [Year] above [Quarter with Year]. Either of these situations will lead to confusion for end-users trying to use and understand the cube.
Do not include more than one non-aggregatable attribute per dimension
Because there is no All member, each non-aggregatable attribute will always have some non-all member selected, even if not specified in a query. Therefore, if you include multiple non-aggregatable attributes in a dimension, the selected attributes will conflict and produce unexpected numbers.
For example, in a time dimension it might not make sense to sum the members of [Calendar Year] or [Fiscal Year], but if both are made non-aggregatable, whenever a user asks for data for a specific [Calendar Year] it will be filtered by the default [Fiscal Year] unless they also specify the [Fiscal Year]. Worse, because [Calendar Year] and [Fiscal Year] do not align but overlap, it is difficult to obtain the full data for either a [Calendar Year] or a [Fiscal Year] because the one is filtered by the other.
Do use key columns that completely and correctly define the uniqueness of the members in an attribute
Usually a single key column is sufficient, but sometimes multiple key columns are necessary to uniquely identify members of an attribute. For example, it is common in time dimensions to have a [Month] attribute include both [Year] and [Month Name] as key columns. This is known as a composite key and identifies January of 1997 as being a different member than January of 1998. When you use [Month] in a time hierarchy that also contains [Year], this distinction between January of 1997 and January of 1998 is important.
It may also make sense to have a separate [Month of Year] attribute that has only [Month Name] as the key. This [Month of Year] attribute contains a single January member that spans all years, which can be useful for comparing seasonal data. However, this attribute should not be used in a hierarchy together with [Year] because there is no relationship between [Month of Year] and [Year].
Similar distinctions between [Quarter] and [Quarter of Year], [Semester] and [Semester of Year], and so on should also be made by setting appropriate key columns.
Do perform Process Index after doing a Process Update if the dimension contains flexible AttributeRelationships or a parent-child hierarchy
An aggregation is considered flexible if any attribute included in the aggregation is related, either directly or indirectly, to the key of its dimension through an AttributeRelationship with RelationshipType set to Flexible. Aggregations that include parent-child hierarchies are also considered flexible.
When a dimension is processed by using the Process Update option, any flexible aggregations that the dimension participates in might be dropped, depending on the contents of the new dimension data. These aggregations are not rebuilt by default, so Process Index must then be explicitly performed to rebuild them.
Do use numeric keys for attributes that contain many members (>1 million)
Using a numeric key column instead of a string key column or a composite key will improve the performance of attributes that contain many members. This best practice is based on the same concept as using surrogate keys in relational tables for more efficient indexing. You can specify the numeric surrogate column as the key column and still use a string column as the name column so that the attribute members appear the same to end-users. As a guideline, if the attribute has more than one million members, you should consider using a numeric key.
Do not create redundant attribute relationships
Do not create attribute relationships that are transitively implied by other attribute relationships. The alternative paths created by these redundant attribute relationships can cause problems for the server and are of no benefit to the dimension. For example, if the relationships A->B, B->C, and A->C have been created, A->C is redundant and should be removed.
Do include the key columns of snowflake tables joined to nullable foreign keys as attributes that have NullProcessing set to UnknownMember
If tables that are used in a dimension are joined on a foreign key column that might contain nulls, it is important that you include in your design an attribute whose key column is the corresponding key in the lookup table. Without such an attribute, the OLAP server would have to issue a query to join the two tables during dimension processing. This makes processing slower; moreover, the default join that is created by the OLAP server would exclude any rows that contain nulls in the foreign key column. It is important to set the NullProcessing option on the key column of this attribute to UnknownMember. The reason is that, by default, nulls are converted to zeros or blanks when the engine processes attributes. This can be dangerous when you are processing a nullable foreign key. Conversion of a null to zero at best produces an error; in the worst case, the zero may be a legitimate value in the lookup table, thereby producing incorrect results.
To handle nullable foreign keys correctly, you must also set UnknownMember to Visible on the dimension. The Cube Wizard and Dimension Wizard currently set this property automatically; however, the Dimension Wizard lets you manually de-select the key attribute of snowflake tables. You must not deselect the key column if the corresponding foreign key is nullable.
If you do not want to browse the attribute that contains the lookup table key column, you can set AttributeHierarchyVisible to False. However, AttributeHierarchyEnabled must be set to True because it is necessary that all other attributes in the lookup table be directly or indirectly related to the lookup key attribute in order to avoid the automatic creation of new joins during dimension processing.
Do set the RelationshipType property appropriately on AttributeRelationships based on whether the relationships between individual members change over time
The relationships between members of some attributes, such as dates in a given month or the gender of a customer, are not expected to change. Other relationships, such as salespeople in a given region or the marital status of a customer, are more prone to change over time. You should set RelationshipType to Flexible for those relationships that are expected to change and set RelationshipType to Rigid for relationships that are not expected to change.
When you set RelationshipType appropriately, the server can optimize the processing of changes and re-building of aggregations.
By default, the user interface always sets RelationshipType to Flexible.
Avoid using ErrorConfigurations with KeyDuplicate set to IgnoreError on dimensions
When KeyDuplicate is set to IgnoreError, it can be difficult to detect problems with incorrect key columns, incorrectly defined AttributeRelationships, and data consistency issues. Instead of using the IgnoreError option, in most cases it is better to correct your design and clean the data. The IgnoreError option may be useful in prototypes where correctness is less of a concern. Be aware that the default value for KeyDuplicate is IgnoreError. Therefore, it is important to change this value after prototyping is complete to ensure data consistency.
Do define explicit default members for non-aggregatable attributes
By default, the All member is used as the default member for aggregatable attributes. This default works very well for aggregatable attributes, but non-aggregatable attributes have no obvious choice for the server to use as a default member, therefore a member will be selected arbitrarily. This arbitrarily selected member is then selected whenever the attribute is not explicitly included in an MDX query. To avoid this, it is important to explicitly set a default value for each non-aggregatable attribute.
Default members can be explicitly set either on the DimensionAttribute or in the cube script.
Avoid creating user-defined hierarchies that do not have attribute relationships relating each level to the level above
Having attribute relationships between every level in a hierarchy makes the hierarchy strong and enables significant server optimizations.
Avoid creating diamond-shaped attribute relationships
A Diamond-shaped relationship refers to a chain of attribute relationships that splits and rejoins but contains no redundant relationships. For example, Day->Month->Year and Day->Quarter->Year have the same start and end points, but do not have any common relationships. The presence of multiple paths can create some ambiguity on the server. If preserving the multiple paths is important, it is strongly recommended that you resolve the ambiguity by creating user hierarchies that contain all the paths.
Consider setting AttributeHierarchyEnabled to False on attributes that have cardinality that closely matches the key attribute
When an attribute contains roughly one value for each distinct value of the key attribute, it usually means that the attribute contains only alternative identification information or secondary details. Such attributes are usually not interesting to pivot or group by. For example, the Social Security number or telephone number may be interesting properties to view, but there is very little value in being able to pivot and group based on SSN or telephone. Setting AttributeHierarchyEnabled to False on such attributes will reduce the complexity of the dimension for end-users and improve its performance.
If you want to be able to browse such attributes, you can set AttributeHierarchyEnabled to True; however, you should consider setting AttributeHierarchyOptimized to NotOptimized and setting GroupingBehavior to DiscourageGrouping. By setting these properties, you can improve performance and indicate to the users that the attribute is not very useful for grouping.
Consider setting AttributeHierarchyVisible to False on the key attribute of parent-child dimensions
Because the members of the key attribute are also contained in the parent-child hierarchy in a more organized manner, it is usually unnecessary and confusing to the end-user to expose the flat list of members contained in the key attribute.
Avoid setting UnknownMember=Hidden
When you suppress unknown members, the effect is to hide relational integrity issues; moreover, because hidden members might contain data, results might appear not to add up. Therefore, we recommend that you avoid use of this setting except in prototype applications.
Do use MOLAP storage mode for dimensions with outline calculations (custom rollups, semi-additive measures, and unary operators)
Dimensions that contain custom rollups or unary operators will perform significantly better using MOLAP storage. The following dimension types will also benefit from using MOLAP storage: an Account dimension in a measure group that contains measures aggregated using ByAccount; the first time dimension in a measure group that contains other semi-additive measures.
Do use a 64 bit server if you have dimensions with more than 10 million members
If a dimension contains more than 10 million members, using an x64 or an IA-64-based server is recommended for better performance.
Do set the OrderBy property for time attributes and other attributes whose natural ordering is not alphabetical
By default, the server orders attribute members alphabetically, by name. This ordering is especially undesirable for time attributes. To obtain the desired ordering, use the OrderBy and OrderByAttributes properties and explicitly specify how you want the members ordered. For time-based attributes, there is frequently a date or numeric key column that can be used to obtain the correct chronological ordering.
Do expose a DateTime MemberValue for date attributes
Some clients, such as Excel, will take advantage of the MemberValue property of date members and use the DateTime value that is exposed. When Excel recognizes the value as DateTime, Excel can treat the value as a date type and apply date functions to the value, as well as provide better formatting and filtering. If the key column is a single DateTime column and the name column has not been set, this MemberValue is automatically derived from the key column and no action is necessary. However, in other cases, you can ensure that the MemberValue is DateTime by explicitly specifying the ValueColumn property of the attribute.
Do set AttributeHierarchyEnabled to False, specify a ValueColumn and specify the MimeType of the ValueColumn on attributes that contain images
Because there is no value in browsing the member names of an attribute that contains an image, you should disable browsing by setting AttributeHierarchyEnabled to False. To help clients recognize and display the member property of the attribute as an image, specify the ValueColumn property of the attribute and then set MimeType to an appropriate image type.
Avoid setting IsAggregatable to False on any attribute other than the parent attribute in a parent-child dimension
Non-aggregatable attributes have non-all default members. These default members affect the result of queries whenever the attributes are not explicitly included. Because parent-child hierarchies generally represent the most interesting exploration path in dimensions that contain them, it is best to avoid having non-aggregatable attributes other than the parent attribute.
Do set dimension and attribute Type properties correctly for Time, Account, and Geography dimensions
For time dimensions, it is important to set the dimension and attribute types correctly so that time-related MDX functions and the time intelligence of the Business Intelligence Wizard can work correctly. For Account dimensions, it is similarly important to set appropriate account types when using measures with the aggregate function ByAccount. Geography types are not used by the server, but provide information for client applications.
A common mistake is to set the Type property on a dimension but not on an attribute, or vice-versa. Another common mistake when configuring time dimensions is to confuse the different time attribute types, such as [Month] and [Month of Year].
Consider creating user-defined hierarchies whenever you have a chain of related attributes in a dimension
Chains of related attributes usually represent an interesting navigation path for end-users, and defining hierarchies for these will also provide performance benefits.
Do include all desired attributes of a logical business entity in a single dimension instead of splitting them up over several dimensions
In Analysis Services 2000, each hierarchy was in reality a separate dimension and attributes such as gender and age would also be separate dimensions. In Analysis Services 2005, a dimension can and should contain the complete information about a logical business entity, including multiple hierarchies and many attributes. This does not mean that every piece of information available must be included in the dimension, but rather that any desired information should be included in one dimension instead of split over many dimensions.
There are two exceptions to this guideline:
1.
A dimension can only contain one parent-child hierarchy.
2.
To model multiple joins to a lookup table within a dimension's schema, you must create a separate dimension based on the lookup table and then use this as a referenced dimension.
Do not combine unrelated business entities into a single dimension
Combining attributes of independent business entities, such as customer and product or warehouse and time, into a single dimension will not only create a confusing model, but also reduce query performance because auto-exist will be applied across attributes within the dimension.
Another way to state this rule is that the values of the key attribute of a dimension should uniquely identify a single business entity and not a combination of entities. Generally this means having a single column key for the key attribute.
Do set NullProcessing to UnknownMember on each attribute that has nulls and is used to join to a referenced dimension
By default, nulls are converted to zeros or blanks when the engine processes attributes. This can be dangerous when processing a nullable foreign key, because if a null is converted to zero when zero is a legitimate value in the reference dimension, the join on the values can produce incorrect results. At best, conversion to zero will produce an error.
To prevent these errors, you must also set UnknownMember to Visible on the referenced dimension.
The Cube Wizard in SQL Server 2005 Analysis Services handles both settings automatically, except when dealing with existing dimensions where UnknownMember is not set to Visible.
Do set NullKeyConvertToUnknown to IgnoreError on the ErrorConfiguration on any measure groups that contain a dimension referenced through a nullable column
By default, nulls are converted to zeros or blanks when the engine processes granularity attributes. This can be dangerous when you are processing a nullable foreign key, because if a null value is converted to zero and zero is a legitimate value in the dimension, the join can produce incorrect results. At best, the conversion will produce errors.
To prevent conversion of nulls, you must also set UnknownMember to Visible on the dimension.
The Cube Wizard in SQL Server 2005 Analysis Services handles these settings automatically, except when dealing with existing dimensions where UnknownMember is not set to Visible.
Consider setting AttributeHierarchyVisible to False for attributes included in user-defined hierarchies
It is usually not necessary to expose an attribute in its own single level hierarchy when that attribute is included in a user-defined hierarchy. This duplication only complicates the end-user experience without providing additional value.
One common case in which it is appropriate to present two views of an attribute is in time dimensions. The ability to browse by [Month] and the ability to browse by [Month-Quarter-Year] are both very valuable. However, these two month attributes are actually separate attributes. The first contains only the month value such as “January” while the second contains the month and the year such as “January 1998”.
Do not use proactive caching settings that put dimensions into ROLAP mode
For performance reasons, we strongly discourage the use of dimension proactive caching settings that may put the dimension in ROLAP mode. To ensure that a dimension with proactive caching enabled will never enter ROLAP mode, you should set the OnlineMode property to OnCacheComplete. You can also prevent use of ROLAP mode by deselecting the Bring online immediately check box in the Storage Options dialog box.
Avoid making an attribute non-aggregatable unless it is at the end of the longest chain of attribute relationships in the dimension
Non-aggregatable attributes have non-all default members that affect the result of queries in which values for those attributes are not explicitly specified. Therefore, you should avoid making an attribute non-aggregatable unless that attribute is regularly used. Because the longest chain of attributes generally represents the most interesting exploration path for users, it is best to avoid having non-aggregatable attributes in other, less interesting chains.
Consider creating at least one user-defined hierarchy in each dimension that does not contain a parent-child hierarchy
Most (but not all) dimensions contain some hierarchical structure to the data which is worth exposing in the cube. Frequently the Cube Wizard or Dimension Wizard will not detect this hierarchy. In these cases, you should define a hierarchy manually.
Do set the InstanceSelection property on attributes to help clients determine the best way to display attributes for member selection
If there are too many members to display in a single list, the client user interface can use other methods, such as filtered lists, to display the members. By setting the InstanceSelection property, you provide a hint to client applications to suggest how a list of items should be displayed, based on the expected number of items in the list.
代理键应该是人造的
In a temporal database, it is necessary to distinguish between the surrogate key and the primary key. Typically, every row would have both a primary key and a surrogate key. The primary key identifies the unique row in the database, the surrogate key identifies the unique entity in the modelled world; these two keys are not the same. For example, table Staff may contain two rows for "John Smith", one row when he was employed between 1990 and 1999, another row when he was employed between 2001 and 2006. The surrogate key is identical (non-unique) in both rows however the primary key will be unique.
微软总部Microsoft Marketing数据分析项目等
笔者在实际工作中，有幸接触到海量的数据处理问题，对其进行处理是一项艰巨而复杂的任务。原因有以下几个方面：
一、数据量过大，数据中什么情况都可能存在。如果说有10条数据，那么大不了每条去逐一检查，人为处理，如果有上百条数据，也可以考虑，如果数据上到千万级别，甚至过亿，那不是手工能解决的了，必须通过工具或者程序进行处理，尤其海量的数据中，什么情况都可能存在，例如，数据中某处格式出了问题，尤其在程序处理时，前面还能正常处理，突然到了某个地方问题出现了，程序终止了。
二、软硬件要求高，系统资源占用率高。对海量的数据进行处理，除了好的方法，最重要的就是合理使用工具，合理分配系统资源。一般情况，如果处理的数据过TB级，小型机是要考虑的，普通的机子如果有好的方法可以考虑，不过也必须加大CPU和内存，就象面对着千军万马，光有勇气没有一兵一卒是很难取胜的。
三、要求很高的处理方法和技巧。这也是本文的写作目的所在，好的处理方法是一位工程师长期工作经验的积累，也是个人的经验的总结。没有通用的处理方法，但有通用的原理和规则。
那么处理海量数据有哪些经验和技巧呢，我把我所知道的罗列一下，以供大家参考：
一、选用优秀的数据库工具
现在的数据库工具厂家比较多，对海量数据的处理对所使用的数据库工具要求比较高，一般使用Oracle或者DB2，微软公司最近发布的SQL Server 2005性能也不错。另外在BI领域：数据库，数据仓库，多维数据库，数据挖掘等相关工具也要进行选择，象好的ETL工具和好的OLAP工具都十分必要，例如Informatic，Eassbase等。笔者在实际数据分析项目中，对每天6000万条的日志数据进行处理，使用SQL Server 2000需要花费6小时，而使用SQL Server 2005则只需要花费3小时。
二、编写优良的程序代码
处理数据离不开优秀的程序代码，尤其在进行复杂数据处理时，必须使用程序。好的程序代码对数据的处理至关重要，这不仅仅是数据处理准确度的问题，更是数据处理效率的问题。良好的程序代码应该包含好的算法，包含好的处理流程，包含好的效率，包含好的异常处理机制等。
三、对海量数据进行分区操作
对海量数据进行分区操作十分必要，例如针对按年份存取的数据，我们可以按年进行分区，不同的数据库有不同的分区方式，不过处理机制大体相同。例如SQL Server的数据库分区是将不同的数据存于不同的文件组下，而不同的文件组存于不同的磁盘分区下，这样将数据分散开，减小磁盘I/O，减小了系统负荷，而且还可以将日志，索引等放于不同的分区下。
四、建立广泛的索引
对海量的数据处理，对大表建立索引是必行的，建立索引要考虑到具体情况，例如针对大表的分组、排序等字段，都要建立相应索引，一般还可以建立复合索引，对经常插入的表则建立索引时要小心，笔者在处理数据时，曾经在一个ETL流程中，当插入表时，首先删除索引，然后插入完毕，建立索引，并实施聚合操作，聚合完成后，再次插入前还是删除索引，所以索引要用到好的时机，索引的填充因子和聚集、非聚集索引都要考虑。
五、建立缓存机制
当数据量增加时，一般的处理工具都要考虑到缓存问题。缓存大小设置的好差也关系到数据处理的成败，例如，笔者在处理2亿条数据聚合操作时，缓存设置为100000条/Buffer，这对于这个级别的数据量是可行的。
六、加大虚拟内存
如果系统资源有限，内存提示不足，则可以靠增加虚拟内存来解决。笔者在实际项目中曾经遇到针对18亿条的数据进行处理，内存为1GB，1个P4 2.4G的CPU，对这么大的数据量进行聚合操作是有问题的，提示内存不足，那么采用了加大虚拟内存的方法来解决，在6块磁盘分区上分别建立了6个4096M的磁盘分区，用于虚拟内存，这样虚拟的内存则增加为 4096*6 + 1024 = 25600 M，解决了数据处理中的内存不足问题。
七、分批处理
海量数据处理难因为数据量大，那么解决海量数据处理难的问题其中一个技巧是减少数据量。可以对海量数据分批处理，然后处理后的数据再进行合并操作，这样逐个击破，有利于小数据量的处理，不至于面对大数据量带来的问题，不过这种方法也要因时因势进行，如果不允许拆分数据，还需要另想办法。不过一般的数据按天、按月、按年等存储的，都可以采用先分后合的方法，对数据进行分开处理。
八、使用临时表和中间表
数据量增加时，处理中要考虑提前汇总。这样做的目的是化整为零，大表变小表，分块处理完成后，再利用一定的规则进行合并，处理过程中的临时表的使用和中间结果的保存都非常重要，如果对于超海量的数据，大表处理不了，只能拆分为多个小表。如果处理过程中需要多步汇总操作，可按汇总步骤一步步来，不要一条语句完成，一口气吃掉一个胖子。
九、优化查询SQL语句
在对海量数据进行查询处理过程中，查询的SQL语句的性能对查询效率的影响是非常大的，编写高效优良的SQL脚本和存储过程是数据库工作人员的职责，也是检验数据库工作人员水平的一个标准，在对SQL语句的编写过程中，例如减少关联，少用或不用游标，设计好高效的数据库表结构等都十分必要。笔者在工作中试着对1亿行的数据使用游标，运行3个小时没有出结果，这是一定要改用程序处理了。
十、使用文本格式进行处理
对一般的数据处理可以使用数据库，如果对复杂的数据处理，必须借助程序，那么在程序操作数据库和程序操作文本之间选择，是一定要选择程序操作文本的，原因为：程序操作文本速度快；对文本进行处理不容易出错；文本的存储不受限制等。例如一般的海量的网络日志都是文本格式或者csv格式（文本格式），对它进行处理牵扯到数据清洗，是要利用程序进行处理的，而不建议导入数据库再做清洗。
十一、 定制强大的清洗规则和出错处理机制
海量数据中存在着不一致性，极有可能出现某处的瑕疵。例如，同样的数据中的时间字段，有的可能为非标准的时间，出现的原因可能为应用程序的错误，系统的错误等，这是在进行数据处理时，必须制定强大的数据清洗规则和出错处理机制。
十二、 建立视图或者物化视图
视图中的数据来源于基表，对海量数据的处理，可以将数据按一定的规则分散到各个基表中，查询或处理过程中可以基于视图进行，这样分散了磁盘I/O，正如10根绳子吊着一根柱子和一根吊着一根柱子的区别。
十三、 避免使用32位机子（极端情况）
目前的计算机很多都是32位的，那么编写的程序对内存的需要便受限制，而很多的海量数据处理是必须大量消耗内存的，这便要求更好性能的机子，其中对位数的限制也十分重要。
十四、 考虑操作系统问题
海量数据处理过程中，除了对数据库，处理程序等要求比较高以外，对操作系统的要求也放到了重要的位置，一般是必须使用服务器的，而且对系统的安全性和稳定性等要求也比较高。尤其对操作系统自身的缓存机制，临时空间的处理等问题都需要综合考虑。
十五、 使用数据仓库和多维数据库存储
数据量加大是一定要考虑OLAP的，传统的报表可能5、6个小时出来结果，而基于Cube的查询可能只需要几分钟，因此处理海量数据的利器是OLAP多维分析，即建立数据仓库，建立多维数据集，基于多维数据集进行报表展现和数据挖掘等。
十六、 使用采样数据，进行数据挖掘
基于海量数据的数据挖掘正在逐步兴起，面对着超海量的数据，一般的挖掘软件或算法往往采用数据抽样的方式进行处理，这样的误差不会很高，大大提高了处理效率和处理的成功率。一般采样时要注意数据的完整性和，防止过大的偏差。笔者曾经对1亿2千万行的表数据进行采样，抽取出400万行，经测试软件测试处理的误差为千分之五，客户可以接受。
还有一些方法，需要在不同的情况和场合下运用，例如使用代理键等操作，这样的好处是加快了聚合时间，因为对数值型的聚合比对字符型的聚合快得多。类似的情况需要针对不同的需求进行处理。
海量数据是发展趋势，对数据分析和挖掘也越来越重要，从海量数据中提取有用信息重要而紧迫，这便要求处理要准确，精度要高，而且处理时间要短，得到有价值信息要快，所以，对海量数据的研究很有前途，也很值得进行广泛深入的研究
创新性应用 数据建模经验谈
2007-09-27 20:38:45 作者： 来源：互联网 文字大小：【大】【中】【小】
笔者从98年进入数据库及数据仓库领域工作至今已经有近八年的时间，对数据建模工作接触的比较多，创新性不敢谈，本文只是将工作中的经验总结出来，供大家一同探讨和指正。 提起数据建模来，有一点是首先 ...
笔者从98年进入数据库及数据仓库领域工作至今已经有近八年的时间，对数据建模工作接触的比较多，创新性不敢谈，本文只是将工作中的经验总结出来，供大家一同探讨和指正。
提起数据建模来，有一点是首先要强调的，数据建模师和DBA有着较大的不同，对数据建模师来说，对业务的深刻理解是第一位的，不同的建模方法和技巧是为业务需求来服务的。而本文则暂时抛开业务不谈，主要关注于建模方法和技巧的经验总结。
从目前的数据库及数据仓库建模方法来说，主要分为四类。
第一类是大家最为熟悉的关系数据库的三范式建模，通常我们将三范式建模方法用于建立各种操作型数据库系统。
第二类是Inmon提倡的三范式数据仓库建模，它和操作型数据库系统的三范式建模在侧重点上有些不同。Inmon的数据仓库建模方法分为三层，第一层是实体关系层，也即企业的业务数据模型层，在这一层上和企业的操作型数据库系统建模方法是相同的；第二层是数据项集层，在这一层的建模方法根据数据的产生频率及访问频率等因素与企业的操作型数据库系统的建模方法产生了不同；第三层物理层是第二层的具体实现。
第三类是Kimball提倡的数据仓库的维度建模，我们一般也称之为星型结构建模，有时也加入一些雪花模型在里面。维度建模是一种面向用户需求的、容易理解的、访问效率高的建模方法，也是笔者比较喜欢的一种建模方式。
第四类是更为灵活的一种建模方式，通常用于后台的数据准备区，建模的方式不拘一格，以能满足需要为目的，建好的表不对用户提供接口，多为临时表。
下面简单谈谈第四类建模方法的一些的经验。
数据准备区有一个最大的特点，就是不会直接面对用户，所以对数据准备区中的表进行操作的人只有ETL工程师。ETL工程师可以自己来决定表中数据的范围和数据的生命周期。下面举两个例子：
1）数据范围小的临时表
当需要整合或清洗的数据量过大时，我们可以建立同样结构的临时表，在临时表中只保留我们需要处理的部分数据。这样，不论是更新还是对表中某些项的计算都会效率提高很多。处理好的数据发送入准备加载到数据仓库中的表中，最后一次性加载入数据仓库。
2）带有冗余字段的临时表
由于数据准备区中的表只有自己使用，所以建立冗余字段可以起到很好的作用而不用承担风险。
举例来说，笔者在项目中曾遇到这样的需求，客户表{客户ID，客户净扣值}，债项表{债项ID，客户ID，债项余额，债项净扣值}，即客户和债项是一对多的关系。其中，客户净扣值和债项余额已知，需要计算债项净扣值。计算的规则是按债项余额的比例分配客户的净扣值。这时，我们可以给两个表增加几个冗余字段，如客户表{客户ID，客户净扣值，客户余额}，债项表{债项ID，客户ID，债项余额，债项净扣值，客户余额，客户净扣值}。这样通过三条SQL就可以直接完成整个计算过程。将债项余额汇总到客户余额，将客户余额和客户净扣值冗余到债项表中，在债项表中通过（债项余额×客户净扣值/客户余额）公式即可直接计算处债项净扣值。
另外还有很多大家可以发挥的建表方式，如不需要主键的临时表等等。总结来说，正因为数据准备区是不对用户提供接口的，所以我们一定要利用好这一点，以给我们的数据处理工作带来最大的便利为目的来进行数据准备区的表设计。
行业借鉴经验：
数据仓库架构经验谈
对于数据仓库的架构方法，不同的架构师有不同的原则和方法，笔者在这里来总结一下当前常采用的架构方式及其优缺点。这些架构方式不限于某个行业，可以供各个行业借鉴使用。
首先需要说明的一点是，目前在数据仓库领域比较一致的意见是在数据仓库中需要保留企业范围内一致的原子层数据。而独立的数据集市架构（Independent data marts）没有企业范围内一致的数据，很可能会导致信息孤岛的产生，除非在很小的企业内或只针对固定主题，否则不建议建立这样的架构方式。联邦式的数据仓库架构（Federated Data Warehouse Architecture）不管是在地域上的联邦还是功能上的联邦都需要先在不同平台上建立各自的数据仓库，再通过参考（reference）数据来实现整合，而这样很容易造成整合的不彻底，除非联邦式的数据仓库架构也采用Kimball的总线架构（Bus Architecture）中类似的功能，即在数据准备区保留一致性维度（Conformed Table）并不断更新它。所以，这两种架构方式不在讨论范围之内。下面主要讨论剩下的三种架构方式。
1）三范式（3NF）的原子层＋数据集市
这样的数据仓库架构最大的倡导者就是数据仓库之父Inmon，而他的企业信息工厂（Corporate Information System）就是典型的代表。这样的架构也称之为企业数据仓库（Enterprise Data Warehouse，EDW）。企业信息工厂的实现方式是，首先进行全企业的数据整合，建立企业信息模型，即EDW。对于各种分析需求再建立相应的数据集市或者探索仓库，其数据来源于EDW。三范式的原子层给建立OLAP带来一定的复杂性，但是对于建立更复杂的应用，如挖掘仓库、探索仓库提供了更好的支持。这类架构的建设周期比较长，相应的成本也比较高。
2）星型结构（Star Schema）的原子层＋HOLAP
星型结构最大的倡导者是Kimall，他的总线架构是该类架构的典型代表。总线架构实现方式是，首先在数据准备区中建立一致性维度、建立一致性事实的计算方法；其次在一致性维度、一致性事实的基础上逐步建立数据集市。每次增加数据集市，都会在数据准备区整合一致性维度，并将整合好的一致性维度同步更新到所有的数据集市。这样，建立的所有数据集市合在一起就是一个整合好的数据仓库。正是因为总线架构这个可以逐步建立的特点，它的开发周期比其他架构方式的开发周期要短，相应的成本也要低。在星型结构的原子层上可以直接建立聚集，也可以建立HOLAP。笔者比较倾向于Kimball的星型结构的原子层架构，在这种架构中的经验也比较多。
3）三范式（3NF）的原子层＋ROLAP
这样的数据仓库架构也称为集中式架构（Centralized Architecture），思路是在三范式的原子层上直接建立ROLAP，做的比较出色的就是MicroStrategy。在三范式的原子层上定义ROLAP比在星型结构的原子层上定义ROLAP要复杂很多。采用这种架构需要在定义ROLAP是多下些功夫，而且ROLAP的元数据不一定是通用的格式，所以对ROLAP做展现很可能会受到工具的局限。这类架构和第一类很相似，只是少了原子层上的数据集市。
总结来说，这三种数据仓库的架构方式都是不错的选择。对于需要见效快、成本低的项目可以考虑采用第二种总线架构，对于资金充足并有成熟业务数据模型的企业可以考虑采用第一种架构或第三种架构。
应用难点技巧：
变化数据捕获经验谈
在数据仓库系统中，一个很重要的目的就是保留数据的历史变化信息。而变化数据捕获（Change Data Capture，CDC）就是为这个目的而产生的一项技术。变化数据捕获常用的方法有：1）文件或者表的全扫描对比，2）DBMS日志获取，3）在源系统中增加触发器获取，4）基于源系统的时间戳获取，5）基于复制技术的获取，6）DBMS提供的变化数据捕获方法等。其中，由DBMS提供变化数据捕获的方法是大势所趋，即具体的捕获过程由DBMS来完成。
像银行、电信等很多行业的操作记录生成后就不会改变，只有像客户、产品等信息会随时间发生缓慢的变化，所以通常的变化数据捕获是针对维度表而言的。Kimball对缓慢变化维的分析及应对策略基本上可以处理维度表的各种变化。
而对于一些零售行业，像合同表中的合同金额类似的数值在录入后是有可能会发生改变的，也就是说事实表的数据也有可能发生变化。通常对于事实表数据的修改属于勘误的范畴，可以采用类似缓慢变化维TYPE 1的处理方式直接更新事实表。笔者不太赞同对事实表的变化采用快照的方式插入一条新的事实勘误记录，这样会给后续的展现、分析程序带来太多的麻烦。
接下来要讨论的是笔者曾经遇到的一个颇为棘手的事实表数据改变的问题，该事实表的主键随表中某些数据的变化发生改变。以其中的一个合同表为例，该合同表的主键是由“供货单位编号”＋“合同号”生成的智能主键，当其中的“供货单位编号”和“合同号”中任何一个发生变化时，该合同表的主键都会发生变化，给变化数据捕获带来了很大的麻烦。
项目中，笔者的处理方式是采用触发器的办法来实现变化数据捕获。具体的实现方式是：
1）建立一个新表作为保存捕获的数据表使用，其中字段有“原主键”、“修改后主键”、及其他需要的字段，称为“合同捕获表”。
2）在原合同表Delete和Update时分别建立触发器，当删除操作发生时，建在Delete上的触发器会插入一条记录到“合同捕获表”，其中“修改后主键”字段为空，表示该记录是删除的记录；当发生更新时，将“原主键”、“修改后主键”及其他需要记录的字段都保存入“合同捕获表”中，表示该记录被修改过，如果“原主键”和“修改后主键”不同，则表示主键被修改，如果相同，则表示主键没有被修改。
3）由于操作系统中的主键通常会成为数据仓库中事实表的退化维度，可能仍会起主键的作用。所以在数据加载时，需要分情况判断“合同捕获表”的数据来决定是否更新事实表中的退化维度。
可以说，这样的基于触发器的变化数据捕获方法并不是一个很好的选择。首先这需要对源系统有较大的权限；其次，触发器会给源系统的性能带来很大的影响。所以除非是没有别的选择，否则不建议采用这种方法。
而对于这样的情况，我们在建立操作型数据库系统时完全可以避免。下面是对操作型数据库系统建立者的几点建议：1）操作型系统的主键不要建立成智能型的，至少不要建立成会变化的。2）操作型系统的表中需要加入操作人和操作时间字段，或者直接加入时间戳。3）操作型系统中操作型数据最好不要直接在原值上修改，可以采用“冲红”的方式加入新的记录。这样后续建立数据仓库时就不需要考虑事实表数据的变化问题。
最后，期待各大数据库管理系统的厂商能尽快在DBMS层提供功能强大、简单好用的变化数据捕获功能，目前Oracle已经有了这个功能。毕竟技术方面复杂的事情留给厂商做是一个趋势，而我们做应用的则更关注于业务。
posted by hunter at 11:52 PM
0 Comments:
Post a Comment
<< Home