Using Element Attribute to Rollup Leaf Level Data !!!!

[pmathur]

Guys,

I am after some help for the following scenario :

We are having a Cube with 5 dimension(including the measure dimension), elements of all are at leaf level and having only one consolidated node named as "ALL Elements". One of the dimension is Cost Center which is having around 5000 leaf level elements and one main top parent, like as shown below,

ALL Cost Center
-- Cost Center 1
-- Cost Center 2
-- Cost Center n

Cost Center dimension is also having some attributes, say one of them is Manager. We have created one Reporting Cube with same dimensions as main cube with only replacement of Cost Center Dimension with Manager Dimension.

Now what we want is to pull data from staging/main cube to reporting cube in such a way that all cost centers which belongs to same managers(which is defined in attribute) should rollup the leaf level data and then feed that consolidated data in reporting cube corresponding to that manager.

One of the way which we figured out is creating a consolidated Manager node for each bunch in Cost Center dimension and then using TI code or Rules pull that consolidated node in reporting cube. But we don't want this. We dont want to create any more consolidated node. You can assume that, its kind of our business ristriction.

Is ther any other work around to do this? Either by TI script or by Rules ...... anything?

Any suggestion will be highly appreciated.

Regards,
Priyank

[rmackenzie]

Now what we want is to pull data from staging/main cube to reporting cube in such a way that all cost centers which belongs to same managers(which is defined in attribute) should rollup the leaf level data and then feed that consolidated data in reporting cube corresponding to that manager.

You could do this in a TI with the following steps:
1. Zero out the target cube
2. Build an 'all' view on the source cube
3. For each datapoint, get the manager for the cost centre
4. Accumulate that value to the 'running' total for the manager in the target cube

One of the way which we figured out is creating a consolidated Manager node for each bunch in Cost Center dimension and then using TI code or Rules pull that consolidated node in reporting cube. But we don't want this. We dont want to create any more consolidated node. You can assume that, its kind of our business ristriction.

Just putting aside the need to move data from cube to cube, the idea that you can't create consolidated nodes seems a bit odd. Building dimension hierarchies is the key way that allows for users to make sense of large transactional data sets. Could you provide a little more explanation as to why this restriction exists?

[lotsaram]

You have a 5 dim GL type cube where all the dimensions are flat with only an ALL rollup with all leaf elements as direct children? And then you want to create a parallel reporting cube with sumarized data of the cost center dimension for example? ... although you have no rollup defined in the source cube? ... although you do have the attributes defined by which you could create such rollups.

Oh me oh my you need some help.

Yes you could process records in the detailed cube into the summarized cube and accumulate values using the manager attribute as a key. But this is NOT the best solution or the right thing to be doing. I would be stronger than Robin in asserting that it seems there is no need for the "reporting cube" in the first place. What you need to do is get the design right of the original cube (and dimensions). This will provide much more utility for users to be able to navigate data and dril-down to detail as required and be much easier for users to use and for administrators to maintain.

It seems there is something fundamentally wrong with the design. This forum is a great resource but in the end there is a limitation that people are giving free advice in their own time for whatever altruistic motives and I have the feeling that you need more than that and really need some direction on the ground from someone with experience in TM1 model design.

[rmackenzie]

Yes you could process records in the detailed cube into the summarized cube and accumulate values using the manager attribute as a key. But this is NOT the best solution or the right thing to be doing. I would be stronger than Robin in asserting that it seems there is no need for the "reporting cube" in the first place. What you need to do is get the design right of the original cube (and dimensions). This will provide much more utility for users to be able to navigate data and dril-down to detail as required and be much easier for users to use and for administrators to maintain.

I totally agree - it seems extraordinary to ignore key functionality in the tool and then have to bend over backwards to accomodate normal requirements. Of course, this situation must be some sort of science fair project or leftfield thought experiment