论文部分内容阅读
摘要：DTS是一个强大而且灵活的数据库迁移工具，它能够实现各种异构数据源的数据转换。本文通过一个数据仓库实例，说明如何利用DTS将原始的业务数据源加载到数据仓库中，重点说明运用DTS中的ActiveX脚本语言转换数据。
关键词：DTS；数据仓库；数据转换；ActiveX
中图分类号：TP311文献标识码：A文章编号：1009-3044(2007)12-21514-02
Application of DTS Component in Data Warehouse Data Transformation
ZHENG Dan, WANG Yao
(School of Polytechnic, East China Jiaotong University, Nanchang 330013, China)
Abstract:DTS is a very powerful and flexible data transfer tool. It can realize the transformation of data source with different construct. This paper by using a practical application in data warehouse, illuminates how to use DTS for loading original operation data into data warehouse, and emphasizes on how to use ActiveX script language in data transformation.
Key words:DTS; Data Warehouse; Data Transformation; ActiveX
1 引言
在建立數据仓库时，数据的抽取、转换和装载是一个重要的环节，它所完成的工作就是将数据迁移到数据仓库系统。在一个数据仓库项目中，大部分的工作都花费在此。但是,不同的事务处理系统可能用到不同的数据库系统，使数据转换的工作变得异常复杂。期间容易产生许多异构数据源，有的是文本文件，有的虽具有表结构但不属于同一种数据源，如EXCEL、FOXPRO、ACCESS，这些情况极大地妨碍着数据的集中处理。因此由原有异构环境向OLAP环境转换就成为数据仓库构建过程中最重要的一步。SQL Server提供数据转换服务（Data Transformation Services，DTS）很好地解决了这个问题。DTS提供了例如引入、引出以及在微软的SQL Server和任何OLE DB、ODBC或者文本格式之间转换数据的功能，使这一工作变得简单高效。
2 设计学生成绩分析系统的星型模式
对一个学校来说，学生成绩无疑是很有价值的数据，它是评估教学质量的重要依据。本文以我院情况为例，计划设计数据仓库对学生成绩数据进行存储和处理，以便有效地挖掘利用这些数据所隐含的价值，帮助教育管理人员做出科学的决策。在构建数据仓库之前创建一个名为gzxy的数据集市，打算采用星型结构。主要结构如图1所示。
图1 星型数据模型
3 ActiveX脚本语言
DTS是一个强大而且灵活的数据库迁移工具，它能够实现各种异构数据源的数据转换。在数据迁移时，DTS 还可以使用ActiveX脚本语言，如VBScript 或JavaScript来转换数据。数据转换脚本可以简单地将一列的内部代码转换为外部系统使用的代码，也可完成复杂的功能。数据转换脚本可以执行能在脚本语言中执行的任何动作，这种灵活性使DTS 成了一个强大的数据迁移工具。
3.1 利用ActiveX脚本语言实现学生维表数据的导入
在SQL Server 2000中新建数据库gzxy，并且创建上图所示的事实表和维表。我院的学生信息和学生成绩信息都是以EXCEL文件的形式存储的。其中学生信息是按班级分别存放在每个表单中，表单名称就是班级名称，字段包括学号，姓名，性别，出生日期等，并没有班级代码。比方说，“网络1”表中，学号为20055120610101的学生：表示她是05网络1班，2005是她的入学年份，5120是院系代码，6101代表是网络专业1班，01表示是1号学生；“网络2”表中，学号为20055120610201的学生就是05网络2班的学生。05网络1班的班级代码是056101，05网络2班的班级代码是056102。根据这样的情况，我们就要考虑在将这张EXCEL表中学生信息导入SQL SERVER学生维表中，如何添加班级代码。当然我们不可能去为每个学生逐个填写班级代码，这是不现实的。在这里用SQL Server的DTS工具就能很轻松的完成这样的工作。
对于其他字段，在导入时直接进行复制就可以了。对于班级代码的建立，就需要编写ActiveX脚本语言。ActiveX脚本可以验证信息的正确性，不正确的数据可以存储在异常日志的文件中，可以随后分析这些数据。
具体的实现过程如下：先运行SQL Server企业管理器，选中“工具”菜单中的“数据转换服务”→导入数据，在“数据源”对话框中选Microsoft Excel 97-2000，文件名选“E:\data\05学生名单.xls”，目的选“用于SQL Server 的Microsoft OLE DB 提供程序”，数据库选gzxy，在“选择数据源表和视图”对话框中，“源”选择以班级名称命名的某张表单，比如“网络1”，在“目的”中选择“[gzxy].[dbo].[xs]”，单击随后的“转换”，弹出“列映射和转换”对话框，单击“转换”选项卡，单击“在将信息复制到目的时对其进行转换”选项，在下面的区域编写我们的脚本语言。
Function Main()
DTSDestination("xh") = DTSSource("学号")
DTSDestination("xm") = DTSSource("姓名")
DTSDestination("xb") = DTSSource("性别")
DTSDestination("csrq") = DTSSource("出生日期")
DTSDestination("bjdm") =mid(DTSSource("学号"),3,2)+mid(DTSSource("学号"),9,4)
Main = DTSTransformStat_OK
End Function
这里编写的ActiveX脚本采用的是VB Script的语法。这里主要是对“班级代码”的一个处理，我们发现班级代码实际上隐含在每个学生的学号里。比方说学号为“20055120610101”，班级代码就是由学号第3位开始的后2位“05”组合上学号第9位开始的后4位“6101”而成的“056101”。有了这样的规律，我们就可以借助脚本语言方便的生成每个学生的班级代码。
写好代码单击“下一步”，在“保存、调度和复制包”对话框中勾选“立即运行”，并且勾选保存DTS包，存为SQL Server，名称为“xs_resource”单击“完成”后，可以看到成功执行“包”的提示。
创建好的包在企业管理器的数据转换服务列表可以中看到，如果还要导入其他班的学生信息，我们只需要择其他的表单名称，重新执行一边包，就不用反复书写代码了。
3.2 利用ActiveX脚本语言实现事实表数据的导入
学生成绩信息按每学期的考试结果保存为一张EXCEL表，其中按班级分别创建多个表单。如表1所示就是05网络1班2005～2006上学期的考试成绩的情况：
表1 05网络1
在导入数据时，主要是对成绩的处理，其他信息可以根据具体情况填入。我们将每门课程分别导入，这里以“英语”课程为例。具体的实现过程如下：
第一步：在当前服务器下的“数据转换服务”，选择“本地包”，单击右键“新建包”，弹出“新建包”界面。向DTS包设计器工作区中添加一个连接，即Microsoft Excel连接，该连接的数据库文件名指向“E:\data\05061_05高职成绩.xls”；再向工作区中添加一个Microsoft OLE DB Provider for SQL Server图标，选择目标数据库为gzxy。
第二步：从任务工具箱中选择“转换数据任务”，为其设置属性。在“源”选项卡选择某个表单，这里选择“网络1”，“目的”选项卡选择“[gzxy].[dbo].[cj]”，在“转换“选项卡中，默认情况下系统会按照先后顺序自动建立源字段到目标字段的一一映射，但这并不是我们想要的，将其全部删除，然后重新建立。
第三步：选中源列表框中的“學号”字段，再选中目的列表框中的“xh”字段，单击“新建”按钮，在“创建新转换”中，选择“Copy Column”，对于学号，只需要直接复制就可以了。
第四步：选中源列表框中的“英语”字段，然后选中目的列表框中的“kch”字段，单击“新建”按钮，在“创建新转换”中，选择“ActiveX Script”，在弹出的“ActiveX脚本转换属性”窗口，“语言”选择“VB Script Language”，在右侧的脚本代码输入窗口中输入如下代码：
Function Main()
DTSDestination("kch") = "0051G057"//英语课程的课程编号
Main = DTSTransformStat_OK
End Function
第五步：选中源列表框的“英语”字段，再选中目的列表框的“cj”字段，对英语成绩的导入我们直接进行列的复制就可以了，选择“Copy Column”。
“cj”表中的其他字段，如“学分”，“教师编号”等按照步骤四的方法填入相应代码就可以了。在所有的字段映射都设置好后，DTS将会把源和目的的映射关系以图形化界面的方式显示出来，如图2所示。
图2 映射关系
第六步：保存创建好的包，并执行包。之后就可以看到EXCEL表中的英语成绩导入了SQL Server的学生成绩事实表中。
其他课程成绩的导入只需要重新打开包，按照具体情况重新设定就可以了。
对于考查科目，我们在导入事实表中的时候，为了成绩分析统计的方便，我们希望将“优秀”，“良好”这样的文字也转换成数字，用“考核方式”字段可以说明这门课程是考试还是考查。如何使其能自动进行转换呢，在转换的时候我们就需要编写脚本。
在上例中的第五步，对成绩字段我们就不能直接进行复制了，而要选择“ActiveX Script”，在右侧的脚本代码输入窗口中输入如下代码：
Function Main()
if DTSSource("组网与综合布线实践")="优" then
DTSDestination("cj") = 90
elseif DTSSource("组网与综合布线实践")="良" then
DTSDestination("cj") = 80
elseif DTSSource("组网与综合布线实践")="中" then
DTSDestination("cj") = 70
elseif DTSSource("组网与综合布线实践")="及格" then
DTSDestination("cj") = 60
elseif DTSSource("组网与综合布线实践")="不及格" then
DTSDestination("cj") = 50
end if
Main = DTSTransformStat_OK
End Function
上面的代码把成绩等级相应地转化为分数导入数据库中，这样考查科目就可以和考试科目一起参与计算学生总成绩，平均分等等。
4 结束语
本文根据构建我院学生成绩分析数据仓库中涉及到的异构数据源转换的问题，主要介绍了通过编写脚本语言实现将大量的EXCEL数据表导入SQL Server数据库中的技巧，实现了数据的快速转换，展现了DTS组件在实现数据仓库中数据导入导出的强大功能，相信对其他院校也具有一定的借鉴作用。
参考文献：
[1]飞思科技产品研发中心. SQL SERVER 2000 OLAP服务设计与应用[M]. 北京：电子工业出版社,2002.
[2]周之昕. 基于DTS的异构数据源格式转换[J]. 电脑知识与技术,2006(8):20-21.
[3]王胜德, 杨学强. 利用DTS实现异构数据库的数据交换[J]. 计算机应用,2003(23):132-134.
[4]崔彬. 利用DTS包进行多维数据集的自动处理[J]. 软件导刊,2006(8):49-50.
“本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文。”
关键词：DTS；数据仓库；数据转换；ActiveX
中图分类号：TP311文献标识码：A文章编号：1009-3044(2007)12-21514-02
Application of DTS Component in Data Warehouse Data Transformation
ZHENG Dan, WANG Yao
(School of Polytechnic, East China Jiaotong University, Nanchang 330013, China)
Abstract:DTS is a very powerful and flexible data transfer tool. It can realize the transformation of data source with different construct. This paper by using a practical application in data warehouse, illuminates how to use DTS for loading original operation data into data warehouse, and emphasizes on how to use ActiveX script language in data transformation.
Key words:DTS; Data Warehouse; Data Transformation; ActiveX
1 引言
在建立數据仓库时，数据的抽取、转换和装载是一个重要的环节，它所完成的工作就是将数据迁移到数据仓库系统。在一个数据仓库项目中，大部分的工作都花费在此。但是,不同的事务处理系统可能用到不同的数据库系统，使数据转换的工作变得异常复杂。期间容易产生许多异构数据源，有的是文本文件，有的虽具有表结构但不属于同一种数据源，如EXCEL、FOXPRO、ACCESS，这些情况极大地妨碍着数据的集中处理。因此由原有异构环境向OLAP环境转换就成为数据仓库构建过程中最重要的一步。SQL Server提供数据转换服务（Data Transformation Services，DTS）很好地解决了这个问题。DTS提供了例如引入、引出以及在微软的SQL Server和任何OLE DB、ODBC或者文本格式之间转换数据的功能，使这一工作变得简单高效。
2 设计学生成绩分析系统的星型模式
对一个学校来说，学生成绩无疑是很有价值的数据，它是评估教学质量的重要依据。本文以我院情况为例，计划设计数据仓库对学生成绩数据进行存储和处理，以便有效地挖掘利用这些数据所隐含的价值，帮助教育管理人员做出科学的决策。在构建数据仓库之前创建一个名为gzxy的数据集市，打算采用星型结构。主要结构如图1所示。
图1 星型数据模型
3 ActiveX脚本语言
DTS是一个强大而且灵活的数据库迁移工具，它能够实现各种异构数据源的数据转换。在数据迁移时，DTS 还可以使用ActiveX脚本语言，如VBScript 或JavaScript来转换数据。数据转换脚本可以简单地将一列的内部代码转换为外部系统使用的代码，也可完成复杂的功能。数据转换脚本可以执行能在脚本语言中执行的任何动作，这种灵活性使DTS 成了一个强大的数据迁移工具。
3.1 利用ActiveX脚本语言实现学生维表数据的导入
在SQL Server 2000中新建数据库gzxy，并且创建上图所示的事实表和维表。我院的学生信息和学生成绩信息都是以EXCEL文件的形式存储的。其中学生信息是按班级分别存放在每个表单中，表单名称就是班级名称，字段包括学号，姓名，性别，出生日期等，并没有班级代码。比方说，“网络1”表中，学号为20055120610101的学生：表示她是05网络1班，2005是她的入学年份，5120是院系代码，6101代表是网络专业1班，01表示是1号学生；“网络2”表中，学号为20055120610201的学生就是05网络2班的学生。05网络1班的班级代码是056101，05网络2班的班级代码是056102。根据这样的情况，我们就要考虑在将这张EXCEL表中学生信息导入SQL SERVER学生维表中，如何添加班级代码。当然我们不可能去为每个学生逐个填写班级代码，这是不现实的。在这里用SQL Server的DTS工具就能很轻松的完成这样的工作。
对于其他字段，在导入时直接进行复制就可以了。对于班级代码的建立，就需要编写ActiveX脚本语言。ActiveX脚本可以验证信息的正确性，不正确的数据可以存储在异常日志的文件中，可以随后分析这些数据。
具体的实现过程如下：先运行SQL Server企业管理器，选中“工具”菜单中的“数据转换服务”→导入数据，在“数据源”对话框中选Microsoft Excel 97-2000，文件名选“E:\data\05学生名单.xls”，目的选“用于SQL Server 的Microsoft OLE DB 提供程序”，数据库选gzxy，在“选择数据源表和视图”对话框中，“源”选择以班级名称命名的某张表单，比如“网络1”，在“目的”中选择“[gzxy].[dbo].[xs]”，单击随后的“转换”，弹出“列映射和转换”对话框，单击“转换”选项卡，单击“在将信息复制到目的时对其进行转换”选项，在下面的区域编写我们的脚本语言。
Function Main()
DTSDestination("xh") = DTSSource("学号")
DTSDestination("xm") = DTSSource("姓名")
DTSDestination("xb") = DTSSource("性别")
DTSDestination("csrq") = DTSSource("出生日期")
DTSDestination("bjdm") =mid(DTSSource("学号"),3,2)+mid(DTSSource("学号"),9,4)
Main = DTSTransformStat_OK
End Function
这里编写的ActiveX脚本采用的是VB Script的语法。这里主要是对“班级代码”的一个处理，我们发现班级代码实际上隐含在每个学生的学号里。比方说学号为“20055120610101”，班级代码就是由学号第3位开始的后2位“05”组合上学号第9位开始的后4位“6101”而成的“056101”。有了这样的规律，我们就可以借助脚本语言方便的生成每个学生的班级代码。
写好代码单击“下一步”，在“保存、调度和复制包”对话框中勾选“立即运行”，并且勾选保存DTS包，存为SQL Server，名称为“xs_resource”单击“完成”后，可以看到成功执行“包”的提示。
创建好的包在企业管理器的数据转换服务列表可以中看到，如果还要导入其他班的学生信息，我们只需要择其他的表单名称，重新执行一边包，就不用反复书写代码了。
3.2 利用ActiveX脚本语言实现事实表数据的导入
学生成绩信息按每学期的考试结果保存为一张EXCEL表，其中按班级分别创建多个表单。如表1所示就是05网络1班2005～2006上学期的考试成绩的情况：
表1 05网络1
在导入数据时，主要是对成绩的处理，其他信息可以根据具体情况填入。我们将每门课程分别导入，这里以“英语”课程为例。具体的实现过程如下：
第一步：在当前服务器下的“数据转换服务”，选择“本地包”，单击右键“新建包”，弹出“新建包”界面。向DTS包设计器工作区中添加一个连接，即Microsoft Excel连接，该连接的数据库文件名指向“E:\data\05061_05高职成绩.xls”；再向工作区中添加一个Microsoft OLE DB Provider for SQL Server图标，选择目标数据库为gzxy。
第二步：从任务工具箱中选择“转换数据任务”，为其设置属性。在“源”选项卡选择某个表单，这里选择“网络1”，“目的”选项卡选择“[gzxy].[dbo].[cj]”，在“转换“选项卡中，默认情况下系统会按照先后顺序自动建立源字段到目标字段的一一映射，但这并不是我们想要的，将其全部删除，然后重新建立。
第三步：选中源列表框中的“學号”字段，再选中目的列表框中的“xh”字段，单击“新建”按钮，在“创建新转换”中，选择“Copy Column”，对于学号，只需要直接复制就可以了。
第四步：选中源列表框中的“英语”字段，然后选中目的列表框中的“kch”字段，单击“新建”按钮，在“创建新转换”中，选择“ActiveX Script”，在弹出的“ActiveX脚本转换属性”窗口，“语言”选择“VB Script Language”，在右侧的脚本代码输入窗口中输入如下代码：
Function Main()
DTSDestination("kch") = "0051G057"//英语课程的课程编号
Main = DTSTransformStat_OK
End Function
第五步：选中源列表框的“英语”字段，再选中目的列表框的“cj”字段，对英语成绩的导入我们直接进行列的复制就可以了，选择“Copy Column”。
“cj”表中的其他字段，如“学分”，“教师编号”等按照步骤四的方法填入相应代码就可以了。在所有的字段映射都设置好后，DTS将会把源和目的的映射关系以图形化界面的方式显示出来，如图2所示。
图2 映射关系
第六步：保存创建好的包，并执行包。之后就可以看到EXCEL表中的英语成绩导入了SQL Server的学生成绩事实表中。
其他课程成绩的导入只需要重新打开包，按照具体情况重新设定就可以了。
对于考查科目，我们在导入事实表中的时候，为了成绩分析统计的方便，我们希望将“优秀”，“良好”这样的文字也转换成数字，用“考核方式”字段可以说明这门课程是考试还是考查。如何使其能自动进行转换呢，在转换的时候我们就需要编写脚本。
在上例中的第五步，对成绩字段我们就不能直接进行复制了，而要选择“ActiveX Script”，在右侧的脚本代码输入窗口中输入如下代码：
Function Main()
if DTSSource("组网与综合布线实践")="优" then
DTSDestination("cj") = 90
elseif DTSSource("组网与综合布线实践")="良" then
DTSDestination("cj") = 80
elseif DTSSource("组网与综合布线实践")="中" then
DTSDestination("cj") = 70
elseif DTSSource("组网与综合布线实践")="及格" then
DTSDestination("cj") = 60
elseif DTSSource("组网与综合布线实践")="不及格" then
DTSDestination("cj") = 50
end if
Main = DTSTransformStat_OK
End Function
上面的代码把成绩等级相应地转化为分数导入数据库中，这样考查科目就可以和考试科目一起参与计算学生总成绩，平均分等等。
4 结束语
本文根据构建我院学生成绩分析数据仓库中涉及到的异构数据源转换的问题，主要介绍了通过编写脚本语言实现将大量的EXCEL数据表导入SQL Server数据库中的技巧，实现了数据的快速转换，展现了DTS组件在实现数据仓库中数据导入导出的强大功能，相信对其他院校也具有一定的借鉴作用。
参考文献：
[1]飞思科技产品研发中心. SQL SERVER 2000 OLAP服务设计与应用[M]. 北京：电子工业出版社,2002.
[2]周之昕. 基于DTS的异构数据源格式转换[J]. 电脑知识与技术,2006(8):20-21.
[3]王胜德, 杨学强. 利用DTS实现异构数据库的数据交换[J]. 计算机应用,2003(23):132-134.
[4]崔彬. 利用DTS包进行多维数据集的自动处理[J]. 软件导刊,2006(8):49-50.
“本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文。”