Pass Data At Consolidation cube to another cube

[jviegas@bi4all.pt]

Hi all,

Maybe someone has an idea regarding this.

I have a source cube where a dimension has this structure:

Parent A
Element A1
Element A2
Element A3
Parent B
Element B1
Element B2
Parent C
...
...

This would be the source cube and I need to pass infortion through TI process to another cube where the a similar dimension has the parent level only as leaf item:

TOTAL
Parent A
Parent B
Parent C
....

When executing the TI process It is trying to match the elements and ignoring the parents (as a result I have error of non-existent elements and no data is passed through the cubes).

Can anyone help me on this?

Thanks in advance,

Jorge

[tomok]

This question has been asked ad nauseum on this forum. Please use the search function first before posting a question. The simple answer is to use a cube view as the source for the TI process that has a subset on the dimension in question, filtering to only the "Parent" elements you want to load in Cube B. Then make sure the view is configured not to "Skip Consolidations". That's pretty much it.

[jviegas@bi4all.pt]

Hi Tomok,

Actually I check other posts but felt like none of solve the situation I have.

Thank you for the feedback but I have a doubt how do you uncheck or unflag the "Skip Consolidations" because this is exactly what I want it to do regarding my souce view in the cube

Jorge

[declanr]

Hi Tomok,

Actually I check other posts but felt like none of solve the situation I have.

Thank you for the feedback but I have a doubt how do you uncheck or unflag the "Skip Consolidations" because this is exactly what I want it to do regarding my souce view in the cube

Jorge

ViewExtractSkipCalcsSet or assuming you are using the GUI just find the box and untick it.

[BariAbdul]

When you build cube view as a data source it prompts you with option skip zero or skip rules derived cells and skip consolidations ,the second option is to use ViewExtractSkipCalcsSet (Cube, ViewName, Flag);
Hope it helps.Thanks

[BariAbdul]

Sorry Declan,You beat me by couple of seconds.

[jviegas@bi4all.pt]

Problem Solved.

Addicionaly I had a problem with an if condition that was giving me a strange behavior on the TI process.

Thanks to all for the help.

[lotsaram]

When processing data TM1 processes leaf cells many times faster than consolidations. With asymmetric cubes where one dimension has leaves which are consolidated nodes in an analog dimension it is almost always faster to process leaf cells from the detailed cube and accumulate into the summary cube. An alternative method to processing consolidated cells is to have a string attribute holding the parent name and use this to direct data to the appropriate node while accumulating.

Nothing wrong with the advice which you have been given but if you have a significant volume of data to process or then accumulating from leaf cells could be quite a bit faster.

[Michel Zijlema]

Nothing wrong with the advice which you have been given but if you have a significant volume of data to process or then accumulating from leaf cells could be quite a bit faster.

I second lotsaram's remark here. I've seen examples where just processing leaf levels was significantly faster than processing the consolidated cells. One example was on a big cube where processing a view on consolidated level took about 20 minutes, while creating on the fly a summary cube, populating that from the big cube (accumulating from leaf cells) and then processing the temporary summary cube (because evaluation on consolidated level was required) took less than a minute...

Michel

[Duncan P]

So this is quite complicated and if this is a TI that is going to be run regularly I would experiment with both approaches.

If there is no rule defined on the source cube I would be very surprised to find that accumulating the leaf values in the TI is faster, very surprised.

If for the leaf values there is an N level rule defined on the descendants of the consolidated source cells then it will depend on how you do it. As you say that you use 10.2 you could take advantage of multi-threading by defining a view corresponding to your source consolidation and using ViewConstruct on that view in the prolog. This will allow the view to be constructed using however many threads you have configured in your tm1s.cfg. The consolidated values will almost certainly be cached in the calculation cache and will take next to no time to read when the main part of the TI runs. Also it is possible that the values will get read straight out of the stargate that gets created. I'm not certain of that though. I know that this technique has been used with success by others on the forum - please correct me if I've got any details wrong.

If there is a rule defined at the C level for the source consolidated values and a rule is also defined for the underlying leaf cells, then things are more complicated. The previous technique will still be useful but you have to be careful because opening a view automatically creates an internal view of the contributing leaf cells and performs the aggregation - even if all the consolidated cells in the view are calculated. This is in addition to all the work that it will do to do the calculations at consolidated level, which will almost certainly involve other consolidation. The aggregation part of this is not the issue, and you can't avoid it anyway, but the unnecessary leaf level calculations are. They add considerably to the overhead and they should be avoided. The only way round this is to make the whole source measure (the element on the measures dimension) consolidated, a parent of a dummy item that has no rule defined for it. This means that the actual contributing leaf cells are the dummy ones so no leaf calculations will be done.

In a cube with many measures calculated from a single main source measure (using drivers and the like from other cubes), the source item can be the child of all the calculated items. This approach was introduced to us by Lotsaram who championed it mostly because you only have to feed the one measure.

[Michel Zijlema]

If there is no rule defined on the source cube I would be very surprised to find that accumulating the leaf values in the TI is faster, very surprised.

Well, Surprise! then
The source cube in the example I was talking about didn't have a rules sheet. What I found in this and other examples is that the slowness on consolidated views occurs on source cubes with lots of dimensions. The example cube had 21 dimensions. The comparison of 20 minutes vs less than one minute was on processing/evaluating one month of data (approx. 1.5 mln. populated leaf cells).

Michel