1 / 33

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

企業資料倉儲利器 - 資料轉換服務 ( DTS) . 李 立 勳 台灣微軟公司顧問. 真實世界的現況. DB2. ACCESS. Oracle. Sybase. Informix. MS SQL 7.0. 整合. DTS. dBase. Excel. SQL V6.x. Text File. VFox. 議題大綱. 何謂 DTS DTS 角色 DTS 能作什麼 Advanced DTS Features ActiveX ® Script, Data Lookups Repository / Data Lineage

svetlana
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


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

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

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

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

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

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

  7. 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

  8. 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

  9. 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

  10. 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

  11. OLTP Schema

  12. Star Schema for DW

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

  14. 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

  15. DTS Designer

  16. 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

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

  18. 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

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

  20. 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

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

  22. 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

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

  24. 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

  25. 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

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

  27. 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

  28. 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

  29. 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/

  30. Q & A

More Related