基于Analyzer+SQL2005的商业智能解决方案介绍
3.1、总体方案
总体框架由数据源、数据中心（按需要而定）、数据仓库及分析平台和信息服务四个部分构成，数据源即为数据仓库的基础数据源，其主要是公司的多个基础业务数据。数据中心是把业务数据源通过数据迁移，经过统一标准转化集中存储，为以后经营分析建立数据仓库。
经营分析系统的总体框架图如下：
.JPG)
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. 脏数据定义
包括错误的，不一致的及没有用的数据：
Ø 单数据源的结构级脏数据：违反数据模式及完整性约束要求的那些数据
|
编号 |
问题 |
脏数据 |
说明 |
|
1 |
非法值域 |
Bdate=30/13/70 |
数据值越界 |
|
2 |
属性依赖关系破坏 |
Age=22，Bdat |
年龄=当前年-出生年 |
|
3 |
唯一性破坏 |
Emp1=（name=J.smith,SSN=135） Emp2=(name=P.Miller.SSN=135) |
不同员工应有不同的SSN号 |
|
4 |
参照完整性破坏 |
EMP=（name=J.Smith,dno=127） |
对应部门号未定义 |
表 单数据源的结构级脏数据
Ø 单数据源的实例级脏数据：在结构上是没有任何错误，但是在数据实例级会有一些错误和矛盾
|
编号 |
问题 |
脏数据 |
说明 |
|
1 |
值缺失 |
Phone=9999.9999 |
空值或不存在 |
|
2 |
值对应错误属性 |
City=“Grmany” |
错误值 |
|
3 |
属性间依赖关系破坏 |
City=‘北京’，Zip=‘ |
两属性间值不对 |
|
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） |
对应部门号有定义但对应错误 |
表 单数据源的实例级脏数据
Ø 多数据源的结构级脏数据：由于各个数据源的结构不一致导致同名异议，异名同义等，表示不一致
Ø 多数据源的实例级脏数据：比如重复数据，矛盾数据等
|
编号 |
问题 |
脏数据 |
说明 |
|
1 |
值域不一致 |
Sex：0/1 Gender：T/M |
异值同义 |
|
2 |
表示不一致 |
Cid：11 Cno：492 |
异值同义 |
|
3 |
表示不一致 |
Cid：24 Cno：24 |
同值异义 |
表 多数据源的实例级脏数据
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所示的概念模型。这种模型适合于传统的数据库设计，但不适合于数据仓库的设计。
A. 星型模型设计
为设计星型模型，需要确定概念模型中的指标实体和维度实体。在表1的用户信息需求表中，可以确定该用户的主题是商品销售的趋势分析。因此，用户的指标实体是销售趋势，该指标实体应位于星型模型的中心。此为，从表1中还可以发现，用户对销售趋势分析中所需要的信息有销售日期、销售地点、销售商品、客户年龄、客户的经济状况和客户的信用状况，这些信息就构成了星型模型的维实体。因此，最终可以获得销售主题的星型模型.
当构成了星型模型后，如果用户希望对相关的维度进行深入的分析，了解销售趋势所产生的更深入的原因，这就需要对星型模型进行修改，使其更深入地反映销售趋势变化的原因。为此，就需要设计一个雪花模型。在星型模型的维度实体增加需要进行深入分析的详细类别实体：商品细节实体和客户细节实体，产生销售主题的雪花模型.
C. 星座模型设计
星座模式是星型模式的直接扩充，为了表示多个事实间的关系，可以通过共享维，将多个星型模式连接在一起构成一种新的模式，称为星座模式
4.3.3 .OLAP逻辑模型
完成概念模型的定义后，还要进一步考察模型中的用户要求和系统环境。分析数据仓库范围内的主要对象，确定系统的主要主题域以及主要主题域之间的关系。分析阶段将详细检查定义阶段所提出的要求，并且研究任何可能提供解决方案的环境。数据仓库的设计者通过对用户的访问，得到用户对数据仓库结构以及数据仓库存在环境的要求，并将分析结果转变成概念模型，提交给被访问者进行确认，以保证设计者对当前环境的正确理解。参见下图。
多维分析模型示例图
纬度建模实例图
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 OLAP)，HOLAP (Hybrid OLAP)等多维度分析架构
Ø 支持星状架构 (Star Schema)、雪花状架构、父子式维度、虚拟维度以及数据挖掘数据维度，同时提供预设接口产生时间维度。
Ø 父子式维度可以透过Skipped Level Column设计不对称与不等深形式之维度，例如会计科目、人事架构。
Ø 提供多维度查询语言MDX (Multi-dimensional Expression)，可以设计复杂之计算逻辑，同时可以呼叫EXCEL以及VBA函数。以设计导出成员(calculated member)、命名集 (name set)、导出资料格 (calculated cell)、自订成员 (custom member)。
Ø 提供自订积存公式 (custom roll-up)，可用来计算不对称非加总形式维度，例如财务报表。
Ø 可针对易变动之维度设定为changing dimension，以减少维度成员增减造成数据汇总储存体变动的问题。同时可以针对大量成员之维度设定为group维度或者是ROLAP维度格式，以降低大量维度成员加载与维护之问题。
Ø 提供Partition功能，可以将大量历史资料根据时间维度切分，然后将个别Partition设定个别的储存模式以及汇总比率。
Ø 可设定汇总百分比，以有效最佳化OLAP Cube处理时间，同时提供使用者记录最佳化功能，可以根据过往使用者存取历史纪录，提供最佳汇总百分比之建议。
Ø 提供钻透 (drill-through)功能，可供使用者调阅组成汇总数据格之明细数据。
Ø 提供回写功能 (Writeback)，可将修改数据另存于数据表中，待分析者确认后删除或者是合并至原始partition。
Ø 可透过虚拟Cube将多个OLAP Cube联集。
Ø 能够透过DTS进行OLAP维度以及Cube处理排程，同时提供命令列进行备份与还原。
Ø 可以透过OLE DB for OLAP、ADOMD以及XML/A存取多维度资料，同时可以透过EXCEL数据透视表直接进行交叉分析。
Ø 可以与OWC (Office Web Componet) 整合，以进行网页应用程序开发。同时可以透过Excel OLAP Report Add-ins设计固定格式以及自由格式Excel OLAP报表。
Ø 提供Action功能，可以直接呼叫网页、应用程序、数据集以及自订函式库。
4.3.6 .OLAP安全控管
由于数据仓储涉及许多重要的营业信息，因此安全性的控管就变的非常重要，在本项目中，我们将针对数据仓储、多维度分析以及静态报表三个层面提出安全控管机制。
透过Windows账号验证模式整合三种层面安全性，可以透过使用者以及使用者群组，赋予各别权限。
数据仓储安全性：数据仓储可根据营运流程规划出以下13种角色，在资料仓储建置完成后，AsiaMiner将会根据过保险业建置经验，提出权限控管计划初稿，明定各类角色之责任范围、拥有者以及签核流程与核准等级。
|
Group Name |
Owner |
使用说明 |
|
1.制式报表 |
信息制作人员/单位主管 基本数据执行人员 信息决策分析执行人员 信息经营及决策人员 |
查询及数据报表使用权限 |
|
2. 多维度报表 |
信息制作人员/单位主管 基本数据执行人员 信息决策分析执行人员 信息经营及决策人员 |
|
|
3.数据仓储数据表数据 |
信息决策分析执行人员 信息经营及决策人员 |
内容数据使用权限 |
|
4.资料下传 |
信息决策分析执行人员 |
交易明细信息使用权限 |
|
5.个人数据保护资料 |
信息决策分析执行人员 信息经营及决策人员 |
保密资料使用权限(姓名、身分证字号、地址、电话) |
|
6.保留区(暂存性数据) |
信息决策分析执行人员 |
DB暂存资料使用权限 |
|
7.操作系统管理 |
系统维护管理者 |
主机操作系统使用权限 1.系统管理 2.批次排程管理 3.系统作业管理 |
|
8.数据库管理(DBA) |
数据库管理员 |
数据库使用权限 1.数据库管理 2.数据库使用权 |
|
9.使用者权限管理(授权OP) |
使用者代号安控员 |
使用权限控管 1.数据仓储使用权限记录表 2.User使用记录表 3.User Profile Listing |
|
10.数据变更管理(账号维护OP) |
数据安控管理员 |
数据变更使用权限 1.数据修改 2.资料复核 |
|
11.信息传输作业(ETL) |
数据传输执行员 |
信息传输作业使用权限 |
|
12.日常操作(备份还原) |
系统操作员 |
数据操作使用权限1.Backup/Restore 2.系统LOG存取 |
|
13.紧急状况作业 |
紧急状况下使用代号 |
紧急状况作业使用权限 |
多维度报表安全性：微软Analysis Services可以透过以下七种安全性进行安全性设定。
Ø 数据库层级安全性(Analysis Services)
Ø Cube层级安全性(Analysis Services)
Ø 数据维度层级安全性(Analysis Services)
Ø 数据格层级安全性(Analysis Services)
Ø 钻研安全性(Analysis Services)
Ø 写入安全性(Analysis Services)
Ø 多维度动态报表安全性(Analyzer 2005)
数据库层级安全性：用以限制使用者能否读取哪些OLAP数据库。因此在设计多维度分析时，可以根据业务别，将Cube存放于不同之数据库中，以便进行控管。
Cube层级安全性：用以限制使用者可以读取哪些OLAP Cube。
数据维度层级安全性：可以限制使用者可以看到哪些维度，甚至可以控管这个使用者可以看到哪些阶层(例如可以看到个别业务单位的业绩达标率，但是不可以看到总体保费收入以及个别客户个人信息)。以及可以限制该使用者可以看到该维度中的哪些成员(例如业务主管只能看到该分组之绩效数字，不能看到其它业务单位)。
数据格层级安全性：这是最精细的权限控管，可以透过微软MDX撰写复杂之数据格安全控管逻辑，例如如果是看保单进件数可以看到各业务单位与个别Agent层级，但是如果要看实收保费则只能看到Agent层级。
钻研安全性：钻研功能是允许使用者调阅组成该汇总值之明细数据，可用来筛选营销名单或产出稽核报表。
写入安全性：微软Analysis Services同时提供回写功能，可供使用者修改Cube资料，我们也可以透过接口附予特定使用者修改资料之权限。
多维度报表安全性：透过Analyzer2005应用程序端控管，可以赋予使用者权限读取特定报表以及数据夹，至于报表内容仍然受到前端六种Analysis Services 安全性控管。
