数据仓库建模详解和建模技巧

1、一、构建企业级数据仓库五步法(一、确定主题即确定数据分析或前端展现的主题。例如:我们希望分析某年某月某一地区的啤酒销售情况,这就是一个主题。主题要体现出某一方面的各分析角度(维度和统计数值型数据(量度之间的关系,确定主题时要综合考虑。我们可以形象的将一个主题想象为一颗星星:统计数值型数据(度量存在于星星中间的事实表;分析角度(维度是星星的各个角;我们将通过维度的组合,来考察量度。那么,“某年某月某一地区的啤酒销售情况”这样一个主题,就要求我们通过时间和地区两个维度的组合,来考察销售情况这个量度。从而,不同的主题来源于数据仓库中的不同子集,我们可以称之为数据集市。数据集市体现了数据仓库某一方面的

2、信息,多个数据集市构成了数据仓库。(二、确定量度在确定了主题以后,我们将考虑要分析的技术指标,诸如年销售额之类。它们一般为数值型数据。我们或者将该数据汇总,或者将该数据取次数、独立次数或取最大最小值等,这样的数据称为量度。量度是要统计的指标,必须事先选择恰当,基于不同的量度可以进行复杂关键性能指标(KPI等的设计和计算。(三、确定事实数据粒度在确定了量度之后,我们要考虑到该量度的汇总情况和不同维度下量度的聚合情况。考虑到量度的聚合程度不同,我们将采用“最小粒度原则”,即将量度的粒度设置到最小。例如:假设目前的数据最小记录到秒,即数据库中记录了每一秒的交易额。那么,如果我们可以确认,在将来的分析

3、需求中,时间只需要精确到天就可以的话,我们就可以在ETL处理过程中,按天来汇总数据,此时,数据仓库中量度的粒度就是“天”;反过来,如果我们不能确认将来的分析需求在时间上是否需要精确到秒,那么,我们就需要遵循“最小粒度原则”,在数据仓库的事实表中保留每一秒的数据,以便日后对“秒”进行分析。在采用“最小粒度原则”的同时,我们不必担心海量数据所带来的汇总分析效率问题,因为在后续建立多维分析模型(CUBE的时候,我们会对数据提前进行汇总,从而保障产生分析结果的效率。关于建立多维分析模型(CUBE的相关问题,我们将在下期栏目中予以阐述。(四、确定维度维度是指分析的各个角度。例如我们希望按照时间,或者按照

4、地区,或者按照产品进行分析,那么这里的时间、地区、产品就是相应的维度。基于不同的维度,我们可以看到各量度的汇总情况,也可以基于所有的维度进行交叉分析。这里我们首先要确定维度的层次(Hierarchy和级别(Level(图四:pic4.bmp。如图所示,我们在时间维度上,按照“年 -季度-月”形成了一个层次,其中“年”、“季度”、“月”成为了这个层次的3个级别;同理,当我们建立产品维度时,我们可以将“产品大类-产品子类-产品”划为一个层次,其中包含“产品大类”、“产品子类”、“产品”三个级别。那么,我们分析中所用到的这些维度,在数据仓库中的存在形式是怎样的呢?我们可以将3个级别设置成一张数据表中

5、的3个字段,比如时间维度;我们也可以使用三张表,分别保存产品大类、产品子类、产品三部分数据,比如产品维度.另外,值得一提的是,我们在建立维度表时要充分使用代理键。代理键是数值型的ID号码(例如图六中每张表的第一个字段,它唯一标识了每一维度成员。更重要的是,在聚合时,数值型字段的匹配和比较,JOIN效率高,便于聚合。同时,代理键对缓慢变化维度有着重要的意义,在原数据主键相同的情况下,它起到了对新数据与历史数据的标识作用。在此,我们不妨谈一谈维度表随时间变化的问题,这是我们经常会遇到的情况,我们称其为缓慢变化维度。比如我们增加了新的产品,或者产品的ID号码修改了,或者产品增加了一个新的属性,此时,

6、维度表就会被修改或者增加新的记录行。这样,我们在ETL的过程中,就要考虑到缓慢变化维度的处理。对于缓慢变化维度,有三种情况:1、缓慢变化维度第一种类型:历史数据需要修改。这种情况下,我们使用UPDATE方法来修改维度表中的数据。例如:产品的ID号码为123,后来发现ID号码错了,需要改写成456,那么,我们就在ETL处理时,直接修改维度表中原来的ID号码为456。2、缓慢变化维度第二种类型:历史数据保留,新增数据也要保留。这时,要将原数据更新,将新数据插入,我们使用UPDATE / INSERT。比如:某一员工2005年在A部门,2006年时他调到了B部门。那么在统计2005年的数据时就应该将

7、该员工定位到A部门;而在统计 2006年数据时就应该定位到B部门,然后再有新的数据插入时,将按照新部门(B部门进行处理,这样我们的做法是将该维度成员列表加入标识列,将历史的数据标识为“过期”,将目前的数据标识为“当前的”。另一种方法是将该维度打上时间戳,即将历史数据生效的时间段作为它的一个属性,在与原始表匹配生成事实表时将按照时间段进行关联,这种方法的好处是该维度成员生效时间明确。3、缓慢变化维度第三种类型:新增数据维度成员改变了属性。例如:某一维度成员新加入了一列,该列在历史数据中不能基于它浏览,而在目前数据和将来数据中可以按照它浏览,那么此时我们需要改变维度表属性,即加入新的字段列。那么,

8、我们将使用存储过程或程序生成新的维度属性,在后续的数据中将基于新的属性进行查看。(五、创建事实表在确定好事实数据和维度后,我们将考虑加载事实表。在公司的大量数据堆积如山时,我们想看看里面究竟是什么,结果发现里面是一笔笔生产记录,一笔笔交易记录 那么这些记录是我们将要建立的事实表的原始数据,即关于某一主题的事实记录表。我们的做法是将原始表与维度表进行关联,生成事实表(图六:pic6.bmp。注意在关联时有为空的数据时(数据源脏,需要使用外连接,连接后我们将各维度的代理键取出放于事实表中,事实表除了各维度代理键外,还有各量度数据,这将来自原始表,事实表中将存在维度代理键和各量度,而不应该存在描述性

9、信息,即符合“瘦高原则”， 即要求事实表数据条数尽量多(粒度最小， 而描述性信息尽量少。 如果考虑到扩展，可以将事实表加一唯一标识列，以为了以 后扩展将该事实作为雪花型维度， 不过不需要时一般建议不用这 样做。 事实数据表是数据仓库 数据仓库的核心，需要精心维护，在 JOIN 后 数据仓库 将得到事实数据表，一般记录条数都比较大，我们需要为其设置 复合主键和索引， 以实现数据的完整性和基于数据仓库 数据仓库的查询性 数据仓库 能优化。事实数据表与维度表一起放于数据仓库 数据仓库中，如果前端需 数据仓库 要连接数据仓库 数据仓库进行查询， 我们还需要建立一些相关的中间汇总 数据仓库 表或物化视图

10、，以方便查询。 二、项目模型实践技巧 (一、准备区的运用 在构建数据仓库 数据仓库时，如果数据源位于一台服务器上，数据仓 数据仓库 数据仓 库在另一台服务器端，考虑到数据源 Server 端访问频繁，并且 数据量大，需要不断更新，所以可以建立准备区数据库(图八： pic8.bmp。先将数据抽取到准备区中，然后基于准备区中的数 据进行处理，这样处理的好处是防止了在原 OLTP 系统中频繁访 问，进行数据运算或排序等操作。 例如我们可以按照天将数据抽取到准备区中， 基于数据准备 区，我们将进行数据的转换、整合、将不同数据源的数据进行一 致性处理。数据准备区中将存在原始抽取表、转换中间表和临时 表以

11、及 ETL 日志表等。 (二、时间戳的运用 时间维度对于某一事实主题来说十分重要， 因为不同的时间 有不同的统计数据信息， 那么按照时间记录的信息将发挥很重要 的作用。在 ETL 中，时间戳有其特殊的作用，在上面提到的缓慢 变化维度中，我们可以使用时间戳标识维度成员;在记录数据库 和数据仓库 数据仓库的操作时，我们也将使用时间戳标识信息。例如：在 数据仓库 进行数据抽取时，我们将按照时间戳对 OLTP 系统中的数据进行 抽取，比如在午夜 0：00 取前一天的数据，我们将按照 OLTP 系 统中的时间戳取 GETDATE 到 GETDATE 减一天，这样得到前一天 数据。 (三、日志表的运用 在

12、对数据进行处理时，难免会发生数据处理错误，产生出错 信息， 那么我们如何获得出错信息并及时修正呢? 方法是我们使 用一张或多张 Log 日志表，将出错信息记录下来，在日志表中我 们将记录每次抽取的条数、处理成功的条数、处理失败的条数、 处理失败的数据、处理时间等等。这样，当数据发生错误时，我 们很容易发现问题所在， 然后对出错的数据进行修正或重新处理。 (四、使用调度 在对数据仓库 数据仓库进行增量更新时必须使用调度(图九： 数据仓库 pic9.bmp，即对事实数据表进行增量更新处理。在使用调度前 要考虑到事实数据量，确定需要多长时间更新一次。比如希望按 天进行查看，那么我们最好按天进行抽取，如果数据量不大，可 以按照月或半年对数据进行更新。如果有缓慢变化维度情况，调 度时需要考虑到