2007年3月10日星期六
BOSS经营分析系统——ETL第一部分
**以下实例中以HuaWei BOSS表为参考
简单先说说项目的情况。
项目基于全省HuaWei BOSS系统数据来源，根据当地客户实际需要指标做分析。目前省公司将原来BOSS1.0升级到BOSS1.5，经营分析也做了第二期，整合了CRM系统、1860 CallCenter系统。
这些大大小小的系统中，BOSS系统是必不可少的，但是对于经营分析、CRM系统，用户使用起来都不怎么好，客户反映存在的问题有：
1）要的指标没有；现在经分中存在的指标也有几百个，但是不是完全能够满足用户的要求；
2）统计口径不符；比如客户需要各个县市放号存活率，经分是按照全年的指标进行统计分析，而地市公司希望看到的是在每个月放号，在后期使用过程间隔每个月后的离网情况；
3）要的数据给用户的感觉不真实；可能也因为是统计口径的原因吧，造成提供的数据有偏差。
我们从这个角度出发，结合用户的实际工作，进行项目的实施。 现在正在做的项目就主要实现用户大量OLTP报表的生成，涵盖有对竞争对手分析、呼转分析、客户通话指标、客户数据业务指标、业务稽核、营销活动效果分析、基站通话分析等等，其中还涉及到一些业务流程上的辅助功能，这点也是很让人伤脑筋的一点，把DW的东西和business flow上的东西搅和到一起是很糟糕的。
一、数据的ETL的初步实现
对于BOSS的取数，需要通过FTP接口从BOSS中取数，地市做数据的预处理。 省公司会在每天同步打包成文件下载数据，地市然后解包进行同步文件中的数据ftp到本地，建立SOURCE Data，ODS，DW。
省公司下载文件列表参照图：
上图的数据列表中表数据类型主要分为有：
1、参数字典文件，例如：privset_item（产品表），FeeType（费用类别表），NetType（网络类型表）等。
此类数据FTP需要注意的是：
1）本地BOSS库表中原来存在数据，而在下载的省分表中次数据有可能删掉；
2）本地BOSS库表中没有，而在省分BOSS库表中增加新的记录；
3）省分BOSS库表中记录属性做了变更。
对于上面3种情况的解决策略：
1）->仍然保留本地BOSS库表中多出来的数据，在本地BOSS库表中用新增字段维护来维护数据的状态（例如：default “1”表示两个库中都存在，0表示省分已经删除这条数据）；
2）->往本地BOSS库表中新增省分BOSS库表中新增的数据（通过主键来比较）；
3）->只同步与省分BOSS库表数据中需要关注的属性值，本地建立需要关注同步的属性（字段）维护表；
2、OD文件的下载，例如：subscriber（入网表）、subs_privilege（优惠办理表）、subs_service（服务办理表）等等，这些表的特征是 在每条记录增加一个起始日和终止日，对当前纪录可以使用特殊终止日作为标记，使用时间戳作为删除标识。这可能是建立ODS中最费功夫的地方。省公司不保留所有的操作记录，OD会把历史的作废的记录剔除掉，本地BOSS需要保留所有的记录作为分析。
解决这个问题的策略是：跟踪主键OID的值，做前期的对应数据同步后进行入库。
3、日OD的下载，此类数据不存在上面2的问题，例如：GSM、GPRS、VPMN、MMS、SMS话单表以及帐单表等。
实现策略：只需要把每日下载文件解压上传SQLLDR到本地库中。
4、月OD文件的下载，存在个别表的数据每个月只把当月的操作数据下发一次。
实现策略：解压直接上传SQLLDR到本地BOSS。
对于上面ODS数据的初步建立后面会做仔细的描述。下面先描述从省公司FTP中下载数据->>unzip->>SQLLDR到本地库的过程。 省分下载文件的命名规则是：例如2007年3月5日这天的GSM话单数据根据文件大小分解成多个文件上传到FTP，每天一般有2G－3G的数据量，如图：
下载的文件没有固定的顺序，文件也没有固定的个数，通过后台shell结合SQLPLUS生成脚本SCRIPT对文件FTP/unzip/sqlldr/sqlplus等操作。这一层数据导入的操作在我的项目中我定义为SOURCE Data层，下一步按照我上面的不同文件数据的不通导入到ODS数据库中。
简单先说说项目的情况。
项目基于全省HuaWei BOSS系统数据来源，根据当地客户实际需要指标做分析。目前省公司将原来BOSS1.0升级到BOSS1.5，经营分析也做了第二期，整合了CRM系统、1860 CallCenter系统。
这些大大小小的系统中，BOSS系统是必不可少的，但是对于经营分析、CRM系统，用户使用起来都不怎么好，客户反映存在的问题有：
1）要的指标没有；现在经分中存在的指标也有几百个，但是不是完全能够满足用户的要求；
2）统计口径不符；比如客户需要各个县市放号存活率，经分是按照全年的指标进行统计分析，而地市公司希望看到的是在每个月放号，在后期使用过程间隔每个月后的离网情况；
3）要的数据给用户的感觉不真实；可能也因为是统计口径的原因吧，造成提供的数据有偏差。
我们从这个角度出发，结合用户的实际工作，进行项目的实施。 现在正在做的项目就主要实现用户大量OLTP报表的生成，涵盖有对竞争对手分析、呼转分析、客户通话指标、客户数据业务指标、业务稽核、营销活动效果分析、基站通话分析等等，其中还涉及到一些业务流程上的辅助功能，这点也是很让人伤脑筋的一点，把DW的东西和business flow上的东西搅和到一起是很糟糕的。
一、数据的ETL的初步实现
对于BOSS的取数，需要通过FTP接口从BOSS中取数，地市做数据的预处理。 省公司会在每天同步打包成文件下载数据，地市然后解包进行同步文件中的数据ftp到本地，建立SOURCE Data，ODS，DW。
省公司下载文件列表参照图：
上图的数据列表中表数据类型主要分为有：
1、参数字典文件，例如：privset_item（产品表），FeeType（费用类别表），NetType（网络类型表）等。
此类数据FTP需要注意的是：
1）本地BOSS库表中原来存在数据，而在下载的省分表中次数据有可能删掉；
2）本地BOSS库表中没有，而在省分BOSS库表中增加新的记录；
3）省分BOSS库表中记录属性做了变更。
对于上面3种情况的解决策略：
1）->仍然保留本地BOSS库表中多出来的数据，在本地BOSS库表中用新增字段维护来维护数据的状态（例如：default “1”表示两个库中都存在，0表示省分已经删除这条数据）；
2）->往本地BOSS库表中新增省分BOSS库表中新增的数据（通过主键来比较）；
3）->只同步与省分BOSS库表数据中需要关注的属性值，本地建立需要关注同步的属性（字段）维护表；
2、OD文件的下载，例如：subscriber（入网表）、subs_privilege（优惠办理表）、subs_service（服务办理表）等等，这些表的特征是 在每条记录增加一个起始日和终止日，对当前纪录可以使用特殊终止日作为标记，使用时间戳作为删除标识。这可能是建立ODS中最费功夫的地方。省公司不保留所有的操作记录，OD会把历史的作废的记录剔除掉，本地BOSS需要保留所有的记录作为分析。
解决这个问题的策略是：跟踪主键OID的值，做前期的对应数据同步后进行入库。
3、日OD的下载，此类数据不存在上面2的问题，例如：GSM、GPRS、VPMN、MMS、SMS话单表以及帐单表等。
实现策略：只需要把每日下载文件解压上传SQLLDR到本地库中。
4、月OD文件的下载，存在个别表的数据每个月只把当月的操作数据下发一次。
实现策略：解压直接上传SQLLDR到本地BOSS。
对于上面ODS数据的初步建立后面会做仔细的描述。下面先描述从省公司FTP中下载数据->>unzip->>SQLLDR到本地库的过程。 省分下载文件的命名规则是：例如2007年3月5日这天的GSM话单数据根据文件大小分解成多个文件上传到FTP，每天一般有2G－3G的数据量，如图：
下载的文件没有固定的顺序，文件也没有固定的个数，通过后台shell结合SQLPLUS生成脚本SCRIPT对文件FTP/unzip/sqlldr/sqlplus等操作。这一层数据导入的操作在我的项目中我定义为SOURCE Data层，下一步按照我上面的不同文件数据的不通导入到ODS数据库中。
BOSS经营分析系统——ETL第二部分
数据ETL的主要处理规则：
一、宏观输入输出
从对数据源的整个宏观处理分，看看一个ETL过程的输入输出，可以分成下面几类：
1、大小交
一般会直接进行Descartes 乘积处理，如果大表的量非常大，会采用空间换时间的代价，将大表中不需要的关注的列删除，尽量减少整体的大小，以及其他索引和sql语句优化处理。
2、大大交
同样去掉不需要的属性，将索引和SQL语句优化用到极致。还有一种需要注意的可以采用横向分解表。例如：大表subs_privilege中办理了计费类别和优惠包的业务，因为存在和大表subscriber的Descartes 的join，我们在设计时候将subs_privilege分解为Subs_privilege_G的计费类别业务表和subs_privilege_B的优惠包业务表 ，那这样subs_privilege的表数据分解成多个表，再分别和subscriber进行Descartes 运算。
3、站着进来，躺着出去。
事务系统中为了提高系统灵活性和扩展性，很多信息放在代码表中维护，所以它的“事实表”就是一种窄表，而在数据仓库中，通常要进行宽化，从行变成列，所以称这种处理情况叫做“站着进来，躺着出去”。有些时候也是为了满足前端层现的需要，将表进行行列转置。在ORACLE使用Decode，case when的方式对表进行宽表化。窄表变宽表的过程主要体现在对窄表中那个代码字段的操作。这种情况，窄表是输入，宽表是输出，宽表的粒度必定要比窄表粗一些，就粗在那个代码字段上。
4、基本聚集。
数据仓库中重要的任务就是沉淀数据，聚集是必不可少的操作，它是粗化数据粒度的过程。聚集本身其实很简单，就是类似SQL中Group by的操作，选取特定字段（维度），对度量字段再使用某种聚集函数。但是对于大数据量情况下，需要对聚集算法的多次优化，例如是直接使用SQL的Group by，还是先排序，再处理。
5、特殊函数聚集
按照方差，偏差等函数聚集。可以通过ORACLE的分析函数实现，根据特殊应用有此类要求。一般比较少。
6、特殊取数
例如：按某一关键字段值需要取操作记录中最有最后有效的记录，需要用到oracle中的row_number() over (partiton by 用户OID order by 时间 desc)的方式获取过滤；需要实现不同时间点上累积值，有点象进销存中的功能，统计某一产品截止到20070101办理了120000户，截止到20070102办理了123000户这样的时间点上的值，我们可以通过操作记录进行聚集，实现方式使用ORACLE的函数sum(产品数) over(partition by 产品 order by 时间)。
二、微观规则
1、原封照搬，将表中的数据不做处理，直接通过insert into ......select ...的方式获取。
2、字段运算：比如通话中时长不足一分钟的算1分钟的处理ceil(duration/60)，以及分转换到元的round等。
3、参照转换：把表中多条记录整合为一条记录，其值用内嵌表或者一个字段存储（其中用分隔符隔开）。
4、字符串处理：主要处理不符合格式要求的字符串的值。
5、空值判断：分情况区别对待
1）如果存在空值的属性作为group by的条件处理，就采用自定义的缺省值填充，例如：增加"no define"的自定义键值进行标识；
2）如果存在控制的属性会作为将来where中的子句，例如：在有些fact table中的表示时间的起止日期，startdate为起始时间，用Enddate为终止时间，我们一般保留enddate的NULL，不作空值处理。
6、日期转换
对于数据聚集或者保留数据痕迹按照yyyy-mm-dd或yyyy-mm的格式统一保存，以及聚集中的日期格式转换等。此字段也作为建立分区表的依据，使用ORACLE的日期运算函数进行日期运算。
8、聚集运算
结合上面的宏观中聚集规则处理，进行多重的concept hierarchy，包括shcema hierarchy（year->month->day;city->town->registerorg;mark->billing->privilege etc）和set-grouping hierarcy（0..100,100..200,200..500,500..1000,1000......etc）。hierarchy运算一般实现的有sum,count,avg,min,max等number聚集的。
9、数据丢失
例如：在subscriber表中有入网记录，但是在对应的产品表subs_privilege中没有对应的有效记录或根本没有对应的记录，按照业务的定义，一个入网用户应该可以找到对应1..M条产品记录。
丢失的原因：1）这笔入网记录已经退网；
2）预入网的批量处理的号码（业务需要）；
3）数据异常。
对于此类问题解决方式：1）对于没有对应有效的记录，则强制匹配一个最后失效的记录作为其办理的产品；
2）完全找不到对应记录的，按照前面的“空值判断”来处理。
三、优化手段
因为设计海量数据的处理，必须采用各种手段对数据的优化预处理，对硬件改良的情况，主要采用软技术手段：
1）使用space换time和time换space的策略；
2）表的适度cut；主要对dimession的cut，事实表中属性字段的cut；
3）采用partition、index来实现。在使用partition、index还需要比较其中不同partition、不同index之间的差异比较，进行性能评估。例如：一般在fact table中，对diemession属性都不采用normal index，采用BITMAP index，B-tree index等；对于LIKE的条件采用function index的方式实现等等。
一、宏观输入输出
从对数据源的整个宏观处理分，看看一个ETL过程的输入输出，可以分成下面几类：
1、大小交
一般会直接进行Descartes 乘积处理，如果大表的量非常大，会采用空间换时间的代价，将大表中不需要的关注的列删除，尽量减少整体的大小，以及其他索引和sql语句优化处理。
2、大大交
同样去掉不需要的属性，将索引和SQL语句优化用到极致。还有一种需要注意的可以采用横向分解表。例如：大表subs_privilege中办理了计费类别和优惠包的业务，因为存在和大表subscriber的Descartes 的join，我们在设计时候将subs_privilege分解为Subs_privilege_G的计费类别业务表和subs_privilege_B的优惠包业务表 ，那这样subs_privilege的表数据分解成多个表，再分别和subscriber进行Descartes 运算。
3、站着进来，躺着出去。
事务系统中为了提高系统灵活性和扩展性，很多信息放在代码表中维护，所以它的“事实表”就是一种窄表，而在数据仓库中，通常要进行宽化，从行变成列，所以称这种处理情况叫做“站着进来，躺着出去”。有些时候也是为了满足前端层现的需要，将表进行行列转置。在ORACLE使用Decode，case when的方式对表进行宽表化。窄表变宽表的过程主要体现在对窄表中那个代码字段的操作。这种情况，窄表是输入，宽表是输出，宽表的粒度必定要比窄表粗一些，就粗在那个代码字段上。
4、基本聚集。
数据仓库中重要的任务就是沉淀数据，聚集是必不可少的操作，它是粗化数据粒度的过程。聚集本身其实很简单，就是类似SQL中Group by的操作，选取特定字段（维度），对度量字段再使用某种聚集函数。但是对于大数据量情况下，需要对聚集算法的多次优化，例如是直接使用SQL的Group by，还是先排序，再处理。
5、特殊函数聚集
按照方差，偏差等函数聚集。可以通过ORACLE的分析函数实现，根据特殊应用有此类要求。一般比较少。
6、特殊取数
例如：按某一关键字段值需要取操作记录中最有最后有效的记录，需要用到oracle中的row_number() over (partiton by 用户OID order by 时间 desc)的方式获取过滤；需要实现不同时间点上累积值，有点象进销存中的功能，统计某一产品截止到20070101办理了120000户，截止到20070102办理了123000户这样的时间点上的值，我们可以通过操作记录进行聚集，实现方式使用ORACLE的函数sum(产品数) over(partition by 产品 order by 时间)。
二、微观规则
1、原封照搬，将表中的数据不做处理，直接通过insert into ......select ...的方式获取。
2、字段运算：比如通话中时长不足一分钟的算1分钟的处理ceil(duration/60)，以及分转换到元的round等。
3、参照转换：把表中多条记录整合为一条记录，其值用内嵌表或者一个字段存储（其中用分隔符隔开）。
4、字符串处理：主要处理不符合格式要求的字符串的值。
5、空值判断：分情况区别对待
1）如果存在空值的属性作为group by的条件处理，就采用自定义的缺省值填充，例如：增加"no define"的自定义键值进行标识；
2）如果存在控制的属性会作为将来where中的子句，例如：在有些fact table中的表示时间的起止日期，startdate为起始时间，用Enddate为终止时间，我们一般保留enddate的NULL，不作空值处理。
6、日期转换
对于数据聚集或者保留数据痕迹按照yyyy-mm-dd或yyyy-mm的格式统一保存，以及聚集中的日期格式转换等。此字段也作为建立分区表的依据，使用ORACLE的日期运算函数进行日期运算。
8、聚集运算
结合上面的宏观中聚集规则处理，进行多重的concept hierarchy，包括shcema hierarchy（year->month->day;city->town->registerorg;mark->billing->privilege etc）和set-grouping hierarcy（0..100,100..200,200..500,500..1000,1000......etc）。hierarchy运算一般实现的有sum,count,avg,min,max等number聚集的。
9、数据丢失
例如：在subscriber表中有入网记录，但是在对应的产品表subs_privilege中没有对应的有效记录或根本没有对应的记录，按照业务的定义，一个入网用户应该可以找到对应1..M条产品记录。
丢失的原因：1）这笔入网记录已经退网；
2）预入网的批量处理的号码（业务需要）；
3）数据异常。
对于此类问题解决方式：1）对于没有对应有效的记录，则强制匹配一个最后失效的记录作为其办理的产品；
2）完全找不到对应记录的，按照前面的“空值判断”来处理。
三、优化手段
因为设计海量数据的处理，必须采用各种手段对数据的优化预处理，对硬件改良的情况，主要采用软技术手段：
1）使用space换time和time换space的策略；
2）表的适度cut；主要对dimession的cut，事实表中属性字段的cut；
3）采用partition、index来实现。在使用partition、index还需要比较其中不同partition、不同index之间的差异比较，进行性能评估。例如：一般在fact table中，对diemession属性都不采用normal index，采用BITMAP index，B-tree index等；对于LIKE的条件采用function index的方式实现等等。
订阅：
博文 (Atom)
