TM1 Forum

Posted: **Thu Apr 12, 2018 2:08 pm**

Hi guys,
is there any difference when exporting data to files and importing it back to the target cube and moving data between cubes using CellPutN in regards of memory consumption?

I'm exporting quite a huge amount of data to files but it consumes a lot of RAM and after the export is finished it''s left as garbage memory.
Would it be better to use direct write to the target cells instead of exporting/importing? Or would the RAM be consumed in the same way?

BR
Vladino

Posted: **Thu Apr 12, 2018 3:06 pm**

If memory is really constrained you could clear the cache periodically by changing a value somewhere and changing it back again. But I suspect your export view includes many more data points than you really need, especially consolidations. Depending (geddit?) on your dependences, the cellputn may be clearing the cache on every write so will be much much slower.

Posted: **Thu Apr 12, 2018 3:17 pm**

David Usherwood wrote: ↑Thu Apr 12, 2018 3:06 pm If memory is really constrained you could clear the cache periodically by changing a value somewhere and changing it back again

What do you mean by clearing the cache? Do you mean deleting stargate views? I'm afraid it won't release garbage memory which is what I need... Or am I wrong?

The other way may be not to generate stargate views but I don't know how to disable this for particular cube.

Posted: **Thu Apr 12, 2018 4:20 pm**

"Garbage" memory isn't really garbage. It can and will be consumed by TM1 as long as the service is up. The only thing it can't be used for is other apps on the box and you shouldn't be housing more then TM1 on one box in the first place.

Posted: **Thu Apr 12, 2018 5:25 pm**

vladino wrote: ↑Thu Apr 12, 2018 2:08 pm Hi guys,
is there any difference when exporting data to files and importing it back to the target cube and moving data between cubes using CellPutN in regards of memory consumption?

I'm exporting quite a huge amount of data to files but it consumes a lot of RAM and after the export is finished it''s left as garbage memory.
Would it be better to use direct write to the target cells instead of exporting/importing? Or would the RAM be consumed in the same way?

BR
Vladino

Are you sure about that?
Typically TM1 never releases memory once requested from the OS. But the one exception to that is the pre-commit memory consumed when writing out text files. TM1 retains the whole of the memory footprint required for a TI export file in RAM until the TI is closed and the lock on the file released. Once a TI process is finished (at least in 10.2.2 and above) this memory is released back to the OS unlike memory for calculation and view cache which stays in TM1's insternal garbage recycling. This was a surprise to me the first time I saw it.

On a very "big data" customer model we had a lot of success in reducing overall peak memory consumption by exporting more (but smaller) files then using powershell to combine the exported files.

Posted: **Thu Apr 12, 2018 5:38 pm**

lotsaram wrote: ↑Thu Apr 12, 2018 5:25 pm Once a TI process is finished (at least in 10.2.2 and above) this memory is released back to the OS unlike memory for calculation and view cache which stays in TM1's insternal garbage recycling. This was a surprise to me the first time I saw it.

I think this is my problem here... All cells in the cube are calculated (and unfortunately not fed - the model is too complicated to be fed correctly but as far as I understand even exporting correctly fed cells won't solve the issue) so all memory is cunsumed by calculations and therefore not released back to the OS.

lotsaram wrote: ↑Thu Apr 12, 2018 5:25 pm On a very "big data" customer model we had a lot of success in reducing overall peak memory consumption by exporting more (but smaller) files then using powershell to combine the exported files.

This is EXACTLY what I'm doing.

But as mentioned above - all exported cells are calculated.

So the question is the same: will direct CellPutN resolve the issue?

Posted: **Thu Apr 12, 2018 6:41 pm**

vladino wrote: ↑Thu Apr 12, 2018 5:38 pm
I think this is my problem here... All cells in the cube are calculated (and unfortunately not fed - the model is too complicated to be fed correctly but as far as I understand even exporting correctly fed cells won't solve the issue) so all memory is cunsumed by calculations and therefore not released back to the OS.

This is an interesting comment. If not using ViewExtractSkipZeroesSet, this seems like you could very well be querying a significant number of unnecessary cells and forcing their calculation.

Posted: **Thu Apr 12, 2018 7:58 pm**

PavoGa wrote: ↑Thu Apr 12, 2018 6:41 pm
vladino wrote: ↑Thu Apr 12, 2018 5:38 pm
I think this is my problem here... All cells in the cube are calculated (and unfortunately not fed - the model is too complicated to be fed correctly but as far as I understand even exporting correctly fed cells won't solve the issue) so all memory is cunsumed by calculations and therefore not released back to the OS.
This is an interesting comment. If not using ViewExtractSkipZeroesSet, this seems like you could very well be querying a significant number of unnecessary cells and forcing their calculation.

As mentioned - no cell is fed so if I would use ViewExtractSkipZeroesSet then no data is exported, even if non-zero values. And that's the reason why I'm using this parameter set to 0.

Posted: **Thu Apr 12, 2018 8:47 pm**

If your dataset is not fed then the RAM consumption on exporting will be much larger than it needs to be, how much bigger depends on the level of sparsity.

How many zero values are in the flat file (assuming you don't filter them out on export). Every zero will cost just as much as a value.

The only way that feeding will not solve/reduce your issue is if there dataset contains no zero values, i.e. it is fully dense.

Feeding can be hard and complex but there ought not be anything that is so complicated as to be impossible, don't admit defeat!

So if your data is sparse then feeders will help.

The other approach mentioned by lotsaram is to export smaller chunks, in theory if you export a view and TM1 uses 1GB to generate the view and this ends up in garbage. If you cut this view into 10 chunks, then your calc space is 0.1GB, which will be reused for each view (though don't expect this to work out exactly)...

HTH

Posted: **Thu Apr 12, 2018 9:10 pm**

Steve Rowe wrote: ↑Thu Apr 12, 2018 8:47 pm If your dataset is not fed then the RAM consumption on exporting will be much larger than it needs to be, how much bigger depends on the level of sparsity.

How many zero values are in the flat file (assuming you don't filter them out on export). Every zero will cost just as much as a value.

The only way that feeding will not solve/reduce your issue is if there dataset contains no zero values, i.e. it is fully dense.

Feeding can be hard and complex but there ought not be anything that is so complicated as to be impossible, don't admit defeat!

So if your data is sparse then feeders will help.

There are lots of zeroes... :-/

Steve Rowe wrote: ↑Thu Apr 12, 2018 8:47 pm The other approach mentioned by lotsaram is to export smaller chunks, in theory if you export a view and TM1 uses 1GB to generate the view and this ends up in garbage. If you cut this view into 10 chunks, then your calc space is 0.1GB, which will be reused for each view (though don't expect this to work out exactly)...

As mentioned above - this is the exact approach I'm currently using. But it seems to me that the RAM consumption is huge... Our box has 300GB, the model eats approx. 100GB after clean start. When I run the export process after the startup it eats another approx. 100GB. And when I run it once again it eats remaining 100GB and then the server "falls down" - not literally but the admin server becomes unresponsive etc. and the only way is to kill the TM1 instance, kill admin server and restart both.
That's why I'm thinking about any other approach...

Btw. I'm not using any specific VMM/VMT settings for the source cube, so it uses defaults.

Posted: **Fri Apr 13, 2018 8:49 am**

Re: Exporting data from view to file consuming memory

Post by vladino » Thu Apr 12, 2018 3:17 pm

David Usherwood wrote: ↑
Thu Apr 12, 2018 3:06 pm
If memory is really constrained you could clear the cache periodically by changing a value somewhere and changing it back again

What do you mean by clearing the cache? Do you mean deleting stargate views? I'm afraid it won't release garbage memory which is what I need... Or am I wrong?

The other way may be not to generate stargate views but I don't know how to disable this for particular cube.

I meant exactly what I said....
TM1 retains calculated values in the 'calculation cache' to improve performance. Changing a value in a cube clears the cache for that cube and any 'downstream' cubes ie those which are linked back to that cube. This can be used to manage server size, though I will admit I last used it some time ago on a 32 bit server to keep within the 3gb limit.
But reading the rest of the thread I think your problem is here:

All cells in the cube are calculated (and unfortunately not fed - the model is too complicated to be fed correctly but as far as I understand even exporting correctly fed cells won't solve the issue)

This is hogwash. Feeders relate to rules and if the rules work then feeders can be written to deliver the correct content. A properly fed system will be many orders of magnitude more efficient and exporting, or writing to another cube, will be much much faster. Bite the bullet and sort them out. There are many good consultants and consultancies who could do this.

Posted: **Fri Apr 13, 2018 6:38 pm**

David Usherwood wrote: ↑Fri Apr 13, 2018 8:49 am Changing a value in a cube clears the cache for that cube and any 'downstream' cubes ie those which are linked back to that cube.

Ok, let's try it this way... After each file is generated and the TI process goes to its Epilog part I will put a value somewhere to the source cube. Let's see what happens, I will keep you posted guys.

EDIT: No success yet - even after putting a number into one cell within the source cube the export process consumes a lot of RAM. :-/

Posted: **Mon Apr 16, 2018 10:00 am**

Gratz on your 100th post.

You have two options (probably).

1. Feed your rules, this is the right solution.
2. Export your data in even smaller chunk, this is a workaround to not doing the above.

Maybe if you explain why your rules are so hard to feed you'll get a solution, you are currently trying to solve the wrong problem...

Posted: **Mon Apr 16, 2018 5:15 pm**

vladino wrote: ↑Thu Apr 12, 2018 7:58 pm
PavoGa wrote: ↑Thu Apr 12, 2018 6:41 pm
vladino wrote: ↑Thu Apr 12, 2018 5:38 pm
I think this is my problem here... All cells in the cube are calculated (and unfortunately not fed - the model is too complicated to be fed correctly but as far as I understand even exporting correctly fed cells won't solve the issue) so all memory is cunsumed by calculations and therefore not released back to the OS.
This is an interesting comment. If not using ViewExtractSkipZeroesSet, this seems like you could very well be querying a significant number of unnecessary cells and forcing their calculation.
As mentioned - no cell is fed so if I would use ViewExtractSkipZeroesSet then no data is exported, even if non-zero values. And that's the reason why I'm using this parameter set to 0.

Hate to pile on, but everyone else got to it before I did. No wonder the performance is bad and, also, anytime you change a number in the cube, the cache is "dirtied" and forces recalculations. On top of that, you're also reading probably a whopping pile of cells you do not need to bother with.

Agree with Steve and everyone else: feed the rules, it is the right solution.

Posted: **Fri May 04, 2018 12:00 pm**

PavoGa wrote: ↑Mon Apr 16, 2018 5:15 pm Agree with Steve and everyone else: feed the rules, it is the right solution.

Even if exporting simple data (with zero suppression on source temporary data form enabled) there's quite big RAM consumption.

So it seems to me that correct feeding doesn't solve the issue... :-/

Any other suggestions?

Posted: **Fri May 04, 2018 12:06 pm**

vladino wrote: ↑Fri May 04, 2018 12:00 pm
Even if exporting simple data (with zero suppression on source temporary data form enabled) there's quite big RAM consumption.

So it seems to me that correct feeding doesn't solve the issue... :-/

Any other suggestions?

How about posting the actual code you used to create and zero suppress the view?

Posted: **Fri May 04, 2018 12:16 pm**

PavoGa wrote: ↑Fri May 04, 2018 12:06 pm How about posting the actual code you used to create and zero suppress the view?

Code: Select all

ViewCreate(sCubeName, sViewName, 1);

ViewExtractSkipZeroesSet(sCubeName, sViewName, 1);
ViewExtractSkipRuleValuesSet(sCubeName, sViewName, 0);
ViewExtractSkipCalcsSet(sCubeName, sViewName, 0);

# Rows
ViewRowDimensionSet(sCubeName, sViewName, 'Product', 1);

vMDX_Product = '{[Product].[' | vProduct | ']}';
SubsetCreatebyMDX('Product for transfer TEMP', vMDX_Product, 1);

ViewSubsetAssign(sCubeName, sViewName, 'Product', 'Product for transfer TEMP');

# Columns
ViewColumnDimensionSet(sCubeName, sViewName, 'Year', 1);
ViewColumnDimensionSet(sCubeName, sViewName, 'Period', 2);

ViewSubsetAssign(sCubeName, sViewName, 'Year', 'Years for transfer');
ViewSubsetAssign(sCubeName, sViewName, 'Period', 'Period hierarchy');

# Context
ViewTitleDimensionSet(sCubeName, sViewName, 'Segment');
ViewTitleDimensionSet(sCubeName, sViewName, 'Organization_parent');
ViewTitleDimensionSet(sCubeName, sViewName, 'Organization_child');
ViewTitleDimensionSet(sCubeName, sViewName, 'Version');
ViewTitleDimensionSet(sCubeName, sViewName, 'Channel');
ViewTitleDimensionSet(sCubeName, sViewName, 'Measure');

vMDX_Segment = '{[Segment].[' | vSegment | ']}';
SubsetCreatebyMDX('Segement for transfer TEMP', vMDX_Segment, 1);

vMDX_Parent = '{[Organization_parent].[' | vOrganization_parent | ']}';
SubsetCreatebyMDX('Organization parent for transfer TEMP', vMDX_Parent, 1);

vMDX_Child = '{TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[Organization_child].[' | vOrganization_parent | '_PARENT]},ALL,RECURSIVE)},0)}';
SubsetCreatebyMDX('Organization child for transfer TEMP', vMDX_Child, 1);

ViewSubsetAssign(sCubeName, sViewName, 'Segment', 'Segment for transfer TEMP');
ViewSubsetAssign(sCubeName, sViewName, 'Organization_parent', 'Organization parent for transfer TEMP');
ViewSubsetAssign(sCubeName, sViewName, 'Organization_child', 'Organization child for transfer TEMP');
ViewSubsetAssign(sCubeName, sViewName, 'Version', 'Current version');
ViewSubsetAssign(sCubeName, sViewName, 'Channel', 'NA');
ViewSubsetAssign(sCubeName, sViewName, 'Measure', 'Measures for transfer TEMP');

ExecuteProcess('Bedrock.Cube.Data.ViewExportToFile',
  'pCube',sCubeName,
  'pExportPath',GetProcessErrorFileDirectory | '..\..\' | sExportPath,
  'pExportFile',sExportFile,
  'pView',sViewName,
  'pSkipRuleValues',0,
  'pSkipCalcValues',0,
  'pSkipNullValues',1,
  'pTitleRecord',1,
  'pDebug',0
  );

Posted: **Fri May 04, 2018 9:31 pm**

Few additional notes but not sure if it changes anything...

I'm using this code in a TI process that is run by Hustle in 12 parallel threads, approx. 380 calls in total (total number of Organization_parent members).

This code is wrapped in a WHILE loop which loops over all leaf products -> the output of each loop is a file containing one Organization_parent (and its children, see MDX for Organization_child dim) and one product.

In total, thousands of small files are generated. These are later merged together (using cmd line calls within a TI process) and finally used as the input file for importing into a different cube.

Posted: **Sat May 05, 2018 10:48 am**

FWIW, these are a waste of time in a TI process as each cell is read as a single record with no concept of context, rows, or columns. These statements are for views you intend to view in the Cube Viewer:

Code: Select all

# Rows
ViewRowDimensionSet(sCubeName, sViewName, 'Product', 1);

# Columns
ViewColumnDimensionSet(sCubeName, sViewName, 'Year', 1);
ViewColumnDimensionSet(sCubeName, sViewName, 'Period', 2);

# Context
ViewTitleDimensionSet(sCubeName, sViewName, 'Segment');
ViewTitleDimensionSet(sCubeName, sViewName, 'Organization_parent');
ViewTitleDimensionSet(sCubeName, sViewName, 'Organization_child');
ViewTitleDimensionSet(sCubeName, sViewName, 'Version');
ViewTitleDimensionSet(sCubeName, sViewName, 'Channel');
ViewTitleDimensionSet(sCubeName, sViewName, 'Measure');

Posted: **Sat May 05, 2018 12:02 pm**

tomok wrote: ↑Sat May 05, 2018 10:48 am FWIW, these are a waste of time in a TI process as each cell is read as a single record with no concept of context, rows, or columns. These statements are for views you intend to view in the Cube Viewer

Ok, understood but I suppose this has nothing to do with the RAM consumption, right?