I recently blogged about columnar and Wayne Eckerson asked me for a clearer explanation of what I mean by columnar databases "discovering hierarchies".
For example consider the approach of two well known products, IBM Cognos TM1, which is multidimensional, and QlikView, which is columnar.
My definition of a data model is a structure that is informed by an administrator, or set down in the master data. To me this is different to a structure derived from analyzing the transactions. In the following simple example, let's say I have two sales teams, one for dental hygiene products and one for soap.
If I were designing a data model in TM1, then I could create a hierarchy, which is a set of parent child relationships between the departments and the products they sell. If the soap people cross-sold some toothpaste, it would have no effect on the hierarchy, because it is predetermined by my idea of how my company is supposed to work.
If I were to import the same data in QlikView I could create a report that showed me the relationship between the sales teams and the products without defining the model. Once the data is imported, QlikView recognizes the relationships automatically.
When the soap guys cross-sell toothpaste, QlikView discovrs the new relationship, but the hierarchies stay the same in TM1, because that's how I defined the model. To me this is the key difference. On the one hand the structures are coming directly from the actuals, and on the other hand they reflect my predefined perception (or "model") of what is going on.
So columnar databases typically discover the relationships automatically, and multidimensional databases allows you to define the relationships as you want them. Another way to look at this is that the transactional data drives the master data structure in a colunmar database, but those structures are wired into the multidimensional model.
So which approach is better? It depends on the application.