SQLite as a Column Oriented Database

According to Michael Stonebraker , one of the pioneers of relational database technology, the future of DBMSs lies with column-oriented databases such as C-Store or Google’s BigTable. In the BI sphere, MOLAP column-oriented data-stores are increasingly the norm. But the fact table implementations of most ROLAP star-schemas tend to favour a row-oriented “wide fact” rather than a single-valued fact ( with the measure attributes being supplied by a “measure dimension”). As I consider the use of SQLite as a micro-BI data-store I’m tending towards a single-value fact table approach for three reasons:

A “measure dimension” is a more flexible approach, allows end-users to easily add new value types and to subsequently populate the values.

Compatible with the way PALO (and all other MOLAP tools) model dimensional cubes. Makes its easier to mix’n’match PALO with SQLite.

Column-based data stores are useful not just for classic numeric-value cubes but can be used to manage delta-tracking cubes i.e. fact value is typically a ‘dimensional attribute’ such as a customer’s credit rating, dimensions consist of the “measure dimension”, and a mixture of other version/audit dimensions. Such “cubes” are often used in data migration projects where an initial cut of the data is migrated but a ‘delta watch’ needs to be maintained during the system cut-over period.

Column-oriented databases are a good fit for OLAP applications but positioning them as OLTP back-ends may be an up-hill struggle. Object-oriented programmers already have a hard-time reconciling themselves to relational databases, selling them a technology that is even further from the purity of the “object” will not be easy. If you’re an old relational hand and need an example of an OLTP type application implemented using column-oriented technology check out this Palo video.