企業資料倉儲利器 － 資料轉換服務 ( DTS)

2. 企業資料倉儲利器－資料轉換服務 (DTS) 李立勳 台灣微軟公司顧問

3. 真實世界的現況 DB2 ACCESS Oracle Sybase Informix MS SQL 7.0 整合 DTS dBase Excel SQL V6.x Text File VFox

4. 議題大綱 • 何謂 DTS • DTS 角色 • DTS 能作什麼 • Advanced DTS Features • ActiveX® Script, Data Lookups • Repository / Data Lineage • Building DTS Custom Tasks • Data Warehousing Framework

5. 何謂 DTS • Data Transformation Services 微軟 SQL “資料轉換功能” 超強 UI 工具 • 取代 MS SQL 之前版本的 bcp 指令 • 使用 OLE DB provider • 存取各式各樣資料 • 建立資料倉儲的不可或缺工具

6. DTS 能作什麼 ? Make it easy to: • Import 轉入 • Export 轉出 • Transform 轉換 Heterogeneous data using OLE DB and ODBC

7. DTS 能做什麼 ? • Move data between DBMSs • Transform • Validation, Scrubbing • Migration, Transformation • Centralize data for populating DW • Improve corporate decision-making F OLAP

8. DTS Wizard Transferring ? • Copy table source ð drop & recreate destination table • Query table with criteria ð drop & recreate destination table ð specify “where” condition • Transfer object(s) and data ð most flexible

9. DTS Provider Support • SQL Server (4.21, 6.0, 6.5, 7.0) via ODBC and OLE DB • Excel, Access, Paradox, dBase via JOLT (OLE DB Provider for Jet) • Fixed Field and Delimited Text Files • Oracle (7.3, 8.0, 8i), Fox, DB2, Informix, Sybase, SQL Anywhere • Microsoft® Data Links

10. Microsoft Data Links • Can be used to specify source or destination • Allows providers to expose custom UI for connection/configuration information • Can be used to resolve source / destination dynamically only for packages created programmatically

11. Data Warehousing 資料倉儲 • Process of integrating, consolidating, and summarizing information from multiple OLTP sources for data analysis • Building the data warehouse (DW) or data mart is hard • Many customers will fail because they lack the infrastructure to manage distributed data

12. OLTP Schema

13. Star Schema for DW

14. Data Warehousing Requirements • Extraction from operational data stores • Data scrubbing and validation • Integration, matching, and consolidating data • Data transformations • Aggregation • Centralized metadata

15. DTS Package • A self-contained definition of all the tasks to be performed as part of a transformation • Can be executed from GUI, scheduler, command line, or script • Each package contains multiple steps • Steps can execute serially or in parallel • Provides simple “workflow” • Transactions across steps

16. DTS Designer

17. ActiveX Scripting • Hosts ALL ActiveX Scripting engines • Visual Basic®, JavaScript, Perl • Current values of source and destination rowset are passed in as named objects • Express complex • Procedural Logic • Data validation and conversions • Hundreds of built-in functions

18. Name Value Type Precision NumericScale ActualSize Attributes DefinedSize OriginalValue UnderlyingValue AppendChunk GetChunk ActiveX Scripting Metadata • DTSSource, DTSDestination, DTSErrorRecords objects are inserted into script

19. Data Pump Return Codes • DTSTransformStat_OK • DTSTransformStat_ExceptionRow • DTSTransformStat_ErrorSkipRow • DTSTransformStat_SkipFetch • DTSTransformStat_SkipInsert • DTSTransformStat_SkipRow • DTSTransformStat_SkipRowInfo • DTSTransformStat_AbortPump • DTSTransformStat_Error Data Driven Queries also add • DTSTransformStat_InsertQuery • DTSTransformStat_DeleteQuery DTSTransformStat_UpdateQuery • DTSTransformStat_UserQuery

20. 展示 Demo • 將分開的 “年”, “月”, “日” 資料合併為“月/日/年” ; 並過濾不正確的資料 • Merging & Validation 合併與驗證

21. Building Custom Tasks • DTS has an extensible architecture for adding new functionality • New custom tasks can be created by implementing the IDTSCustomTask Interface • Your tasks can be integrated with the desginer by implementing IDTSCustomTaskUI

22. 展示 Demo • Multiple sources merged into one destination table

23. Microsoft Repository • Provides “Shared” Metadata • #1 “Issue” in Building and Maintaining DW • Integrates Business and Technical Metadata • Supported by Major Industry Vendors • Integration point for all Vendors/Products • Information Models • Database Schema • Transformations/Data Lineage • Future: Scheduling (Data Flow), MOLAP • Meta-Data Coalition support

24. Data Lineage • Unique execution of packages • Package • Versions • Lineage • MS Patented 16 byte or 4 byte per row

25. How To Use Lineage • Create new package • Set package options to enable lineage and write to repository • select source and destination • Select Lineage columns destination • SAVE the package before you execute it • Execute it

26. Microsoft Data Warehousing Framework Building Using Data Warehouse/Data Mart Design (Visual Data Tools) End-User Tools (Internet Explorer, Access, Excel, English Query, Visual Basic) Operational Data (OLE-DB/ ODBC) Data Transform/ Cleansing (DTS) Data Marts (SQL Server/ Plato) Information Dir Managing Microsoft Repository (Persistent Shared Meta-Data) Schema* Transform* Schedule Repl InfoPublish OLAP* Data Warehouse Management (SQL Server Enterprise Manager, SQLAgent, SQL Profiler, Tuning Expert) Data Flow Meta-Data Flow

27. 展示 Demo • Data from OLTP to data warehouse through star schema using DTS

28. Industry Support • Design Tools • Popkin’s System Architect • LogicWorks/Platinum ER-Win • Visio Visual Modeler • Transformation Tools • Platinum Technologies • Sagent • Informatica • Ardent, Data Junction, etc. • Database Vendors • IBM, Informix, Sybase

30. Summary • Microsoft committed to working well in the heterogeneous environment • Provide the infrastructure for interoperability • Provide tools for applications to access data and participate in distributed transactions • Committed to supporting interoperability through use of standards • More resources • http://www.microsoft.com/interoperability • http://www.microsoft.com/standards

31. Get help and Support • Books Online for MS SQL Server 7.0 • http://www.microsoft.com/sql/ • http://support.microsoft.com/ • http://search.microsoft.com/ • http://msdn.microsoft.com/ • http://technet.microsoft.com/

32. Q & A