There are many proven business intelligence and analytic tools on the market than can transform raw data into meaningful information. Because this process can be complex and involve large volumes of data however, it makes sense to use the right technologies at each step in the process … tools and techniques that combine well to deliver the fastest, most accurate results for business decision making, and make the process of metadata management and report design simpler and more efficient.

Founders of modern data warehousing like Ralph Kimball have long recommended the external preparation of big transaction data outside the database and ETL tool layer, in sequential (flat) files. This approach removes a regular, resource-draining overhead from systems designed for queries and integrations, and provides a central, platform-independent way to exchange and manage data.

The same approach for business intelligence (BI) tools — also data franchising or data wrangling — has been espoused for years by experts like Richard Sherman for logically related reasons; see Data Franchising on Information Management. In addition to better performance, Sherman points out that pre-staging data to be visualized avoids the inherent metadata complexity, redundancy and synchronization problems of performing transformations in the BI layer.

One of the best examples of BI optimization through technology combination is the use of the IRI CoSort product — or the IRI Voracity data management platform powered by CoSort — to wrangle, or prepare, high volumes of transactional data for data visualization outside SAP Business Objects (aka BOBJ). Business Objects delivers visual information on cumulative financial performance, strategy management and business intelligence, typically for analytic purposes. CoSort Sort Control Language (SortCL) data transformation program simultaneously filters, sorts, joins, aggregates, segments and reformats data into subset CSV and other popular ‘feed’ files outside the BI layer.

As you would expect, staging data in flat files with CoSort — especially within a modern, metadata-managing, data-federating GUI like the IRI Workbench built on Eclipse — avoids the data integrity and reconciliation issues Sherman describes. Moreover, when high volumes of data are prepared in advance with CoSort, Business Objects users can get their business intelligence information in at least half the time it would otherwise take (using Business Objects alone).

A simple laptop-based benchmark running Business Objects under Windows XP on an Intel® Core™ CPU M380 @2.53GHz with 3 GB of DDR3 memory bears this out. When the necessary data transformations of sorting, joining, and aggregation were performed in Business Objects for creating a report based on source data in 100MB and 50MB flat files, the entire job took 70 seconds:

Accelerating SAP’s Business Objects with SortCL is straightforward, especially if you are familiar with Eclipse, or at least the structure of your source data. The IRI Workbench GUI for CoSort users is a convenient, graphical environment for discovering, defining, managing, and transforming data in flat files and/or data in RDBMS tables with SortCL. But whether you define the SortCL data definitions or job specifications in text files or with the GUI, the self-documenting nature of the 4GL makes it easy to create and maintain both forms of metadata. The free GUI merely automates many of these tasks:

The same transforms run in this one CoSort’s Sort Control Language (SortCL) program took 23 seconds on the same laptop, with the results available to multiple applications. In this case, the Business Objects user who created the same report (above in 70 seconds) only needed another 6 seconds to run this report, meaning that the start-to-finish time using CoSort was only 29 seconds.

For more information on using CoSort for standalone or accelerated business intelligence, please see Business Intelligence Solutions on the IRI website found here http://www.iri.com/solutions/business-intelligence and the other articles in the BI section of the IRI blog site.