Centerprise Data Integrator 5 brings together high-performance data warehousing ETL features in a unified and intuitive package. Designed to be used by business experts and developers alike, Centerprise is widely acclaimed for its superior usability, high performance and scalability. Centerprise offers a number of features and optimizations to support data warehouse loading. This includes a high-performance slowly changing dimensions component, lookup caching, high-performance engine, and optimized database writes. This document provides a brief overview of some of these features.
Centerprise is designed from the ground up as a multithreaded, parallel processing engine. The design of the product ensures minimal blocking and starvation of threads thus assuring a high degree of parallelism. When combined with today’s multicore and multiprocessor hardware, this approach results in a data transformation engine that can scale to handle high data volumes. In most instances, throughput increases in direct proportion to increase in processing power. For data warehouses with ever increasing data volumes, this ensures continued scalability of the warehouse and the ETL processes.
Centerprise 5 features a dedicated writing strategy to load and update slowly changing dimension tables. The SCD Transformation uses Centerprise’s data synchronization engine to efficiently handle large dimension tables. The SCD Transformation supports Type 1 and Type 2 slowly changing dimensions and provides multiple row versioning patterns including effective and expiration dates, active/inactive value, and version number fields.
Loading fact tables efficiently is vital to a successful data warehouse project. With ever increasing data volumes and shrinking transfer windows, it is imperative to load data quickly and correctly. Centerprise supports native bulk inserts for popular databases. Additionally, a number of other technologies and features combine to deliver throughput to handle large data sets.
With a large number of records and dimension table lookups, the need for a high-performance lookup transformation cannot be overemphasized. Centerprise lookup transformations provide the features necessary to handle high data volumes including intelligent caching and parallel execution. Dimension table versioning and late arriving facts are supported via effective/expiration dates, active/inactive row, and version number fields.
Another technology that improves throughput considerably is Change Data Capture. CDC refers to a set of approaches where incremental changes are applied to destination tables. Centerprise supports two distinct CDC patterns: incremental read from source using audit fields and incremental update at destination using CRC Hash. These approaches, when used appropriately, can bring about a substantial reduction in transfer runtimes.
The Centerprise 5 aggregate transformation applies aggregate functions such as sum, count, minimum, maximum, average, and other aggregate functions to elements. Additionally, users can specify group-by elements and create output grouped by the specified fields. As with all Centerprise components, the aggregate transformation uses Centerprise’s parallel processing technology to enable users to process high data volumes.
Centerprise supports popular databases and file formats including Oracle, SQL Server, DB2, Sybase, and MYSQL. Centerprise also supports the most popular file formats including delimited, fixed length, Excel, COBOL, XML, and others.