|
|
关于数据仓库维度数据处理的方法探究
1 维概述
1.1概述
维度是多维数据集的结构性特性。它们是事实数据表中用来描述数据的分类的有组织层次结构（级别）。这些分类和级别描述了一些相似的成员集合，用户将基于这些成员集合进行分析。(此概念引之于SQLServer2000联机帮助)
所有维度均直接或间接地基于表。当从某个表创建维度时，您会选择定义此维度的列。选择列的顺序至关重要，因为它将影响维度层次结构内成员的位置。(此概念引之于SQLServer2000联机帮助)
维度是有层次的，在大多数情况下维度的成员会按金字塔形布局排列。水平布局由维度层次结构中具有相同级别的列值生成，而垂直布局由维度层次结构中具有不同级别的列值生成。(此概念引之于SQLServer2000联机帮助)
相对应于OLTP系统，维度表数据主要来自于OLTP系统中的各个基础表。但是在OLTP系统中数据变化比较剧烈，通常只保留最近最新的基础信息；也不会记载基础数据的删除、更新和插入的整个历史过程。而数据仓库则是面向主题的、集成化的、稳定的、随时间变化的数据集合，这就要求数据必须是递增的，反映历史变化的；因此数据仓库对于维度数据的处理通常的要求是做插入和更新处理，不做删除，否则将无法完整反映数据的历史和整体情况。
1.2分类
在实际的业务系统中，维度根据变化剧烈程度主要分为以下几类：
1. 无变化维度
如性别维度只有男女，如年龄阶段维度0~10岁，10~20岁等等。
2. 缓慢变化维度
维度的变化剧烈程度虽然比较小，但是数据仓库项目中仍要根据实际情况进行维度更新，大部分的维度应属于此部分。
3. 剧烈变化维度
维度的变化剧烈程度比较高，通常做法是对该维度表进行拆解，使其成为缓慢变化维度进行处理。
1.3处理对策
关于维度的处理主要根据业务需要和数据库对数据处理的处理能力和性能。对于比较敏感和极其重要的数据一定要求其反映整个历史变化，或根据版本或根据时间戳等等；反之对于不太重要的数据处于数据库性能考虑，建议采用直接更新的办法；而对于没有变化的数据采取一次性加载的方式。在这里为了讨论方便，初步分为以下几类：
1. 无变化维度处理
2. 缓慢变化维处理
3. 急剧变化维处理
2 无变化维度处理
2.1概述
无变化维度一般都是比较简单的维度，都是经过一次性处理后就不再变化的数据；通常这些维度都是一些常识数据(如性别，重要程度)和分段数据(为了减少单层维度的数量，按照一定业务标准进行的人为分组)。
2.2实现
这类维度的实现一般都比较简单，即一次性插入之后就完成了；当然如果需要考虑以后的变化，还是建议按照缓慢变化维的第一种处理方式。
2.3代码
--创建一个维度表，简单维度表的ID字段通常为手工生成字段
CREATE TABLE t_dem_xxx
(
ID VARCHAR(20) NOT NULL,
Name VARCHAR(50),
CONSTRAINT PK_t_dem_xxx PRIMARY KEY (ID)
)
go
CREATE PROCEDURE p_dem_xxx
AS
--维度抽取存储过程
BEGIN
DECLARE
@num NUMERIC(10,0)
SELECT @num = COUNT(*) FROM t_dem_xxx
--下列情况为手工加入
IF @num = 0
BEGIN
INSERT INTO t_dem_xxx(ID,Name) values('1','aaa')
INSERT INTO t_dem_xxx(ID,Name) values('2','bbb')
INSERT INTO t_dem_xxx(ID,Name) values('3','ccc')
END
--下列情况为从临时表中加入
IF @num = 0
BEGIN
INSERT INTO t_dem_xxx (ID,Name) SELECT ID,Name FROM t_tmp_xxx
END
END
3 关于数据仓库维度数据处理的方法探究系列——缓慢变化维概述和原理
发表人:PercyWang | 发表时间: 2006年十二月07日, 16:33
缓慢变化维Slowly Changing Dimensions（A typical slowly changing dimension is a
product dimension in which the detailed description of a given product is casionally adjusted.）： 数据仓库数据加载规则，数据仓库中用于实现历史数据与当前数据的同时记录。
3.1概述
缓慢变化维Slowly Changing Dimensions（A typical slowly changing dimension is a product dimension in which the detailed description of a given product is casionally adjusted.）：数据仓库数据加载规则，数据仓库中用于实现历史数据与当前数据的同时记录。与OLTP系统不同的是，数据仓库中可以保留下数据的变化信息，及历史数据的记录。
由于系统的设计不同，OLTP系统可以方便的得到实时的报表，却难于得到混合的、复杂的、包含历史的统计信息。数据仓库系统却由于有其专有的建模方式，可以方便的做到这些。在数据仓库中，一个典型的事实表设计，其包含多个代理主键，周围由多个星形围绕着它的维表构成，这些维表则是一些具有简单主键的表。对于维表数据的历史存储的需要，一种典型的数据加载规则即是Slowly Changing Dimensions方式。即当维表数据发生改变，数据仓库需要感知，并真实的记录下数据的每一步细微变化。
但是通过代理键的方式进行维度的加载，一方面导致维度表设计过于复杂，另一方面事实表数据中的相应的键值也需要进行相应的代理键替换，从而导致了设计的复杂性、数据库性能的下降和维护的成本的提高，因此这些维度通常是根据用户需求和业务敏感度极高的基础表信息，例如某些企业内的工号，因为工号分配的限制，员工流失后，新进入的员工仍使用原来的员工号，对于OLTP系统只需要一个简单的UPDATE即可；而对于数据仓库系统则要区分同一个工号前后不同的人员信息，以避免查询时对同一个工号发生的歧义。
替代键方式 概图
对于一般性的维度，仍建议使用最简单的方式进行维度处理，即覆盖法，对于维度只做更新和插入处理，不做删除，即只保留最后一次更新的状态，不保留历史状态，对于大多数维度信息已经足够。
简单维度更新 概图
3.2原理
缓慢变化维的实现原理，采用的是数据仓库的3种常用数据加载规则中的全表比对方式。在ETL过程中，抽取所有源数据，并进行相应规则转换，完成后先不插入目标，而对每条数据进行目标表比对。根据主键值进行插入与更新的判定，目标表已存在该主键值的，表示该记录已有，并进行其余字段比对，如有不同，则进行Update操作，如目标表没有存在该主键值，表示该记录还没有，即进行Insert操作。通过全表比对，感知数据的变化，并进行相应的处理。
数据仓库的数据加载规则可以有多种方式实现，缓慢变化维是其中一种非常强大的方式。而根据它的具体设计不同又可分为3种：覆盖、全历史记录、记录最新纪录及上一次历史。在以下的介绍中将把数据仓库的数据加载规则融入进去，并结合SQLServer脚本的缓慢变化维的实现详细说明。
3.3实现
3.3.1覆盖（Type 1 Dimension -- keep most recent values in target）
3.3.1.1原理
此种方式是缓慢变化维中最简单的一种，它用于保证数据仓库中的数据为当前的最新值，不保留历史数据，如发现数据仓库中当前数据已为旧数据，则对当前已有记录进行值更新，主键值不变，如发现有新数据，则把新数据加载到数据仓库中，并赋予新的代理主键值。通俗地说，就是指对于源表中的同一条数据，目标（数据仓库）中始终只会保留一条，也就是最新的一条，一旦第一次插入后，数据就存在了，其在维表中的ID（代理主键）就不再改变了，发生数据改变时，只对其字段作Update操作。
这种方式在确认数据的历史不需要记录，只需保留当前最新信息的时候使用。
CREATE TABLE t_dem_xxx
(
ID VARCHAR(20) NOT NULL,
Name1 VARCHAR(50),
Name2 VARCHAR(50),
CONSTRAINT PK_t_dem_xxx PRIMARY KEY (ID)
)
go
CREATE TABLE t_tmp_xxx
(
ID VARCHAR(20) NOT NULL,
Name1 VARCHAR(50),
Name2 VARCHAR(50),
CONSTRAINT PK_t_tmp_xxx PRIMARY KEY (ID)
)
go
CREATE PROCEDURE p_dem_xxx
AS
--维度抽取存储过程
BEGIN
DECLARE
@num NUMERIC(10,0)
SELECT @num = COUNT(*) FROM t_dem_xxx
--如果原表为空，构造缺省值
IF @num = 0
BEGIN
INSERT INTO t_dem_xxx (ID,Name1,Name2) SELECT '-2','NULL值',''
INSERT INTO t_dem_xxx (ID,Name1,Name2) SELECT '-1','缺失外键',''
END
--根据主键插入在维度表中找不到的基础数据
INSERT INTO t_dem_xxx
(
ID ,
Name1 ,
Name2
)
SELECT a.ID,a.Name1,a.Name2
FROM t_tmp_xxx a LEFT OUTER JOIN t_dem_xxx b
ON a.ID = b.ID
WHERE b.ID IS NULL
--根据主键更新原基础表中变化的各属性字段
UPDATE t_dem_xxx
SET Name1 = a.Name1,
Name2 = a.Name2
FROM t_tmp_xxx A,t_dem_xxx B
WHERE a.ID = b.ID
AND b.ID NOT IN ('-1','-2')
END
3.3.2 全历史记录（Type 2 Dimension -- keep a full history of changes in the target）
全历史记录是缓慢变化维中最为强大的一种加载方式。它将可以完全实现覆盖方式能实现的加载方式，且可以实现对数据的历史记录，可以记录下每一个数据的细微变化。通过比对，如发现数据仓库中当前数据已为旧数据，则对当前已有记录进行旧数据标记，主键值不变，同时把修改过的数据作为新的一条数据插入，并赋予新的代理主键值；如发现有新数据，则把新数据加载到数据仓库中，并赋予新的代理主键值。通俗地说，就是指对于源表中的同一条数据，目标（数据仓库）中会根据变化保留下多条，即记录下每次的变化，并对最新的一条进行标记。
而对于全历史记录，根据数据仓库建模思想，又可采取三种记录方式。版本号方式、标志方式及时戳方式。
1. 版本号方式
需要在目标表中增加版本号字段，用于记录该数据的版本号。
采用该方式，可以根据记录的版本号对比，版本号最大的将是最新的记录，而数据的历史信息，可以由版本号的递增得出或者数据库的递增字段特性来实现。
2. 标志方式
需要在目标表中增加标志字段，用于标记同样的数据那一条是最新记录。
采用该方式，根据标志字段值即可得出当前最新记录，如标志为1的表示为最新记录，标志为0的为历史信息，历史信息可由其主键值大小得出历史记录的先后。
3. 时戳方式
需要在目标表中增加起始时间及结束时间字段，用于标记该条记录抽取进入目标表的时间及因为其数据被更改后，变为历史数据的时间。
采用该方式，根据其开始时间及结束时间字段来得到当前最新记录，即结束时间为空的就是最新的记录，其余的填入结束时间的记录，可以根据时间的先后，得出历史信息的演变。而此种方式因为利用了时间字段，也详细记录下了历史信息的演变时间，是缓慢变化维中最为强大的一种加载方式，它记录的信息也是最全的。
下面分别对三种方式作一个详细说明：
3.3.2.1版本号方式
它的操作方式与覆盖方式相同之处在于对新增数据的处理，只是根据原主键进行判断后进行数据的插入处理，代理主键利用数据库的Identity类型字段进行自动生成。
主要差别在于对更新字段数据的处理，它的操作方式为，为了每条记录预留相当部分的版本空间如1000，对于不同的两条记录，其进入数据库后代理主键的相差值将是1000，而中间的这些值，既是留给每条记录的版本空间。当进入为新数据时，系统根据上一个序列号值*1000，产生新记录的代理主键值，并插入目标表。当发现是已有的数据发生改变，则把修改后的数据插入目标表，其代理主键值取原记录值的代理主键值+1，而版本号亦是取原记录的版本号+1，每条新记录的版本号均是从0开始编号。
以上方法需要利用游标进行逐条数据处理，性能不高；同时为了简化处理本例子采用系统步增列进行代理主键的生成，插入时默认从1~N依次插入，当发现已有数据改变时，把修改的数据插入目标列，其代理主键自动取最大的主键值+1，这样对于同一条记录最后更新的过的对应到维度表的记录必然是ID值相同主键值最大的那条记录。
代码
--关于维度表的设计是版本方式，标志方式，时戳方式的合集，后续不再重复建表
CREATE TABLE t_dem_xxx
(
SurID INT IDENTITY(1,1),
--缓慢维度变化中的代理键,也可以采用非递增字列，为简单起见最好使用递增字段
ID VARCHAR(20) NOT NULL,
Name1 VARCHAR(50),
Name2 VARCHAR(50),
IsNew BIT, --标志方式类型中标志字段 是否为最新 0否，1是
BeginDate DATETIME, --时戳方式类型中开始时间
EndDate DATETIME, --时戳方式类型中结束时间
CONSTRAINT PK_t_dem_xxx PRIMARY KEY (SurID)
)
go
CREATE TABLE t_tmp_xxx
(
ID VARCHAR(20) NOT NULL,
Name1 VARCHAR(50),
Name2 VARCHAR(50),
CONSTRAINT PK_t_tmp_xxx PRIMARY KEY (ID)
)
go
CREATE PROCEDURE p_dem_xxx
AS
--维度抽取存储过程
BEGIN
DECLARE
@num NUMERIC(10,0)
SELECT @num = COUNT(*) FROM t_dem_xxx
--如果原表为空，构造缺省值
IF @num = 0
BEGIN
INSERT INTO t_dem_xxx (ID,Name1,Name2) SELECT '-2','NULL值',''
INSERT INTO t_dem_xxx (ID,Name1,Name2) SELECT '-1','缺失外键',''
END
--根据主键插入在维度表中找不到的基础数据
INSERT INTO t_dem_xxx
(
ID ,
Name1 ,
Name2
)
SELECT a.ID,a.Name1,a.Name2
FROM t_tmp_xxx a LEFT OUTER JOIN t_dem_xxx b
ON a.ID = b.ID
WHERE b.ID IS NULL
--根据主键插入在维度表中找到但是已经发生变化的的基础数据
INSERT INTO t_dem_xxx
(
ID ,
Name1 ,
Name2
)
SELECT a.ID,a.Name1,a.Name2
FROM t_tmp_xxx a JOIN t_dem_xxx b
ON a.ID = b.ID
WHERE a.Name1<>b.Name1 OR a.Name2<>b.Name2
END
3.3.2.2标志方式
它的操作方式类似版本号方式，为每条记录预留1000个变化空间或者为步增方式递增。新记录的插入代理主键的产生类似版本号方式，以1000为倍数增加，而其新记录标志设置为 1，而当为旧数据时，一旦发现数据被更改过，则把该修改后的数据新插入目标，代理主键值取旧数据代理主键+1或者取最大值然后+1，并同时找到旧数据，更新其标志位为 0。
代码
CREATE PROCEDURE p_dem_xxx
AS
--维度抽取存储过程
BEGIN
DECLARE
@num NUMERIC(10,0)
SELECT @num = COUNT(*) FROM t_dem_xxx
--如果原表为空，构造缺省值
IF @num = 0
BEGIN
INSERT INTO t_dem_xxx (ID,Name1,Name2,IsNew) SELECT '-2','NULL值','',1
INSERT INTO t_dem_xxx (ID,Name1,Name2,IsNew) SELECT '-1','缺失外键','',1
END
--根据主键插入在维度表中找不到的基础数据
INSERT INTO t_dem_xxx
(
ID ,
Name1 ,
Name2 ,
IsNew
)
SELECT a.ID,a.Name1,a.Name2,1
FROM t_tmp_xxx a LEFT OUTER JOIN t_dem_xxx b
ON a.ID = b.ID
WHERE b.ID IS NULL
--根据主键更新在维度表中找到但是已经发生变化的的基础数据的标志位为0
UPDATE t_dem_xxx
SET IsNew = 0
FROM t_tmp_xxx a,t_dem_xxx b
WHERE a.ID = b.ID
AND (a.Name1<>b.Name1 or a.Name2<>b.Name2)
--根据主键插入在维度表中找到但是已经发生变化的的基础数据
INSERT INTO t_dem_xxx
(
ID ,
Name1 ,
Name2 ,
IsNew
)
SELECT a.ID,a.Name1,a.Name2,1
FROM t_tmp_xxx a JOIN t_dem_xxx b
ON a.ID = b.ID
WHERE a.Name1<>b.Name1 OR a.Name2<>b.Name2
END
3.3.2.3时戳方式
它的操作方式为当新记录插入时，取系统的当前时间，为其置上开始时间，此时其结束时间为空，当为旧数据时，把修改后的数据插入目标，代理主键由序列号生成器顺序产生，同时找到旧数据，更新其结束时间为当前系统时间。这种方式在系统中，只要结束时间为空的即是当前的最新值，而旧数据也可根据其开始时间结束时间看出它的实效空间，及旧数据的先后顺序。
全历史记录的方式是缓慢变化维的最为强大的一种记录方式，由于其设计的精妙，可以完全冗余发生的在抽取过程中出现的灾难事故及数据恢复工作，即使在运行过程中发生了断电等事故，我们完全不需要去关系，抽取到底进行到那个步骤，抽取了多少，目标插入了多少。。。。。。我们只要在环境恢复后，重新运行抽取，即可重新完成数据的整合。
代码
CREATE PROCEDURE p_dem_xxx
AS
--维度抽取存储过程
BEGIN
DECLARE
@num NUMERIC(10,0)
SELECT @num = COUNT(*) FROM t_dem_xxx
--如果原表为空，构造缺省值
IF @num = 0
BEGIN
INSERT INTO t_dem_xxx (ID,Name1,Name2,BeginDate)
SELECT '-2','NULL值','',GETDATE()
INSERT INTO t_dem_xxx (ID,Name1,Name2,BeginDate)
SELECT '-1','缺失外键','',GETDATE()
END
--根据主键插入在维度表中找不到的基础数据
INSERT INTO t_dem_xxx
(
ID ,
Name1 ,
Name2 ,
BeginDate
)
SELECT a.ID,a.Name1,a.Name2,GETDATE()
FROM t_tmp_xxx a LEFT OUTER JOIN t_dem_xxx b
ON a.ID = b.ID
WHERE b.ID IS NULL
--根据主键更新在维度表中找到但是已经发生变化的的基础数据的结束时间为当前
UPDATE t_dem_xxx
SET EndDate = GETDATE()
FROM t_tmp_xxx a,t_dem_xxx b
WHERE a.ID = b.ID
AND (a.Name1<>b.Name1 or a.Name2<>b.Name2)
--根据主键插入在维度表中找到但是已经发生变化的的基础数据
INSERT INTO t_dem_xxx
(
ID ,
Name1 ,
Name2 ,
BeginDate
)
SELECT a.ID,a.Name1,a.Name2,GETDATE()
FROM t_tmp_xxx a JOIN t_dem_xxx b
ON a.ID = b.ID
WHERE a.Name1<>b.Name1 OR a.Name2<>b.Name2
END
3.3.3记录最新记录及上一次历史（Type 3 Dimension -- keep the current and previous values in the target）
此种方式是缓慢变化维中较为折中的做法，但需要表结构随之适应。它的原理是把目标表字段扩充一倍，其中一组用来记录最新的记录信息，另一组用来记录上一次的历史记录。新记录插入时，将其插入到最新记录的位置，一旦记录发生改变，修改后的记录变为最新记录，则把修改后记录替换原来的记录，放在最新记录位置，同时把原记录移至旧记录位置。
这种方式，对于那些不是需要所有历史信息的表可以适用，但同时带来的是表结构需要
相应的调整，增加相同的一组字段，在字段本身就多的情况下，其新表的字段数将是源表字段数*2 ，故使用此种方式需要斟酌。
同时采用此方法对于OLAP中维度的建模并没有太大的益处，因为这两套数据只是前后版本的区别，并不存在层次的差别。
代码
CREATE TABLE t_dem_xxx
(
ID VARCHAR(20) NOT NULL,
Name1 VARCHAR(50),
Name2 VARCHAR(50),
PreName1 VARCHAR(50),
PreName2 VARCHAR(50),
CONSTRAINT PK_t_dem_xxx PRIMARY KEY (ID)
)
go
CREATE TABLE t_tmp_xxx
(
ID VARCHAR(20) NOT NULL,
Name1 VARCHAR(50),
Name2 VARCHAR(50),
CONSTRAINT PK_t_tmp_xxx PRIMARY KEY (ID)
)
go
CREATE PROCEDURE p_dem_xxx
AS
--维度抽取存储过程
BEGIN
DECLARE
@num NUMERIC(10,0)
SELECT @num = COUNT(*) FROM t_dem_xxx
--如果原表为空，构造缺省值
IF @num = 0
BEGIN
INSERT INTO t_dem_xxx (ID,Name1,Name2) SELECT '-2','NULL值',''
INSERT INTO t_dem_xxx (ID,Name1,Name2) SELECT '-1','缺失外键',''
END
--根据主键插入在维度表中找不到的基础数据
INSERT INTO t_dem_xxx
(
ID ,
Name1 ,
Name2
)
SELECT a.ID,a.Name1,a.Name2
FROM t_tmp_xxx a LEFT OUTER JOIN t_dem_xxx b
ON a.ID = b.ID
WHERE b.ID IS NULL
--根据主键更新在维度表中找到但是已经发生变化的的基础数据，重新设置数据
UPDATE t_dem_xxx
SET Name1 = a.Name1,
Name2 = a.Name2,
PreName1 = b.Name1,
PreName2 = b.Name2
FROM t_tmp_xxx a,t_dem_xxx b
WHERE a.ID = b.ID
AND (a.Name1<>b.Name1 or a.Name2<>b.Name2)
END
备注：
文档中所有代码均可执行，且执行步骤均相同，以下附调用代码
--第一次执行插入操作
INSERT INTO t_tmp_xxx VALUES ('1','AA','')
INSERT INTO t_tmp_xxx VALUES ('2','BB','')
EXEC p_dem_xxx
SELECT * FROM t_dem_xxx
SELECT * FROM t_tmp_xxx
--更新数据后的变化
UPDATE t_tmp_xxx SET Name2 = 'A' WHERE ID = '1'
EXEC p_dem_xxx
SELECT * FROM t_dem_xxx
SELECT * FROM t_tmp_xxx
4.1概述
剧烈变化维缓慢变化维通常是指基础数据表中经常发生变化或者部分字段数据经常更新，例如一个人的相关信息，身份证号和姓名性别等信息数据不变的部分，政治面貌和婚姻状态属于缓慢变化，而工作经历工作单位培训经历等在某种程度上属于急剧变化字段。
对于剧烈变化维度，通常情况下都是一分为二进行处理的，把其中不常变动的部分单独抽出来作为一个维表，按照缓慢变化维方式进行处理；另外一部分也单独抽取出来，通常作为维度的属性进行处理。
4.2实现
略 |
|
IP卡
狗仔卡
发表于 2006-12-9 20:02
收藏
提升卡
置顶卡
变色卡
显身卡
发表于 2006-12-11 09:00
