Solution

Integration through the data sources makes it possible to provide application integration between heterogeneous systems. Using a database middleware layer, this integration can occur without making obtrusive and costly changes to the critical business applications. Data movement and transformation are well known technologies that can be used for this integration process.

In the proposed scenario, we will use ODBC or OLE DB for this database middleware layer. The Data Transformation Services (DTS) feature of SQL Server will be used for data transformation and movement between different data sources. DTS is a general-purpose tool for importing and exporting data between heterogeneous systems. DTS uses ODBC drivers and OLE DB providers to connect to heterogeneous systems to move and transform data. DTS is extremely flexible and is surprisingly fast. The DTS Import/Export Wizard included with SQL Server makes it easy to create a DTS package that is used to drive the process. All of the information needed for the data movement and transformation is stored in the DTS package. Using the DTS Wizard, it is easy to input and store any data type mappings or transformations that are needed. The DTS package can be stored in the SQL Server local repository, a metadata repository to be shared with other SQL Servers, or saved as a file accessible from Visual Basic. The DTS package can also be scheduled for later execution within the SQL Server scheduler.

For this scenario, we are assuming that the data sources exist as tables in an IBM DB2 database running on an AS/400 minicomputer used for core business applications. For simplicity, we will assume that only one AS/400 and DB2 data source is involved, but the scenario could just as easily be applied to multiple remote systems running DB2. The only difference would be that additional DTS packages would be required to access each different remote DB2 system. Host Integration Server 2000 includes the Microsoft ODBC Driver for DB2 and the Microsoft OLE DB Provider for DB2 that are needed to connect to DB2. Host Integration Server also provides the necessary network protocol support to connect to AS/400 and mainframe systems over either SNA or TCP/IP.

Many database administrators may be reluctant to allow access to the DB2 database containing critical business data from other applications that bypass the business application logic. In order to mitigate these concerns, the critical DB2 database and tables will not be accessed directly using DTS. The necessary information from DB2 will be copied to tables in a temporary (Temp) database (sometimes referred to as a staging table in the literature). DTS will only access these Temp or staging tables. These Temp tables will be generated automatically by the DB2 system when changes are made to the core data tables using triggers. These triggers can be considered special constraints placed on a database that can cause other events in DB2 to occur. In our scenario, a trigger will be created and attached to each core DB2 table that is to be made available to DTS. When the trigger event is activated by an insertion, deletion, or update of rows in a specific table, the change will be propagated to the Temp tables. Since SQL triggers are applied directly to the DB2 data tables, they do not require changes to the core business applications that use these data tables.

SQL Server can be used as the destination for the data movement, but this is not a requirement. The destination could just as well be an Oracle server or a series of Excel spreadsheets. The only requirement for using DTS is that the data source and destination can be accessed using an ODBC driver or OLE DB provider. In the proposed scenario, we will target an SQL Server database as the destination for the data. The destination SQL Server where the data is to be moved could be on the same Windows system where the DTS packages will be run or on a remote SQL Server somewhere in the enterprise. We will assume for simplicity that the destination SQL Server is same computer where the DTS packages will be run.

Using DTS for data transformation and movement

Once the data is transformed and moved into the SQL Server database, it can be accessed for analysis and reporting using a variety of methods including the following:

• SQL Server Query Analyzer (to build custom queries)
• English Query (to translate questions in English to SQL queries)
• SQL Server Analysis Services (to provide integrated and web-enabled analysis services using OLAP
• Excel import using the OLE DB Provider for SQL Server or the ODBC driver for SQL Server
• Visual Basic programs and Visual Basic for Application (VBA) scripts
• Data Transformation Packages that export the portions of the data to Excel spreadsheets for manipulation

Previous  Next