[原创][原创]基于Analyzer+SQL2005的商业智能解决方案介绍

3.1、总体方案

总体框架由数据源、数据中心（按需要而定）、数据仓库及分析平台和信息服务四个部分构成，数据源即为数据仓库的基础数据源，其主要是公司的多个基础业务数据。数据中心是把业务数据源通过数据迁移，经过统一标准转化集中存储，为以后经营分析建立数据仓库。

经营分析系统的总体框架图如下：

3.2、应用层次

系统纵向应用层次分为四个层次，分别是数据集成部分，数据服务部分，应用逻辑部分，信息展现部分。数据集成部分主要分为基础数据源层次，数据源为各个部门的业务系统；数据服务部分主要分为数据加工转换层次和数据存储中心层次，数据加工层次主要是各个部门的业务数据与标准数据进行比较转换。应用逻辑部分主要是对各个业务信息系统的业务数据进行整合并按分析模型进行存储,根据需求建立各种分析模型，以及查询、报表应用层次；信息展现部分主要是为各个部门以及公司领导提供信息服务的应用层次。

3.3、系统架构

如下图，通过ETL工具（DTS/SSIS）从数据中心提取数据，根据不同的业务主题存储到数据仓库里面

4.1、SSIS概览

4.1.1.传统资料整合方式

Ø        数据整合与数据仓储建立过程中需要额外的暂存性数据区(Staging)与数据库汇总作业

Ø        数据处理需要不同多种工具(如CRM系统的文字采矿工具) 且工具间有兼容性问题存在

Ø        回报机制与问题层级提升的速度缓慢

Ø        数据过多时往往会造成该策略无法运作

传统数据整合模式图

4.1.2.SSIS整合模式

Ø        使用Integration Services可以在作业过程将需要数据库处理的部分，直接处理完毕

Ø        所有的作业包括文字采矿、汇总、合并、资料比对等都可以整合在同一个服务中

Ø        分离增加数据库与ETL工具的作业范围，强化数据库的高可用度

Ø        直接透过Integration Services进行问题的警示与回报

SSIS数据整合模式

4.1.3.SSIS 功能

A．整合多种企业异质数据来源

Ø        SQL、Oracle、XML、Excel 、 Text…

Ø        使用.NET 、OLEDB 、 ODBC…

B．建置数据仓储与数据超市

Ø        透过Restart机制增强处理速度

Ø        提供Slowly Changing Dimension 强化数据更改功能

Ø        提供汇总函数功能 SUM、AVG、COUNT、PIVOT等

C．整理与标准化数据

Ø        提供多种数据的合并转换、去除重复作业功能

Ø        提供Fuzzy 群组化与寻查的功能，Micro Soft = MSFT = Microsoft

D．整合商业智能作业

Ø        提供多种Container、Task、数据转换功能

Ø        可以提供给报表服务作为数据来源

E．自动管理与整合数据库

Ø        整合数据、对象、账号转换

Ø        整合在Management Studio进行管理Integration Services

Ø        整合SQL Server代理程序作业

4.1.4.SSIS 架构

A.Integration Services service

Ø        独立的服务程序(Windows service)

Ø        整合在Management Studio一起管理

Ø        监控封装的执行状况

Ø        管理封装的储存与移转

Ø        封装开发与封装执行可以不用启动该服务

B.Integration Services Object Model

Ø        提供应用程序API接口整合.NET

提供整合组件进行程序开发

Ø        提供客制化组件开发包括

Task、Data Flow Component…

C.Integration Services runtime +executables

Ø        管理封装执行、记录、交易与连结处理

Ø        封装, Containers, Tasks, Event handlers…

D.Data Flow task

Ø        Data Flow Engine提供在内存处理数据搬移、转换、修改等作业

Ø        项目有数据来源、转换、数据目的三大组件

SSIS架构图

4.2、数据ETL方案

ETL的过程就是数据流动的过程，从不同异构数据源流向统一的目标数据。其间，数据的抽取、清洗、转换和装载形成串行或并行的过程。

ETL的核心还是在于T这个过程，也就是转换，而抽取和装载一般可以作为转换的输入和输出，或者，它们作为一个单独的部件，其复杂度没有转换部件高。和OLTP系统中不同，那里充满这单条记录的insert、update和select等操作，ETL过程一般都是批量操作，例如它的装载多采用批量装载工具，一般都是DBMS系统自身附带的工具，例如Oracle SQLLoader、DB2的autoloader、MS的SSIS等,数据ETL方案如下图所示

图 数据仓库ETL设计方案

本方案选取的是企业级数据整合平台Integration Services（SSIS）工具，它提供了构建企业级ETL应用程序所需的功能和性能。SSIS是可编程的、可嵌入的和可扩展的，这些特性使其成为理想的ETL平台。

开发环境。用于快速开发SSIS包的、非常直观的开发界面被集成在Business Intelligence Development Studio中。在这个Studio中，可以构建与分析服务、报表服务共享的解决方案，包括源控制、元数据整合等。同时，这个Studio还是一个完整的商业智能应用程序的开发环境，在其中，你可以设计、测试、部署和维护端到端的商业智能应用程序。

可视化调试。在Data Viewers中，设计人员能够非常清楚地看到数据管道的工作状况，它通过图表的形式来可视化数据的传输，同时，断点、变量和调用堆栈提供了非常强大的调试功能。除传统ETL的功能之外

Ø          支持非传统的数据（Web Service，XML）：

Ø          SSIS可对不持续的数据进行分析

Ø          在数据流中的数据挖掘和文本挖掘

Ø          数据流中的数据挖掘和分析可用于数据质量和数据清洗

4.2.1.数据抽取

提取（Extraction）就是从源系统中获取数据（无论是何种格式）。这个过程可能很简单，只需要从数据库或者电子表格转储文本文件（flat file）；也可能很复杂，需要建立与外部系统的联系，然后控制数据到目标系统的传输。

A． 数据质量

“不要绝对的数据准确，但要知道为什么不准确。”这是对数据准确性的要求。准确的东西需要一个标准，但首先要保证这个标准对目前企业是准确的，  

导致数据质量问题的原因可以分为下面几类：

Ø        数据格式错误：例如缺失数据、数据值超出范围或是数据格式非法等。要知道对于同样处理大数据量的数据源系统，他们通常会舍弃一些数据库自身的检查机制，例如字段约束等。他们尽可能将数据检查在入库前保证，但是这一点是很难确保的。这类情况诸如身份证号码、手机号、非日期类型的日期字段等。

Ø        数据一致性：数据源系统为了性能的考虑，会在一定程度上舍弃外键约束，这通常会导致数据不一致。例如在帐务表中会出现一个用户表中没有的用户ID，在例如有些代码在代码表中找不到等。

Ø        业务逻辑的合理性：通常，数据源系统的设计并不是非常严谨，例如让用户开户日期晚于用户销户日期都是有可能发生的，一个用户表中存在多个用户ID也是有可能发生的。

B． 数据连接。

整合不同的数据源和数据目标变得非常容易。除了那些常见的数据源，例如文本文件、OLEDB和ADO.NET（包括针对.NET的ODBC），在SSIS中还简化了访问SAP中数据的方式。内置的对XML和Web Services的支持使得与面向服务的架构以及其它非标准数据源的整合变得非常轻松。用于数据装载的SQL Server数据目标经过了优化，甚至SQL Server Mobile数据库也能被直接定位。由于具有整合元数据的能力和可共享的解决方案，报表服务的报表或者分析服务的多维数据集都能通过SSIS管道直接读取。

在SSIS中多源抽取整合案例图

4.2.2.数据清洗

清洗（Data Clean）就是对进入数据仓库的数据清除那些脏数据（dirty data）或噪音，以保证一定数据质量。

A. 脏数据定义

    包括错误的，不一致的及没有用的数据：

Ø        单数据源的结构级脏数据：违反数据模式及完整性约束要求的那些数据

表 单数据源的结构级脏数据

Ø        单数据源的实例级脏数据：在结构上是没有任何错误，但是在数据实例级会有一些错误和矛盾

表 单数据源的实例级脏数据

Ø        多数据源的结构级脏数据：由于各个数据源的结构不一致导致同名异议，异名同义等，表示不一致

Ø        多数据源的实例级脏数据：比如重复数据，矛盾数据等

表 多数据源的实例级脏数据

B．数据保证

针对于数据的质量，Fuzzy Lookup和Fuzzy Grouping组件提供了不精确匹配和消除重复数据的功能。为了保证数据质量，可使用预测模型来避免数据的不完整和丢失，或者使用关联和聚集模型来找出异常的和不规则的数据。如下图为使用了Fuzzy Lookup的数据清洗的例子

C. 脏数据清洗

    一般分为结构级和实例级两种清洗类型，通过对脏数据的改造或清除，保证进入数据仓库的数据是有效的，一致的和清洁的

Ø        结构级清洗规则：

1．统一的数据模式（包括数据类型）定义

2．统一的完整性约束定义

3．统一的安全性约束定义

4．统一的函数依赖要求定义

Ø        实例级清洗规则：可以通过在SSIS中设置数据有效值检验机制与重复值检查机制

1．分析脏数据。

2．定义转换规则

3．评估与验证

4．执行转换。

在SSIS中清洗脏数据例图

4.2.3.数据转换

转换通常不仅仅是数据格式的转换（虽然这是将数据导入系统的关键一步）。外部系统中的数据可能包含不一致或者不正确的信息，这取决于外部系统上实施的检查和平衡。转换步骤的一部分是”净化”或”拒绝”不符合条件的数据。这个阶段常用的技术包括字符检查（拒绝包含字符的数值性数据）和范围检查（拒绝超出可接受范围的数据）。被拒绝的记录通常存放在单独的文件中，然后使用更复杂的工具处理，或者手工改正问题。然后将这些数据合并到已转换集合中。

A.转换规则组成

    1．数据类型转换。将数据源中的不同数据类型转换成数据仓库中的类型

    2．数据表示方法转换。命名代码统一，汉字编码同义，度量衡表示统一以及其他数据表示方法的统一等

    3．命名转换。将数据模式，表名，属性名转换成数据仓库中的统一命名方式

    4．数据综合。按粒度要求对动态属性数据进行统计，汇总形成综合性数据

5．数据筛选。按照分析及决策的需要从数据源中作纵向的属性选择及横向的实例选择。

在SSIS中规则的设置图例

B．高效率转换

在SQL Server Integration Services中包含了许多非常有效的组件，例如数据和字符相互转化、计算列、用于分区和筛选的条件操作符、查找、排序、聚集以及合并。高级组件简化了其它复杂的操作，例如缓慢变化维度的装载等。对于某些自定义的需求，我们可以使用灵活和快速的VisualBasic.NET脚本来实现，同时，开发人员可以毫不费力地编写和分发他们自创的可重用组件

以下组件使用整批处理方式

Ø        汇总处理：例如Sum、Average、Group By

Ø        排序处理：递增、递减并将结果输出

Ø        模糊寻查与去除重复：Fuzzy Lookups and Deduplication

Ø        取样处理：百分比取样、笔数取样

Ø        枢纽功能：Pivot、Unpivot

C．高可靠性：

包可以通过配置来定制其在不同情况下运行的方式，例如针对不同的服务器环境。使用数字签名来保证用于部署的包的安全性；运行一个简单的向导就能完成已部署包的安装；检查点重启和对事务的支持；加上WMI侦听器和灵活的异常处理、事件驱动功能保证无人值守运行的可复原性；与SQL Server Management Studio的集成简化了在SQL Server环境中对包的管理和监视。

C． 数据的输入输出：

大小交，这种处理在数据清洗过程是常见了，例如从数据源到ODS阶段，如果数据仓库采用维度建模，而且维度基本采用代理键的话，必然存在代码到此键值的转换。如果用SQL实现，必然需要将一个大表和一堆小表都Join起来，当然如果使用ETL工具的话，一般都是先将小表读入内存中再处理。这种情况，输出数据的粒度和大表一样。

大大交，大表和大表之间关联也是一个重要的课题，当然其中要有一个主表，在逻辑上，应当是主表Left Join辅表。大表之间的关联存在最大的问题就是性能和稳定性，对于海量数据来说，必须有优化的方法来处理他们的关联，另外，对于大数据的处理无疑会占用太多的系统资源，出错的几率非常大，如何做到有效错误恢复也是个问题。对于这种情况，我们建议还是尽量将大表拆分成适度的稍小一点的表，形成大小交的类型。这类情况的输出数据粒度和主表一样。

站着进来，躺着出去。事务系统中为了提高系统灵活性和扩展性，很多信息放在代码表中维护，所以它的“事实表”就是一种窄表，而在数据仓库中，通常要进行宽化，从行变成列，所以称这种处理情况叫做“站着进来，躺着出去”。大家对Decode肯定不陌生，这是进行宽表化常见的手段之一。窄表变宽表的过程主要体现在对窄表中那个代码字段的操作。这种情况，窄表是输入，宽表是输出，宽表的粒度必定要比窄表粗一些，就粗在那个代码字段上。

  聚集。数据仓库中重要的任务就是沉淀数据，聚集是必不可少的操作，它是粗化数据粒度的过程。聚集本身其实很简单，就是类似SQL中Group by的操作，选取特定字段（维度），对度量字段再使用某种聚集函数。但是对于大数据量情况下，聚集算法的优化仍是探究的一个课题。例如是直接使用SQL的Group by，还是先排序，在处理

4.2.4.数据加载与数据刷新

加载阶段将获取并转换的数据存放到新的数据存储中（数据仓库、数据集市等）。

在SSIS中可以对增量更新数据进行封包设计，按照不同的进度来调用。

对于数据刷新一般都采取增量方式，其方式有：

A．时标法

  大多数据源中需刷新的数据记录均会有时间属性。所谓时标法即是根据该属性判断数据是否需要更新

B．Delta文件

  由应用生成的Delta文件，记录了应用所改变的所有内容。利用Delta文件可以判断要刷新的数据。效率较高，避免了扫描整个数据库

C．日志文件

  利用数据源中数据库的日志文件来判别要更新的数据

4.3、逻辑数据模型

4.3.1. OLAP概述

逻辑数据模型对任何企业元数据来说都是相当重要的。事实上，逻辑数据模型是建立一个企业级元数据管理最终目标的第一步。这一步的实现方式主要是将类似ERWIN中的模型信息纳入到元数据管理中

这里使用归纳推理方法――联机处理分析，简称OLAP

4.3.2. OLAP概念模型

概念模型的设计是整个概念模型开发过程的第三阶段。设计阶段依据概念模型分析以及分析过程中收集的任何数据，完成星型模型和雪花型模型的设计。如果仅依赖ERD，那只能对商品、销售、客户主题设计成如图2所示的概念模型。这种模型适合于传统的数据库设计，但不适合于数据仓库的设计