TM1 Forum

Hi there,

I am working to bring static data from one cube (source cube) to another cube (target cube). The source cube contains rules and feeders while the target cube shall contain no rules and feeders since it only stores static data from the source cube. I managed to bring the data from source cube to the target cube using cellgetn and cellputn. The issue I am having now is the data at consolidated levels like Q1, Q2, H1 in the time dimension. In the source cube, the data at these consolidated levels is calculated in rules and not merely summing up. How do I copy the data from the consolidated levels in the source cube to consolidated levels in the target cube? E.g. Q1 in source cube to Q2 in source cube.

Thanks in advance.

If you want the data to consolidate in the same way they consolidate in source cube, you need to write c: level rules in destination cube too. The only other way would be to use a string measure and then store the consolidated amounts as string but this will be a dirty solution and might have other issues. Another way is to use a flat dimension for your period dim where quarters are just leaf elements and then you can store anything in them but again you loose the drilling capability

Hi ardi,

Thanks for your prompt reply. The target cube acts like a repository for the source cube with no rules or any calculation. Ideally, it should take every single value from the source cube and stores it. That's it.

I am considering the string value or the flat dimension workaround that you have suggested and like you said there are pros and cons. At first, I was thinking if there is a single rule that can override the rollup behaviour of an hierarchy in TM1 but looks like that's not how it works.

Thanks.

Sorry, if I were to go for the string value workaround, do I have to re-create the measures dimension? FYI, the same measures dimension is being used in both the source cube and target cube. Is there a on the fly numeric to string conversion that can be used in the TI process?

FYI, I am getting the following error in the TM1Process log.

"Error: Data procedure line (5): Cell type is real"

Thanks.

If you want to copy numeric and consolidated data as strings into a static cube then you don't need to change any dimensions. All you need is a cube with otherwise identical dimensions plus one additional measure with one string measure. This dimension must be the LAST dimension in the new cube. Then as you copy the data in you will need to use the implicit sValue variable or the NumberToString function.

BUT! First stop and explain WHY you are doing this? What's the business requirement? What do you hope to achieve?

Because it doesn't sound like a good idea to me. You need to be careful or you are going to end up with a lot of data in the string cube and the time to process it in could also be extreme. I can tell you how to do it but that doesn't mean you should. Always go back to the business case and start with that. There might be much better ways to achieve what you are after.

Hi lotsaram,

Thanks for the reply. Let me explain what the requirement is. The repository cube is meant to hold the "locked", "freezed" or "snapshot" data of the source cube in different version at any point of time. No rules or calculation required as it should copy the data as it is from the source cube to the repository cube. Ideally, the repository cube should have the same structure as the source cube. The reason I want both the source cube and repository cube to share the same dimension is we don't have to maintain several dimensions if let's say a new account is added.

The repository cube will be used for all reporting. Is there a best practice to achieve this?

Thanks.

You want to be careful using the same dimensions if you want to keep historical data. If an element is removed from one of the source dimensions( it may no longer be operational), then all the historical data for that element will also be removed from your repository. You may want to think about creating new dimensions that have the same elements in now, and adding if new ones are added to the source dimension, but when an element is removed from the source it is not removed from the repository. It depends, as mentioned above, what your requirements for this repository are. Do you need to keep all data historically, or do you only care about historical data for current data elements?

Hi failurehappening,

Thanks for the reply. You're are right, we need to be careful in using the same dimension for both the source cube and repository cube. FYI, the account element will only get added and will not be removed. To answer your last question, yes, we need to keep all the data versions in the repository cube.

Thanks.

Hi there,

For now, I copied the C level rules from the source cube to the repository cube to calculate the data at the consolidated level. However, I am still thinking if this is the practice as the data from the source cube supposed to be copied to the repository cube without any rule or calculation.

On a separate matter, I have a question pertaining to cellgetn and cellputn. I noticed some data (only some weird though) were not copied over to the repository cube. I am wondering if this has anything to do with feeders. I checked the feeder for the particluar cell which data was not copied over in the source cube view and it was not fed so I am thinking if this could be an issue. For the same measure across all the months, only in Jan, it was not fed and for the rest of the months, feb to dec, it was fed. It is weird.

Thanks all for the helpful replies thus far.

tm1green wrote:Hi there,

For now, I copied the C level rules from the source cube to the repository cube to calculate the data at the consolidated level. However, I am still thinking if this is the practice as the data from the source cube supposed to be copied to the repository cube without any rule or calculation.

Right, assuming that your C-Level rules are just for the purpose of calculating averages or holding Quarter End Balances at the same amount as the final month in that quarter as opposed to the total of the 3 then a consolidated rule is the perfect way of doing it; your fear of it being "risky" as it is a "live" calculation can be put aside as long as the n-level data is copied correctly and the rules are only internal to the cube; that way it will be entirely dependent on the n-level data you have copied across... so if that's fine the c-levels will be as well. Plus you don't have to worry about feeding etc as it will be done automatically from the n-level values underneath the consolidation.
The one thing you need to be wary of is making sure that the rules stay in line between the 2 cubes to whatever extent you require (i.e. if you change 1 do you need to chaneg the other as well.)

tm1green wrote: On a separate matter, I have a question pertaining to cellgetn and cellputn. I noticed some data (only some weird though) were not copied over to the repository cube. I am wondering if this has anything to do with feeders. I checked the feeder for the particluar cell which data was not copied over in the source cube view and it was not fed so I am thinking if this could be an issue. For the same measure across all the months, only in Jan, it was not fed and for the rest of the months, feb to dec, it was fed. It is weird.

Well regardless of the copying issue; if you have a rule driven cell that isn't fed and you want it to consolidate or anything then you should focus on working out why the feeder failed.
As to the question though - why are you asking about "CellgetN"? If youa re copying between 2 essentially identical cubes the "get" part of the equation should simply be resolved by setting the datasouce in the TI process (this will need cells to be fed or a skip zeroes will force it to ignore that cell.)

Your best approach should be to write C: level rules in your destination cube. The good thing about these C: rules is that you don't need to FEED them. All the other approaches I mentioned can be used but they might really complicate your model a lot.

I hate Rules myself and I try to avoid using them, but when it comes to calculate consolidated cells or Ratio type of calculations, you cannot avoid them.

Hi declanr,

Thanks for my addressing my thoughts.

declanr wrote:As to the question though - why are you asking about "CellgetN"? If youa re copying between 2 essentially identical cubes the "get" part of the equation should simply be resolved by setting the datasouce in the TI process (this will need cells to be fed or a skip zeroes will force it to ignore that cell.)

Thanks for pointing out. I thought cellgetn and cellputn work in pair. I have removed the cellgetn part since it is not necessary. One question though. The variables order doesn't follow the dimension order defined for cube. The workaround now requires all the dimensions to be moved to the rows. Is it supposed to work this way?

Hi ardi,

Thanks for pointing out all the possible approaches since the beginning.

The variables order doesn't follow the dimension order defined for cube. The workaround now requires all the dimensions to be moved to the rows. Is it supposed to work this way?

Try manually define the variables in the order of cube you have,i.e department is first dimension then the first variable would be vDepartment and so on.Thanks

BariAbdul wrote:

The variables order doesn't follow the dimension order defined for cube. The workaround now requires all the dimensions to be moved to the rows. Is it supposed to work this way?

Try manually define the variables in the order of cube you have,i.e department is first dimension then the first variable would be vDepartment and so on.Thanks

I advise you not to follow BariAbdul's suggestion.

If you use a view intended for the server explorer UI for the data source preview this can happen due to precedence of title, row & column dimensions. What you need to do is create the view for your preview & defining dimensions by right-click "export as text data" or directly from the data tab of the TI itself. Then the variables will be in the same order as the cube.

I advise you not to follow BariAbdul's suggestion.

Thanks for the correction,lotsaram.I saw Jim wood giving similar advice for variables not in correct order for CellGetN and CellPutN .May be it was different scenario,Apologies.