Introducing Coglin Mill’s RODIN Data Warehousing Solution:

Supporting Middle and Large Market Data Warehouse Success

by:

William H. Inmon

Data Warehousing has provided tremendous benefits to businesses worldwide. Many companies have yet to realize these benefits. Now there is a solution for many of those companies. This solution supports the creation and management of a data warehouse, dependent data marts and operational data stores. It also enables the subsequent business intelligence implementations required for analytical success. This solution is RODIN for the IBM eServer iSeries. It is important to note that these servers, formerly known as the AS/400, are in wide use by a growing number of businesses. The success of these servers is a testament to their operational excellence.

RODIN, by Coglin Mill, brings analytical excellence to these servers. It will soon extend that analytical excellence to the IBM eServer pSeries, formerly known as the RS/6000. Taken together, the market for data warehousing solutions the IBM eServer line represents is truly massive. This market has remained largely untapped, until now.

RODIN provides the “missing link” in the chain of success for IBM eServer owners and users. This link is the bridge between operational systems and Business Intelligence (BI) based analysis. The ability to look over extended periods of time to understand customer behavior is just one example of the power of BI. RODIN brings the power of BI to this massive IBM eServer market at a critical time. ERP and CRM implementations require extensive data warehouse success to provide meaningful analysis. RODIN is critical to these customers in their efforts to build BI success.

RODIN is the logical “next step” in the evolution of the Corporate Information Factory (CIF) for thousands of businesses. The pressure to evolve the CIF is greater than ever for these companies. Garnering lasting advantage from ERP & CRM requires long-term analysis in the form of a data warehouse. RODIN provides the means to accomplish this important corporate mandate. Those companies willing to extend their CIF in this way will reap significant and lasting rewards.

Bill Inmon, October 1, 2001

PRODUCT OVERVIEW

RODIN by Coglin Mill is data asset management software that provides advanced data extraction, transformation and loading (ETL) capabilities. The product was originally developed on IBM’s award winning AS/400 platform and illustrates some of the advantages and forward thinking that went into the AS/400. It is about to be released in a Unix based version as well. Both versions use a Java client to provide a graphical interface that makes the design, management and execution of ETL processes logical and easy. Because of RODIN’s history on the AS/400 platform as well as a solid understanding of data management fundamentals by its creators the software is exceptionally well suited for performing complex transformations in relatively short order. It utilizes IBM’s DB2 Universal Database (UDB) technology to manage its metadata. RODIN stacks up well to other ETL packages and because it can leverage the power of the iSeries (AS/400) or pSeries (RS/6000) platforms natively it may be a strong choice for organizations that need ETL to be more robust than typical NT and ODBC based products.

PRODUCT FEATURES

The leading features in the product are found in it’s use of High Level Language, DB2 and an exceptional Java interface to provide the user with a high performance and intuitive process for building and populating data Corporate Information Factory (CIF) elements. Key features include:

· High Level Language (HLL) Transformations - Unlike other ETL software RODIN is not an SQL code generator and does not rely on an interpreted scripting language to apply its rules and logic to data transformations and loading. Instead it maintains the logic for transformation rules in an “active metadata” database and converts the rules in natively compiled code on the underlying platform. This HLL code is compiled natively on the ETL engine platform. That platform has been the AS/400 (IBM eServer iSeries) but is expanding to other environments.

· Active Metadata - RODIN is designed as an object based system. This means that virtually all of the items that represent a transformation (e.g. data elements, rules etc.) are stored and documented in the RODIN database. RODIN utilizes an open relational database (IBM’s DB2) to store this repository. The items in this repository constitute what Coglin Mill calls “Active Metadata”. The concept here is that all aspects of a transformation are stored “actively” and can be dynamically updated as the user modifies the transformation logic. Metadata in RODIN is not a by-product of the ETL definition. In the RODIN Active Metadata concept the metadata drives the creation and re-creation of processes, tables and other warehouse entities ensuring that the warehouse and metadata are always synchronized. RODIN currently makes this Active Metadata accessible via the client interface, standard reports, HTML publication and via standardized IBM DB2 tables. An XML based method for exporting metadata will be available later in 2001.

· Free Format Text - All objects in RODIN have the ability to track an unlimited amount of free form text for the purpose of describing and documenting the object. This text is not only available in the graphical interface, but is included in published metadata and reports.

· Multiple Instances - RODIN can operate with multiple separate instances on a single iSeries (AS/400) server. It utilizes iSeries (AS/400) libraries allowing multiple separate instances without conflicts. This compares favorably to NT DLL based environment where DLL instancing can be a problem. The iSeries (AS/400) supports a variety of SMP and MPP configurations up to 32 x 24 way processors with terabytes of high availability storage and memory.

· Individual RODIN Sub-Systems - RODIN subsystems can be assigned different processing priorities assuring that the impact of RODIN on the iSeries (AS/400) is managed. This also allows certain transformation processes to take or yield precedence to other RODIN transformations as well as other iSeries (AS/400) applications and jobs.

· Rules Creation - Rule creation does not require the use of a scripting or macro language like BASIC. Rules are created through a graphical point and click interface and can easily be managed by a data or business analyst with no programming experience. The rule editor interface validates rule syntax and dynamically builds pick lists based on context. In spite of this ease of use the ability to create extensive and complex rules is robust and because the rules are translated in the HLL they execute on the data with a high level of efficiency. Rule logic is also captured as part of the active metadata and can be reviewed, exported and documented with ease.

· Globally Defined Rules for Source Tables - RODIN allows the user to define a series of transformation rules and associate them with a specific source table. This makes the rules appear by default whenever the source table is used in an extraction definition. Globally defined rules save the user time when creating extracts and assures consistency in the application of certain source data to the transformation process.

· Virtual Elements - Extraction design includes the ability to create and maintain virtual elements. These derived data values can be based on a combination of one or more source columns, constants and expressions. Virtual elements allow the user to define data that may or may not be directly available from the source tables. They can also be used to join tables where no logical join criteria exists making RODIN capable of relating tables that standard SQL syntax can not join.

· Extensibility (External Program Calls) - While this is a feature that is found in most high end ETL offerings it’s worthy of mention. Extractions can include external programs to add extensibility to the transformation logic. The interface definitions of which (i.e. input and output parameters) are stored as active metadata and may be managed globally. This assures that every time an external program is called it is used in the same standardized manner.

· Suspense Processing (Error Recovery) - RODIN automatically tracks errors during the transformation process and gathers the failed rows in a duplicate of the source table. A full error report with a detailed description of why rows were rejected is generated. This information is stored historically in the process metadata and may be reviewed at any point in the future. It also allows the transformation to be re-executed on the failed rows after corrections have been made. This can be an iterative process allowing the user to resolve all errors in successive passes on the source data.

· Source Table Joining - RODIN handles the joining of source tables in a manner, which is more robust and efficient than traditional SQL joins. It allows the user to graphically join tables together and then leverages the underlying HLL of the server to perform the actual data access. This allows the user to define joins between tables that use partial keys, retrieve records prior or after an exact match or a variety of other complex selection capabilities. RODIN also provides exceptional auto-linking capabilities making it easy and quick to get tables joined. Source table joining is performed using a well-engineered graphic interface with drag and drop functionality (see figure 1).

Figure 1 (Source table joining in RODIN)

· Source Table Interrogation - RODIN has advanced and well planned features to aid in the creation of metadata concerning data sources. This includes the automatic retrieval or creation of the following metadata elements:

· Primary Keys

· Unique Indices

· Column & Table Names (10 char system name)

· 50 Char Descriptions (tables & columns)

· 3 x 20 char descriptions for column headings

· 30 char SQL Names (columns & tables)

In order to avoid empty metadata fields that may be needed and useful RODIN will auto populate several of these fields with data from other information found in the source data structures. Users can override these default values.

· Source Table Flagging - Source tables can be flagged for use as an extract source, a lookup source or both when they are introduced and interrogated. RODIN analyzes tables to be used for lookups and warns the user if the appropriate indices don’t exist. This can prevent failures in the subsequent ET processes and represents the kind of forward thinking that went into the product design.

· Special Date Handling - RODIN auto detects numeric date fields and allows them to be managed when the source table is defined. This includes customizable handling of non-Y2K compliant data. The date field handling is separate and in addition to ET rules which can be set up later. This advanced date handling can evaluate for valid date values iteratively and take action on invalid dates several times, if required, to correct problems in source data.

· Encoded Vector Indexes (EVI) - EVI is an IBM innovation that dramatically improves performance for data warehouse centric query operations. It’s currently available on the iSeries (AS/400) version of DB2 and will be available on Unix versions in the near future. RODIN manages the indexes in its active metadata tables, which allows it to capitalize on EVI and other index technologies in an automatic and robust manner. This can dramatically improve database I/O.

PRODUCT LINE FEATURE SETS

RODIN offers a variety of features to users seeking to create data warehouses, data marts and operational data stores and manage the process of populating and maintaining them. Some of these features exist because of the products birth in the AS/400 world. However, most of these capabilities are likely to survive the port to Unix and will make RODIN one of the more interesting players in the cross platform ETL market. Key features include:

· Exceptional Rule Processing Performance

This is primarily due to the use of HLL for the implementation of rules. On the iSeries (AS/400) this language is RPG (C on Unix). These languages are native to the platform and there are not interpreted like SQL and scripting languages resulting in the very best possible performance. No knowledge of any programming language or SQL scripting is required.

· Easy Rule Building and Maintenance

Also because of HLL the ability to point and click through the creation of reasonably complex rules (see figure 2) is a key benefit in RODIN. The rules are stored as metadata and then compiled into native code. This means that rule building can include some very interesting and complex capabilities without requiring the user to know a scripting language.

RODIN includes a series of highly evolved built in functions to assist in the application and processing of rules. These include an advanced “Convert to Numeric” operation that not only converts string number value to numeric values, but also can automatically handle formatting characters like commas, embedded period and currency symbols. Other approaches utilizing scripting languages usually require several lines of code to solve these types of problems. Other built in functions include advanced date arithmetic capabilities including the ability to derive last day of month, test for leap year and a variety of standard date-time, numeric and string functions.

Figure 2 - Rule Building (without a scripting language)

PRODUCT ARCHITECTURE

The RODIN system is comprised of two main parts. They include the Client and Server software. The mechanisms for maintaining communications between them are industry standard and current technology. The RODIN client uses iSeries (AS/400) system calls and JDBC to control the OS and DB2 tables where the active metadata is maintained (see figure 3a)

AS/400 - 2

Figure 3a (RODIN Architecture)

In a future release the System Call connection will be implemented as a Java Remote Method Interface (RMI) for cross compatibility with Unix. The iSeries (AS/400) version can utilize several iSeries (AS/400) data sources and targets. Access to external data is currently being facilitated through 3^rd Party products for the iSeries (AS/400) including:

· IBM DataJoiner - Makes remote data sources like Oracle or SQL Server appear as native DB2.

· IBM Warehouse Manager - Provides remote procedure and SQL call capability across virtually all IBM and non-IBM platforms and databases.

· IBM DataPropagator - Provides real-time data copying from any DB2 data sources onto the iSeries (AS/400).

Similar functionality will be included in the pSeries (RS/6000) version, but a more “open systems” approach will provide native driver interfaces to other database types. Note that ODBC does not show up. While ODBC may be included farther down the stream with some 3^rd party products if RODIN is operating against DB2 it uses native connectivity and should easily outperform products bound to ODBC based connections.

The RODIN server engine utilizes the iSeries (AS/400) or pSeries (RS/6000) to provide a potential backend engine for facilitating intelligent data sourcing and targeting of DB2 data sources. This part of the architecture has the potential of being “unplugged” from the user interface to act as an advanced data provider for other ETL processes that need high performance access to DB2 tables on the iSeries (AS/400) or pSeries (RS/6000) platforms. This also has the advantage of pre-scrubbing and joining the data so that transformations are clean and easy on the front end. Of course such processing would have all of its rules available for review via the active metadata tables in DB2 making the metadata capture for this process straightforward and complete. Figure 3b illustrates how the RODIN server engine might “plug in” to an external ETL process managed by a foreign system.

Figure 3b (possible use of RODIN Server Engine)

Note, RODIN via one of the IBM middleware products shown above can source either by the external ETL process or non-DB2 data.

RODIN System Components:

· The RODIN Client

The client software is written in Java and offers a well-engineered graphic interface to virtually all of the necessary functions for creating, maintaining and executing ETL. The client requires a minimal amount of disk space (around 20MB) and capitalizes on standard TCP/IP communications. It finds it’s way around complex firewall configurations and does not require a connection to a database or database client software. The RODIN client doesn’t directly access production data at either end of the process. It manages a variety of server-based programs, commands, and JDBC based connections to Active Metadata tables. The client’s ability to mange server side processing allows it to leverage the power of the server to perform complex and resource intensive tasks. This makes the client a low overhead application allowing it to operate reasonably well over low speed and remote connections.

· The RODIN Server Software

RODIN leverages the power of the iSeries (AS/400) or pSeries (RS/6000) to manage and execute ETL and data definition tasks. This illustrates the product’s ability to perform complex transformations that can’t be designed using SQL syntax. RODIN’s HLL concept makes the power and the performance of its transformation logic exceptional.

· Security

Access security is managed through the underlying server operating system and requires no configuration that varies from standard system administration. Specific levels of task-oriented security can be managed with the RODIN graphical user interface ensuring only authorised personnel have access to functions as well as data.

· Interface and Metaphor

The RODIN user interface (see figure 4) is based on concepts from the CIF model making it easy to build and manage transformations that lend them selves to data warehousing. The user interface known as the “graphic integration manager” includes links to related tools like Erwin, and IBM Warehouse Manager, Brio, and Cognos. It includes the ability to customize the interface to user preferences including font attributes for different parts of the system. The more complex functions in RODIN are facilitated through graphical wizards and designers. For example RODIN makes the creation of new target data sets easy by dragging and dropping data elements from a predefined global list of data element to create new target tables. This includes the ability to define primary and secondary keys, user authorities, descriptive metadata and automatic summaries.

The metaphor for defining and managing extractions, transformations and load logic is one of the strongest features of the RODIN system. It lends itself to users who do not have a background in HLL programming or SQL. The interface is designed with appropriate and relevant graphical elements that make navigating easy to learn and remember.

The system concept allows the establishment of multiple environments e.g. development, test and production. It also allows distinct source, target and transformation objects to be maintained and grouped independently of each other. Because the system is object based RODIN allows the user to copy and move objects between the separate environments.

Figure 4 (The RODIN Client Interface)

PRODUCT POSITIONING

RODIN is positioned for flexible and scalable utilization in the iSeries (AS/400) environment. The forthcoming pSeries (RS/6000) RODIN version was also evaluated architecturally. Both product versions will support the features already described with minimal limits or differences.

· Data flows, including sources, targets and uses

RODIN supports data flows within and between iSeries (AS/400) systems utilizing various data formats. Flat files, sequential and related files as well as DB2/400 are all supported sources. Targeting DB2 provides the ability to create data warehouses, data marts and operational data stores on the iSeries (AS/400) or pSeries (RS/6000) platform. The customer base of IBM eServers, iSeries and pSeries, often specialize their IT resources to support those platforms. RODIN offers these customers the opportunity to develop analytical success on the same platform as their transactional systems occupy. The scalability and logical partitioning capabilities of these platforms makes this possible. CPU, storage and back plane resources can be increased dramatically and partitioned to support both of these environments. Many customers utilize a multiple platform strategy, often using several iSeries (AS/400) or pSeries(RS/6000) servers to accommodate their mixed needs. RODIN supports a variety of strategies and hybrids for IBM customers.

· Metadata management, sharing, serialization and exchange with other tools

RODIN manages metadata via DB2. This enables metadata access and sharing with a variety of business intelligence tools. It also allows extensive searching and replication capabilities for advanced users. The key to this repository is the standard relational storage format is utilizes. Many ETL tools utilize a proprietary repository format that severely limits external access. Additional products, often from third parties, are required to provide complete access. The other feature unique to RODIN is the depth of metadata it provides. The text capture capability of the user interface empowers very deep metadata development. This is what makes the difference between a data dictionary and an information catalog or encyclopedia.

· Scalability

RODIN leverages the scalability of both the iSeries (AS/400) and pSeries (RS6000) without limitation. This is much more sophisticated than the traditional CPU sharing algorithms found in standard SMP environments. The entire IBM eServer line is architected to provide complete tuning, re-configuration and recovery facilities. This eliminates the majority of guesswork when implementing an analytical environment. Critical time is not lost to the performance tuning and physical portioning of these systems, as is often the case on less mature platforms. This time can be invested in the business processes and the acceleration of the modelling effort.

· Modeling, use of predefined models and iterative development

Modeling efforts often prove invaluable in promoting rapid design and implementation. The iterative nature of data warehousing relies on solid and complete models in order to generate valuable information assets. These models are driven by a combination of business process evaluation and data modeling skills. The goal is the creation of prototype models and templates upon which to base the construction work. RODIN promotes this accelerated approach by allowing several stages or phases of models to exist and be connected throughout the design process. These models are often captured, or imported from very robust modeling and prototyping tools such as Computer Associate’s ERWIN. This allows many key people in the customer site to be involved at the best level for their skills and knowledge. It also empowers parallel design and development strategies for consultancies engaged in this process for customers. And finally it facilitates an iterative development environment, which RODIN fully supports.

· Alignment with Business Process / Skill Set Utilization

RODIN allows the successful IBM eServer customer to extend that success without imposing additional barriers in the form of new systems or practices. This is because the same business practices that support the eServer applications also provide the critical instructions, business rules and definitions required to construct analytical success with RODIN. The RODIN Client Interface provides business and data analysts with a code free environment for capturing and translating complex business rules and definitions. No basic like coding or scripting is required of a job designer at any level of job complexity. This true ease of use promotes adoption by business analysts and others responsible for the operational functions of the IBM eServer environment. The gap that often develops between business analysts and ETL programmers is not a threat when using RODIN. This ensures consistent application of standardized business rules and definitions.

· Support levels required / provided

RODIN requires little extra support over and above normal operational support provided by IBM, IBM partners and the internal systems administrator responsible for the iSeries (AS/400) environment. RODIN respects and extends the standard controls found in OS/400 thereby relieving the pressure of additional and often incompatible security and maintenance schemes of systems administrators. RODIN does require usage and configuration support. This support is currently available from Coglin Mill as well as its partners. Initial training of 4-5 days conducted during implementation is sufficient to provide users with the skills to develop their ETL environment with RODIN. Coglin Mill also integrates basic DW / CIF training content into the adoption process to support new DW projects and participants. Coglin Mill, as well as its partners, provides ongoing product usage support. Users often find that additions, upgrades and platform changes are managed as part of their lifecycle planning. In this way, RODIN integrates seamlessly into the iSeries (AS/400) environment in existence in customer sites worldwide.

CORPORATE INFORMATION FACTORY

Figure 5: CIF Physical View

By: Bill Inmon, Claudia Imhoff, Jonathon Geiger and Dan Meers

For a detailed discussion on the Corporate Information Factory and how RODIN addresses the architectural requirements see the recent white paper from Claudia Imhoff.

Figure 6: RODIN Positioning in the CIF

©2001 by BILLINMON.COM

RODIN is positioned as a traditional Integration/Transformation (Extract, Transform, Load) tool in the CIF. This positioning is extended within the IBM eServer platforms by virtue of RODIN’s feature set. Unlike generic ETL tools, RODIN provides seamless end-to-end data integration. This is made possible by RODIN’s complete lack of programming and resulting High Level Language functionality. It is important to note that RODIN extends design and integration work all the way through the CIF to the Information Delivery process.

It is not necessary for the RODIN customer to build the data warehouse and then sink into a morass of SQL statements and shell scripts in order to deliver the integrated information in their data warehouse for Business Intelligence usage. RODIN’s seamless approach provides complete re-use of the Active Metadata and ETL designs. This delivers unparalleled ease of use and speed for BI success. It is this unique and powerful architectural approach that positions RODIN on both sides of the data warehouse, ensuring rapid BI success by allowing the same RODIN team to source and target their integrated data. This is essential for customers who are concerned with immediate data warehouse success and with the prospect of many iterations of data warehouse development. RODIN customers find that each new iteration or subject area addition of the data warehouse becomes easier to complete because they are using RODIN for construction and delivery. Process management also benefits from the tight integration RODIN enjoys with the IBM eServer platforms.

Figure 7: RODIN Active Metadata and the CIF

The additional feature, also a RODIN exclusive, Active Metadata Management, ensures the seamlessness of integration from transactional source systems through BI and Analytical System information delivery. This is due as much to the broad capacity for different metadata types as to the method of capture and storage. RODIN is architected to accept all three major types of metadata: Technical, Process and Business. Technical metadata is captured through automated discovery of CASE and other models, automatically generated as designs and processes are defined in the RODIN interface, and catalogued into the standardized tables of its IBM DB2 repository. Process metadata is continually monitored and captured as processes execute and terminate. The most important distinction for RODIN is its extremely robust Business metadata. This category is often very limited in generic ETL tools. RODIN emphasizes Business metadata at every step of development by including unlimited text entry for objects and processes. These form the basis of the Business Information Catalog that a robust metadata repository must provide. This exclusive feature of RODIN sets is apart from the generic tools.

INTEGRATION WITH OTHER PRODUCTS & SERVICES

RODIN is designed to integrate directly into the DB2environment. It allows for metadata based integration with several key CIF components. It also allows for downstream integrations to occur.

· Current product interaction (APIs, Plug-ins, etc.)

RODIN enjoys deep integration with the IBM eServer iSeries (AS/400). This integration extends into the DB2/400 database system and its optional management console, Warehouse Manager from IBM. This integration assures customer success from the outset by providing a complete business catalog of data warehouse components for administrators. Warehouse Manager and its sibling products including DataJoiner, DataPropagator and DB2 OLAP all integrate with RODIN to provide total data warehouse and business intelligence success for IBM eServer customers.

· Active Metadata Management

The creation of the Active Metadata concept by the RODIN team at Coglin Mill is a revolutionary step forward for enterprise metadata management. RODIN is the first data warehouse system to provide total command of the data warehouse from the metadata repository. This is extremely important because the metadata repository provides the foundation for enterprise Business Intelligence delivery.

· Integrated Service Delivery via IBM, IBM Partners and Consultants

RODIN enjoys the support of IBM and IBM partners and consultants worldwide. This provides the IBM customer with complete services to ensure adoption to completion success. IBM customers have long enjoyed the assurance of IBM certification to validate the business process of IT investing. Consultancies are increasingly turning to Coglin Mill in order to gain access to RODIN for their clients. These consultancies recognize the power of RODIN’s interface, Active Metadata and IBM eServer integration features as critical to their client’s success.

· Jump Starting Analytical Success using RODIN with Embedded Metadata

RODIN’s ability to capture detailed metadata from CASE tools such as Computer Associate’s ERWIN provide a rapid project start for competent data and business modellers. This feature is critical to middle market customers who are quite competent at modelling their business processes and at using the highly intuitive RODIN interface to integrate their data but may lack programming resources. There is no reason these customers should be forced to invest in specialized programming to enjoy the benefits of their Corporate Information Factory. Programming resources are better utilized in the source system side of the CIF. ERWIN and other models provide RODIN with embedded metadata that provides the basis for an immediate design and population of the data warehouse. Universal Data Models available from many respected industry sources can be easily integrated into the RODIN environment to further accelerate data warehouse success. Coglin Mill provides access to many of these models directly.

SUMMARY

RODIN is architected for ease of use and reuse. It provides users with arguably the most powerful and friendly interface yet developed. This interface performs very well and works in a distributed environment. The use of "Active Metadata" supports re-use of many elements within the ETL process. This reusability positions RODIN in several key areas of the CIF. This positioning extends the adoption of RODIN in the enterprise. RODIN is also differentiated by its native intelligence about DB2. The ability to natively source and target DB2 is an exclusive feature in the ETL environment. The use of DB2 to house all RODIN Active Metadata ensures access and exchange mechanisms can utilize this repository.

We find RODIN to be the superior selection for IBM eServer customers undertaking data warehouse and related projects. The robust nature of RODIN in the Corporate Information Factory enables its users to accelerate their success. RODIN also empowers users to iterate their success across subject areas, architected distributed warehouses and platforms. Open platform users will benefit from a Linux or comparable version of RODIN.

BILLINMON.COM, October 1, 2001

For more information :-

Dan Meers

BILLINMON.COM

Email: dmeers@billinmon.com

Phone: (614) 846 1885

Coglin Mill Web Site: www.coglinmill.com

Email: rodin@coglinmill.com

Sales Inquiries: 1-866-RODINDW