Exporting data from view to file consuming memory

Post Reply
vladino
Posts: 100
Joined: Sat Nov 06, 2010 10:10 am
OLAP Product: Cognos TM1
Version: 10.2.2
Excel Version: Excel 2013

Exporting data from view to file consuming memory

Post by vladino » 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

David Usherwood
Site Admin
Posts: 1314
Joined: Wed May 28, 2008 9:09 am

Re: Exporting data from view to file consuming memory

Post by David Usherwood » 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.

vladino
Posts: 100
Joined: Sat Nov 06, 2010 10:10 am
OLAP Product: Cognos TM1
Version: 10.2.2
Excel Version: Excel 2013

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.

User avatar
tomok
MVP
Posts: 2412
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Exporting data from view to file consuming memory

Post by tomok » 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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

lotsaram
MVP
Posts: 3041
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

Re: Exporting data from view to file consuming memory

Post by lotsaram » 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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

vladino
Posts: 100
Joined: Sat Nov 06, 2010 10:10 am
OLAP Product: Cognos TM1
Version: 10.2.2
Excel Version: Excel 2013

Re: Exporting data from view to file consuming memory

Post by vladino » 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? :?:

User avatar
PavoGa
Community Contributor
Posts: 179
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 fixpack 7
Excel Version: 2013
Location: Cleveland, Tennessee

Re: Exporting data from view to file consuming memory

Post by PavoGa » 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.
Ty
Cleveland, TN

vladino
Posts: 100
Joined: Sat Nov 06, 2010 10:10 am
OLAP Product: Cognos TM1
Version: 10.2.2
Excel Version: Excel 2013

Re: Exporting data from view to file consuming memory

Post by vladino » 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.

User avatar
Steve Rowe
Site Admin
Posts: 1742
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Exporting data from view to file consuming memory

Post by Steve Rowe » 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

vladino
Posts: 100
Joined: Sat Nov 06, 2010 10:10 am
OLAP Product: Cognos TM1
Version: 10.2.2
Excel Version: Excel 2013

Re: Exporting data from view to file consuming memory

Post by vladino » 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.

David Usherwood
Site Admin
Posts: 1314
Joined: Wed May 28, 2008 9:09 am

Re: Exporting data from view to file consuming memory

Post by David Usherwood » 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.

vladino
Posts: 100
Joined: Sat Nov 06, 2010 10:10 am
OLAP Product: Cognos TM1
Version: 10.2.2
Excel Version: Excel 2013

Re: Exporting data from view to file consuming memory

Post by vladino » 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. :-/

User avatar
Steve Rowe
Site Admin
Posts: 1742
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Exporting data from view to file consuming memory

Post by Steve Rowe » 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...

User avatar
PavoGa
Community Contributor
Posts: 179
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 fixpack 7
Excel Version: 2013
Location: Cleveland, Tennessee

Re: Exporting data from view to file consuming memory

Post by PavoGa » 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.
Ty
Cleveland, TN

Post Reply