第五章第五章 SQL Server 数据转换服务数据转换服务1sqlserver2000olap数据挖掘课件SQL Server数据转换服务 创创建建数数据据仓仓库库的的一一大大挑挑战战就就是是在在构构建好数据仓库之后的数据装入工作。建好数据仓库之后的数据装入工作。 SQL SERVER 2000增增加加了了一一套套新新的的工工具具DTS（Data Transformation Services,数数据据转转换换服服务务），支支持持有有序序而而有控制的导入数据。有控制的导入数据。2sqlserver2000olap数据挖掘课件DTS中最常用的两大工具是DTS向导与DTS设计器，DTS向导提供了循序渐进的指引功能，依序的指示用户进行数据导入、导出以及转换的工作。我们将于本章使用DTS设计器介绍数据转移的工作，因为他的功能比DTS向导更强大。SQL Server数据转换服务3sqlserver2000olap数据挖掘课件 5.1 DTS设计器简介 DTS设计器提供了图形客户机接口，用户可以将所需要的对象用鼠标拉出来，并且在来源数据与目的数据之间进行转换的工作，比起DTS向导，DTS设计器的弹性大得多。 DTS设计器可以让我们将转换过程存储成包（package），以方便重复使用。4sqlserver2000olap数据挖掘课件 数据转移任务的步骤： 设置数据源 设置数据目的地 设置转换方式（如果有需要的话） 将数据转移任务存储为一个包 执行包实际进行数据转移5.1 DTS设计器简介 5sqlserver2000olap数据挖掘课件 5.2 5.2 新建目的数据库新建目的数据库在我们正式开始执行数据转换之前，首先建立一个新的目的数据库，用来存放数据仓库的数据。我们为该数据库命名为foodmart_dw。 在Enterprise Manager上右击【数据库】图标在快捷菜单中选取【新建数据库】选项。6sqlserver2000olap数据挖掘课件 5.2 5.2 新建目的数据库新建目的数据库7sqlserver2000olap数据挖掘课件 5.3 5.3 激活激活DTSDTS设计器设计器激活DTS设计器可以开始设计数据转换的任务。在Enterprise Manager 上右击【数据转换服务】图标，在快捷菜单中选取【新建包（P）】选项。8sqlserver2000olap数据挖掘课件 5.3 5.3 激活激活DTSDTS设计器设计器9sqlserver2000olap数据挖掘课件 5.3 5.3 激活激活DTSDTS设计器设计器10sqlserver2000olap数据挖掘课件 5.4 5.4 创建数据源与目的地创建数据源与目的地在进行数据转移之前，首先要设置的就是数据源与数据目的地。就我们的示范数据仓库而言，源数据库是示范数据库foodmart，而目的数据库库则是我们刚刚创建的foodmart_dw，到目前为止，他还没有任何表。sql server analysis services为我们提供了一个实验数据库foodmart,是ACCESS格式。我们需要将它利用DTS转换成SQL SERVER格式。11sqlserver2000olap数据挖掘课件 单击Microsoft ACCESS 图标 5.4 5.4 创建数据源与目的地创建数据源与目的地12sqlserver2000olap数据挖掘课件 单击Microsoft ACCESS 图标 5.4 5.4 创建数据源与目的地创建数据源与目的地13sqlserver2000olap数据挖掘课件 5.4 5.4 创建数据源与目的地创建数据源与目的地14sqlserver2000olap数据挖掘课件 同理，我们创建sql server的目的数据库 5.4 5.4 创建数据源与目的地创建数据源与目的地15sqlserver2000olap数据挖掘课件 同理，我们创建sql server的目的数据库 5.4 5.4 创建数据源与目的地创建数据源与目的地16sqlserver2000olap数据挖掘课件 5.4 创建数据源与目的地 5.4 5.4 创建数据源与目的地创建数据源与目的地17sqlserver2000olap数据挖掘课件 5.5 5.5 转移表转移表此处我们以转移CUSTOMER表为例来说明这个问题。我们需要创建一个【转换数据任务】。18sqlserver2000olap数据挖掘课件 步骤步骤1：单击：单击microsoftaccess步骤步骤2：先按住：先按住ctrol键，再单击键，再单击步骤步骤3：单击：单击“转换数据任务转换数据任务”按钮按钮 5.5 5.5 转移表转移表19sqlserver2000olap数据挖掘课件 DTS设计器在设计器在sqlserver和和access之间创建了一个之间创建了一个带箭头的指针，箭头位置带箭头的指针，箭头位置指向目的数据库。转换数指向目的数据库。转换数据任务是具有方向性的，据任务是具有方向性的，所以单击数据连结的顺序所以单击数据连结的顺序是很重要的。是很重要的。 5.5 5.5 转移表转移表20sqlserver2000olap数据挖掘课件 在我们的应用中没有从access数据库中拷贝所有的列，这样在传输数据时有两种选择： 使用T-SQL命令来从数据源读取数据 选择customer表，使用transformation选项卡移走不必要的列。 5.5 5.5 转移表转移表21sqlserver2000olap数据挖掘课件 源数据 可以直接使用一个已知的表 5.5 5.5 转移表转移表22sqlserver2000olap数据挖掘课件 源数据-可以使用一个SQL语句确定原数据的内容。 5.5 5.5 转移表转移表23sqlserver2000olap数据挖掘课件 目的地如果目的表存在，可以直接选择 5.5 5.5 转移表转移表24sqlserver2000olap数据挖掘课件 目的地如果目的表不存在，可以在此处直接创建。 5.5 5.5 转移表转移表25sqlserver2000olap数据挖掘课件 转换 5.5 5.5 转移表转移表26sqlserver2000olap数据挖掘课件 转换虽然我们第一次单击转换标签，但是系统已经出虽然我们第一次单击转换标签，但是系统已经出现了一些转换，但是实际上系统并没有为我们定现了一些转换，但是实际上系统并没有为我们定义好转换。因此，如果微软不显示这些箭头，误义好转换。因此，如果微软不显示这些箭头，误会可能会少一些。会可能会少一些。 5.5 5.5 转移表转移表27sqlserver2000olap数据挖掘课件 转换单击全部删除单击全部删除 5.5 5.5 转移表转移表28sqlserver2000olap数据挖掘课件 转换单击单击“新建新建” 5.5 5.5 转移表转移表29sqlserver2000olap数据挖掘课件 转换选择列之间的选择列之间的直接拷贝直接拷贝 5.5 5.5 转移表转移表30sqlserver2000olap数据挖掘课件 转换依次选择源列和目标列依次选择源列和目标列 5.5 5.5 转移表转移表31sqlserver2000olap数据挖掘课件 转换 5.5 5.5 转移表转移表32sqlserver2000olap数据挖掘课件 转换 5.5 5.5 转移表转移表33sqlserver2000olap数据挖掘课件 转换 5.5 5.5 转移表转移表34sqlserver2000olap数据挖掘课件 转换单击确定单击确定 5.5 5.5 转移表转移表35sqlserver2000olap数据挖掘课件 转换 5.5 5.5 转移表转移表36sqlserver2000olap数据挖掘课件 转换这时布局已经作了改变，以反映列的映射。源数据的映射都这时布局已经作了改变，以反映列的映射。源数据的映射都集中成一条线，然后又发散开。正是中间的那条线决定了转集中成一条线，然后又发散开。正是中间的那条线决定了转换选型卡式如何工作的。换选型卡式如何工作的。 5.5 5.5 转移表转移表37sqlserver2000olap数据挖掘课件 转换单击确定单击确定 5.5 5.5 转移表转移表38sqlserver2000olap数据挖掘课件 5.6 5.6 保存包保存包39sqlserver2000olap数据挖掘课件 保存包的过程很直接。单击磁盘图标，或者从保存包的过程很直接。单击磁盘图标，或者从package菜单菜单中选择中选择save，这时就会弹出关于包的细节信息的窗口。，这时就会弹出关于包的细节信息的窗口。 5.6 5.6 保存包保存包40sqlserver2000olap数据挖掘课件 5.6 5.6 保存包保存包41sqlserver2000olap数据挖掘课件 5.6 5.6 保存包保存包42sqlserver2000olap数据挖掘课件 5.7 5.7 包的执行包的执行单击执行单击执行43sqlserver2000olap数据挖掘课件 5.7 5.7 包的执行包的执行44sqlserver2000olap数据挖掘课件 数据转换服务数据转换服务包可以重复执行多遍，但必须清除上一次运行时留在数据库中的信息。这样包将删除将要创建的数据库中的所有表，但会保留其它用户自建的表。因此，在前面的例子中，我们首先利用SQL任务实现表的删除，重新创建，最后在进行数据的转换，从而保证数据的正确性。45sqlserver2000olap数据挖掘课件 5.8 5.8 删除表删除表首先删除数据库中所有的ACCOUNT表的实例。46sqlserver2000olap数据挖掘课件 首先删除数据库中所有的ACCOUNT表的实例。 5.8 5.8 删除表删除表47sqlserver2000olap数据挖掘课件 首先删除数据库中所有的ACCOUNT表的实例。 5.8 5.8 删除表删除表48sqlserver2000olap数据挖掘课件 删除了数据库中的account之后，我们要重新建立一个新的account表。可以将这两个任务（删除和创建表）放在一个任务中，但如果表不存在，那么第一个动作会产生错误，则第二个动作就不能执行了，或者会将产生的错误传递到下一步中。因此，最好将它们放在两个任务中。 5.8 5.8 删除表删除表49sqlserver2000olap数据挖掘课件 5.8 5.8 创建表创建表50sqlserver2000olap数据挖掘课件 5.8 5.8 创建表创建表51sqlserver2000olap数据挖掘课件 5.8 5.8 创建表创建表52sqlserver2000olap数据挖掘课件 5.9 5.9 创建工作流创建工作流53sqlserver2000olap数据挖掘课件 面对一个包里的多个任务，我们需要建立工作流来指定任务面对一个包里的多个任务，我们需要建立工作流来指定任务的执行过程和执行条件。的执行过程和执行条件。 5.9 5.9 创建工作流创建工作流54sqlserver2000olap数据挖掘课件 删除当前数据库中的删除当前数据库中的account表表创建一个新的创建一个新的account表表进行进行account表的数据表的数据转换转换 5.9 5.9 创建工作流创建工作流55sqlserver2000olap数据挖掘课件 数据流的建立 工作流有三种，表示执行的条件： 完成时 成功时 失败时 5.9 5.9 创建工作流创建工作流56sqlserver2000olap数据挖掘课件 工作流的建立第一步：按住第一步：按住shift，先后单击，先后单击delete 和和create 任务任务第二步：单击工作流第二步：单击工作流 5.9 5.9 创建工作流创建工作流57sqlserver2000olap数据挖掘课件 5.9 5.9 创建工作流创建工作流58sqlserver2000olap数据挖掘课件 根据我们前面的分析，当第一次执行这个包的根据我们前面的分析，当第一次执行这个包的时候，时候，account表并不存在，因此这个删除任表并不存在，因此这个删除任务可能不能成功执行。但是务可能不能成功执行。但是create table一定一定要执行，因此，我们选择一个完成时工作流。要执行，因此，我们选择一个完成时工作流。 5.9 5.9 创建工作流创建工作流59sqlserver2000olap数据挖掘课件 然后在然后在create table 和数据转换任务之和数据转换任务之间建立成功时数据流间建立成功时数据流 5.9 5.9 创建工作流创建工作流60sqlserver2000olap数据挖掘课件 5.9 5.9 创建工作流创建工作流61sqlserver2000olap数据挖掘课件 包的执行第一次执行 5.9 5.9 创建工作流创建工作流62sqlserver2000olap数据挖掘课件 包的执行第二次执行 5.9 5.9 创建工作流创建工作流63sqlserver2000olap数据挖掘课件 5.10 5.10 设置表的主键域外键设置表的主键域外键64sqlserver2000olap数据挖掘课件 单单击击管管理理关关系系图图标标，设设定定外外键键 5.10 5.10 设置表的主键域外键设置表的主键域外键65sqlserver2000olap数据挖掘课件 5.10 5.10 设置表的主键域外键设置表的主键域外键66sqlserver2000olap数据挖掘课件 5.11 5.11 数据转换中公式的使用数据转换中公式的使用如：目标表的一个字段可以是源表多个属性构成的表达式的值。这个功能我们可以通过在转换的过程中利用ActiveXScript实现67sqlserver2000olap数据挖掘课件 数据转换的过程中可以使用公式源表源表 5.11 5.11 数据转换中公式的使用数据转换中公式的使用68sqlserver2000olap数据挖掘课件 数据转换的过程中可以使用公式目的表目的表Account_id=a+b 5.11 5.11 数据转换中公式的使用数据转换中公式的使用69sqlserver2000olap数据挖掘课件 数据转换的过程中可以使用公式 5.11 5.11 数据转换中公式的使用数据转换中公式的使用70sqlserver2000olap数据挖掘课件 5.1.7 数据转换的过程中可以使用公式1.在左侧的在左侧的【源源】标签中同时选中标签中同时选中a,b字段（按住字段（按住Ctrl键不键不放）放）2.在右侧的在右侧的【目的目的】列表框中单击列表框中单击account_id字段字段3.单击单击【新建新建w】按钮按钮 5.11 5.11 数据转换中公式的使用数据转换中公式的使用71sqlserver2000olap数据挖掘课件 数据转换的过程中可以使用公式选选择择转转换换方方式式 5.11 5.11 数据转换中公式的使用数据转换中公式的使用72sqlserver2000olap数据挖掘课件 数据转换的过程中可以使用公式转转换换选选项项的的常常规规选选项项卡卡 5.11 5.11 数据转换中公式的使用数据转换中公式的使用73sqlserver2000olap数据挖掘课件 数据转换的过程中可以使用公式在在转转换换选选项项的的常常规规选选项项卡卡中中，单单击击“属属性性” 5.11 5.11 数据转换中公式的使用数据转换中公式的使用74sqlserver2000olap数据挖掘课件 数据转换的过程中可以使用公式选择语言：选择语言：vb，java 5.11 5.11 数据转换中公式的使用数据转换中公式的使用75sqlserver2000olap数据挖掘课件 数据转换的过程中可以使用公式 5.11 5.11 数据转换中公式的使用数据转换中公式的使用76sqlserver2000olap数据挖掘课件 数据转换的过程中可以使用公式* Visual Basic 转换脚本转换脚本* 将各源列复制到目的列将各源列复制到目的列Function Main()DTSDestination(account_id) = DTSSource(a) + DTSSource(b) Main = DTSTransformStat_OKEnd Function 5.11 5.11 数据转换中公式的使用数据转换中公式的使用77sqlserver2000olap数据挖掘课件 数据转换的过程中可以使用公式单单击击“分分析析”验验证证表表达达式式是是否否正正确确 5.11 5.11 数据转换中公式的使用数据转换中公式的使用78sqlserver2000olap数据挖掘课件 数据转换的过程中可以使用公式单单击击“分分析析”验验证证表表达达式式是是否否正正确确 5.11 5.11 数据转换中公式的使用数据转换中公式的使用79sqlserver2000olap数据挖掘课件 5.12 5.12 数据转换服务数据转换服务小节小节 SQLSERVER 2000提供了数据转换服务，它是一套功能强大的数据转换工具，可以在SQL与其它的OLDB、ODBC数据源以及文字文件之间执行数据导入导出以及转换的服务。 DTS中最常用的两大工具是DTS向导与DTS设计器。 DTS向导提供了循序渐进的指引功能，依序的指示用户进行数据导入、导出以及转换的工作。 DTS设计器提供了图形客户机接口，用户可以将所需要的对象鼠标拉出来，并且在源数据与目的数据之间进行转换的任务。80sqlserver2000olap数据挖掘课件 5.1 5.1 数据转换服务数据转换服务小节小节 比起DTS向导，DDTS设计器的弹性大得多，他允许用户输入许多类型的定义。我们可以使用DTS设计出更复杂、功能更强大的转换任务。 DTS设计器可以让我们将转换过程存储成包，以方便重负使用。 当我们想同时由数个表取得数据并转换至目的数据库，这时候就非得使用DTS设计器不可。 数据转移的工作步骤：设置数据源、设置数据的目的地、设置转换方式、将数据转移任务存储为一个包、执行包实际进行数据转移。81sqlserver2000olap数据挖掘课件 5.1 5.1 数据转换服务数据转换服务小节小节 在进行数据转换之前，首先得为转移后的数据创建一个数据仓库。 激活DTS设计器的方法为：在Enterprise Manager上右击【数据转换服务】图标，在快捷菜单中选取【新建包（P）】选项。 DTS设计器的 图标是在创建SQL SERVER数据源或数据目的地对象。 DTS设计器的 图标是在创建ACCESS数据源或数据目的地的对象。 DTS设计器的 图标是在创建数据源与目的地之间的转换任务。 DTS设计器的 图标是在执行一个包。82sqlserver2000olap数据挖掘课件 5.1 5.1 数据转换服务数据转换服务小节小节 设置数据源可以直接使用一个表或是使用SQL语句。 执行包的方法是右击PackageName图标在快捷菜单中选取【执行包（X）】选项。 转换数据完成后还应该为维度表与事实标设置主键与外键的关系。83sqlserver2000olap数据挖掘课件