Tests of seven enterprise information integration suites reveals a top pick with easy configuration, caching capabilities and exceptional data integration.
Most of the products--Cincom's is the exception--are J2EE applications, and we discovered to our chagrin that Microsoft does not let vendors distribute its SQL Server JDBC client with their products. You can download it for free from Microsoft (go to Microsoft.com and search for JDBC), but it can't be included with J2EE apps. Cincom provided a SQL Server adapter, but its Tiger failed miserably at dealing with SQL Server 2000. Seems its adapter is broken, and we needed a Cincom-provided work-around to integrate the Microsoft RDBMS into its system. We weren't impressed with the flubbing of such a core feature nor with the work-around, for which we had to construct a SQL query just to add connectivity to the system.
Metadata support levels vary and depend entirely on the JDBC driver used by the platform to access the RDMBS. Snapbridge's JDBC driver is not a true JDBC implementation; it's a CSV-based interface, which means that no metadata--information about columns, data types, keys and constraints, for instance--can be retrieved from RDMBS data sources. The CSV format gives you only field names and values, and doesn't tell you that FieldA is a string or FieldB is a decimal. That information is important when you join tables and for transforming data to other formats. We could swap in a different, full-fledged JDBC driver.
Nearly every product we tested--the exception was Ipedo's Information Hub--let us standardize on an XML interface, while supporting our legacy reporting applications, Excel 2003 and Crystal Reports 9. Ipedo does not provide ODBC connectivity, and discussions with the company on this subject yielded an XML-centric approach to solving the problem. Crystal Reports 10 and Excel 2003 support XML as a data source, and Ipedo suggested that the lack of ODBC support would be less of a problem as companies migrate to the latest versions of both products.
Although the other entries all supported ODBC, they did so with varying degrees of implementation ease and a variety of architectures. Composite, IBM and Cincom provide client software that make deployment a breeze. Administrators familiar with installation of ODBC client drivers and configuration of DSNs (data source names) will find the process familiar and simple. MetaMatrix and XAware use third-party OpenRDA ODBC drivers, and to make things more complicated (and pricey)--XAware requires a separately purchased JDBC server before client connectivity could be achieved. Configuration was tricky at times for both, requiring some extra work--not to mention extra cash--to provide client-side ODBC access. Snapbridge requires an ODBC-to-JDBC bridge and suggested we use EasyLink's product.
XML and SOAP support also varied widely, with the XML-centric products providing the most flexible feature sets. Composite offers the most comprehensive set of XML and SOAP features of the three RDBMS-based products, providing the point-and-click SQL-to-XML/SOAP abilities we craved. However, data transformation using Composite's modeling tool, Composite Studio, was less flexible and required manual XSL creation, a process we dread.
Snapbridge's XML Studio is a sexy, easy-to-use tool that gave us everything necessary to achieve drag-and-drop XSL functionality, while Ipedo's XQuery Builder made creation and mapping of columns to XML a breeze. Cincom's solution to the XML and SOAP problem is to use its TotalXML product with NetBeans' IDE. Too developer-oriented, but effective.
Client access over SOAP was all over the map, with security provided through proprietary XML elements or HTTP Basic Authorization rather than via WSSE (Web Services Security Extensions) 1.0. IBM was the standards-based standout here, though its SOAP producer support of Web services is provided separately, over WebSphere Application Server, and a developer must take advantage of IBM's support for WSSE 1.0. We were disappointed by the other vendors' lack of standards support, but most said they would do better in future versions. We did enjoy the flexible SOAP APIs provided by MetaMatrix, XAware and Ipedo, which let us create specific SOAP operations on a per-query basis and perform ad hoc queries using a standard SOAP operation. Ipedo required us to include XQuery, an XML-based human-readable query syntax derived from XPath 2.0, while the others let us submit straight SQL 92-compliant queries. We like SOAP's flexibility and hope to see support in other offerings sooner rather than later.
Caching capabilities also set products apart. We were most pleased with Composite's model, which includes automated invalidation on a user-configurable basis, and found IBM's use of MQTs (materialized query tables) simple and effective. MQTs are RDBMS tables that mirror a data source's structure but reside on the EII server, making queries and general access to that table faster. Caching support and maturity seemed to fall in line with the products' RDBMS versus XML origins: RDBMS products support caching far better than products on the XML side. XAware supports caching, for a fee (do you see a theme here?), but the configuration must be defined manually in a configuration file and then associated using an esoteric setting in a dialog cleverly hidden in the administrative console.
Ipedo's caching capabilities are similar to XAware's, using an XML collection or document in much the same way IBM or Composite use an RDBMS table. XAware provides caching of XML only and does not allow for direct caching of SQL result sets; this functionality is provided by shoving a SQL result set into an XML document and caching the XML. XAware says it will provide full support of SQL result set caching in a September release. MetaMatrix's caching is limited to small lookup tables, making caching larger tables a real pain, involving proprietary SQL extensions within queries. Cincom does not provide any caching, though the company says it could offer such capabilities with its professional services ... for a fee.
For some products, notably those from Composite and Snapbridge, caching was easy to manage and let us automate cache invalidation, forcing a refresh of the data from its original source. Other products offer this capability but require manual creation of job schedules using cron or Windows scheduler. Snapbridge provides an interesting twist on caching that requires its proprietary XML-based XRAP language to indicate which cache a particular query result would be stored in--one minute, two minute, five minute, one hour. Composite and IBM simply cache results and let individual cache contents be invalidated.
Riddle Us This
We discovered that though some products (Composite's and IBM's) provide exemplary--and automated--support for query optimization, others (Ipedo's, Snapbridge's and XAware's) do not optimize queries at all, and MetaMatrix's architecture requires manual manipulation of queries to eke the best performance out of the system. Composite and IBM offer detailed query plans, and we were pleased with IBM's explanation of the join algorithms available. The architecture of the XML-based products makes it nearly impossible for them to take advantage of native database capabilities to join disparate sources, though XAware does provide basic optimizations, such as query-join blocking and push-down. Mechanisms for trimming down result sets in Snapbridge and Ipedo to reduce load on databases are somewhat painful, requiring the creation of XQuery code (Ipedo) and XSL (Snapbridge) to do what Composite and IBM do easily.
As you may have guessed, Composite Information Server (CIS) takes our Editor's Choice award, with IBM's DB2 Information Integrator (DB2II) coming in second. MetaMatrix made a decent showing as well. See our pricing chart for the product's list pricing as configured for our tests.
The Agile ArchiveWhen it comes to managing data, don’t look at backup and archiving systems as burdens and cost centers. A well-designed archive can enhance data protection and restores, ease search and e-discovery efforts, and save money by intelligently moving data from expensive primary storage systems.
2014 Analytics, BI, and Information Management SurveyIT’s tried for years to simplify data analytics and business intelligence efforts. Have visual analysis tools and Hadoop and NoSQL databases helped? Respondents to our 2014 InformationWeek Analytics, Business Intelligence, and Information Management Survey have a mixed outlook.
Join InformationWeek’s Lorna Garey and Mike Healey, president of Yeoman Technology Group, an engineering and research firm focused on maximizing technology investments, to discuss the right way to go digital.