经营分析系统的DW模型.docx
《经营分析系统的DW模型.docx》由会员分享，可在线阅读，更多相关《经营分析系统的DW模型.docx（18页珍藏版）》请在冰豆网上搜索。
经营分析系统的DW模型
经营分析系统的DW模型
一、写在
前面
本文的目的是为了介绍联通经营分析系统的统一DW模型（UniDW），这份模型已经通过概念模型（CDM）的形式发布，为了深入理解设计的思路，这里做一个大致的说明。
本文首先回答为什么会有统一DW模型，再简要述说经营分析关注的业务领域，最后再逐一介绍统一DW模型中的结构。
希望能够和正在建设联通经营分析系统的同志们或是在其他领域建设数据仓库系统的同行们一起探讨一下数据仓库设计。
二、为什么有统一DW模型？
在联通经营分析系统中，第一阶段主要关注的分析主题是围绕用户而展开的，以前，对于用户、客户的概念模糊不清，经常混淆两者。
甚至在总部统一经营分析系统业务规范中也是如此。
客户相关的分析主题也有，诸如客户的年龄构成、职业构成等，但是由于数据源不干净，无法保证客户资料的准确性，所以这部分的分析不是目前阶段的重点。
因此，我们现在提出的统一DW模型主要围绕用户的信息而设计的。
关于用户和客户的区别在后面有详细介绍。
首先，我们要明确统一DW模型的目的。
在经营分析数据仓库中，我们一般将它划分层ODS、DW和DM层，DW层指的是位于ODS之上，DM层数据之下的那一层数据结构，这一层的数据结构的主要任务是完成数据的预处理和沉淀。
ODS的全称是OperationalDatastore，它基本是按照数据源的模式存储数据，也就是偏向于事务型的数据存储，从这一层的数据结构，我们要进行OLAP分析很困难，因为它不是维度建模的，虽然我们在数据源到ODS这一过程的主要任务是进行代码转化（将代码转换为ID）和数据清洗。
ODS层存在的目的在于降低数据仓库系统与OLTP系统的耦合程度，因为从OLTP数据源到ODS层表的规则一般都是非常简单，数据源抽取的规则复杂度很低。
ODS层存在的另一个目的是提供即席查询，诸如离网客户名单，高价值客户名单等，涉及到详细信息时，必须从ODS层出数据。
DM层的目的很简单，它不是做数据沉淀之用，而是作为装载Cube所需的临时表，主要为性能考虑，便于流程处理，存储的数据也是最近一个时期的数据。
因为多维分析的需求是经常变化的，所以这一层的结构也是频繁变化的，我们在设计时考虑将这一层数据结构放在逻辑设计之外（要让逻辑设计尽量保持稳定）。
所幸的是这一层的结构非常简单，当一个分析主题确定时，也即维度、度量确定下来后，DM表的结构也随之确定，所以这一层表的创建和数据的装载工作目前都归于多维分析实施的工作。
正是由于用户需求的频繁变化，我们必须要有一个强大而稳定的DW层表，它包含绝大多数的分析维度和度量，并且具有足够时间跨度的数据沉淀。
这种数据沉淀必须要是稳定的，在经营分析系统建设初期，数据沉淀的概念还是不清楚的，很多情况是将在若干维度汇总若干度量的数据当作沉淀，但是一旦从一个新的维度分析同样的度量，那么这个汇总数据一点用都没有。
另外，这个DW层必须是和OLTP系统是绝对低耦合的，DW应该只和业务主题、分析逻辑相关，而不应该同OLTP系统的某个字段扯不清。
只有这样，我们的DW层才能被复用。
对于用户不断变化的需求，DW设计在一定范围内也要能够适应这种变更，这就要求对扩展性设计的要求。
综上所述，设计DW层要达到以下目标：
• 数据沉淀的稳定性。
能够保证数据沉淀不是白费空间、时间；
• 数据结构的重用性。
能够保证数据结构的通用性，和数据源无关；
• 数据结构的扩展性。
能够适应需求的变更；
都说数据仓库是面向主题的、集成的、相对稳定的、反映历史变化的，可是究竟这些字里行间的意义是什么呢？
我们认为在面向主题之前，首先要面向对象，这里的对象指的是电信业务中的实体，用户可以算是一个实体，其他的诸如客户、产品、渠道、竞争对手等都是实体。
对每个实体，要将他们的基本信息、消费信息集成起来，形成稳定的数据结构，并在时间上进行沉淀。
前面我们提到目前经营分析的重点实体是用户，按照不同的维度对用户进行分群，分析他们的数量、消费、业务使用量等。
三、经营分析系统关注的业务
经营分析关注的主要是结果，例如关注用户的信用度，而不是如何对用户进行信用控制；关注用户的开帐金额，而不是用户的开帐优惠规则；关注用户的二次批价详单，而不是如何进行二次批价等。
目前，经营分析所获取的数据源来自综合营帐系统、计费系统、客服系统和结算系统，主要也就是来自前两者。
对于不同的厂商，它们的系统各自不同，通过分析看到，它们的结构自然都是类似的，因为毕竟有一个综合营帐系统的技术规范在那里，并且也经过一段时间的洗礼，厂商也不能走得太远。
它们对于详细的处理细节可能不尽相同，但是对于最终我们关注的结果性的数据结构，还是可以统一的。
目前，在ODS层，我们没有统一，因为历史原因，我们现在也很难统一这一层，但是毫无疑问，这一层在一定程度上也是可以统一的，将主要差异体现数据源到ODS的规则上，而不要体现在结构上，只是目前做到统一DW层还是比较现实的。
在此，可以将这些需要关注的结果性数据归类如下：
客户资料类
这里有一系列表存放客户信息，核心概念就是三户关系，客户、帐户与用户。
它将客户的基本信息，客户消费的信息和与运营商某个业务的契约信息分开。
这三者之间的关系可以想象成是一棵树，客户位于树的根部，帐户在中间，用户是第三层叶节点。
一个客户拥有多个帐户，一个帐户下可以有多个用户，用户是最细的单位，在经营分析中，目前的统计分析大多都是针对用户进行统计，但是由于术语定义的不严格，在很多场合下，将客户和用户混为一谈，例如经常说的客户数通常是用户数（不是绝对），用户级别其实是客户级别。
当一个客户入网时，例如选定CDMA业务，一般情况下会为这个客户建立一套三户关系，当然如果一个客户申请一项新业务，例如193长途，如果想用已有的帐户缴费，可以只需建一个新用户，如果它想在另一个帐户缴费，则再建一个新的帐户。
在客户信息，我们关注的信息是客户类型、客户级别、性别、年龄、职业等。
在帐户中，我们通常关注的是帐户的缴费类型、预存款等，用户入网时可以首先指定他是用现金缴费还是银行代扣等信息，对于银行代扣等类型，还记录银行名称、帐号等。
用户信息中，有用户的手机号、服务状态、入网时间、是否有效等契约信息。
关于用户，还有一个子业务信息，或称特服，例如来电显示、移动秘书台、三方通话，这些信息表明该用户除了基本的通话功能外，还有哪些特殊的服务，它们通常和服务属性存储在一块（其实特服本身就是一种服务属性），例如漫游级别、长途级别等。
有的系统通过掩码来表明用户订购了哪些特服或是哪些服务属性（如东软、神码的营帐），有的是通过单独的表来维护用户开通了哪些特服（如亚信的营帐）。
帐务类
每月月初，系统有一个开帐的业务环节，它将每个用户的上月消费的金额计算出来，这个计算根据用户的详单、他的套餐、用户类型的信息作不同的优惠、减免。
通过用户的预存款或是缴费，系统完成销帐操作，如果用户在缴费期（一般指帐期的后一个月期间）没有缴费，这个用户就算欠费。
在帐务类信息中，我们关注的是应收、实收、缴费、欠费。
• 应收
即开帐完毕后，得出所有用户应该缴付的费用，这些信息存储在月帐单中。
我们每月收到联通寄来的帐单上面，明细列出本地通话费多少、长途费、漫游费多少，这就是从月帐单中来的。
月帐单中存放的是所有开帐用户在某个帐期内每种费用类型的费用。
通常情况下，这里还存放着优惠费用和减免费用。
优惠费用有详单级优惠、帐单级优惠，详单级优惠指在详单二次批价时所作的优惠，在月帐单中，通常也将这些值复制过来。
帐单级优惠则指在开帐时为用户所作优惠。
优惠费用在月帐单表中有两种方式表示，一种在列上体现，月帐单有优惠字段，在这里存放一个值表示优惠信息，一般为正值；还有一种是在行上体现，通过费用类型体现这笔费用是一个帐务优惠，一般为负值，这样累加起来的应收就会去掉这种优惠。
另外还有一些由于批价错误，导致该用户的当月应收错误，不同营帐的处理也是不同的，有的可能会去修改月帐单，有的则是在下月开帐作减免。
• 实收
实收信息从销帐表统计，而非从缴费表得到，因为用户所缴费用可能有些部分是作为预存款的，这部分不应该算是实收。
当开帐后，营帐的一个操作是根据用户的预存款来销帐，在接下来的一个月期间内，通过用户的缴费来销帐。
对于预存款销帐，经常会有半销的情况，例如应收是100元，但是预存款只有50元，对这种情况，不同营帐也是不同处理，有的是作半扣处理，即将它作为一个特殊的费用类型冲抵欠费，有的是按照预定义的销帐顺序来依次销特定类型费用，如现销月租费，再销本地费等。
销帐表的结构和月帐单表大同小异，它的每条记录表示哪个帐期的哪笔费用在何时销帐的。
因为存在欠费的情况，所以一个月可能会销用户几个月的费用。
所以销帐表中重要的信息包括销帐月份、费用月份、费用类型。
在开帐后统计该帐期的实收是没有意义的，因为缴费期才刚刚开始，因此我们关注的是实收上期费用是实收往月费用，后者又叫欠费回收，因为实收往月就是指欠费的销帐。
• 缴费
用户可以随时缴费，有的用户未雨绸缪，先缴够费用再消费，有的是不见棺材不落泪，欠费停机才缴费。
所缴费用存放在用户的帐户中，用于销帐或是作为预存款。
用户可以通过各种方式缴费，现金、支票、信用卡、银行托收、代扣等，现在还有更多通过充值卡缴费，这叫做缴费方式。
用户可以在营业厅缴费、也可以在银行、通过客户中心缴费，这叫做缴费渠道，另外还有一个信息，缴费时段，也是我们关注的信息，这主要为分析充值卡缴费之用。
缴费表存储的是缴费流水，一个用户在一天当中可以以多种方式或是多种渠道缴费，所以在统计时，分析缴费用户数时，特别注意它在缴费时段、缴费方式、渠道上是不可汇总的。
• 欠费
一个用户在某个帐期的费用在一个月内没有销掉，则为欠费。
因为信用控制的方式不同，有的用户欠费立即停机，有的可能连续几个月欠费。
在欠费表中，存放着每个用户欠哪个月的哪种类型的费用。
欠费和上面的应收、实收、缴费有一个区别，上面三个都是在一个期间内相对静态的、发生的费用，例如某个月的应收在以后看还是那么多。
但对于欠费，如果要看发生的欠费，其实是应收减实收，我们这里更关注累计的欠费，它是动态的，用户在一月欠了
100元，到二月可能欠了200元，也可能不再欠费了。
如果用户欠费超过一定时间，例如1年，营帐可能就要作呆坏帐处理。
有的营帐系统的欠费表并不能都算欠费，而是将开帐后没有销帐的都记录在案，但其实上个帐期没有销帐的费用并不能算欠费。
在计算欠费时长时也要特别注意这一点，要用最大欠费减去最小欠费月份，例如最小欠费月份是2004年1月，最大欠费月份是2004年4月，那么欠费时长就是三个月。
如果用统计月份来计算的话，这时的统计月份应当是2004年5月，欠费时长＝MonthBetween（最小欠费时长，统计月份）－1。
计费类
计费类数据一般不在营帐系统中，而是单独的专业计费系统，它们都是一些详单数据，例如GSM详单、CDMA详单、短信详单、CDMA1.X流量、193、IP详单等，当然，我们最关注的还是GSM、CDMA详单。
这些详单都是二次批价后的数据，它记录的是每个用户每次通话的呼叫信息、网络信息、计费信息，呼叫信息例如是否主被叫、长途类型、漫游类型、对方运营商、呼叫起始时间、通话时长等，网络信息诸如基站、MSC等，计费信息诸如基本费、长途费、长途附加费、特服费、计费时长等，另外还有对应的优惠费，这就是前面提到的详单级优惠。
计费时长根据长途通话和本地通话而不同，本地通话按分钟计费，例如70秒的本地通话的本地计费时长为2分钟，长途计费时长按6秒计费，如70秒的长途通话，长途计费时长为72秒。
用户可以每天上网或是拨打特服电话查询自己的实时话费，营帐每天都会统计它，将他们存放在一张实时话费表中，这张表不仅仅是简单地将详单的费用汇总，还将用户的短信费用、月租费用都加入其中。
到月初开帐时，它是开帐的一个基本依据。
在这张表中一般也有一个费用类型，为了以示区分，我们将它叫做详单费用类型，帐单中的费用类型叫做帐单费用类型，从详单费用类型到帐单费用类型存在者映射关系。
营业类
这里包括营业受理、营业收费和一系列的日志。
营业受理指在营业厅每笔受理记录，例如用户开户、改号等操作，但是并不是每种受理都是收费的，或者一次受理会收取不同类型的费用，这里的费用类型我们叫做营业费用类型，例如开户费、SIM卡费等，这些费用信息都是存放在营业收费中的。
对于用户、客户、帐户的信息变动，必须有日志记录，特别是针对套餐、用户状态、特服等。
日志表要记录这些信息变动前后的信息，有的营帐用一张大表记录所有日志，统一描述异动信息比较困难，有的是针对特定类型异动设计特定日志。
其他类
除了上面提到到这些分类，经营分析系统关注的业务还有资源、智能网等一系列业务，资源主要包括卡和号码，智能网其实也是移动业务的一种，但是它是单独平台的数据，主要关注的业务是充值数据、详单数据等。
联通经营分析活动关注的是什么？
经营分析系统是为联通的经营活动提供决策支持，在这个过程中，要提供一系列重要的指标来衡量经营活动的成效和指导未来的策略。
在目前前端，纵然用户的需求并不是非常明确，究竟哪些指标是经营活动中关注的？
这已经逐渐由混沌走向明晰。
• 业务发展
联通关注的首先是每种业务的用户数情况，这些用户数主要包括在网用户数、新增用户数、离网用户数、出帐用户数、通话用户数等。
这在抢占市场时期是特别关注。
• 业务收入
即使用户数得到迅速发展，但是收入并不一定同比增长，特别是项CDMA业务的发展趋势，起初用户的增长特别迅猛，但是因为采取很多优惠政策，收入却增长缓慢。
业务收入中主要也是围绕应收、实收和欠费来分析，目前成本这一块数据难以获取，大大限制了经营分析的作用。
• 业务量
这里关注的是用户使用业务的情况，例如移动业务中，通话时长、通话次数、计费时长是比较重要的指标。
对于CDMA1x业务，流量是重要的指标。
针对上面的指标，经营分析对于它们在用户套餐、用户的入网渠道上的分布还特别关心，前者为套餐（产品）的制定提供决策依据，后者为优化渠道建设提供依据。
四、统一DW模型结构
通过了解上面的业务介绍，我们现在可以看看统一DW模型的设计思路。
前面提到，现阶段我们的分析主要围绕用户主题展开。
因此，在DW层，就必须围绕用户设计出一系列的核心数据结构。
为了说明DW层的作用，我们设想一些典型的分析：
1、2004年4月1日，爪哇市的C网新增用户数，离网用户数；
2、2004年4月6日，爪哇市C网的当日话费收入和当月累计话费收入；
3、2004年2月爪哇市的C网应收，和3月的应收同期比较，增长率为多少；
4、2004年3月份爪哇市C网应收费用中，月租费、本地通话费、长途费、漫游费、短信费等各占多少比例？
在套餐上细分的比例是多少？
5、截止到2004年2月底，爪哇市C网的累计欠费是多少？
有多少用户欠费？
哪种套餐发生的欠费较多？
哪个代理商所发展用户欠费较多？
6、截止到2004年3月1日，爪哇市C网的实收上期（1月份）费用是多少？
回收欠费多少？
7、2004年4月4日，爪哇市C网主叫国内漫游本地通话次数是多少？
计费时长是多少？
在套餐上的分布如何？
用户级数据沉淀
为了满足数据沉淀稳定性的要求，DW设计不能简单的根据需求将数据预先在可能的维度上汇总若干度量，因为需求是不断变化的，此时提出的需求很可能并不是真正有用的需求，当这个分析需求需要加入一个新的分析维度时，那这个汇总数据将被无情废掉，要不你就将尽可能多的维度放在一起汇总，但那样恐怕是不现实的，因为汇总性能和空间都不会允许。
因此，我们需要还一个思路来沉淀数据。
很自然的，刚才那种汇总数据的沉淀方式是去除用户id的，那么如果我们保留用户id呢？
那样沉淀数据是可以一定程度保证稳定性的，至少沉淀下来的数据不会白费。
但是要注意，对于一个用户对应多条记录的数据源，可以将它们想象成一个窄表，例如月帐单表，它通过费用类型区分不同费用，在DW层，我们就需要形成一个宽表，每个用户每月一条记录，根据费用类型形成若干字段，如通话费、月租费等。
我们这里将它们称作用户信息表（一系列）。
这样的数据沉淀能够很大程度上保证数据的稳定性，如果有新的需求需要在新的维度上分析，可以扩展这些用户信息表。
经营分析系统围绕用户，主要关注用户的服务情况、消费情况、业务使用情况、缴费行为、欠费行为等。
因此，我们可以针对这些用户主题，建立不同的用户信息表，请注意各个用户信息表的用户集合并不是一样的。
假设有如下的符号描述：
• U（W）：
表示整个用户集合，所有有记录的用户；
• U（N）：
表示在网用户的集合，表示截止到某一时刻，所有未离网的用户；
• U（B）：
表示某月出帐的用户集合，即出现在该月帐单表中的用户集合；
• U（C）：
表示某月（日）产生通话行为的用户集合，即出现在该月（日）通话详单中的用户集合；
• U（M）：
表示某月（日）使用短信的用户集合，即出现在该月（日）短信详单中的用户集合；
• U（X）：
表示某月（日）使用Cdma1.x业务的用户集合，即出现在该月（日）CDMA1.x详单中的用户集合；
• U（A）：
表示截止到某时间点，发生欠费的用户集合，一般指出现在欠费表中的用户；
统一DW层有一系列的表存放用户的各种信息，我们这里所指的用户信息是一种广义上的含义，和营帐系统的用户信息表不同，广义的用户信息具有一个特征，用户和它是一对一关系的，例如用户的手机号，一个用户只有一个手机号，再如用户的应收费用分档，一个用户一个月也只有一个分档。
对于类似费用类型、呼叫类型，用户和它们都是一对多关系，不能称之为用户信息。
用户信息可以分成两类，一种是固有的信息，一种是统计出来的信息，前者一般在分析中作为维度，例如用户套餐、用户欠费时长；后者即可以衍生为维度，也可以作为度量，例如用户的应收话费，既可以作为费用分档维度，也可以作为出帐应收度量。
用户有两个最基本的信息就是归属城市和业务类型，这两个信息作为维度几乎出现所有的分析主题，而且下面的用户信息表都包含了这两个信息，这个冗余可以便于统计主题数据。
用户信息表在命名上具有相似性，一般都以FW_UserInfo作为前缀，后面跟上1-2个字符表示信息类型。
下面按照用户信息的分类分别介绍这一系列用户信息表。
• 1、服务信息
用户是一种客户订购某种业务的合约关系，我们在ODS中一般都保存了一份全部的用户表，到了DW层，我们还要进一步综合。
用户静态表（FW_UserInfoS），用户静态表存放着用户的基本信息，用户ID作为主键，每个用户一条记录。
前面在业务介绍中提到，客户、帐户与用户是一对多关系的，所以客户和帐户的信息都能唯一映射到一个用户上，在静态表中，我们就是集成了ODS层中的用户表、客户表和帐户表，一般来说，我们在客户表中要取的信息包括：
• 客户类型：
例如个人客户、单位客户、集团客户等；
• 客户级别：
例如普通客户、大客户等；
• 客户年龄：
一般可以从身份证ID计算，但是数据不是非常整洁；
• 客户性别：
一般可以从身份证ID计算，但是数据不是非常整洁；
• 客户职业：
例如教师、学生、公务员等；数据不整洁；
从ODS帐户表中，我们一般要取的信息包括帐户缴费方式，用户在入网时，可以预先设定一种缴费方式，例如选择银行代扣，要指定银行帐号和银行代码等；从分析的角度，这个维其实很少用到，所以在实际项目中，可以适当取舍这个表的关联；
从ODS用户表中，我们要将大部分的用户信息直接搬过来，主要的信息包括用户所属地市、用户状态、用户套餐、用户入网渠道、用户入网月份（在网时长）等。
另外有几个特别的信息需要强调：
• 上次用户状态，因为我们需要每天增量更新DW用户静态表，所以对于状态发生变化的用户，我们需要将改变前的状态记录在这个信息中。
• 状态保持时长，从上次状态改变至今维持了多长时间，这对于分析诸如停机1个月以上用户数有用。
• 是否在网，是否在网通常需要通过好几个字段进行判断，而且不同的营帐系统判断方法还不一样，因此，为了屏蔽这一个判断算法，用此标识来区分用户是否在网，这对统计在网用户数非常有帮助；
用户静态表每日增量更新，它的用户集合是U（W）。
一般在进行日分析中都会涉及到该表，例如业务使用日分析，从用户套餐、用户代理商、呼叫类型、漫游类型等维度分析通话次数、计费时长度量时，就必须关联用户静态表和详单来统计。
另外，对于分析每日用户发展，截止某日的在网用户数等都需要从这里统计。
到了月底最后一天，为了为月分析提供支持，还需要备份一张静态表，叫做月底静态备份表（FW_UserInfoSM），它和静态表的结构一摸一样，因为月分析的统计通常要到月初的5、6号才能完成，那时，用户静态表已经是下个月的了，不能代表上月的情况。
每个月备份用户表是非常重要的，这对补充历史数据也非常有意义，因此建议即使在ETL工作没有完成的时候，也要注意每月备份用户表。
• 2、消费信息
这里的消费信息是指每月系统开帐后，每个用户的应该交付的费用，通常你每月收到手机的帐单也就是这个了。
它从ODS月帐单而来，在帐单中，可以看到上个月本地话费消费多少，漫游话费消费多少等。
本地话费、漫游话费等信息是帐单的费用类型，它在逻辑上是主键之一。
为了形成用户开帐信息，在此要作一次窄表变宽表的操作。
用户开帐信息表（FW_UserInfoB），这就是形成的宽表，Month_ID和UserID作为联合主键，每月每用户一条记录，这个表中的用户集合是U（B）。
开帐信息包括：
• 出帐应收：
在进行帐务优惠后的应收费用，一般就是你的帐单上的费用总和，表示你要缴那么多钱；
• 通话费：
费用类型是通话费的应收费用只和，通话费的费用类型诸如长途费、漫游费等；
• 出帐类型：
表示该用户当月的出帐费用中是否有月租费用，是否有通话费用？
这也是通过费用类型区分的。
• 优惠费用：
优惠通常包括详单级、帐单级优惠，这在ODS月帐单中存放方式不尽相同，有的可能使用字段来表示优惠费用，有的可能使用一种费用类型来表示。
在统一DW模型中，没有将所有的费用类型都进行宽化处理，这需要根据实际需要，看是否有这方面的需求，酌情增加用户开帐信息。
例如有一个分析需求是统计月租费等于0的出帐用户数，那就可以加上一个当月月租费这个字段。
• 3、欠费信息
当用户超过一定期限没有缴费，就产生欠费行为。
在ODS欠费表中，通常存储着每月哪些用户欠了多少钱，并且可以细到每种费用类型。
但一般从分析的角度，分析某种费用类型的欠费没有什么意义，因为营帐的实现要不一欠都欠，要不有一个内部销帐规则，这个细节也不是我们关注的。
用户欠费信息表（FW_UserInfoA），该表的主键为Month_ID和UserID，每月每用户一条记录，这里的用户集合为U（A），U（A）中又可能也包含已经离网的用户。
用户欠费信息主要包括，为了说明方便，假设对于2004年4月1日的统计，一个用户从2003年12月份开始欠费，欠了
80元，2004年1月欠了70元，2月份又欠了110元，那么在ODS欠费表中有12月、1月和2月的欠费记录，3月的不算欠费，因为到4月1日，三月份的帐还没出呢。
• 最小欠费月份
升级会员 