TI ODBC and Memory consumption

[gujsa01]

Hi,

I'm sourcing a SQL Server table to load data from with 6 columns. TI has no metadata updates within. Cube has 6 dimensions. No rules defined. Last dimension is version dimension with 2 elements.

Server consumes 11 GB RAM to load 7.5 million rows. Once process finishes, restarting the server shows 1 GB RAM consumed.

No errors logged in TM1Server log.

10.2.2 version.

11 GB RAM consumption is much more than expected. Any suggestions ??

Thanks!

[Steve Rowe]

Hi,
11GB does seem like a lot of overhead for loading this data. Is the load single threaded or do you have multiple threads processing the data which might make it more understandable.

If the 11 GB is an actual problem then you might want to consider processing the rows in multiple passes so the overhead for uncommitted data is much lower.
http://www.tm1forum.com/viewtopic.php?f ... 503#p62503 This thread whilst it is about parrallelisation of data processing should give you some pointers on how to break your query into multiple passes.

Having the version dimension last does not sound optimal for RAM consumption, you should consider re ordering which may bring down the overhead from the data processing.

Cheers,

[gujsa01]

Thanks Steve!

Unfortunately can't run TI's in Parallel as have just 2 CPUS available on this server.

Tested the following today:
1. Ran TI with no code in DATA tab, completed right away, no surprises there.
2. Ran TI to write to CSV, same behavior. 11 GB consumption.
3. Ran TI to not update any cube or CSV but just a small conditional logic to pad zeroes to Cost center. Same behavior, 11 GB consumption.
IF ( LONG ( vCC ) = 1 ) ;
vCC = '00000' | vCC ;
ELSEIF ( LONG ( vCC ) = 4)..
4. Ran TI by modifying SQL statement to incorporate conditional logic in Step 3 above, same behavior, 11 GB consumption.

Any other test I miss, please keep me posted.

Appreciate your help.

[gujsa01]

Also manually re-ordered dimension and found optimal order resulting in Version dim as last one.

[tomok]

Is this 11GB in memory as reported by Windows or as reported in the Stats cube? TM1 uses system memory for all its operations. I'm not surprised that you have system memory being taken for this operation. The key is how much is actually being used by TM1 versus reserved for use by TM1, the difference being what is commonly referred to as garbage memory. TM1 uses and releases memory all the time. The caveat is that once memory is used by TM1 it is never given back to the OS, it is only there for TM1 to use. You have to restart TM1 in order to release the garbage memory.

[lotsaram]

The caveat is that once memory is used by TM1 it is never given back to the OS, it is only there for TM1 to use. You have to restart TM1 in order to release the garbage memory.

Actually there's a caveat to the caveat. The temp memory used by TM1 when exporting out a text file is released back to the OS once the TI process commits and the lock on the file is released. (You can see this if you write out a really massive file, memory consumed by TM1 will increase as the whole file contents is held in memory then memory used by TM1 will actually go down when the file output is finished.)

[lotsaram]

Also manually re-ordered dimension and found optimal order resulting in Version dim as last one.

The optimal dimension ordering for cube size and query read performance might not be the same as the best ordering for write and commits to the cube.