0 Infobright Puts a Clever Twist on the Columnar Database

It took me some time to form a clear picture of analytical database vendor Infobright, despite an excellent white paper that seems to have since vanished from their Web site. [Note: Per Susan Davis' comment below, they have since reloaded it here.] Infobright’s product, named BrightHouse, confused me because it is a SQL-compatible, columnar database, which makes it sound similar to systems like Vertica and ParAccel (click here for my ParAccel entry).

But it turns out there is a critical difference: while those other products rely on massively parallel (MPP) hardware for scalability and performance, BrightHouse runs on conventional (SMP) servers. The system gains its performance edge by breaking each database column into 65K chunks called “data packs”, and reading relatively few packs to resolve most queries.

The trick is that BrightHouse stores descriptive information about each data pack and can often use this information to avoid loading the pack itself. For example, the descriptive information holds minimum and maximum values of data within the pack, plus summary data such as totals. This means that a query involving a certain value range may determine that all or none of the records within a pack are qualified. If all values are out of range, the pack can be ignored; if all values are in range, the summary data may suffice. Only when some but not all of the records within a pack are relevant must the pack itself be loaded from disk and decompressed. According to CEO Miriam Tuerk, this approach can reduce data transfers by up to 90%. The data is also highly compressed when loaded into the packs—by ratios as high as 50:1, although 10:1 is average. This reduces hardware costs and yields even faster disk reads. By contrast, data in MPP columnar systems often takes up as much or more storage space as the source files.

This design is substantially more efficient than conventional columnar systems, which read every record in a given column to resolve queries involving that column. The small size of the BrightHouse data packs means that many packs will be totally included or excluded from queries even without their contents being sorted when the data is loaded. This lack of sorting, along with the lack of indexing or data hashing, yields load rates of up to 250 GB per hour. This is impressive for a SMP system, although MPP systems are faster.

You may wonder what happens to BrightHouse when queries require joins across tables. It turns out that even in these cases, the system can use its summary data to exclude many data packs. In addition, the system watches queries as they execute and builds a record of which data packs are related to other data packs. Subsequent queries can use this information to avoid opening data packs unnecessarily. The system thus gains a performance advantage without requiring a single, predefined join path between tables—something that is present in some other columnar systems, though not all of them. The net result of all this is great flexibility: users can load data from existing source systems without restructuring it, and still get excellent analytical performance.

BrightHouse uses the open source MySQL database interface, allowing it to connect with any data source that is accessible to MySQL. According to Tuerk, it is the only version of MySQL that scales beyond 500 GB. Its scalability is still limited, however, to 30 to 50 TB of source data, which would be a handful of terabytes once compressed. The system runs on any Red Hat Linux 5 server—for example, a 1 TB installation runs on a $22,000 Dell. A Windows version is planned for later this year. The software itself costs $30,000 per terabyte of source data (one-time license plus annual maintenance), which puts it towards the low end of other analytical systems.

Infobright was founded in 2005 although development of the BrightHouse engine began earlier. Several production systems were in place by 2007. The system was officially launched in early 2008 and now has about dozen production customers.

0 OpenBI Finds Success with Open Source BI Software

I had an interesting conversation last week with Steve Miller and Rich Romanik of OpenBI a consultancy specializing in using open source products for business intelligence. It was particularly relevant because I’ve also been watching an IT Toolbox discussion of BI platform selection degenerate into a debate about whose software is cheaper. The connection, of course, is that nothing’s cheaper than open source, which is usually free (or, as the joke goes, very reasonable*) .

Indeed, if software cost were the only issue, then open source should already have taken over the world. One reason it hasn’t is that early versions of open source projects are often not as powerful or easy to use as commercial software. But this evolves over time, with each project obviously on its own schedule. Open source databases like MySQL, Ingres and PostgreSQL are now at or near parity with the major commercial products, except for some specialized applications. According to Miller, open source business intelligence tools including Pentaho and JasperSoft are now highly competitive as well. In fact, he said that they are actually more integrated than commercial products, which use separate systems for data extraction, transformation and loading (ETL), reporting, dashboards, rules, and so on. The open source BI tools offer these as services within a single platform.

My own assumption has been that the primary resistance to open source is based on the costs of retraining. Staff who are already familiar with existing solutions are reluctant to devalue their experience by bringing in something new, and managers are reluctant to pay the costs in training and lost productivity. Miller said that half the IT people he sees today are consultants, not employees, whom a company would simply replace with other consultants if it switched tools. It’s a good point, but I’m guessing the remaining employees are the ones making most of the selection decisions. Retraining them, not to mention end-users, is still an issue.

In other words, there still must be a compelling reason for a company will switch from commercial to open source products—or, indeed, from an incumbent to any other vendor. Since the majority of project costs come from labor, not software, the most likely advantage would be increased productivity. But Miller said productivity with open source BI tools is about the same as with commercial products: not any worse, now that the products have matured sufficiently, but also not significantly better. He said he has seen major productivity improvements recently, but these have come through “agile” development methods that change how people work, not the tools they use. (I don’t recall whether he used the term "agile".)

I did point out that certain products—QlikView being exhibit A—can offer meaningful productivity improvements over existing standard technologies. But I’m not sure he believed me.

Of course, agile methods can be applied with any tool, so the benefits from open source still come down to savings on software licenses. These can be substantial for a large company: a couple hundred dollars per seat becomes real money when thousands of users are involved. Still, even those costs can easily be outweighed by small changes in productivity: add a database administrator here and an extra cube designer there, and in no time you’ve spent more than you saved on software.

This circles right back to the quality issue. Miller argued that open source products improve faster than commercial systems, so eventually any productivity gaps will be eliminated or even reversed in open source’s favor. Since open source allows day-to-day users to work directly on the problems that are bothering them, it may in fact do better at making incremental productivity improvements than a top-down corporate development process. I'm less sure this applies to major as well as incremental innovations, but suppose examples of radical open source innovation could be found.

Whatever. At the moment, it seems the case for open source BI is strong but not overwhelming. In other words, a certain leap of faith is still required. Miller said most of OpenBI’s business has come from companies where other types of open source systems have already been adopted successfully. These firms have gotten over their initial resistance and found that the quality is acceptable. The price is certainly right.
*******************************************************************************

* (Guy to girl: “Are you free on Saturday night?” Girl to guy: “No, but I’m reasonable”. Extra points to whoever lists the most ways that is politically incorrect.)