商业智能

生成 BI 解决方案的基础数据

Derek Comingore

代码下载位置： SampleCode2009_08.exe(151 KB)

一眼：

• 以下维度模型生成一个数据集市
• 开发 ETL 过程和数据映射
• 使用集成 ETL 服务
• 在报价中创建的集成服务项目

内容

了解要求
使用该维度的模型
创建一个总线矩阵
创建一个数据映射
生成数据集市
部署数据集市
开发 ETL 过程
创建报价中的 SSIS 项目
创建公共数据连接
开发 ETL 的维度
定义一个数据流中提取并加载
开发其他程序包
开发 Internet 销售额事实数据表包
最终的步骤

数据是最重要一部分任何商业智能 (BI) 解决方案。 如所述 Stacia Misner 的" 规划您的第一个 Microsoft BI 解决方案"BI 解决方案中获取数据和维护后，它涉及几个步骤。 BI 专业人员，请参阅下列步骤提取、 转换和加载 (ETL) 过程。 即使不准备以寻求找工作重点 BI，可以仍利用 ETL 技术和工具来管理，通知您在您的工作中进行日常决策所需的数据。 本文，我将向您展示如何设计和构建一个简单数据集市来说明如何使用您自己的 BI 解决方案执行 ETL SQL Server 2008 Integration Services (SSIS)。

了解要求

与任何的 IT 项目开始的 ETL 项目，最好是了解一般的 BI 解决方案要求您希望生成，然后工作以确定如何最佳支持这些要求您方式返回到数据。 在第一个系列文章中，设置为虚构公司艾德需要通过几个问题需要回答该公司作为描述分析要求 BI 解决方案阶段。 这些问题突出显示需要了解其产品销售额，从不同角度艾德： 按分发渠道 （销售商或 Internet）、 产品的需求随时间，更改和实际销售情况和按产品、 销售人员、 地理区域和分销商类型的销售预测差排列盈利率。 在这些问题的答案将有助于艾德决定公司应该集中到增加利润，调整到最符合其制造流程要求，以及销售策略中的更改可能有助于满足其销售目标的公司的通讯通道。 您将看到如何将数据支持回答这些业务 SQL Server 报告服务 （SSRS） 添加到 BI 解决方案时。

开始设计记住这些要求，数据集市艾德之前，希望的模型需要从业务的角度来看的信息。 换而言数据集市的设计应基于用户将询问问题，而不是来自数据源的方式将数据的方式。

您将需要 SQL Server 2008 Adventure Works OLTP 示例数据库的这篇文章中找到该代码示例。

使用该维度的模型

一个数据集市通常是使用是很好地适合于分析数据库架构的维度的模型设计生成的。 （很好的学习有关维度建模资源是 kimballgroup.com.) 一个维度的模型的用户，所熟悉的方式显示数据，并有助于您构建最适合的查询大量数据的数据结构。 您可以通过 denormalizing 数据有助于快速选择和有效地在查询时聚合大量数据，数据库引擎实现此优化。 非规范化为艾德解决方案架构，在中，我将包括两种类型的表： 维度和事实。 一个维度表存储有关业务实体和诸如，销售商或产品的信息。 一个事实数据表，我将使用为数字的销售数据需要聚合、 包含数字的度量值和事实与维度表的键。 我将有关本文中稍后介绍的事实数据表更多说明。

您可以实现的一个维度的模型表架构的两种类型： 星型和雪花型。 简单的术语一个星型架构使用一个表为每个查询中与一个事实数据表与单个联接的维度。 一个雪花型架构用于每个维度的两个或多个表，因此需要查看所有数据查询中的多个联接。 此集合的级联联接意味着查询通常运行速度更慢比一个星型架构中一个雪花型架构中。 本文为了我将简化设计，并使用一个星型架构。

创建一个总线矩阵

一部分维度建模过程创建总线矩阵以帮助标识与销售，维度对于艾德 BI 解决方案的焦点。 请记住艾德有两个销售渠道： wholesale 分销商销售和通过 Internet 的单个销售。 我还使用总线矩阵来确定每个维度，以一种或两种类型的销售的关系。 图 1 显示我的示例总线矩阵 Adventure Works 销售。

我的下一步是确定解决方案的度量值。 度量值是所需的分析的数字值。 它们可以直接来自源，如销售美元或产品成本，或通过计算，如数量乘以美元金额的扩展的销售金额进行派生。 此外需要确定要包含与每个维度的属性。 属性是日期维度中的维度 （对应于表中的列） 可以使用组或筛选器的数据进行分析，如年或销售区域的维度中的国家中单个元素。 我不会详细介绍每个标识的度量值或在本文中的维度属性 — — 只是意识到的标识进程需要的位置。

创建一个数据映射

在创建该数据集市物理表之前需要执行一些其他的规划。 特别，需要自定义数据映射文档映射到列的数据集市架构中的每个目的列，在 Adventure Works OLTP 源系统中 （在的 AdventureWorks2008 数据库，您可以下载和安装上 p Stacia Misner 文章所述。 31)。 可以使用各种应用程序创建的数据映射。 格式不是与内容一样重要。 我喜欢开发在 Office Excel 中的数据映射。 图 2 显示我的数据映射中创建的 DimProduct 选项卡。 此外创建 DimCustomer 和 FactInternetSales 数据映射。 工作簿中的每个工作表表示的一个表中我数据集市。 每张只是有两个列： 一个用于源列和一个目的列。

图 2 DimProduct 数据映射选项卡

每个维度表 (except for 日期维度） 具有主键称为一个代理键 （通常是一个标识列）。 使用代理键的一个好处是您可以组合多个不重复的键的风险的系统中的数据。

维度表还有一个备用的键列。 这些可选键表示有时也称为业务键的自然的键。 这些是源系统的标识符。 渚嬪的方式  客户维度中 CustomerAlternateKey 列将映射到 Adventure Works OLTP 数据库字段 AccountNumber Sales.Customer 表中。 通过将这些注册表项存储在维度表中，我有一种维度中已为每个维度的 ETL 过程运行时从源提取的记录的记录相匹配的方法。

几乎每个数据集市包括日期维度，因为业务分析通常比较按日期、 周、 月、 季度或年的度量值中的更改。 该日期维度几乎从不来自于在源系统以便使用 SQL Server IDENTITY–based 密钥原因不能应用。 而是，我将使用什么称为智能密钥具有 YYYYMMDD SQL Server 整数列作为存储的格式。 智能密钥是一个由逻辑或相对于一个自动递增的键，如 SQL Server 在一个标识列的脚本生成的密钥。

请记住该日期维度不通常映射到源表。 而是，将使用脚本来生成数据加载到表中的记录。

因为我小的架构所需 ETL 过程相当简单，我的数据映射是原样。 在一个真实的项目中我将注释指出需要复杂的转换时将数据映射。

生成数据集市

现在逻辑建模是完成，需要创建物理表 ETL 过程将加载和主机数据库中的为这些表。 我将使用基本的 T-SQL 脚本创建我的数据库和其相关联的维度和事实数据表。 您可以在随附的下载中找到整个 T-SQL 脚本，为示例 BI 解决方案 (在 2009 代码下载).

本文为了我生成只有整个销售数据集市架构的一个子集，使我可以涵盖整个 ETL 过程在 SSIS。 在较小的架构版本，我包括只在 OrderQuantity 和 SalesAmount 措施在 Internet Sales 事实数据表。 此外，我较小的架构包括客户、 产品和日期维度表的简化的版本。

部署数据集市

若要部署该数据集市，只需执行 T-SQL 我编写了前面要实例化 SQL Server 实例上的新表。 若要执行 T-SQL 的单击 Start\All Programs\Microsoft SQL Server 2008\SQL 服务器管理 Studio 首先 SQL Server 管理 Studio (SSMS）。 打开 SSMS 后我键入我指定的 SQL Server 实例的名称，然后再单击使用 Windows 身份验证，在连接对话框的连接。 使用 SQL Server 管理 Studio 打开文件 TECHNET_AW2008SalesDataMart.sql 并执行脚本。

开发 ETL 过程

设计和开发 ETL 过程是构建 BI 解决方案中的下一步。 查看，ETL 包括从数据源转换，和然后加载到目标库的提取数据的所有技术进程。 通常，BI 解决方案中的 ETL 过程从平面文件和 OLTP 操作数据库中提取数据、 更改以适应维度模型 （例如，一个星型架构） 数据，然后将结果数据加载到一个数据集市。

创建报价中的 SSIS 项目

在开发一个 ETL 过程第一步是创建新项目业务智能开发 Studio （报价） 中。 报价与 SQL Server 2008 但时您选择工作站组件在安装过程中安装。 报价 SSIS SSAS，SSRS 包括项目模板。 它还支持源控件的集成，就像 Visual Studio 不会。

要启动报价，转到 Start\Programs\Microsoft SQL Server 2008\Business 智能开发 Studio，并选择 File\New 项目。 您应该看到新建项目模板 图 3 所示。

图 3 在投标 2008年中的新项目模板

选择集成服务项目模板窗格中，键入在名称文本框中的 ssis_TECHNET_AW2008，然后重然后单击确定。 报价现在应显示一个打开的 SSIS 项目。

创建公共数据连接

SSIS 2008 中的另一种不错的功能是能够创建单个包以外的数据源连接。 可以定义数据源连接一次，然后在解决方案中的一个或多个 SSIS 包中引用它。 若要了解有关创建报价数据源，请参阅" 如何： 定义使用向导 （Analysis Services) 数据源的数据源".

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

开发 ETL 的维度

加载产品维度 ETL 就非常简单。 需要从 Adventure Works Production.Product 表中提取数据，并将数据加载到 TECHNET_AW2008SalesDataMart 数据库。 第一次，需要重命名为我 SSIS 的项目创建的报价的默认包。 (包是一个容器 SSIS 将执行的工作流中的所有步骤。 使用鼠标右键单击在解决方案资源管理器中，默认包，然后选择重命名。 键入 DIM_PRODUCT.dtsx，然后再按 Enter。

下一步中，需要创建连接管理器使用预建的数据源本地程序包。 创建两个新连接管理器引用前面生成数据源。

定义一个数据流中提取并加载

SSIS 包括封装所有需要实现一个简单的维度的 ETL 一个数据流任务。 我只是一个数据流任务从工具箱拖到控制流设计器图面上拖重命名任务 EL （对于提取和负载）。 右击数据流任务在设计器中，并选择编辑。 报价现在显示数据流设计器。

产品维度程序包提取部分需要查询 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 软件包现在已完成。

您可以轻松地执行包内报价。 测试产品维度包、 打开包和按 F5 键。

开发其他程序包

我将与相同产品包相同的方式创建客户维度包。 我我们不会刚才说过您需要按照创建此新的包步骤。 您应该自己生成它。 注意此程序包源需要分析出单个人口统计相关属性中使用的 XML 数据类型列 （Person.Person.Demographics）。 若要分析 SQL Server XML 数据类型列的单个值，您可以使用 XML 数据类型的本机值 () 方法利用一个 XQuery。 完成 DIM_CUSTOMER.dtsx 包的名称。

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

开发 Internet 销售额事实数据表包

Internet 销售额事实数据表包的所有 Internet 销售额的查询，并返回按产品，分解的销售客户和日期 （即，订单日期）。 与在维度包不同事实数据表包需要查找将代理和智能的密钥，才能将数据加载到该事实数据表相应维度表中的其他步骤。 可以创建一个新包并将其命名 FACT_INTERNET_SALES.dtsx。

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

图 4 的 Internet 销售额产品、 日期和客户的 T-SQL 代码

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

创建一个新的数据流任务，包的控制流图面中。 打开数据流设计器，并创建一个 OLE DB 源组件。 名称 AW_OLTP，该组件，并使用其源 图 4 中查询。 此查询 Adventure Works 销售表中找到 OrderQuantity 和 SalesAmount 度量值的产生一种聚合 （总和）。

现在您需要配置查找的转换。 查找转换组件的两个新实例从工具箱向数据流设计器图面拖产品和客户命名它们。 配置第一个 （产品） 查找，在该产品维度表 ProductKey AW_OLTP 源查询中的该维度表在 AlternateKey 加入传入产品 ID 字段。

配置第二个一 （客户） 查找，在该客户维度表 CustomerKey AW_OLTP 源查询中的该维度表在 AlternateKey 加入传入 AccountNumber 字段。

最终的步骤

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

图 5 对事实 Internet SalesFact 表的 OLE DB 目标映射

测试 Internet 销售事实包、 投标中打开包和按 F5 键。

现在，您了解维度建模和构建与 SSIS ETL 设计的包的基础知识。 在此系列中第三个文章中, 您学习使用一个填充的数据集市创建维度和多维数据集为 SSAS 数据库。 一旦构建多维数据集可以开发更新这些不断地在每次新数据添加到该数据集市 SSAS 数据库中的对象的 SSIS 包。 不能满足报告要求使用单个查询时 SSIS 甚至可以 SSRS 报告中显示准备数据。 您可以看到 SSIS 如何帮助您管理 BI 解决方案比只是 ETL 处理更多。

Derek Comingore 是与一个高级架构师 ComFrame. 作为一个 SQL Server MVP 和认证专业人士的几个包括 SQL Server BI 的 Microsoft 技术他说出在本地组和国家/地区 SQL Server 用户组和一个已发布的作者 SQL Server 主题。 Derek 重点介绍生成和企业级的数据仓库和 SQL Server 与 BI 解决方案，与其他 Microsoft BI 软件产品一起提供。 您可以与在 Derek dcomingore@comframe.com.