0 Quantivo Offers High-Volume Customer Analytics at a Modest Price

These are exciting times in the world of analytical systems. The Web has created new demands to handle unprecedented data volumes and semi-structured data. Cloud-based deployment offers near-infinite hardware scalability and flexibility.  Acquisitions by enterprise software giants have opened opportunities for smaller, more nimble alternatives. The result has been an explosion of companies using new techniques for managing and analyzing huge data volumes. Many, including Vertica, Aster Data, Greenplum, and Netezza, have also been assimilated by enterprise vendors. Others, including Paraccel, Kognitio, SAND Technology, and 1010data, have grown steadily while remaining independent.

Quantivo is part of this latest technical flowering. Its core is a data structure that sits somewhere between columnar – now the most common approach for analytical processing – and hierarchical indexes. Specifically, Quantivo identifies unique pairs of values within incoming records and stores each pair only once. It then builds new pairs by identifying unique combinations of each pair with another element (which might itself be previously-identified pair). For example, the first analysis might find all transactions that involve a specific product on a single date. The second pass might find all transactions with that product/date pair that were made by a given customer. The process repeats, working up a hierarchy of combinations. The system also tracks the number of times each pair occurs and the specific records involved, so the full detail of the original data can always be reconstructed. The system doesn’t build pairs for all possible combinations of data elements, although users can define several hierarchies if they want the same element to be part of several pairs. Indexes also allow analysis across pairs that are not directly built into the data.

Quantivo doesn’t hide the details of its approach, but it doesn’t talk much about them, either. This likely reflects a (correct) judgment that users will care more about the system’s benefits than how it works. Those benefits include data compression (typically 10% of the original volume), fast response, high scalability, flexible schemas, handling unstructured data, and efficient processing of queries that cause problems in standard SQL. The choice of hierarchy also inherently organizes data around “concepts”, which can be different from the physical structures of the inputs. Queries are relatively simple because users see only a flat list of data elements; relationships are managed automatically, behind the scenes.

Yes there are trade-offs. The data must be processed during the load – a computation-intensive task that takes longer than creating a simple columnar database. The schema must be designed, which requires some technical expertise and can lead to slower response for queries outside the expected paths. Non-SQL queries require a Quantivo-built user interface, meaning users cannot stick with their familiar SQL-based business intelligence tools.

Some of these issues are mitigated by Quantivo’s other major differentiator: it was designed from the start as a true multi-tenant cloud-based solution.  This means it can easily spread its workload across multiple servers and replicate data as needed to support higher data volumes (billions of rows), reduce load times (typically one to two hours per day for daily updates), and improve response time. Support for multiple hierarchies and queries across hierarchies also minimize the price of making a bad decision during initial schema design, since even unplanned queries will execute reasonably efficiently. Designing a schema is relatively simple: in fact, Quantivo plans to add self-service data provisioning, including schema design, in the near future.

The Quantivo user interface is purposely designed to look like other business intelligence tools: users get a list of measures and dimensions, which they drag into place to create pivot tables. They can create filters, define calculated values, add summary levels, and drill down to details. There are simple graphics such as bar and pie charts, but no advanced visualization.

The unique power of the system lies within the filters, which can select data that’s outside the standard dimensions. For example, a filter could select all transactions for customers who purchased a specific product – the type of market basket analysis that’s hard with traditional SQL queries. The system also calculates “association metrics” which compare the data across two groups, such as products in the baskets including product A vs. products in baskets including product B.

Quantivo pricing is based on data volume and complexity. A typical client pays $40,000 to $50,000 per year for 100 million to one billion rows of data, making the system remarkably affordable for a product of its type.  Most clients are using the system for customer data analytics, and the vendor has created connectors for SAP, Salesforce.com, Marketo, Responsys, ExactTarget, Omniture, Google Analytics, IBM, Microsoft, and NCR systems. Quantivo launched as a cloud-based service in 2008 and has an undisclosed number of paying clients.

0 ParAccel Toots Its Horn and Revs Its Database Engine

Summary: Over the past year, columnar analytical database vendor ParAccel has methodically proven its claims about speed, scalability and easy deployment. Now it's looking to grow fast.

When I first wrote about analytical database vendor ParAccel in a February 2008 post, it was one of several barely distinguishable vendors offering massively parallel, SQL-compatible columnar databases. Their main claim to fame was a record-setting performance on the TPC-H benchmark, but even the significance of that was unclear since few vendors bother with the TPC process.

Since then, ParAccel has delivered an impressive string of accomplishments, including deals with demanding customers (Merkle, PriceChopper, Autometrics, TRX) and an important alliance with EMC to create a “scalable analytic appliance”. To top it off, they recently announced their 2.0 release, a new TPC-H record, and $22 million Series C funding. (Full disclosure: they also hired me to write a white paper.)

Of all these, perhaps the most significant news is that the new TPC-H benchmark comes at the 30 terabyte level.* ParAccel’s previous TPC-H championships were at the 100 GB to 1 TB levels.

The change reflects a general growth in the scale of systems supported by MPP columnar databases. ParAccel reports its largest production installation holds 18 TB of compressed data, which probably translates to something more than 50 TB of input. Segment-leader Vertica reports several production installations larger than 100 TB. Neither had more than 10 TB in production a year ago.

These figures still don’t put the columnar systems in the same ballpark as the petabyte-scale database appliances like Netezza, Greenplum and Aster Data, but they do open up some major new possibilities. In case you’re wondering, ParAccel’s TPC-H results were seven times faster and had 16 times better price / performance than the previous record, held by Oracle.

But pure scalability isn’t the key selling point for ParAccel. More than anything, the company stresses its ability to handle complex queries without specialized data schemas or indexes. This means that existing data structures can be loaded as is and queried immediately. The net result is a much faster “time to answer” than competitive systems, which do tailor schemas and/or indexes to specific questions. It also means that new queries can be answered immediately, without waiting for schema modifications or new indexes.

The 2.0 release extends these advantages with a new query optimizer that handles very complex joins and correlated subqueries; parallel data loading (nearly 9 TB per hour in the TPC-H benchmark) and User Defined Functions; enhanced compression; and “blended scans” that avoid Storage Area Network (SAN) controller bottlenecks by loading SAN data onto compute nodes and querying them directly. It also adds some special features such as Oracle SQL support and column encryption for financial data. Another set of enhancements are designed to provide enterprise-class reliability, availability and manageability, such as back-up and failover. Several of these features are already in production, although the official 2.0 release date is August.

The new release and added funding mark a transition of ParAccel from quiet introduction to full-throated selling. Over the past year, the company has carefully limited its participation in Proof of Concept (POC) competitions, the key selection tool in this segment. This gave it time to refine its POC processes, add system features, and build initial client references. It says it can now complete a typical POC in three days, often leaving while other vendors are still getting started. The company is now ramping up its lead generation and inside sales operations, aiming to grow quickly beyond its dozen-plus existing installations. (To provide some context: Vertica reports more than 100 clients.) We'll see what comes next.


______________
* For some serious doubt-sowing about the new benchmarks, see Daniel Abadi's post (be sure to read the comments) and ParAccel's response. What really matters, as ParAccel points out, is performance in customer POCs. The company says its performance has never been beaten, although there was one tie. (For sheer entertainment, check out the related string on Curt Monash's blog.)

0 More on Vertica

I finally had a conversation with columnar database developer Vertica last week. They have done such an excellent job explaining their system in white papers and other published materials that most of my questions had already been answered. But it’s always good to hear things straight from the source.

The briefing pretty much confirmed what I already knew and have written here and elsewhere. Specifically, the two big differentiators of Vertica are its use of sorted data and of shared-nothing (MPP) hardware. Loading the data in a sorted order allows certain queries to run quickly because the system need not scan the entire column to find the desired data. Of course, if a query involves data from more than one column, all those columns be stored in the same sequence or must be joined on a common ID. Vertica supports both approaches. Each has its cost. Shared-nothing hardware allows scalability and allows redundant data storage which simplifies recovery.

Our conversation did highlight a few limits that I hadn’t seen clearly before. It turns out that the original releases of Vertica could only support star and snowflake schema databases. I knew Vertical was star schema friendly but didn’t realize the design was required. If I understood correctly, even the new release will not fully support queries across multiple fact tables sharing a dimension table, a fairly common data warehouse design. Vertica’s position is that everybody really should use star/snowflake designs. The other approaches were compromises imposed the limits of traditional row-oriented database engines, which Vertica makes unnecessary. I suspect there are other reasons people might want to use different designs, if only to save the trouble of transforming their source data.

On a somewhat related note, Vertica also clarified that their automated database designer—a major strength of the system—works by analyzing a set of training queries. This is fine so long as workloads are stable, but not so good if they change. A version that monitors actual queries and automatically adjusts the system to new requirements is planned for later this year. Remember that database design is very important to Vertica, since performance depends in part on having the right sorted columns in place. Note also that the automated design will become trickier as the system supports more than start/snowflake schemas. I wouldn’t be surprised to see some limits on the automated designs as a result.

The other bit of hard fact that emerged from the call is that the largest current production database for Vertica is 10 terabytes. The company says new, bigger installations are added all the time, so I’m sure that number will grow. They added that they’ve tested up to 50 TB and are confident the system will scale much higher. I don’t doubt it, since scalability is one of the key benefits of the shared-nothing approach. Vertica also argues that the amount of data is not a terribly relevant measure of scalability—you have to consider response time and workload as well. True enough. I’d certainly consider Vertica for databases much larger than 10 TB. But I’d also do some serious testing at scale before making a purchase.

0 Sybase IQ vs. Vertica: Comparisons are Misleading, But Fun

I received the “Vertica Fast Lane” e-newsletter yesterday, which I am amused to note from its URL is generated by Eloqua. (This is only amusing because I’m researching Eloqua for unrelated reasons these days. Still, if I can offer some advice to the Vertica Marketing Department, it’s best to hide that sort of thing.)

The newsletter contained a link to a post on Vertica’s blog entitled “Debunking a Myth: Column-Stores vs. Indexes”. Naturally caught my attention, given my own recent post suggesting that use indexes is a critical difference between SybaseIQ and the new columnar databases, of which Vertica is the most prominent.

As it turned out, the Vertica post addressed a very different issue: why putting a conventional B-tree index on every column in a traditional relational database is nowhere near as efficient as using a columnar database. This is worth knowing, but doesn’t apply to Sybase IQ because IQ’s primary indexes are not B-trees. Instead, most of them are highly compressed versions of the data itself.

If anything, the article reinforced my feeling that what Sybase calls an index and what Vertica calls a compressed column are almost the same thing. The major difference seems to be that Vertica sorts its columns before storing them. This will sometimes allow greater compression and more efficient searches, although it also implies more processing during the data load. Sybase hasn’t mentioned sorting its indexes, although I suppose they might. Vertica also sometimes improves performance by storing the same data in different sort sequences.

Although Vertica’s use of sorting is an advantage, Sybase has tricks of its own. So it’s impossible to simply look at the features and say one system is “better” than the other, either in general or for specific applications. There's no alternative to live testing on actual tasks.

The Vertica newsletter also announced a preview release of the system’s next version, somewhat archly codenamed “Corinthian” (an order of Greek columns—get it?. And, yes, “archly” is a pun.) To quote Vertica, “The focus of the Corinthian release is to deliver a high degree of ANSI SQL-92 compatibility and set the stage for SQL-99 enhancements in follow-on releases.”

This raises an issue that hadn’t occurred to me, since I had assumed that Vertica and other columnar databases already were compliant with major SQL standards. But apparently the missing capabilities were fairly substantial, since “Corinthian” adds nested sub-queries; outer-, cross- and self-joins; union and union-all set operations; and VarChar long string support. These cannot be critical features, since people have been using Vertica without them. But they do represent the sort of limitations that sometimes pop up only after someone has purchased a system and tried to deploy it. Once more, there's no substitute for doing your homework.

0 Sybase IQ: A Different Kind of Columnar Database (Or Is It The Other Way Around?)

I spent a fair amount of time this past week getting ready for my part in the July 10 DM Radio Webcast on columnar databases. Much of this was spent updating my information on SybaseIQ, whose CTO Irfan Khan was a co-panelist.

Sybase was particularly eager to educate me because I apparently ruffled a few feathers when my July DM Review column described SybaseIQ as a “variation on a columnar database” and listed it separately from other columnar systems. Since IQ has been around for much longer than the other columnar systems and has a vastly larger installed base—over 1,300 customers, as they reminded me several times—the Sybase position seems to be that they should be considered the standard, and everyone else as the variation. (Not that they put it that way.) I can certainly see why it would be frustrating to be set apart from other columnar systems at exactly the moment when columnar technology is finally at the center of attention.

The irony is that I’ve long been fond of SybaseIQ, precisely because I felt its unconventional approach offered advantages that few people recognized. I also feel good about IQ because I wrote about its technology back in 1994, before Sybase purchased it from Expressway Technologies—as I reminded Sybase several times.

In truth, though, that original article was part of the problem. Expressway was an indexing system that used a very clever, and patented, variation on bitmap indexes that allowed calculations within the index itself. Although that technology is still an important feature within SybaseIQ, it now supplements a true column-based data store. Thus, while Expressway was not a columnar database, SybaseIQ is.

I was aware that Sybase had extended Expressway substantially, which is why my DM Review article did refer to them as a type of columnar database. So there was no error in what I wrote. But I’ll admit that until this week’s briefings I didn’t realize just how far SybaseIQ has moved from its bitmapped roots. It now uses seven or nine types of indexes (depending on which document you read), including traditional b-tree indexes and word indexes. Many of its other indexes do use some form of bitmaps, often in conjunction with tokenization (i.e., replacing an actual value with a key that points to a look-up table of actual values. Tokenization saves space when the same value occurs repeatedly, because the key is much smaller than the value itself. Think how much smaller a database is if it stores “MA” instead of “Massachusetts” in its addresses. )

Of course, tokenization is really a data compression technique, so I have a hard time considering a column of tokenized data to be an index. To me, an index is an access mechanism, not the data itself, regardless of how well it’s compressed. Sybase serenely glides over the distinction with the Zen-like aphorism that “the index is the column” (or maybe it was the other way around). I’m not sure I agree, but the point doesn’t seem worth debating

Yet, semantics aside, SybaseIQ’s heavy reliance on “indexes” is a major difference between it and the raft of other systems currently gaining attention as columnar databases: Vertica, ParAccel, Exasol and Calpont among them. These systems do rely heavily on compression of their data columns, but don’t describe (or, presumably, use) these as indexes. In particular, so far as I know, they don’t build different kinds of indexes on the same column, which IQ treats as a main selling point. Some of the other systems store several versions of the same column in different sort sequences, but that’s quite different.

The other very clear distinction between IQ and the other columnar systems is that IQ uses Symmetrical Multi-Processing (SMP) servers to process queries against a unified data store, while the others rely on shared nothing or Massively Multi-Processor (MMP) servers. This reflects a fundamentally different approach to scalability. Sybase scales by having different servers execute different queries simultaneously, relying on its indexes to minimize the amount of data that must be read from the disk. The MPP-based systems scale by partitioning the data so that many servers can work in parallel to scan it quickly. (Naturally, the MPP systems do more than a brute-force column scan; for example, those sorted columns can substantially reduce read volumes.)

It’s possible that understanding these differences would allow someone to judge which type of columnar system works better for a particular application. But I am not that someone. Sybase makes a plausible case that its approach is inherently better for a wider range of ad hoc queries, because it doesn’t depend on how the data is partitioned or sorted. However, I haven’t heard the other vendors’ side of that argument. In any event, actual performance will depend on how the architecture has been implemented. So even a theoretically superior approach will not necessarily deliver better results in real life. Until the industry has a great deal more experience with the MPP systems in particular, the only way to know which database is better for a particular application will be to test them.

The SMP/MPP distinction does raise a question about SybaseIQ’s uniqueness. My original DM Review article actually listed two classes of columnar systems: SMP-based and MPP-based. Other SMP-based systems include Alterian, SmartFocus, Infobright, 1010Data and open-source LucidDB. (The LucidDB site contains some good technical explanations of columnar techniques, incidentally.)

I chose not to list SybaseIQ in the SMP category because I thought its reliance on bitmap techniques makes it significantly different from the others, and in particular because I believed it made IQ substantially more scalable. I’m not so sure about the bitmap part anymore, now that realize SybaseIQ makes less use of bitmaps than I thought, and have found that some of the other vendors use them too. On the other hand, IQ’s proven scalability is still much greater than any of these other systems—Sybase cites installations over 100 TB, while none of the others (possibly excepting Infobright) has an installation over 10 TB.

So where does all this leave us? Regarding SybaseIQ, not so far from where we started: I still say it’s an excellent columnar database that is significantly different from the (MPP-based) columnar databases that are the focus of recent attention. But, to me, the really important word in the preceding sentence is “excellent”, not “columnar”. The point of the original DM Review article was that there are many kinds of analytical databases available, and you should consider them all when assessing which might fit your needs. It would be plain silly to finally look for alternatives to conventional relational databases and immediately restrict yourself to just one other approach.