Design question about drill through

[Jeroen Eynikel]

Hi,

I have the following technical issue. A customer has asked me about the feasibility of doing some cost analysis in TM-1.

There is nothing difficult about the calculation. Basically standard TM1 consolidations will probably suffice.

The tricky part is the following. One of the dimensions is going to have millions of elements. Think in the range of 6 to 12 million.

There are several aggregated levels in it, all in 1-N relation to the next level. As such millions of elements at the lowest level.

Customer intends to very actively drill down in these aggregations for their analysis.

In my experience a single dimension will not work in this case - I.e. I would expect horrible performance in drilling down on the consolidations.

I am thinking of an alternative solution though, I.e. set up multiple dimensions / cubes and use drill through to link them. I.e. that way I could probably keep the 4 top level consolidations in 1 dimension (having maybe 100.000 elements at most) and stick the lower two in seperate dimensions only containing themselves and the one level above them. So I would have one other one with maybe a million elements and one other with 6-12 million.

I think this would allow for decent performance drilling down over the first dimension and hopefully still give pretty OK performance using drill through for the lower two if further detail is needed.

I have two questions on this:

1) does anyone have any experience with a similar set-up and if so will it work as well as I think it should?

2) eventually the data will be reported on from within cognos BI. Will this still support my architecture? I.e. if it works ok in TM-1 will it still do so when there is a cognos BI layer over it?

Thanks for any input

[Steve Rowe]

The other option to multiple cubes of increasing and branching granularity is at some point to step out into the relatonal world and do a SQL drill instead.

Not sure about BI but my I guess its going to depend on the number of Ns under the last level of Cs. In fact your whole design hinges on that problem. If the L1 Cs have 100 Ns or 100k Ns are two different problems. I can't see BI working with the latter problem?

Interesting problem not sure what the answer is or if pure TM1 is the right approach...

Cheers,

[Jeroen Eynikel]

The other option to multiple cubes of increasing and branching granularity is at some point to step out into the relatonal world and do a SQL drill instead.

Not sure about BI but my I guess its going to depend on the number of Ns under the last level of Cs. In fact your whole design hinges on that problem. If the L1 Cs have 100 Ns or 100k Ns are two different problems. I can't see BI working with the latter problem?

Interesting problem not sure what the answer is or if pure TM1 is the right approach...

Cheers,

The relation of L1 to L0 will be about 1 to 20. So for the estimated 6-12 million L0 elements you will have about 300K to 600K of L1 elements. In what way is this important for Cognos BI? (I.e; can you expand on your remark?)

Also I haven't used drill through that much, can you drill-though on a cell that is itself already a drill-through cell? I.e. I know that technically I could put the drill through on both of my 'lower level' cubes, but when actually using it - can you still drill through when already in a drill through window?

(If not I would have to stick the L0 in a relational db for sure I guess)

[Steve Rowe]

Hi, I'm not an expert on BI but my understanding is that it is a primarly a dashboard / static reporting tool, rather than a replacement front end for TM1. It's web based so you have additional performance issues to consider if you are trying to retrieve large volumes of data dynamically from TM1. You may well be OK drilling from a single L1 to your 20 L0s but if you drill straight from higher up the tree and pull back 5,000 elements then there may be issues.

The reason I asked about the relationship between L1 and L0 is this will probably drive the scale of the final report?

You can chain drills together just fine.

And just to clarify in case there is an other drill through, I'm talking about the TM1 cube view to cube view drilling!

Cheers

[rmackenzie]

As such millions of elements at the lowest level.
Customer intends to very actively drill down in these aggregations for their analysis.
In my experience a single dimension will not work in this case - I.e. I would expect horrible performance in drilling down on the consolidations.

I agree that very large dimensions often have significant performance issues. I've seen 2 million plus render the cube viewer unusable and 750,000 plus left active forms hanging for minutes. Where you have over a million elements, the TM1 engine copes admirably, but it is Subset Editor that gets crushed - especially if you have a lot of attribute data. Regards Cognos BI, my understanding is that it talks to a TM1 package via MDX, and that communication layer is also going to give you additional performance headaches with very large dimensions. For that integration, I'd definitely try and do a proof of concept/ prototype before plunging into the main project.

Just a guess here, but at the volume of 6-12 million elements - are you dealing with customer accounts, or subscribers, or something like that? Does your customer really need that level of analysis for any and all of elements in all time periods, or do they just want detailed analysis on a specific subset depending on the focus of analysis? Typically, I find the latter to turn out to be the real requirement and it means you can approach the modelling in a slightly different manner. For example, you could build temporary cubes based on a subset of accounts/ customers that don't need to persist over time. I like the chained cube-to-cube drill-through idea and that could work alongside having smaller cubes that target the information that people want to analyse. Obviously, not having one persistent cube is a pain when it comes to the BI, but equally I'd be concerned about how well chained cube-to-cube drills dovetail with a BI reporting architecture. Looks like you've got some more investigation to do!

[lotsaram]

Hi Jeroen

I agree with the other comments. In general I think your design would work. I have used a similar design once before with 2 or 3 layers of staging and summary cubes and summarizing the main large dimension and cube to cube drill-through to navigate down to the next level. It worked well with good performance. Mind you the scale of the dimension wasn't quite the same scale it was between 1 - 2 million elements at the lowest level not 6+ million.

One additional step you can take with this design approach is to split the base level of the big dimension and the lowest level data into separate dimensions and cubes to further manage dimension and cube size and performance. Also I would include the higher level rollups in the dimension(s) starting from the base as it won't impact performance if the consolidations aren't queried but it gives the option to reconcile to the summarized cubes if needed.

[Jeroen Eynikel]

Thanks all for the comments & tips so far. And yes - my worry is not so much the server getting crushed, my worry is the network / clients performance.

So general consensus is that it will probably work but a POC is recommended

As to the question about the exact contents of these millions of L0 elements.

The analyis is not quite 100% but very similar to a Bill Of Materials.
I.e. goal is to identify what the drivers are when Certain programs cost more or less than expected.

A program consists of several subprograms, that have theitr own children and at the bottom level you have 'operations' which can be either physical materials or certain actions.

The interesting thing which made me consider the alternate approach is that when it comes to analysis there are going to be two groups of users. 1 group is upper management who will want the ability to drill down but will probably not look at the lowest two levels of details and leave that up to controllers. For management the standard TM1 interfaces surely will not do hence the link to BI. I would guess that the controllers on the other hand could live with a pure TM1 interface for the lowest detail levels if it proves unfeasible to report on those in BI.

[rmackenzie]

... It worked well with good performance. Mind you the scale of the dimension wasn't quite the same scale it was between 1 - 2 million elements at the lowest level not 6+ million.

It's worth mentioning that any performance issues with very large dimensions isn't proportional to the size of the dimension per se, but also to the number and complexity of other dimensions in the cube, the sparsity of the data, the optimality of the dimension ordering, the presence of rules, etc.

[Jeroen Eynikel]

... It worked well with good performance. Mind you the scale of the dimension wasn't quite the same scale it was between 1 - 2 million elements at the lowest level not 6+ million.

It's worth mentioning that any performance issues with very large dimensions isn't proportional to the size of the dimension per se, but also to the number and complexity of other dimensions in the cube, the sparsity of the data, the optimality of the dimension ordering, the presence of rules, etc.

I guess the system is going to be pretty sparse, I will not need any rules and the other dimensions will have a limited number of elements. That being said I still don't think it is feasible as I expect the client will not be able to handle browsing a 6-12 million element dimension anywhere near gracefully.