商业智能：为BI解决措施构建数据基础

　数据在任何商业智能 (BI) 解决方案中都是最重要的部分。正如 Stacia Misner 在“规划您的首个 Microsoft BI 解决方案”中所说，收集和维护 BI 解决方案数据涉及几个步骤。BI 专业人员称这些步骤为提取、转换和加载 (ETL) 过程。即使您不准备将 BI 作为自己的工作重心，您仍可以利用 ETL 方法和工具来管理日常工作中进行决策所需的数据。在本文中，我将向您介绍如何设计和构建一个简单的数据市场，从而演示如何使用 SQL Server 2008 Integration Services (SSIS) 对您自己的 BI 解决方案执行 ETL。

 

　　了解要求

 

　　和所有 IT 项目一样，开始 ETL 项目的最佳做法是了解您希望构建的 BI 解决方案的总体要求，然后再决定怎样利用数据才能最好地满足这些要求。第一篇系列文章提供了 BI 解决方案的案例背景。在案例中需要 BI 解决方案的是虚拟公司 Adventure Works。文章通过叙述该公司要回答的几个问题列出了分析要求。从这些问题中可以明显看出 Adventure Works 需要从以下不同角度了解其产品销售：不同分销渠道（经销商或 Internet）的收益率、产品需求随时间推移发生的变化以及按产品、销售人员、地理区域和经销商类型统计的实际销售额与预测销售额之间的差异。回答这些问题有助于 Adventure Works 决定将重点放在哪个分销渠道才能增加利润、如何调整生产流程才能最好地满足需求、如何改变销售战略才能帮助该公司实现销售目标。将 SQL Server Reporting Services (SSRS) 添加到 BI 解决方案后，您就能看出数据是如何帮助回答这些商业问题的。

 

　　在按照这些要求开始为 Adventure Works 设计数据市场之前，我希望从商业角度建立信息需求模型。换言之，设计数据市场的依据是用户如何提问，而不是从数据源获取数据的方式。

 

　　本文中的代码示例需要 SQL Server 2008 Adventure Works OLTP 示例数据库。

 

　　使用维度模型

 

　　构建数据市场通常要使用维度模型设计。维度模型设计是非常适合用于分析的数据库架构。（kimballgroup.com 是了解维度模型的一个非常好的资源。）维度模型以用户熟悉的方式呈现数据，而且能够帮助您构建适合查询大量数据的优化数据结构。您可以通过对数据进行去规范化实现这种优化。去规范化能够使数据库引擎在查询时快速选择并高效聚合大量数据。我将在 Adventure Works 解决方案的去规范化架构中设定两种类型的表：维度表和事实表。维度表存储经销商或产品等有关业务实体和对象的信息。事实表用来存储需要聚合的销售额数值，表中包含将事实表与维度表关联起来的度量值和键。稍后我将在下文中详细介绍事实表。

 

　　可以采用两种架构实现维度模型表：星型架构和雪花型架构。简单说来，星型架构中每种维度使用一个表，因此每个查询与事实表之间都是单一联接的。雪花型架构中每种维度使用两个或多个表，因此查询中需要更多联接才能查看所有数据。这种级联联接的集合意味着雪花型架构的查询速度通常要比星型架构的查询速度慢。出于本文的需要，为了简化设计，我将使用星型架构。

 

　　创建总线矩阵

 

　　Adventure Works 的 BI 解决方案中的重点是与销售相关的维度。为了确定与销售相关的维度，我要创建一个总线矩阵，这是维度建模过程中的一个步骤。Adventure Works 有两种销售渠道：向经销商批发和通过 Internet 零售。我还使用总线矩阵来确定每个维度与以上两种类型的销售渠道或其中一种的关系。图 1 所示为我的 Adventure Works 销售总线矩阵示例。

 

Adventure

Works 销售 日期 产品 客户 促销 地域 经销

商 销售

区域 员工 货币

Internet 销售 X X X X X X

经销商销售 X X X X X X X

　　图 1 Adventure Works 销售总线矩阵

 

　　下一步是确定解决方案的度量值。度量值是进行分析所需的数值。这些数值可以直接取自销售额或产品成本等数据源，也可以经过计算得出，如将某一数量乘以一定的金额得到扩展销售额。此外，还需要确定在每个维度中应包含哪些属性。属性是维度中的单个元素（对应于表中的列），如销售区域维度中的国家/地区或日期维度中的年份。您可以根据分析需要使用属性对数据进行分组或筛选。本文不会对所有标识的度量值或维度属性进行详细介绍，但您需要注意有必要执行标识过程。

 

　　创建数据映射

 

　　在创建数据市场的物理表之前，我需要进行一些其他规划。具体而言，我需要构建一个数据映射文档，以便将数据市场架构中的每个目标列映射到 Adventure Works OLTP 源系统中的列。源系统就是 AdventureWorks2008 数据库，您可以按照 Stacia Misner 文章第 31 页的内容下载和安装该数据库。您可以使用各种应用程序创建数据映射。与格式相比，更重要的是内容。我习惯在 Microsoft Office Excel 中开发数据映射。图 2 所示为我在数据映射中创建的 DimProduct 选项卡。此外，我还创建了 DimCustomer 和 FactInternetSales 数据映射。工作簿中的每个工作表表示数据市场中的一个表。每个工作表中只有两列：一个源列和一个目标列。

 

      

 

　　图 2 DimProduct 数据映射选项卡

 

　　每个维度表（日期维度表除外）中都包含名为代理键的主键（通常为标识列）。使用代理键的好处之一是在合并多个系统的数据时不会出现重复键。

 

　　维度表还有一个可选键列。这些可选键表示自然键，有时也称为业务键。自然键用于标识源系统。例如，客户维度中的 CustomerAlternateKey 列映射到 Adventure Works OLTP 数据库中 Sales.Customer 表的 AccountNumber 字段。通过将这些键存储在维度表中，每次对各个维度运行 ETL 过程时，我可以将维度中已有的记录与从数据源中提取的记录进行匹配。

 

　　几乎每个数据市场都包含日期维度，因为业务分析往往按照日期、星期、月份、季度或年份来比较度量值变化。由于日期维度很少从源系统中获取，因此不应使用基于 SQL Server 标识的键。为此，我将改用以 YYYYMMDD 格式存储为 SQL Server 整数列的智能键。智能键是根据逻辑或脚本生成的键，而不是像 SQL Server 中的标识列那样自动递增的键。

 

　　请注意，日期维度通常并不映射到源表。因此，我将使用脚本生成数据，以便将记录加载到表中。

 

　　由于我的小型架构所需的 ETL 过程相当简单，因此这样的数据映射足以满足需要。在实际项目中，我会为数据映射添加注释，指出何时需要进行复杂的转换。

 

　　构建数据市场

 

　　逻辑建模完成之后，现在我需要创建 ETL 过程要加载的物理表及这些表的承载数据库。我将使用基本的 T-SQL 脚本来创建数据库及其关联的维度表和事实表。在（2009 代码下载）的示例 BI 解决方案的相应下载中可以找到完整的 T-SQL 脚本。

 

　　出于本文的需要，我仅构建了整个销售数据市场架构的一个子集，以便在 SSIS 中涵盖整个 ETL 过程。在精简版架构中，我仅加入了 Internet 销售事实表中的 OrderQuantity 和 SalesAmount 两个度量值。此外，在此精简版架构中，我还加入了简化的客户、产品以及日期维度表。

 

　　开发 ETL 过程

 

　　构建 BI 解决方案的下一步是设计和开发 ETL 过程。我们先回顾一下，ETL 包含从数据源提取数据、转换数据然后再将数据加载到目标库的整个技术过程。一般来说，BI 解决方案中的 ETL 过程先从平面文件以及 OLTP 操作数据库中提取数据，然后转换数据使其适应维度模型（例如，星型架构），最后再将结果数据加载到数据市场。

 

　　在 BIDS 中创建 SSIS 项目

 

　　开发 ETL 过程的第一步是在 Business Intelligence Development Studio (BIDS) 中创建新项目。SQL Server 2008 附带了 BIDS，在安装过程中选择“工作站组件”选项即可安装 BIDS。BIDS 中提供适用于 SSIS、SSAS 和 SSRS 的项目模板。与 Visual Studio 一样，BIDS 也支持源代码控制集成。

 

　　要启动 BIDS，请转到“开始”“程序”“Microsoft SQL Server 2008”“Business Intelligence Development Studio”并选择“文件”“新建项目”。您会看到如图 3 所示的“新建项目”模板。

 

      

 

　　图 3 BIDS 2008 中的“新建项目”模板

 

　　在“模板”窗格中选择“Integration Services 项目”。在“名称”文本框中，键入“ssis_TECHNET_AW2008”，然后单击“OK”。BIDS 应显示一个打开的 SSIS 项目。

 

　　创建公共数据连接

 

　　SSIS 2008 中的另一项出色功能是可以在单个数据包之外创建数据源连接。您可以定义数据源连接一次，然后在解决方案中的一个或多个 SSIS 数据包中引用此连接。有关如何创建 BIDS 数据源的更多信息，请参阅“如何：使用数据源向导定义数据源（分析服务）”。

 

　　创建两个新的数据源连接：一个用于 TECHNET_AW2008SalesDataMart 数据库，另一个用于 AdventureWorks2008 OLTP 数据库。将这两个数据源连接分别命名为 AW_DM.ds 和 AW_OLTP.ds。

 

　　开发维度 ETL

 

　　用来加载产品维度的 ETL 非常简单。我需要从 Adventure Works Production.Product 表提取数据，并将这些数据加载到 TECHNET_AW2008SalesDataMart 数据库中。首先，我要重命名 BIDS 为我的 SSIS 项目创建的默认数据包。（数据包就是一个容器，存储 SSIS 将执行的工作流中的所有步骤。）在解决方案资源管理器中右键单击默认数据包，选择“重命名”。键入“DIM_PRODUCT.dtsx”，然后按 Enter。

 

　　接下来，我需要使用预建的数据源来创建本地数据包连接管理器。新建两个引用先前生成的数据源的连接管理器。

 

　　定义数据流以提取并加载

 

　　SSIS 中的一个数据流任务封装了对于简单维度实施 ETL 所需的全部数据。我只需将一个数据流任务从工具箱拖动到控制流设计器图面并将其重命名为 EL（用于提取和加载）。右键单击设计器中的数据流任务，然后选择“编辑”。BIDS 现在显示数据流设计器。

 

　　产品维度数据包的提取部分需要查询 AdventureWorks2008 Production.Product 表。为设置此任务，我从工具箱中将一个 OLE DB 源组件拖动到数据流设计器图面上，然后将该 OLE DB 源组件重命名为“AW_OLTP”。

 

　　接下来，我定义数据包的加载部分，以便加载到数据市场。我只将 OLE DB 目标组件的新实例拖动到数据流设计器图面，并将其重命名为“AW_OLTP”。然后，我单击 OLE DB 源 (AW_OLTP) 组件并将 OLE DB 源上显示的绿色箭头拖动到 AW_DM OLE DB 目标组件以连接这两个组件。

 

　　此时，我已将必需的组件添加到数据流中，但我仍需要配置每个组件，以便 SSIS 知道我要如何提取和加载数据。右键单击 AW_DM OLE DB 目标组件，然后选择“编辑”。打开 OLE DB 目标编辑器后，我确保选择 AW_DM 作为 OLE DB 连接管理器。然后，我展开表名称下拉列表并选择 dbo.DimProduct 表。最后，我单击“映射”选项卡确认映射正确。单击“确定”确认映射。如果您已经有了可以引用的数据映射，此过程就简单多了，特别是在处理大型表时。产品维度的 ETL 数据包现已完成。

 

　　在 BIDS 中可以轻松执行该数据包。要测试产品维度数据包，请打开该数据包并按 F5。

 

　　开发其他数据包

 

　　我按照创建产品数据包的方式创建客户维度数据包。此处将不重述创建此新数据包应遵循的步骤。请自行尝试创建此数据包。请注意，此数据包使用数据源中的 XML 数据类型列 (Person.Person.Demographics)，这需要您解析出单独的人口统计相关属性。要解析 SQL Server XML 数据类型列中的单个值，您可以在 XML 数据类型固有的 Value() 方法中使用 XQuery。请将完成的数据包命名为 DIM_CUSTOMER.dtsx。

 

　　为日期维度开发 SSIS 数据包是可选的。由于此维度通常没有源数据，因此加载它的最简单方法是使用基本的 T-SQL 脚本。您可以在已完成的解决方案中找到我使用的脚本。

 

　　开发 Internet 销售事实表数据包

 

　　Internet 销售事实表数据包查询所有的 Internet 销售，并按照产品、客户和日期（即订单日期）返回销售详情。与维度表数据包不同，事实表数据包在向事实表加载数据前需要一个额外的步骤，即查询相应维度表中的代理键和智能键。您可以创建一个新数据包并将其命名为 FACT_INTERNET_SALES.dtsx。

 

　　此数据包的提取部分需要使用图 4 所示的 T-SQL 代码查询 AdventureWorks2008 OLTP 数据库。

 

SELECT 

       P.ProductID  

       ,CONVERT(INT,CONVERT( CHAR(4), YEAR(H.OrderDate) ) + RIGHT('0'+ CONVERT(VARCHAR(3),   

            MONTH(H.OrderDate) ),2) + RIGHT('0'+ CONVERT( VARCHAR(3),   

DAY(H.OrderDate) ),2)) AS OrderDateKey  

       ,C.AccountNumber   

       ,SUM(D.OrderQty) AS OrderQuantity  

       ,SUM(D.LineTotal) AS SalesAmount  

FROM 

       [Sales].[SalesOrderDetail] D  

INNER JOIN 

       [Sales].[SalesOrderHeader] H ON (D.SalesOrderID = H.SalesOrderID)  

INNER JOIN 

       [Production].[Product] P ON (D.ProductID = P.ProductID)  

INNER JOIN 

       [Sales].[Customer] C ON (H.CustomerID = C.CustomerID)  

WHERE 

        H.OnlineOrderFlag = 1  

GROUP BY 

        P.ProductID  

        ,H.OrderDate   

        ,C.AccountNumber 

　　图 4 用于按产品、日期和客户划分的 Internet 销售的 T-SQL 代码

 

　　在此数据包的控制流图面中创建一个新的数据流。打开数据流设计器，创建一个 OLE DB 源组件。将该组件命名为 AW_OLTP，使用图 4 中的查询作为其源。此查询会生成 Adventure Works 销售表中 OrderQuantity 和 SalesAmount 两个度量值的聚合（总和）。

 

　　现在，您需要配置一个查询转换。将查询转换组件的两个新实例从工具箱中拖动到数据流设计器图面，并将其重名为“产品”和“客户”。配置第一个实例（产品），使其查询产品维度表中的 ProductKey。配置方法为将维度表的 AlternateKey 与从 AW_OLTP 源查询传入的 ProductID 字段相联接。

 

　　配置第二个实例（客户），使其查询客户维度表中的 CustomerKey。配置方法为将维度表中的 AlternateKey 与从 AW_OLTP 源查询传入的 AccountNumber 字段相联接。

 

　　最后步骤

 

　　最后一步是将数据加载到 FactInternetSales 事实表，并将每个维度的自然键替换为查询转换找到的代理键。拖放 OLE DB 目标组件的新实例，并将其命名为“AW_DM”。编辑该 OLE DB 目标组件，选择 AW_DM 连接管理器。然后，选择 dbo.FactInternetSales 表并单击“映射”选项卡。确保映射如图 5 所示。单击“确定”完成此数据包逻辑。

 

      

 

　　图 5 用于 Internet 销售事实表的 OLE DB 目标映射

 

　　要测试 Internet 销售事实数据包，请在 BIDS 中打开此数据包并按 F5。

 

　　现在，您基本了解了维度建模和使用 SSIS 构建按 ETL 设计的数据包。在第三篇系列文章中，您将学习如何使用填充的数据市场创建用于 SSAS 数据库的维度和多维数据集。多维数据集构建完成后，您可以开发一个 SSIS 数据包。这样，每次有新数据添加到数据市场中，您都可以在 SSAS 数据库中持续更新这些对象。当使用单一查询无法满足报告要求时，SSIS 甚至能够准备在 SSRS 报告中显示的数据。如您所见，SSIS 可以做大量工作来帮助您管理 BI 解决方案，而不仅仅是 ETL 处理。