25 Jan 98
Data Transformation Services (DTS)
In many ways, this was the coolest thing I saw at the briefing. It was hidden away in the data warehousing presentation but has uses far beyond that. I was totally unclear about this features function from a limited scan of the beta documentation.
The purpose of DTS is to make it easier to import, export and transform heterogeneous data. It provides for data lineage if desired. It supports transformations between the source and target data sources (which need not be SQL Server). These transformations include such things as
Data quality checks and validation; supplying missing values, scrubbing data
Data integration and duplicate elimination
Combine multiple columns into one
Build several rows from one input
It has the concept of a package, which is a series of tasks to be performed as a part of a transformation. This provides a simple "workflow".
It has its own engine (In process COM Server) and can be used independent of SQL Server.
The demo I saw quickly moved a database from Oracle 8 and an Excel spreadsheet to SQL Server.
This is clearly a super tool for conversion and massaging of data.
Microsoft has made a distinction between the Query Processor ("Relational Engine") and the Storage Engine. They are distinct subsystems. The overall design of both is controlled by Gertz Grefer (I am not sure of the spelling). He comes from Tandem. He did not present but did participate in the final panel discussion.
Client processes communicate with the Relational Engine via DBLIB (old, not being enhanced), ODBC 3.6 and OLE DB. SQL Server still receives client requests via TDS. DBLIB clients communicate with TDS 4.2/6, ODBC and OLE DB clients use TDS 7. The relational engine communicates with the storage engine via OLE DB.
The query processor (QP) has been completely redesigned. Goals are to provide excellent decision support performance, support for very large databases, and to support heterogeneous query and update. The following picture illustrates the ultimate goal. Note that the SQL is pseudo code. Initially there will only be support for SQL Server as a database engine.
Many thanks to Sharon for providing these notes - drop her a note at email@example.com and tell her thanks!