put value in consolidated cells

[doroeui]

I am currently copying values from cube A to cube B by CellGetN and CellPutN. However, consolidated cells cannot be copied on cube B since the values are overwritten by the consolidation from the children's values. Is there any ways to bypass the automatic consolidation feature on cube B?

Thanks!

[Steve Rowe]

The short answer is yes but without knowing alot more about your system I would not recommend it.

If you put

[]=C: stet;

At the beginning (after skipcheck) of your rule sheet then all C levels will become input, you will probably have a nasty slow running cube though.

HTH

[Steve Vincent]

and more to the point, why would you want to? its more normal to go from detailed cube to high level cube, rather than the other way around. On the very rare occasions i've had to (site dim consolidated by things like UK / Europe / Rest of World etc) i've added a dummy level zero to the consolidation to hold it, rather than try to oevrride the consolidation itself.

[Martin Ryan]

If you put

[]=C: stet;

At the beginning (after skipcheck) of your rule sheet then all C levels will become input, you will probably have a nasty slow running cube though.

Must've been late when you wrote that Steve. You can never type into a consolidation for a numeric cell.

If you stet a C: cell then all that happens is that it naturally consolidates as normal. This only makes any difference if there is a C: level rule later in the rule. It over-rides this and reverts the consolidation back to being a plain old consolidation.

I tend to create an "Input" element that rolls up into the consolidation. E.g. if you want to write to "USA", but USA is a consolidation, I would create "USA Input", and write to that, which rolls up in "USA". If you want to ensure that only the Input fields are written to you can write a rule that says ['Actuals'] = N: if(scan('Input', !Region)<>0, stet, 0); or something similar. That will force everything else to zero.

Martin

[Alan Kirk]

If you put

[]=C: stet;

At the beginning (after skipcheck) of your rule sheet then all C levels will become input, you will probably have a nasty slow running cube though.

Must've been late when you wrote that Steve. You can never type into a consolidation for a numeric cell.

Data spreading would tend to disagree with the unequivocal "Never" , but the key part of your point is correct.

[doroeui]

Thanks, everyone!!

Basically I need to create a subset (cube B) from a full set cube (cube A). Say cube A contains 10 years data of all regions, and cube B will contains only 3 years of 1 region. Since cube B is only a reference for the users, users will only read from it and no cell values will be updated. In this case, I created a process to copy the corresponding data (no matter is input cells or calculated cells) from cube to cube, and didn't copy the rules file from cube A to cube B. Copying data can also maintain the opening amount (which is brought forward from last year) in the year 1 of cube B.

The problem is that, there is cell called GP% which is caluclated from a rule GP%=(RP-Cost)/RP and there is also a product dimension with consolidated and simple elements. The data is copied correctly in the cells of the GP% and the product simple elements, but it is not correct in the cells of the GP% and the product consolidated elements since the values are rolled up from the lower level. That's why I wonder if there is any ways to disable the automatic consolidation feature on a cube.

In other OLAP tools, I think the cube B can be simply done by building virtual cube. I am new in TM1. Please let me know if it is not the way to do it in TM1.
Thank you very much, all~!

[Martin Ryan]

Sounds like you could simply create a view that only had subsets that show the data that you want in the original cube. No need for a second cube at all.

You could either create the view with subsets, or set up security so that users can only see the regions you want to see anyway, so any view they create will force them to only look at the elements you want them to look at.

Martin

[doroeui]

Thanks for your suggestion, Martin!

Actually the cube B will be sent to the region for their own use

[Martin Ryan]

You're sending a cube to the region? Are you using replication or do they have their own copy of TM1 and you're sending the .cub files?

Can you please explain what you're doing, and what you're trying to achieve there?

Martin

[Steve Rowe]

Must've been late when you wrote that Steve. You can never type into a consolidation for a numeric cell.

I'm seem to be using this icon alot at the moment , maybe the beef eating is catching up with me...
I know there was something wrong with it when I was writing it, I thought it was just a dumb idea not something that would not work at all!
Cheers,