为 BI 解决方案构建数据基础_.NET_编程开发_程序员俱乐部

了解要求　　

和所有 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 销售总线矩阵示例。
图 1：Adventure Works 销售总线矩阵

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

创建数据映射

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

图 2：DimProduct 数据映射选项卡
每个维度表（日期维度表除外）中都包含名为代理键（通常为标识列）的主键。 使用代理键的好处之一是在合并多个系统的数据时不会出现重复键。
维度表还有一个可选键列， 这些可选键表示自然键，有时也称为业务键。 自然键用于标识源系统。 例如，客户维度中的 CustomerAlternateKey 列映射到 Adventure Works OLTP 数据库中 Sales.Customer 表的 AccountNumber 字段。 通过将这些键存储在维度表中，每次对各个维度运行 ETL 过程时，我可以将维度表中已有的记录与从数据源中提取的记录进行匹配。
几乎每个数据市场都包含日期维度，因为业务分析往往按照日期、星期、月份、季度或年份来比较度量值变化。 由于日期维度很少从源系统中获取，因此不应使用基于 SQL Server 标识的键。 为此，我将改用以 YYYYMMDD 格式存储为 SQL Server 整数列的智能键。 智能键是根据逻辑或脚本生成的键，而并非自动递增键（如 SQL Server 中的标识列）。
请注意，日期维度通常并不映射到源表。 因此，我将使用脚本生成数据，以便将记录加载到表中。
由于我的小型架构所需的 ELT 过程相当简单，因此这样的数据映射足以满足需要。 在实际项目中，我会给数据映射添加注释，指出何时需要进行复杂的转换。

构建数据市场

逻辑建模完成之后，我需要创建 ETL 过程要加载的物理表及其主用数据库。 我将使用基本的 T-SQL 脚本创建数据库以及与其相关的维度表和事实表。 您可以在 2009 年代码下载中找到 BI 解决方案示例随附下载项中的完整 T-SQL 脚本。
出于本文的需要，我仅构建了整个销售数据市场架构的一个子集，以便在 SSIS 中涵盖整个 ETL 过程。 在我所构建的精简版架构中，我仅加入了 Internet 销售事实表中的 OrderQuantity 和 SalesAmount 两个度量值。 此外，在此精简版架构中，我还加入了简化的客户、产品以及日期维度表。

部署数据市场

为部署数据市场，我只需执行先前编写的 T-SQL 脚本，以便在 SQL Server 实例上将新表实例化。 为执行 T-SQL，我单击“开始”\“所有程序”\“Microsoft SQL Server 2008”\“SQL Server Management Studio”，启动 SQL Server Management Studio (SSMS)。 打开 SSMS 后，我键入指定的 SQL Server 实例名称，然后在“连接”对话框中单击“使用 Windows 身份验证连接”。 使用 SQL Server Management Studio 打开文件 TECHNET_AW2008SalesDataMart.sql 并执行此脚本。

开发 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，然后单击“确定”。 现在，BIDS 应显示一个打开的 SSIS 项目。

创建公共数据连接

SSIS 2008 中的另一项出色功能是可以在单个数据包之外创建数据源连接。 您可以定义数据源连接一次，然后在解决方案中的一个或多个 SSIS 数据包中引用此连接。 若要了解有关创建 BIDS 数据源的详情，请参阅“如何使用数据源向导定义数据源 (Analysis Services)”。
创建两个新的数据源连接：一个用于 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 源组件从工具箱拖动到数据流设计器图面，并将其重命名为 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 数据库。

开发其他数据包

我按照创建产品数据包的方式创建客户维度数据包。 此处将不重述创建此新数据包应遵循的步骤。 请您自行尝试生成此数据包。 请注意，此数据包使用数据源中的 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 所示的 T-SQL 代码查询 AdventureWorks2008 OLTP 数据库。

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

　　在此数据包的控制流图面中创建一个新的数据流。打开数据流设计器，创建一个 OLE DB 源组件。将该组件命名为 AW_OLTP，使用图 3 中的查询作为其源。此查询会生成 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 表并单击“映射”选项卡。确保映射如图 4 所示。单击“确定”完成此数据包逻辑。

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

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

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

原文地址：http://technet.microsoft.com/en-ca/magazine/2009.08.bipartii.aspx