数据库设计外文翻译.docx

英文摘要 data transformation servicesdts facilitates the import, export, and transformation of heterogeneous data. it supports transformations between source and target data using an ole db-based architecture. this allows you to move and transform data between the following data sources: native ole db providers such as sql server, microsoft excel, microsoft works, microsoft access, and oracle. odbc data sources such as sybase and informix using the ole db provider for odbc. ascii fixed-field length text files and ascii delimited text files.for example, consider a training company with four regional offices, each responsible for a predefined geographical region. the company is using a central sql server to store sales data. at the beginning of each quarter, each regional manager populates an excel spreadsheet with sales targets for each salesperson. these spreadsheets are imported to the central database using the dts import wizard. at the end of each quarter, the dts export wizard is used to create a regional spreadsheet that contains target versus actual sales figures for each region.dts also can move data from a variety of data sources into data marts or data warehouses. currently, data warehouse products are high-end, complex add-ons. as companies move toward more data warehousing and decision processing systems, the low cost and ease of configuration of sql server 7.0 will make it an attractive choice. for many, the fact that much of the legacy data to be analyzed may be housed in an oracle system will focus their attention on finding the most cost-effective way to get at that data. with dts, moving and massaging the data from oracle to sql server is less complex and can be completely automated.dts introduces the concept of a package, which is a series of tasks that are performed as a part of a transformation. dts has its own in-process component object model (com) server engine that can be used independent of sql server and that supports scripting for each column using visual basic and jscript development software. each transformation can include data quality checks and validation, aggregation, and duplicate elimination. you can also combine multiple columns into a single column, or build multiple rows from a single input.using the dts wizard, you can: specify any custom settings used by the old db provider to connect to the data source or destination. copy an entire table, or the results of an sql query, such as those involving joins of multiple tables or distributed queries. dts also can copy schema and data between relational databases. however, dts does not copy indexes, stored procedures, or referential integrity constraints. build a query using the dts query builder wizard. this allows users inexperienced with the sql language to build queries interactively. change the name, data type, size, precision, scale, and nullability of a column when copying from the source to the destination, where a valid data-type conversion applies. specify transformation rules that govern how data is copied between columns of different data types, sizes, precisions, scales, and nullabilities. execute an activex script (visual basic or jscript) that can modify (transform) the data when copied from the source to the destination. or you can perform any operation supported by visual basic or jscript development software. save the dts package to the sql server msdb database, microsoft repository, or a com-structured storage file. schedule the dts package for later execution.once the package is executed, dts checks to see if the destination table already exists, then gives you the option of dropping and recreating the destination table. if the dts wizard does not properly create the destination table, verify that the column mappings are correct, select a different data type mapping, or create the table manually and then copy the data.each database defines its own data types and column and object naming conventions. dts attempts to define the best possible data-type matches between a source and a destination. however, you can override dts mappings and specify a different destination data-type, size, precision, and scale properties in the transform dialog box.each source and destination may have binary large object (blob) limitations. for example, if the destination is odbc, then a destination table can contain only one blob column and it must have a unique index before data can be imported. for more information, see the ole db for odbc driver documentation.notedts functionality may be limited by the capabilities of specific database management system (dbms) or ole db drivers.dts uses the source objects name as a default. however, you can also add double quote marks (“ “) or square brackets ( )around multiword table and column names if this is supported by your dbms.data warehousing and olapdts can function independent of sql server and can be used as a stand-alone tool to transfer data from oracle to any other odbc- or ole db-compliant database. accordingly, dts can extract data from operational databases for inclusion in a data warehouse or data mart for query and analysis.figure 4. dts and data warehousingin the previous diagram, the transaction data resides on an ibm db2 transaction server. a package is created using dts to transfer and clean the data from the db2 transaction server and to move it into the data warehouse or data mart. in this example, the relational database server is sql server 7.0, and the data warehouse uses olap services to provide analytical capabilities. client programs (such as excel) access the olap services server using the ole db for olap interface, which is exposed through a client-side component called microsoft pivottable service. client programs using pivottable service can manipulate data in the olap server and even change individual cells.sql server olap services is a flexible, scalable olap solution, providing high-performance access to information in the data warehouse. olap services supports all implementations of olap equally well: multidimensional olap (molap), relational olap (rolap), and a hybrid (holap). olap services addresses the most significant challenges in scalability through partial preaggregation, smart client/server caching, virtual cubes, and partitioning.dts and olap services offer an attractive and cost-effective solution. data warehousing and olap solutions using dts and olap services are developed with point-and-click graphical tools that are tightly integrated and easy to use. furthermore, because the pivottable service client is using ole db, the interface is more open to access by a variety of client applications.issues for oracle versions 7.3 and 8.0oracle does not support more than one blob data type per table. this prevents copying sql server tables that contain multiple text and image data types with modification. you may want to map one or more blobs to the varchar data type and allow truncation, or split a source table into multiple tables. oracle returns numeric data types such as precision = 38 and scale = 0, even when there are digits to the right of the decimal point. if you copy this information, it will be truncated to integer values. if mapped to sql server, the precision is reduced to a maximum of 28 digits.the oracle odbc driver does not work with dts and is not supported by microsoft. use the microsoft oracle odbc driver that comes with sql server. when exporting blob data to oracle using odbc, the destination table must have an existing unique primary key.heterogeneous distributed queriesdistributed queries access not only data currently stored in sql server (homogeneous data), but also access data traditionally stored in a data store other than sql server (heterogeneous data). distributed queries behave as if all data were stored in sql server. sql server 7.0 will support distributed queries by taking advantage of the uda architecture (ole db) to access heterogeneous data sources, as illustrated in the following diagram.figure 5. accessing heterogeneous data sources with uda翻译dts 使进口，出口和不同的数据的转变变得容易。 转变在源头和使用某位奥利基于分贝的建筑的目标数据之间支持。 这允许你移动并且改变在以下的数据源之间的数据： 本地奥利分贝提供者( 例如sql 服务器， microsoft excel)，微软公司工作， microsoft access 和oracle。 odbc 数据源象sybase 和informix 那样使用奥利分贝提供者给odbc。 ascii 确定文本文件和ascii 限定文本文件的字段长度。例如，与每对一个预先规定的地理的地区负责的4 个地区办公室考虑一家训练公司。 公司正使用一台中心的sql 服务器储存出售数据。 在每四分之一，每地区经理的开始居住于一用出售去每名销售员的目标突出电子表格。 这些电子表格被进口到中心的数据库( 使用dts 进口术士)。 在每四分之一结束时， dts 出口术士用来创造包含目标对真实出售身材适合每个地区的一地区电子表格。dts 也能把数据从多种数据源移动到数据市场或者数据仓库。 目前，数据仓库产品是高档，复杂的附加物。 公司移向储存于仓库的更多数据和决定处理系统，低成本并且减轻服务器7.0 将使它一有吸引力选择的sql 的构造当时。对很多， 事实分析的遗产数据的很多可能在系统将把注意力集中于发现大多数节约经费方法得到那数据的一oracle 内安放。 有dts，移动并且按摩数据不不是不那么复杂并且可能被完全使自动化。dts 介绍一个包的概念，这是被作为一转变的一部分执行的一系列任务。 dts 有它自己的加工过程中的零部件目标模型 可能使用不依赖sql 服务器的( com) 服务器发动机那支持适合每根柱子使用直观的basic?和jscript?发展软件写稿。 每转变包括数据品质能检查和生效，集中，并且复制消除。 你也能将多柱子合并成一根单个的柱子，或者从一次单个的输入中建造多排。使用dts 术士，你能： 指定任何风俗确定以连接数据源或者目的地的旧分贝提供者使用。 复制一张整个桌子或者一个sql 质问的结果，例如多桌子的那些包含的联合或者分配质问。 dts 也能复制schema 和在相关的数据库之间的数据。 但是， dts 不复制索引，储存程序或者参考的完整限制条件。 建造一个质问( 使用dts 质问建筑者术士)。 这允许用sql 语言无经验的用户交互式建造质问。 兑换名字，数据类型，尺寸，精密，刻度，和什么时候临摹源头的作品给目的地的一柱子的nullability， 这里有充分根据的数据类型转换使用。 指定管理数据怎样被在不同的数据类型，尺寸，精密，规模和nullabilities 的专栏之间复制的转换规则。 执行能修改的一activex 书写( visual basic 或者jscript)( 改变) 什么时候临摹源头的作品给目的地的数据。 或者你能执行visual basic 或者jscript 发展软件支持的任何操作。 救dts 包给sql 服务器msdb 数据库，微软仓库或者一个com 组织的存储文件。 计划dts 包适合后实行。一旦包被执行， dts 检查看看是否目的地桌子已经存在，那么给你放下和再创造目的地桌子的选择。 如果dts 术士不正确建立目的地表格，证实专栏映射是正确的，选择不同的数据类型映射， 或者手工建立表格然后复制数据。每数据库确定它自己数据类型和柱子和物体命名约定。 dts 试图确定在一个来源和目的地之间的尽可能最好数据类型比赛。 但是，你推翻dts 映射并且指定不同目的地数据类型，尺寸，精密和刻度性质能在内改变对话座位。每个来源和目的地可能有二进制的大的物体( 一滴) 限度。 例如，如果目的地是odbc， 然后，一目的地表格能包含一团点纵行只并且在数据可能被进口之前，它必须有一个唯一索引。 更多信息，看见奥利分