Read a TM1 Cube through ODBO

[normanbobo]

... Support for .... multiple hierarchies is one of TM1's strengths (and palo and InforPM and other similar technologies for that matter) as it means the database can reflect real world structures and is much more intuitive for users to navigate....

Lotsaram....forgot to talk about multiple hierarchies.

I completely agree that having multiple hierarchies in the dimensions is critical to the success of most TM1 applications. In fact, the system I am currently building for this client has many alternate hierarchies on each of several dimensions and those hierarchies can be dynamically updated by simply feeding in a new input file (separate from the main hierarchy build processes). There is one particular set of alternate hierarchies that have proved to be extremely useful. Anyone familiar with Transformer knows that you can build very flexible, powerful date-based alternate hierarchies in that tool extremely easily. Just a few clicks and it's done. Sadly, similar functionality does not exist in Turbo Integrator. We have, however, built a TI process which automatically builds very similar dimension structures (YTD, Prior YTD, Last 12 months, etc.) by simply adding parameters to a control cube. Pretty nifty. But I'm wandering again.

The question of the day is whether or not the alternate hierarchies should have the same number of levels as the base hierarchy.

Because we are using Cognos BI for much of our reporting. and are thus concerned with MUNS and drill-through settings and the like, we played it safe by choosing to ensure our alternate hierarchies had the same number of levels as the base hierarchy. This guarantees that the leaf level elements are at the same level down from the top node regardless of which hierarchy you drill down. We think this solution will offer us the greatest ease of implementation on the analysis and reporting side. But the jury is still out on this and we may change our mind and adjust the alternate hierarchy designs.

I would not go so far as to say that alternate hierarchies should have the same number of levels as the base hierarchy. However, I recommend that each alternate hierarchy not be ragged within itself, for the same reasons I suggest that the base hierarchy not be ragged.

[tomok]

But I avoid ragged hierarchies like the plague whenever possible.

You are certainly entitled to your opinion but I don't ever like to make generalizations like this. I originally came to TM1 from the user side and I can tell you that, as a user, if I encountered a balanced hierarchy in a dimension where it wasn't necessary for usability I would be extremely annoyed. A shining example of this would be the chart of accounts. I can't think of any need for a balanced hierarchy in this dimension. I could care less that Compensation Expense went down three more levels than Occupance Expense or that Assets had more levels than Liabilities and Equity. Why would I ever run a query based on level in that dimension? I can't think of any. However, certain dimensions may certainly benefit from a balanced hierarchy. One example may be geography, where you have zip codes that roll to cities, wihch roll to states, which roll to regions, etc., and some regions may have no cities at all or zips. In this case it would be benficial for it to be balanced because the levels actually mean something. My philosophy is balanced where needed for usability, or interfacing with another system that requires it, and ragged everywhere else.

[normanbobo]

I stand by my statements for the reasons given, knowing that they would be controversial and not all would agree. I understand your statements and the value you are placing on the user drill function and using ragged hierarchies in TM1 to make that function work as well as possible. I contemplated the reasons you mention in my discussion of the issue and stated that they are "trumped" by other, more significant issues arguing for the use of balanced hierarchies. You believe the user drill requirement trumps all other issues. And so here we are. Hopefully we are still on the same side, helping each other and many others achieve the greates benefits from TM1 for our clients and users the best we all know how.