Data warehousing tools

By John H. MayerSpecial to GCNRecent developments in data warehousing have run toward the emerging issues of getting information to the Web, developing enterprise information portals and integrating wireless technology. But the basic steps are still a challenge for developers.The process of identifying, acquiring, transforming and loading data into the warehouse remains the most complex, time-consuming and costly portion of the development cycle.The difficulties information technology managers face when they tackle this task have been chronicled many times. A recent study by the Data Warehousing Institute of Gaithersburg, Md., for instance, found that almost one-third of IT managers surveyed indicated that their efforts to collect data did not meet expectations.More often than not, projects failed simply because the data that IT departments want to collect from legacy sources is inaccurate, inconsistent or difficult to acquire in its original form.This guide focuses on commercial data warehouse tools that perform the data extraction, transformation and loading (ETL) functions.Industry analysts typically define extraction as the process of identifying and retrieving a set of data from an operational system. Transformation tools play a key role as IT managers integrate data from multiple sources. They permit a system administrator to develop rules for integrating data from different sources and tables to form a single table or entity.Just as crucial is the process of formatting the output from the transformation process into a form acceptable to the target database's load utility. This typically includes the use of a scheduling mechanism to trigger the loading process.All of these functions usually are performed by a single set of tools. The tool sets also often support a metadata repository and a scheduling engine.IT managers facing the prospect of integrating data from multiple sources into a single repository traditionally have had to start by grappling with the build-or-buy question.There are excellent reasons to build your own ETL system. First, your in-house programming staff is invariably better acquainted with the legacy database and operating environment than any vendor could be. Second, few IT department personnel have the free time to evaluate, compare, select and install commercial tools, let alone learn to properly use them. And finally, few IT budgets today can afford the additional capital expense of the tools, especially for a first-time, proof-of-concept project.Despite the valid arguments for building your own system, vendors have over the past few years made a convincing case for buying one instead.The learning curve issue is a good example. Vendors have dramatically shortened the time it takes a developer to get up to speed with an ETL tool by integrating highly graphical and wizard-driven interfaces that guide developers through the implementation process.Today, developers can construct data flow diagrams to visually model a task and automatically extract, transform, validate and load data without writing a single line of code. And highly intuitive interfaces are simplifying the task of mapping such things as complex enterprise resource planning data to a target database.At the same time, vendors are extending the scope of their tool sets and putting an end to the days when developers had to write code to string together different functions. Products such as Oracle Warehouse Builder, for example, replace a bevy of individual point tools with a single common environment capable of modeling and design, data extraction, loading, aggregation and warehouse administration.In addition, many tools such as Warehouse Builder, IBM's Visual Warehouse, Computer Associates International Inc.'s DecisionBase and others add extensive metadata management capabilities.Tool performance isn't taking a backseat, either. Many vendors have recently added enhanced loading capabilities for the leading target databases. New loaders in Hummingbird Ltd.'s Genio Suite, for instance, significantly reduce the time required to manage large volumes of transactions.Other products, like Formation from Informix Software Inc., integrate parallelism into their architectures to take advantage of all available CPUs. By automatically segmenting a job across multiple CPUs, such tools boost performance while making more efficient use of enterprise resources. Users can even specify parallel settings on individual operators or groups of operators.Meanwhile, companies have introduced new techniques to help reduce the data processing workload, a particularly crucial consideration as warehouses sag under mountains of data and an escalating number of transactions.Products such as ChangeDataMove from BMC Software Inc. lets designers almost instantly reflect in the data warehouse incremental updates and changes in the operational transaction processing environment.By tracking the changed records during the input/output operation, the tool captures changes virtually as soon as they occur. That not only keeps target databases more current and consistent with source databases than they have been in the past, but it also allows the tool set to transform and transport to the target database only the data that has changed, thereby reducing the workload.When purchasing an ETL tool, a number of issues are worth considering. Will the tool support the key legacy databases and data types your application requires? Will the tool support performance levels that could easily reach hundreds of transactions per second without adversely affecting traditional operational performance?Take a close look at the architecture of the tool as well. Tools that offer reusable modules, for instance, often can save considerable development time and effort. If you have already designed a custom in-house process and are looking to replace it, examine where the current commercial product can improve efficiency. Will the purchase of this tool force you to revamp an established process? And if so, what are the costs involved?At the same time, look for tools that offer performance enhancements, such as loading files into memory for lookup or adding parallelism to maximize performance.Another issue to consider is the support a tool vendor can provide. Although vendors have made their products much easier to use, the tools are still software and require a lengthy learning curve.One way to prevent the learning curve from undermining your project is to ask the vendor, as a condition of the sale, to provide someone who understands how to design with the tool. Such a consultant will come at a high price, but the investment will be worth it if it guarantees success.Although vendors continually have enhanced their products, a number of factors in your legacy systems will affect how well a tool extracts, transforms or loads data. The availability of network bandwidth, for example, is one issue. Bandwidth bottlenecks can easily slow down the performance of the most optimized tool.The volume of data to be moved and collected in a project also is crucial. With the rise of the Web and electronic commerce, businesses are continually surprised at how much data they can collect in a short period of time. Yet the larger the prospective data warehouse, the longer and more complex the process of extracting, transforming and loading data.Other issues such as hardware configurations'the amount of memory and speed of disk performance on the source and target systems'also can influence the speed of the process, as will the type of interconnect used to access source and target data. Will the system have native access or use Open Database Connectivity or some other interface?Finally, the construction of the target database itself also will have a major impact on data transfer. Are there constraints in the data warehouse in terms of the complexity of the data structure or number of indices? And has the database been tuned to maximize performance?The task of extracting, transforming and loading the data from legacy sources into a central repository doesn't grow any simpler as the amount of data you collect skyrockets.But the good news is that there is a wider choice of more capable and comprehensive tools today than there ever has been.The key to selecting the right tools will lie in your own understanding of your operating environment and the unique requirements you face as you build your data warehouse.XXXSPLITXXX-