|
With columnar technology, the data is the index. With this technology, a query can be processed 100 times faster than searching an RDBMS
By Brian Pereira
Traditional Relational Database Management Systems (RDBMS) were designed for OLTP (online transaction processing), which is largely write-intensive. But analytics is a read-intensive activity. Over the years companies such as C-Store, Sybase and others created technology that conditioned RDBMS and data warehousing for analytics. Sybase, for instance, has been an early adopter of column-oriented technology in databases, a technology that was invented by researchers at Brown University, Brandeis University and MIT.
Sybase claims to have created the first commercial columnar database, and to have offered it a decade ago.
Says Sudesh Prabhu, Director – Services and Presales, Sybase Software (India), “With columnar technology the data itself is the index. So when you put a query it is much faster. In a typical scenario, it is about 100 times faster than searching an RDBMS. So you can actually pass on the control to the user without worrying about the limitations or number of users. The analytical server can handle more queries and it is much faster and high on performance. Further, you make do with existing hardware and provide access to hundreds of users concurrently.”
Typical OLTP RDBMS systems are more row-oriented; they use two dimensional tables for organizing data. A column-oriented database serializes all the values of a column in one block, then the values of the next column, and so on. This combination of row-based and column-based data orientation is better suited to OLAP (online analytical processing).
|
Risk Analytics
|
There is a set of users that have very specialized and demanding requirements. They rely heavily on this data for taking business decisions in almost real-time. Typically from the BFSI sector, these users need risk analytics solutions. Their decisions are largely governed by the quality of the data extracted from the database. For instance, those concerned with fraud management, trading decisions and qualitative analytics would require risk analytics.
When Sybase launched its Risk Analytics Platform (RAP) - The Trading Edition, at the start of the recession, it was not surprised to see that the top 25 financial institutions started using it within six months. It also brought in significant revenue for the company, at a time when others were sinking into the red. Sybase is about to launch the Telco Edition of RAP. “RAP is a very specialized analytics solution. It analyzes live data and guides you as the event happens; other solutions perform a post mortem,” says Sudesh Prabhu, Director - Services and Presales, Sybase Software. |
Database compression is also crucial for analytics. The data set is usually duplicated and a copy is used for reporting and BI. One technique to improve response time is to limit the dataset. But the fallout is that the results to a query may not offer accurate results and that’s why database companies are now using compression—a by-product of columnar technology. “You can achieve 60 - 70 percent compression with columnar technology. So if you look at a 20 terabyte customer who is duplicating the data with a cost per terabyte of Rs 10 to 12 lakh, you are talking about a net saving of close to Rs 1 crore just coming out of compression. This is a benefit of columnar technology,” says Prabhu.
Sybase (and others) are careful how they go about implementing columnar technology. The idea is to make it all transparent to the user and give him the feeling that he is still using an RDBMS. This is achieved through a layer in front of the RDBMS.
|