基于ANALYZER+SQL2005的商业智能解决方案

1、基于analyzer+sql2005的商业智能解决方案三、方案描述3.1、总体方案总体框架由数据源、数据中心（按需要而定）、数据仓库及分析平台和信息服务四个部分构成，数据源即为数据仓库的基础数据源，其主要是公司的多个基础业务数据。数据中心是把业务数据源通过数据迁移，经过统一标准转化集中存储，为以后经营分析建立数据仓库。经营分析系统的总体框架图如下：3.2、应用层次系统纵向应用层次分为四个层次，分别是数据集成部分，数据服务部分，应用逻辑部分，信息展现部分。数据集成部分主要分为基础数据源层次，数据源为各个部门的业务系统；数据服务部分主要分为数据加工转换层次和数据存储中心层次，数据加工层次主要是各个

2、部门的业务数据与标准数据进行比较转换。应用逻辑部分主要是对各个业务信息系统的业务数据进行整合并按分析模型进行存储,根据需求建立各种分析模型，以及查询、报表应用层次；信息展现部分主要是为各个部门以及公司领导提供信息服务的应用层次。3.3、系统架构如下图，通过etl工具（dts/ssis）从数据中心提取数据，根据不同的业务主题存储到数据仓库里面四、数据仓库设计4.1、ssis概览4.1.1.传统资料整合方式 数据整合与数据仓储建立过程中需要额外的暂存性数据区(staging)与数据库汇总作业 数据处理需要不同多种工具(如crm系统的文字采矿工具) 且工具间有兼容性问题存在 回报机制与问题层级提升的

3、速度缓慢 数据过多时往往会造成该策略无法运作传统数据整合模式图4.1.2.ssis整合模式 使用integration services可以在作业过程将需要数据库处理的部分，直接处理完毕 所有的作业包括文字采矿、汇总、合并、资料比对等都可以整合在同一个服务中 分离增加数据库与etl工具的作业范围，强化数据库的高可用度 直接透过integration services进行问题的警示与回报ssis数据整合模式4.1.3.ssis 功能a整合多种企业异质数据来源 sql、oracle、xml、excel 、 text 使用.net 、oledb 、 odbcb建置数据仓储与数据超市 透过restar

4、t机制增强处理速度 提供slowly changing dimension 强化数据更改功能 提供汇总函数功能 sum、avg、count、pivot等c整理与标准化数据 提供多种数据的合并转换、去除重复作业功能 提供fuzzy 群组化与寻查的功能，micro soft = msft = microsoftd整合商业智能作业 提供多种container、task、数据转换功能 可以提供给报表服务作为数据来源e自动管理与整合数据库 整合数据、对象、账号转换 整合在management studio进行管理integration services 整合sql server代理程序作业4.1.4.s

5、sis 架构a.integration services service 独立的服务程序(windows service) 整合在management studio一起管理 监控封装的执行状况 管理封装的储存与移转 封装开发与封装执行可以不用启动该服务b.integration services object model 提供应用程序api接口整合.net提供整合组件进行程序开发 提供客制化组件开发包括task、data flow componentc.integration services runtime +executables 管理封装执行、记录、交易与连结处理 封装, contain

6、ers, tasks, event handlers d.data flow task data flow engine提供在内存处理数据搬移、转换、修改等作业 项目有数据来源、转换、数据目的三大组件ssis架构图4.2、数据etl方案etl的过程就是数据流动的过程，从不同异构数据源流向统一的目标数据。其间，数据的抽取、清洗、转换和装载形成串行或并行的过程。etl的核心还是在于t这个过程，也就是转换，而抽取和装载一般可以作为转换的输入和输出，或者，它们作为一个单独的部件，其复杂度没有转换部件高。和oltp系统中不同，那里充满这单条记录的insert、update和select等操作，etl过程

7、一般都是批量操作，例如它的装载多采用批量装载工具，一般都是dbms系统自身附带的工具，例如oracle sqlloader、db2的autoloader、ms的ssis等,数据etl方案如下图所示图 数据仓库etl设计方案本方案选取的是企业级数据整合平台integration services（ssis）工具，它提供了构建企业级etl应用程序所需的功能和性能。ssis是可编程的、可嵌入的和可扩展的，这些特性使其成为理想的etl平台。开发环境。用于快速开发ssis包的、非常直观的开发界面被集成在business intelligence development studio中。在这个studio

8、中，可以构建与分析服务、报表服务共享的解决方案，包括源控制、元数据整合等。同时，这个studio还是一个完整的商业智能应用程序的开发环境，在其中，你可以设计、测试、部署和维护端到端的商业智能应用程序。 可视化调试。在data viewers中，设计人员能够非常清楚地看到数据管道的工作状况，它通过图表的形式来可视化数据的传输，同时，断点、变量和调用堆栈提供了非常强大的调试功能。除传统etl的功能之外 支持非传统的数据（web service，xml）： ssis可对不持续的数据进行分析 在数据流中的数据挖掘和文本挖掘 数据流中的数据挖掘和分析可用于数据质量和数据清洗4.2.1.数据抽取提取（ex

9、traction）就是从源系统中获取数据（无论是何种格式）。这个过程可能很简单，只需要从数据库或者电子表格转储文本文件（flat file）；也可能很复杂，需要建立与外部系统的联系，然后控制数据到目标系统的传输。a 数据质量 “不要绝对的数据准确，但要知道为什么不准确。”这是对数据准确性的要求。准确的东西需要一个标准，但首先要保证这个标准对目前企业是准确的， 导致数据质量问题的原因可以分为下面几类： 数据格式错误：例如缺失数据、数据值超出范围或是数据格式非法等。要知道对于同样处理大数据量的数据源系统，他们通常会舍弃一些数据库自身的检查机制，例如字段约束等。他们尽可能将数据检查在入库前保证，但是

10、这一点是很难确保的。这类情况诸如身份证号码、手机号、非日期类型的日期字段等。 数据一致性：数据源系统为了性能的考虑，会在一定程度上舍弃外键约束，这通常会导致数据不一致。例如在帐务表中会出现一个用户表中没有的用户id，在例如有些代码在代码表中找不到等。 业务逻辑的合理性：通常，数据源系统的设计并不是非常严谨，例如让用户开户日期晚于用户销户日期都是有可能发生的，一个用户表中存在多个用户id也是有可能发生的。b 数据连接。整合不同的数据源和数据目标变得非常容易。除了那些常见的数据源，例如文本文件、oledb和ado.net（包括针对.net的odbc），在ssis中还简化了访问sap target=

11、_blank class=link_tagsap中数据的方式。内置的对xml和web services的支持使得与面向服务的架构以及其它非标准数据源的整合变得非常轻松。用于数据装载的sql server数据目标经过了优化，甚至sql server mobile数据库也能被直接定位。由于具有整合元数据的能力和可共享的解决方案，报表服务的报表或者分析服务的多维数据集都能通过ssis管道直接读取。在ssis中多源抽取整合案例图4.2.2.数据清洗 清洗（data clean）就是对进入数据仓库的数据清除那些脏数据（dirty data）或噪音，以保证一定数据质量。a. 脏数据定义包括错误的，不一致的

12、及没有用的数据： 单数据源的结构级脏数据：违反数据模式及完整性约束要求的那些数据编号问题脏数据说明1非法值域bdate=30/13/70数据值越界2属性依赖关系破坏age=22，bdat10/12/60年龄=当前年-出生年3唯一性破坏emp1=（name=j.smith,ssn=135）emp2=(name=p.miller.ssn=135)不同员工应有不同的ssn号4参照完整性破坏emp=（name=j.smith,dno=127）对应部门号未定义表 单数据源的结构级脏数据 单数据源的实例级脏数据：在结构上是没有任何错误，但是在数据实例级会有一些错误和矛盾编号问题脏数据说明1值缺失phone

13、=9999.9999空值或不存在2值对应错误属性city=“grmany”错误值3属性间依赖关系破坏city=北京，zip=821002两属性间值不对4重复记录emp1=（name=j.simth,）emp1=（name=j.simth,）同一记录两次录入5矛盾记录emp1=（name=m.smith,ssn=135）emp2=(name=j.smith,ssn=135)同一记录有不同值6参照错误emp=（name=j.smith,dno=17）对应部门号有定义但对应错误表 单数据源的实例级脏数据 多数据源的结构级脏数据：由于各个数据源的结构不一致导致同名异议，异名同义等，表示不一致 多数据源

14、的实例级脏数据：比如重复数据，矛盾数据等编号问题脏数据说明1值域不一致sex：0/1gender：t/m异值同义2表示不一致cid：11cno：492异值同义3表示不一致cid：24cno：24同值异义表 多数据源的实例级脏数据b数据保证 针对于数据的质量，fuzzy lookup和fuzzy grouping组件提供了不精确匹配和消除重复数据的功能。为了保证数据质量，可使用预测模型来避免数据的不完整和丢失，或者使用关联和聚集模型来找出异常的和不规则的数据。如下图为使用了fuzzy lookup的数据清洗的例子c. 脏数据清洗一般分为结构级和实例级两种清洗类型，通过对脏数据的改造或清除，保证进

15、入数据仓库的数据是有效的，一致的和清洁的 结构级清洗规则：1统一的数据模式（包括数据类型）定义2统一的完整性约束定义3统一的安全性约束定义4统一的函数依赖要求定义 实例级清洗规则：可以通过在ssis中设置数据有效值检验机制与重复值检查机制1分析脏数据。2定义转换规则3评估与验证4执行转换。在ssis中清洗脏数据例图4.2.3.数据转换 转换通常不仅仅是数据格式的转换（虽然这是将数据导入系统的关键一步）。外部系统中的数据可能包含不一致或者不正确的信息，这取决于外部系统上实施的检查和平衡。转换步骤的一部分是”净化”或”拒绝”不符合条件的数据。这个阶段常用的技术包括字符检查（拒绝包含字符的数值性数据

16、）和范围检查（拒绝超出可接受范围的数据）。被拒绝的记录通常存放在单独的文件中，然后使用更复杂的工具处理，或者手工改正问题。然后将这些数据合并到已转换集合中。a.转换规则组成1数据类型转换。将数据源中的不同数据类型转换成数据仓库中的类型2数据表示方法转换。命名代码统一，汉字编码同义，度量衡表示统一以及其他数据表示方法的统一等3命名转换。将数据模式，表名，属性名转换成数据仓库中的统一命名方式4数据综合。按粒度要求对动态属性数据进行统计，汇总形成综合性数据5数据筛选。按照分析及决策的需要从数据源中作纵向的属性选择及横向的实例选择。在ssis中规则的设置图例b高效率转换在sql server inte

17、gration services中包含了许多非常有效的组件，例如数据和字符相互转化、计算列、用于分区和筛选的条件操作符、查找、排序、聚集以及合并。高级组件简化了其它复杂的操作，例如缓慢变化维度的装载等。对于某些自定义的需求，我们可以使用灵活和快速的visualbasic.net脚本来实现，同时，开发人员可以毫不费力地编写和分发他们自创的可重用组件以下组件使用整批处理方式 汇总处理：例如sum、average、group by 排序处理：递增、递减并将结果输出 模糊寻查与去除重复：fuzzy lookups and deduplication 取样处理：百分比取样、笔数取样 枢纽功能：pivot

18、、unpivotc高可靠性：包可以通过配置来定制其在不同情况下运行的方式，例如针对不同的服务器环境。使用数字签名来保证用于部署的包的安全性；运行一个简单的向导就能完成已部署包的安装；检查点重启和对事务的支持；加上wmi侦听器和灵活的异常处理、事件驱动功能保证无人值守运行的可复原性；与sql server management studio的集成简化了在sql server环境中对包的管理和监视。c 数据的输入输出：大小交，这种处理在数据清洗过程是常见了，例如从数据源到ods阶段，如果数据仓库采用维度建模，而且维度基本采用代理键的话，必然存在代码到此键值的转换。如果用sql实现，必然需要将一个大

19、表和一堆小表都join起来，当然如果使用etl工具的话，一般都是先将小表读入内存中再处理。这种情况，输出数据的粒度和大表一样。大大交，大表和大表之间关联也是一个重要的课题，当然其中要有一个主表，在逻辑上，应当是主表left join辅表。大表之间的关联存在最大的问题就是性能和稳定性，对于海量数据来说，必须有优化的方法来处理他们的关联，另外，对于大数据的处理无疑会占用太多的系统资源，出错的几率非常大，如何做到有效错误恢复也是个问题。对于这种情况，我们建议还是尽量将大表拆分成适度的稍小一点的表，形成大小交的类型。这类情况的输出数据粒度和主表一样。站着进来，躺着出去。事务系统中为了提高系统灵活性和扩

20、展性，很多信息放在代码表中维护，所以它的“事实表”就是一种窄表，而在数据仓库中，通常要进行宽化，从行变成列，所以称这种处理情况叫做“站着进来，躺着出去”。大家对decode肯定不陌生，这是进行宽表化常见的手段之一。窄表变宽表的过程主要体现在对窄表中那个代码字段的操作。这种情况，窄表是输入，宽表是输出，宽表的粒度必定要比窄表粗一些，就粗在那个代码字段上。u 聚集。数据仓库中重要的任务就是沉淀数据，聚集是必不可少的操作，它是粗化数据粒度的过程。聚集本身其实很简单，就是类似sql中group by的操作，选取特定字段（维度），对度量字段再使用某种聚集函数。但是对于大数据量情况下，聚集算法的优化仍是探

21、究的一个课题。例如是直接使用sql的group by，还是先排序，在处理4.2.4.数据加载与数据刷新加载阶段将获取并转换的数据存放到新的数据存储中（数据仓库、数据集市等）。在ssis中可以对增量更新数据进行封包设计，按照不同的进度来调用。对于数据刷新一般都采取增量方式，其方式有：a时标法大多数据源中需刷新的数据记录均会有时间属性。所谓时标法即是根据该属性判断数据是否需要更新bdelta文件由应用生成的delta文件，记录了应用所改变的所有内容。利用delta文件可以判断要刷新的数据。效率较高，避免了扫描整个数据库c日志文件利用数据源中数据库的日志文件来判别要更新的数据4.3、逻辑数据模型4.

22、3.1. olap概述逻辑数据模型对任何企业元数据来说都是相当重要的。事实上，逻辑数据模型是建立一个企业级元数据管理最终目标的第一步。这一步的实现方式主要是将类似erwin中的模型信息纳入到元数据管理中这里使用归纳推理方法联机处理分析，简称olap4.3.2. olap概念模型概念模型的设计是整个概念模型开发过程的第三阶段。设计阶段依据概念模型分析以及分析过程中收集的任何数据，完成星型模型和雪花型模型的设计。如果仅依赖erd，那只能对商品、销售、客户主题设计成如图2所示的概念模型。这种模型适合于传统的数据库设计，但不适合于数据仓库的设计。a. 星型模型设计为设计星型模型，需要确定概念模型中的指

23、标实体和维度实体。在表1的用户信息需求表中，可以确定该用户的主题是商品销售的趋势分析。因此，用户的指标实体是销售趋势，该指标实体应位于星型模型的中心。此为，从表1中还可以发现，用户对销售趋势分析中所需要的信息有销售日期、销售地点、销售商品、客户年龄、客户的经济状况和客户的信用状况，这些信息就构成了星型模型的维实体。因此，最终可以获得销售主题的星型模型.当构成了星型模型后，如果用户希望对相关的维度进行深入的分析，了解销售趋势所产生的更深入的原因，这就需要对星型模型进行修改，使其更深入地反映销售趋势变化的原因。为此，就需要设计一个雪花模型。在星型模型的维度实体增加需要进行深入分析的详细类别实体：商

24、品细节实体和客户细节实体，产生销售主题的雪花模型.c. 星座模型设计星座模式是星型模式的直接扩充，为了表示多个事实间的关系，可以通过共享维，将多个星型模式连接在一起构成一种新的模式，称为星座模式4.3.3.olap逻辑模型完成概念模型的定义后，还要进一步考察模型中的用户要求和系统环境。分析数据仓库范围内的主要对象，确定系统的主要主题域以及主要主题域之间的关系。分析阶段将详细检查定义阶段所提出的要求，并且研究任何可能提供解决方案的环境。数据仓库的设计者通过对用户的访问，得到用户对数据仓库结构以及数据仓库存在环境的要求，并将分析结果转变成概念模型，提交给被访问者进行确认，以保证设计者对当前环境的正

25、确理解。参见下图。多维分析模型示例图纬度建模实例图4.3.4.olap物理模型一般olap存储分为molap/rolap/holap三种类型，molap是在多位数据库上实现，rolap是在关系数据库上实现，holap是综合以上两种类型的概念模型4.3.5.olap功能实现相较于固定式报表，olap可以透过预先建置的维度与量值，以及事先汇总的统计值，让使用者透过简易的接口拖拉即可产生实时分析数据，以找出营运问题之所在。采用微软analysis services作为olap引擎工具，其主要功能如下：支持molap (multi-dimentional olap)，rolap (relational

26、 olap)，holap (hybrid olap)等多维度分析架构 支持星状架构 (star schema)、雪花状架构、父子式维度、虚拟维度以及数据挖掘数据维度，同时提供预设接口产生时间维度。 父子式维度可以透过skipped level column设计不对称与不等深形式之维度，例如会计科目、人事架构。 提供多维度查询语言mdx (multi-dimensional expression)，可以设计复杂之计算逻辑，同时可以呼叫excel以及vba函数。以设计导出成员(calculated member)、命名集 (name set)、导出资料格 (calculated cell)、自订成

27、员 (custom member)。 提供自订积存公式 (custom roll-up)，可用来计算不对称非加总形式维度，例如财务报表。 可针对易变动之维度设定为changing dimension，以减少维度成员增减造成数据汇总储存体变动的问题。同时可以针对大量成员之维度设定为group维度或者是rolap维度格式，以降低大量维度成员加载与维护之问题。 提供partition功能，可以将大量历史资料根据时间维度切分，然后将个别partition设定个别的储存模式以及汇总比率。 可设定汇总百分比，以有效最佳化olap cube处理时间，同时提供使用者记录最佳化功能，可以根据过往使用者存取历史纪

28、录，提供最佳汇总百分比之建议。 提供钻透 (drill-through)功能，可供使用者调阅组成汇总数据格之明细数据。 提供回写功能 (writeback)，可将修改数据另存于数据表中，待分析者确认后删除或者是合并至原始partition。 可透过虚拟cube将多个olap cube联集。 能够透过dts进行olap维度以及cube处理排程，同时提供命令列进行备份与还原。 可以透过ole db for olap、adomd以及xml/a存取多维度资料，同时可以透过excel数据透视表直接进行交叉分析。 可以与owc (office web componet) 整合，以进行网页应用程序开发。同时

29、可以透过excel olap report add-ins设计固定格式以及自由格式excel olap报表。 提供action功能，可以直接呼叫网页、应用程序、数据集以及自订函式库。4.3.6.olap安全控管由于数据仓储涉及许多重要的营业信息，因此安全性的控管就变的非常重要，在本项目中，我们将针对数据仓储、多维度分析以及静态报表三个层面提出安全控管机制。透过windows账号验证模式整合三种层面安全性，可以透过使用者以及使用者群组，赋予各别权限。数据仓储安全性：数据仓储可根据营运流程规划出以下13种角色，在资料仓储建置完成后，asiaminer将会根据过保险业建置经验，提出权限控管计划初稿，

30、明定各类角色之责任范围、拥有者以及签核流程与核准等级。group nameowner使用说明1.制式报表信息制作人员/单位主管基本数据执行人员信息决策分析执行人员信息经营及决策人员查询及数据报表使用权限2. 多维度报表信息制作人员/单位主管基本数据执行人员信息决策分析执行人员信息经营及决策人员3.数据仓储数据表数据信息决策分析执行人员信息经营及决策人员内容数据使用权限4.资料下传信息决策分析执行人员交易明细信息使用权限5.个人数据保护资料信息决策分析执行人员信息经营及决策人员保密资料使用权限(姓名、身分证字号、地址、电话)6.保留区(暂存性数据)信息决策分析执行人员db暂存资料使用权限7.操作系统管理系统维护管理者主机操作系统使用权限1.系统管理2.批次排程管理3.系统作业管理8.数据库管理(dba)数据库管理员数据库使用权限1.数据库管理2.数据库使用权9.使用者权限管理(授权op)使用者代