CATEGORY: Data Acquisition, Transformation and Replication

REVIEWER: Mark Chaffin, senior data warehouse architect for Encore Development.

BACKGROUND: Encore tailors its business intelligence solutions for each client's unique decision support and business analysis needs. We work closely with clients as partners to deliver scalable solutions that meet the strategic business and decision-making needs of the organization while providing clear, quantifiable business value. Encore has created some of the largest data warehousing solutions in the world covering marketing, financials, sales and portfolio management.

PLATFORMS: SQL Server 2000 Data Transformation Services (DTS) runs on Windows 98, ME and NT 4.0 and 2000.

PROBLEM SOLVED: Before Encore began using DTS, our ETL solutions for loading data warehouses required either expensive third-party tools or custom applications and significant database code. The third-party tools required significant customization, were counter-intuitive for the novice user or lacked many necessary capabilities. DTS comes installed with 17 built- in tasks, or units of work, that allow the developer to perform such actions as transforming and bulk inserting data, executing SQL queries or commands, FTPing data or even executing VBScript or JScript code. All of these tasks can be configured graphically and be added to the application just by clicking and dragging. Along with these tasks comes the ability to create complex workflows, which determine the order and precedence by which tasks are executed. Because of its graphical nature, knowledge transfer and support are much easier after deployment. Working with DTS has allowed Encore to deliver the most complete solution for the most economical price to our business intelligence clients.

PRODUCT FUNCTIONALITY: Encore uses DTS to perform many activities related to data warehousing and data loading. In one project, DTS is being used to extract data into flat files from several hundred geographically distributed data sources, consolidate these files at a central location, and then transform and load them into a centralized data warehouse. In another project, DTS extracts data directly from several data sources and loads a data warehouse staging area. The data is then cleansed and loaded into the data warehouse. DTS also processes Analysis Services OLAP cubes once all data loading has been completed.

STRENGTHS: The main strengths of DTS include the graphical and intuitive design environment, the numerous built-in tasks that obviate the need for custom development and the speed at which the data transformations occur. DTS is also scalable because it is multithreaded and can execute many tasks simultaneously. Other strengths include the ability to connect to any ODBC- or OLE DB-compliant data source and the ability to create custom tasks where the built-in functionality is not sufficient. These custom tasks can be built using VB or C++ and can be integrated into any DTS application very easily to perform tasks such as auditing or error handling.

WEAKNESSES: DTS does not include a separate installer apart from the one included with the RDBMS. However, there are no dependencies, and it is possible to build your own.

SELECTION CRITERIA: The most important reasons for Encore's choice of DTS were functionality, price, and support and maintenance costs. DTS delivers a tremendous amount of functionality for a commodity-priced application. With the majority of the development for DTS applications being graphical, knowledge transfer and support costs were lessened. Generally, an experienced SQL Server DBA can support and maintain a DTS application without needing an expensive developer on staff.

DELIVERABLES: DTS can integrate directly with the Microsoft Meta Data Services (formerly Repository) to produce data lineage reports that show the full trail of transformations that occur to each data element; this can also provide an invaluable source of information for knowledge management solutions.

VENDOR SUPPORT: Microsoft includes DTS in the standard installation of SQL Server 2000. This installation is usually quick and painless. Microsoft also provides support via public news groups, online knowledge base and standard support agreements. This wealth of information makes development and support of DTS applications much easier.

DOCUMENTATION: DTS documentation is delivered via SQL Server's online help and is quite comprehensive. DTS also comes with several built-in wizards to assist with complex tasks that are helpful for the novice developer.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access