CN102349081A - Etl构建器 - Google Patents

Abstract

一种构建中间数据库的方法，其检查中间数据库元数据并构建操作的一览表以利用规范化、变换块和过滤来提取数据、对数据进行变换并将其加载到中间数据库中，以生成可以利用标准查询语言看到的数据的商务视图。在构建OLAP立方体以用于商务智能应用中时，将这应用于诸如Navision的产品。

Description

ETL构建器

本发明涉及用于在BI(商务智能)系统中使用的数据仓库的创建。

背景技术

通常对用于CRM和ERP的关系数据库进行定制，以适合特定行业的业务需要。尽管一些计算机公司提供了能够与这些数据库一起使用的立方体，但是他们并没有考虑所发生的定制。为了使BI系统执行其分析，需要一个繁冗的、专业人士推动的使数据库与分析立方体同步的过程。这一过程的成本成为了购买和实施BI系统的障碍，只有大型企业才能证明所涉及的成本的合理性。

在准备用于BI的ERP系统的过程中，通常的步骤是制定业务要求、提供数据要求的来源、设计、构建、实施以及管理安全性。

这一过程的第一个步骤是从所述组织中的用户得出针对该系统的业务要求。其通常涉及顾问围绕业务处理会见用户，共同地基于每一天确定这些用户完成其工作所需的信息，以及为他们提供提高其决策能力的信息。一旦收集到了业务要求，顾问就将识别出需要什么样的数据以及这一数据当前存在的系统。

所述设计阶段由技术顾问承担，该阶段由下述事项构成。

●提取、变换和加载(ETL)

将数据从每一源系统提取到中间数据库(staging database)内。将这一数据库变换成星形模式结构。必须将每一ETL任务设计成能够有效地完成这一任务。在这一点上还必须设计数据的变换，例如，将复合ERP结构转换成简单的报告结构的转换方法。

●数据仓库设计

必须通过某种方式对仓库进行设计，从而允许对大量的数据快速访问。其还必须具有允许针对所述数据容易地创建报告的结构。

●立方体设计

必须将所述立方体设计成能够支持所有的业务要求。这通常是一个复杂的迭代过程，涉及业务分析和商务智能专家。立方体由度量和维度构成。度量表示怎样对一个项目进行衡量。例如，参照收入和盈余来衡量销售代表。维度将度量分解成业务类别。例如，销售代表是一个维度，客户是一个维度，日期是一个维度。

●报告设计

必须将报告设计成满足业务要求。必须彻底地全面考虑报告参数、小计、标题和格式。

一旦完成了设计阶段，构建阶段就开始了，并且必须创建下述项目。由于其本质上是技术性的，因而通常由商务智能开发者执行这一任务。例如，借助Microsoft的SQL Server，产品专家必须执行下述任务：

实施阶段包括的步骤：

●安装

安装ETL作业、立方体、数据仓库和报告。

●测试

必须对整个过程进行测试，从而确保所述立方体和报告为用户递交了正确的结果。通常通过检查来自源系统的报告，之后参照BI系统对所述报告加以确认来完成这一操作。

●训练

确保用户能够有效地使用所述立方体和报告，以及确保技术支持人员有能力随着时间的推移对所述系统进行维护和定制。

就常规而言，对BI系统的安全要求是在要求阶段的一部分时收集的。其被人工植入到立方体当中，但是一个主要的产生劳动的根源是持续发生的维护和人工同步工作，而所述工作只是为了确保适当的人看到特许信息。

如上所述，为ERP系统构建商务智能解决方案是一个非常复杂的劳动密集型的、专业人士推动的过程。

美国专利申请No.2005/0149583公开了一种合并同一数据库的两个不同版本中的数据的方法，其方式是比较两个数据库的元数据，并采用差分算法识别差异，之后开发出元数据交换策略，以合并两个数据库。

WO 2007/95959公开了一种在不需要了解数据库询问语言的情况下生成数据仓库和OLAP立方体的方法。所述系统采用了星形模式。这一方法需要专业技术为OLAP构建数据仓库，而且对于较小规模的企业来讲往往价格过于高昂。

WO 2007072501公开了一种用于经营绩效平台的系统，其具有数据源、用于从多种格式导出测量信息并将其整合成规范格式的实现层、用于对实现层输出过滤和预处理的巩固层、业务建模层和表示层。

美国申请2006/0271568公开了一种采用数据简化、聚合以及维度和履行过程装配数据仓库的方法。

美国专利申请2005/0033726公开了一种商务智能系统，其采用数据存储运行，并且采用元数据视图模块访问在数据连接、数据基础、业务元素以及业务视图和安全的基础上组织的数据。

WO 2007072501公开了一种用于经营业绩平台的系统，具有：数据源；实现层，用于从多种格式导出测量信息并将其整合成规范格式；统一层，用于过滤和预处理仪表测量层的输出；商务建模层和表示层。

本发明的目的是提供一种准备数据存储的自动方法，然后构造一种操作的一览表(schedule of operation)以提取数据、对数据进行变换并将其加载到中间数据库中，从中间数据库可以生成OLAP立方体。

发明内容

为此目的，本发明提供了一种构建中间数据库的方法，所述中间数据库用于创建用于商务智能应用的OLAP立方体，所述方法检查中间元数据数据库并构造操作的一览表以利用规范化、变换块和过滤来提取数据、变换所述数据并将所述数据加载到中间数据库中，以生成数据的商务视图，可以利用标准查询语言在立方体中看到该视图。

如上所示，为ERP系统构建商务智能方案是一种非常复杂的劳动密集型、由专业人士驱动的过程。本发明提供了一种用于使在基于ERP系统构建BI方案时通常需要的复合ETL过程自动化的方法。特别指出，本发明不需要传统的数据仓库来构建立方体。

本发明的最终输出是在先前描述的同样处于审查阶段的申请2008905207中的过程中用作源数据库的中间数据库。根据共同待审申请的方法准备本发明中使用的中间元数据数据库。

定义

CRM

客户关系管理

立方体

针对数据的快速检索和归并优化的多维数据库

ETL-在数据仓库操作中的提取、变换和加载涉及从外部源提取数据、变换数据以符合OLAP立方体中的需要并将其加载到中间数据库中。

DSV

数据源视图—比原始数据更加自然地映射至其在立方体中的定义的基础系统数据的视图

数据库模式

数据库系统的模式是其通过数据库管理系统(DBMS)支持的形式语言描述的结构。在关系数据库中，模式定义了表格、每一表格中的字段以及字段和表格之间的关系。

ERP

企业资源规划是由多模块应用软件支持的涵盖范围很宽的活动集合的行业术语，其有助于制造商或其他企业管理其业务的重要部分，包括产品规划、零件购置、库存的保持

MDX

多维数据库的主要询问语言是MDX，创建其的目的是对OLAP数据库进行询问，其在OLAP应用领域已经得到了广泛的采纳。

规范化

关系数据库设计领域的规范化，规范化是一种系统的确保数据库结构适于通用询问，并且没有某些不符合要求的可能导致数据完整性的损失的特征的方式，例如，所述特征可以是插入、更新和删除异常。但是，这一形式对于询问而言不是最佳的，这也是OLAP立方体具有不同的结构的原因。

OLAP

在线分析处理系统通过提供对信息的各种可能的视图的快速、交互式访问而使用户获得对数据的透彻了解。

下述定义将介绍反映多维视图的原理，其对于OLAP而言具有基础性意义。

“维度”是对数据分类的结构。通常采用的维度包括客户、产品和时间。典型地，维度与一个或多个层次相关。与度量结合的几个不同的维度能够使终端用户回答业务疑问。例如，按月对数据分类的时间维度有助于回答“我们的小机械一月卖得多还是六月卖得多？”的问题。

“度量”包括能够检查和分析的通常为数值并且以比例尺度衡量的数据。典型地，一个或多个维度对给定的度量进行分类，这一点又被描述为被它们“维度分割”。

“层次”是采用排序级别作为按照母子关系组织维度成员的手段的逻辑结构。典型地，终端用户可以通过在层次的级别上向下钻探或向上钻探而对该层次进行拓展或压缩。

“级别”是层次中的位置。例如，时间维度可以具有按照日、月、季、年的级别表示数据的层次。

“属性”是终端用户能够指定以选择数据的维度的元素的描述性特征。例如，终端用户可以采用颜色属性选择产品。在这种情况下，正在将颜色属性用作“归并轴”。一些属性能够将关键字或关系表示到其他表格当中。“询问”是针对特定数据集的规约，其又被称为询问的结果集。所述规约需要选择、归并、计算或操控数据。如果需要这样的操控，那么它就是询问的内在部分。

“元数据”是本发明涉及的关键概念。元数据实质上是有关数据的数据。元数据是描述数据库(或者是关系的或者是多维的)内的实体的信息。元数据还含有有关这些实体之间的关系的信息以及详细规定信息用户允许查看的内容的安全信息。

关系

数据通常存储在数据库内的多个表格内。一个表格内的记录往往涉及另一个表格内的实体。在存在这种情况时，认为两个表格有关系。例如，在关系数据库中，可以将特定值与每一行存储在一起，该行将其链接至所述基础实体。例如，设想具有客户表格和地址表格的数据库。地址表格具有额外的字段，即客户ID，其将该表格与记录在客户表格中的对应客户链接在一起。

具体实施方式

现在将参考附图描述本发明的优选实施例，其中：

图1是利用本发明ETL构建器的系统简图。

本发明使用了如共同未决申请PCT/AU09/001326那样准备的中间元数据，在此通过引用将其公开并入本文中。

ETL构建器

ETL构建器检查中间数据库元数据并构造操作的一览表(schedule)以提取数据、对其进行变换并将其加载到中间数据库中。

然后可以将这个一览表转换成用于数据库管理系统的适当语言，例如SQL服务器集成服务套件(package)，然后交接加以执行。

去规范化

大多数数据库都具有相对规范化的形式，其为更小的数据库尺寸和更快的更新提供了便利，但是其对于询问和分析而言就不是那么理想了。它们还会导致相对复杂的立方体。

本发明包括去规范化步骤，其简化了所得到的立方体结构并且提高了性能。在共同未决申请AU 2009900509中概述了这个过程。一旦导出了这种新表格结构，本发明就自动产生使这种去规范化生效所需的ETL代码。在此包括了对这种结构改变推导的描述以供参考。

这是通过将两个或更多表格组合在一起实现的，使得每行包含与每条记录相关的所有信息(原来是在整个表格中共享的)。

例子

考虑下面的表格：

用户希望报告销售值、销售成本和盈余，其通常是通过汇总销售线表格上的项目完成的。然而，在这种情况下，用户还想根据销售人员查看相同的值。

通常，在立方体中执行所述操作将意味着必须包括销售标题表格，而实际上只是需要该表格的客户编号和销售人员标号字段而已。

解决方案

解决该问题有3种方式：

1.修改立方体中的询问以包括所有的三个表格

2.将三个表格中的字段合并成单个表格

3.添加项目作为维度和度量组

选项1表示现状，其将导致性能差的复杂立方体。选项3将导致具有参考维度的具有不必要的复杂度的立方体。

最佳的解决方案是选项2，其将得到下述表格，该表格保持了所有信息，又能实现更快、更简单的询问。

变换块

专门应用的源数据常常会有其自身的微小差别以及做事情的方式，使得难以对数据进行展开和分组，以便进行分析。本发明提供了允许你处理专用的源数据以便于综合分析和报告的应用块。受支持的一般操作是

-查找表格

-递归地扩展范围

-过滤

-对基本数学表达式进行解析并将它们转变成MDX

可以配置这些基本能力以支持专门应用，允许用户直接向方案中输入这些报告设计，提供忠实反映其当前系统的无缝报告环境(例如，从Microsoft Dynamics NAV中的账户一览表导出的财务报表设计)。

用于Microsoft Dynamics NAV(Navision)的财务报表设计

Navision将财务报表设计称为账户一览表。

Navision ERP系统使用账户调度模块允许用户生成财务报表或关键性能指标。用户能够从一般分类账或表达式使用包含账户数目范围的行和列生成报告。下面的范例引用了来自Navision系统的这两个表格。

表1-账户一览表

表2-账户图表

表3-表格展开

步骤1-处理账户一览表

向方案中输入账户一览表，以允许用户保持通过生成一览表构建的所有IP并具有Zap提供的高级报告能力。

以下是NAV账户一览表的范例。就Zap方案而言，主要部件为一览表名称、线数、类型、描述和账号。

类型

本范例处理两种帐号类型。

1.账户-这些包含账户或账户范围，账户用于汇总总账总数。使用“..”指定账户范围。

2.表达式-这些包含报告上使用的表达式，该报告用于计算的项目，在我们的范例中，使用+将线加在一起。在生成表达式时也可以使用如下内容：

+加

-减

/除

*乘

|加

..范围

该应用必须对这些表达式进行解析，允许利用立方体报告它们。为了这样做，需要遵循以下步骤。

步骤2-查阅一般总帐

使用一览表中的帐号，应用将查阅NAV之内账户图表上的相关账户。在表2中，利用表1中所示账户一览表中的账户，在查阅它们之后返回账户。

为了汇总来自总账交易(GL交易)表的值，需要使用过账。在表1中返回的账户是总帐户，在总账中没有任何针对它们张贴的值。总计字段包含链接到交易表所需的过账范围。账户11299“总现金和现金等价物”包含11100到11299的范围，将需要针对本范围查阅账户以链接到GL交易表。

步骤3-账户展开

表3所示的记录现在将被插入阶段账户一览表中。如果展开的帐号具有总账户，例如账户11169，还必须要展开它以确保仅插入过账。

步骤4-插入动态NAV维度

每个账户一览表线还可以具有链接到它们的NAV维度，使得对于每个增加的维度必须进行另一水平的展开：

在链接回GL交易表时，所有这些记录的展开结果现在提出了问题，因为新账户一览表线的粒度低于该帐号。为了解决这个问题，在立方体中创建“多对多”维度。

步骤5-插入表达式记录

一旦展开了所有的账户一览表记录和账户，就可以插入表达式。现在必须将Nav表达式解析成MDX。在我们的范例中，将线加在一起“10+20+30+40”。

这是通过查阅相关账户并获得最大和最小账号以生成MDX集合来实现的。线号10将被转变成以下形式：

[Account].[11100]：[Account].[11299]+[Account].[11300]：[Account].[11399]+[Account].[11500]：[Account].[11599]+[Account].[11600]：[Account].[11699]

现在，这样在分析服务立方体中形成常规卷起。

多到多维度

在本范例中使用的本发明具体实施例中，利用Microsoft AnalysisServices的“多到多”功能将新的转换账户一览表链接回GL输入表。

慢变维度

“慢变维度(slowly changing dimension)”问题是报告特有的共有问题。简言之，对于记录属性随时间推移而改变的情况适于这种情况。下面给出范例：

Lisa是ABC公司的客户。她一开始生活在Brisbane，Queensland。因此，客户查找表格中的原始条目具有以下记录：

后来她在2009年1月搬到新南威尔士州的悉尼。ABC公司现在应当如何修改其客户表格以反映这种变化？这就是“慢变维度”问题。

通常有三种方式解决这种问题，将它们进行如下分类：

类型1：新记录替换原始记录。不存在旧记录的痕迹。

类型2：向客户维度表格中增加新记录。因此，实质上将客户作为两个人处理。

类型3：修改原始记录以反映变化。

我们提出的发明通过应用以下策略自动解决了这个问题：

每个记录将默认采用类型1的方法，不过，如果用户选择对他们的立方体执行递增更新，那么将使用类型2的方法。

商务视图

为了允许商务用户迅速且容易地创建其数据的视图以进行分析，本发明引入了“商务视图”的概念。本发明授权商务用户过滤数据并向ETL工作引入计算的字段，而无需任何技术知识。

常常需要基于用户定义的标准从立方体排除数据。将在表格水平应用每个过滤器。用户还可能希望包括从其他列计算的新列。

范例

TIVA公司改变了其方向并中断了其“议价箱”生产线。这种数据在报告中或对于立方体分析无用，需要从立方体中排除。为了这么做，必须要在参考生产线的表格上应用过滤器。

以上屏幕快照例示了本发明如何允许用户简单地指定过滤器条件。在场景后方，本发明将这种表达式转换成工具创建的SSIS套件中的SQL语句。

过滤器支持对任意数量的列进行一整套布尔逻辑运算。类似地，可以使用任何数学表达式或对表格列的集合运算导出计算的字段。

全局过滤器和计算的表达式

可以将使用与商务视图相同的方法生成的计算和过滤器应用于使用了该表达式中所含列的所有表格。

这消除了向很多相关表格引入公共运算的冗长且重复过程。

范例1

“议价箱”生产线可以存在于各种表格上，例如销售报价或存货清单。该功能将允许用户自动将所有过滤器拷贝到其他表格。如果字段名不同，将使用数据样本确定对其施加过滤器的正确的列。

范例2

考虑部门代码的前2个字符标识部门的成本中心的情况。可以生成标识成本中心的导出列，这会自动在存在部门代码的所有表格间共享。

在将大量交易性质的数据包括到立方体中时，超过特定阈值，Wizard生成所谓的关系维度或ROLAP维度，而不是标准的OLAP维度。这实现了更小的立方体、较少处理时间和更大的查询性能。

从上文可以看出，本发明通过产生必要的ETL代码，提供了用于自动实施所产生的OLAP立方体设计的节省时间和成本的方案。

本领域的技术人员将认识到，可以在除所述实施例之外的实施例中实施本发明，而不脱离本发明的核心教导。

Claims (8)

1.一种构建中间数据库的计算机可操作方法，所述中间数据库用于创建用于商务智能应用的OLAP立方体，所述方法使用计算机检查中间数据库元数据并构造操作的一览表以利用变换块提取数据、对所述数据进行变换并将所述数据加载到立方体中，其中所述变换块便于查找表格、递归扩展范围、过滤和将数学表达式解析成MDX。

2.根据权利要求1所述的方法，用于生成ETL套件，所述方法使用计算机再现商务逻辑，所述商务逻辑用于源系统中存储的报告设计。

3.根据权利要求2所述的方法，包括利用已存储于这些设计之内的表达式扩展范围层次并将所述范围层次转换成OLAP结构，从源系统的报告设计系统提取数据。

4.根据权利要求1所述的方法，其中所述用户能够通过创建额外的表格和商务视图向所述立方体增加额外的报告功能。

5.根据权利要求1所述的方法，通过替代改变的记录并删除原始记录或在用户激励下创建新记录，透明解决了慢变维度。

6.根据权利要求4所述的方法，其中通过允许用户插入新表达式或过滤器自动发布所述商务视图中指定的实体。

7.根据权利要求2所述的方法，其中所述源数据库为ERP或CRM数据库。

8.一种用数据结构编码的计算机可读介质，用于检查中间数据库元数据并构造操作的一览表以利用变换块提取数据、对所述数据进行变换并将所述数据加载到立方体中，其中所述变换块便于查找表格、递归扩展范围、过滤和将数学表达式解析成MDX。

Images