Saturday, July 09, 2011

Discovering hierarchies in columnar databases

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.

4 comments:

Ari Toth said...

Can you please elaborate on how columnar databases automatically discover hierarchies? Is there an intelligent layer?

Shalinder Verma said...

Are you trying to create a hierarchy using two different dimensions - Department and Products. IMHO, they should be left alone and linked to the Fact.

Shalinder Verma said...

Are you trying to create a hierarchy using two different dimensions - Department and Products. IMHO, they should be left alone and linked to the Fact.

Unknown said...

Ari, here's a hypothetical example: Let's say I have sales organizations and products. If I design a hierarchical dimension, I might put the products on a level below the sales organizations. (Truth Prevails suggests it's probably not a good idea, and he may be right in most cases. But the example is hypothetical.) The discovery parts when you see which products were associated with which products. That's all I'm talking bout -- nothing fancy.

Post a Comment

Please leave a comment