TM1 Forum

Hoping the hive mind can help out on this one. I'm curious if anyone has come up with a good method to find out when the data from a cube was last pulled. We're attempting to do a little housecleaning, and at the top of the list is cubes not being used.

I can get the time the .cub file was last updated. Windows handles that aspect. But... Read from?

I don't want to break applications in the cleanup, but there are too many models to go through full manual.

I got pulse, N++, winmerge, and windirstat at my disposal.

I'm not aware of any way of detecting this. The traditional, slightly naughty, approach is to remove access to the cubes in question and wait for someone to whinge....

The only thing I can think of would be to turn on stargate view logging (tm1s-log.properties).
You would then theoretically be able to see from this point on whether the cube is queried, but you wouldn't be able to retroactively check to see in the past before the point you enabled logging.
It would also be creating you some pretty hefty log files until you turned it off again.

Maybe you could tweak David's approach a little bit and instead of removing access just unload the cube/s.
Then if a user queries the cube you would see "loading body for cube XXXX" in the server log file and it wouldn't stop the users from getting their data, it would just slow them down by a few seconds.

Hi,

You can use "load on Demand" parameter for this. Check it on every cube, and monitor the memory usage in }StatsByCube. The cubes which are not loaded - were not used.

Elessar wrote: ↑Thu Apr 22, 2021 1:22 pm Hi,

You can use "load on Demand" parameter for this. Check it on every cube, and monitor the memory usage in }StatsByCube. The cubes which are not loaded - were not used.

That's a good piece of lateral thinking

@david Hacker by trade. don't mind being naughty. But that is a fantastic way to poll the usage.
I'll offer another though, just to put in your toolbox. I could save off the .cub files, then zero the cubes/restrict access.

For the hive mind's knowledge. You can hot load cube data. If you Do an unload cube, that saves it to disk (I'd probably do a save data anyway before performing this action, because...tm1) You can then replace the .cub file with another one that has the same dimensionality.Was super useful to me when we had to test dev fixes with prod data.

@declanr Helpful tip. Don't need more logs

@Elessar That might be the way to go. Would take a month or so to get the relevant data, but it works.

Thanks all,

If I come up with something I'll try to remember to post it here.

I would expect IBM provide value for getting last access timestamp for a cube via REST API in the future.

macsir wrote: ↑Thu Apr 22, 2021 8:19 pm I would expect IBM provide value for getting last access timestamp for a cube via REST API in the future.

You, good sir, win the "optimist of the year" award. The need for something like this has been obvious for literally decades, yet it's nowhere in sight. One glimmer of hope is that if it comes it will come from the server side team which sits astride Mt Awesome, rather than the client side "purveyors of half baked, ever changing not-quite-solutions" teams.

Unfortunately even the server guys are constrained by the IBM "prooritise what looks good in sales demos" mentality.

Elessar wrote: ↑Thu Apr 22, 2021 1:22 pm Hi,

You can use "load on Demand" parameter for this. Check it on every cube, and monitor the memory usage in }StatsByCube. The cubes which are not loaded - were not used.

Very cool idea.

PavoGa wrote: ↑Thu Apr 22, 2021 11:21 pm

Elessar wrote: ↑Thu Apr 22, 2021 1:22 pm Hi,

You can use "load on Demand" parameter for this. Check it on every cube, and monitor the memory usage in }StatsByCube. The cubes which are not loaded - were not used.

Very cool idea.

There's a caveat to LoadOnDemand -- every cube that is rule referenced or has feeders will be loaded regardless, so it's not as useful as the documentation would have you think
https://www.ibm.com/support/pages/demandload-property

ykud wrote: ↑Fri Apr 23, 2021 2:10 am There's a caveat to LoadOnDemand -- every cube that is rule referenced or has feeders will be loaded regardless, so it's not as useful as the documentation would have you think
https://www.ibm.com/support/pages/demandload-property

Yeah, good point, I didn't think about this...

Another lateral idea (inspired by Declanr's Stargate logging idea) (didn't check it, not sure it will work): tune your VMM+VMT so that little stargates would be saved and check "Number of Stored Views" in StatsByCube

Hi,

i would go Davids way and remove the access to the cube.
But maybe the }StatsByCubeByClient cube could be usefull - it logs user/cube use.

best regards,

orlando

You could try using the TM1 Loggers e.g.
This will give you details on the cube read, dimensions, time etc. You could read and parse the text file into TM1, Excel etc.

gtonkin wrote: ↑Fri Apr 23, 2021 12:32 pm You could try using the TM1 Loggers e.g.

Code: Select all

#--VIEWARRAY
log4j.logger.TM1.ViewArray=DEBUG, VIEWARRAY
log4j.appender.VIEWARRAY=org.apache.log4j.SharedMemoryAppender
log4j.appender.VIEWARRAY.MemorySize=5 MB
log4j.appender.VIEWARRAY.File=TM1Views.txt
log4j.appender.VIEWARRAY.MaxFileSize=5 MB
log4j.appender.VIEWARRAY.MaxBackupIndex=5
log4j.appender.VIEWARRAY.Timezone=Local

This is a good idea and will work but requires the caveat that on any productive system with quite a few users and concurrency this is going to really explode the tm1server.log

macsir wrote: ↑Thu Apr 22, 2021 8:19 pm I would expect IBM provide value for getting last access timestamp for a cube via REST API in the future.

I'm still waiting on Monospaced font in the TI editor.

ykud wrote: ↑Fri Apr 23, 2021 2:10 am There's a caveat to LoadOnDemand -- every cube that is rule referenced or has feeders will be loaded regardless, so it's not as useful as the documentation would have you think
https://www.ibm.com/support/pages/demandload-property

To me, that would indicate the the cubes are being used as a resource, at least somewhere.

orlando wrote: ↑Fri Apr 23, 2021 7:46 am Hi,

i would go Davids way and remove the access to the cube.
But maybe the }StatsByCubeByClient cube could be usefull - it logs user/cube use.

best regards,

orlando

It's a good idea for smaller platforms, I think, but regrettably will not work in this particular scenario. Still putting the idea in my mental toolbox for later. Stats by cube by client might be the way to go.

gtonkin wrote: ↑Fri Apr 23, 2021 12:32 pm You could try using the TM1 Loggers e.g.

Code: Select all

...

You could read and parse the text file into TM1, Excel etc.

I'd probably use Find in Files from N++. It's a good notion.

lotsaram wrote: ↑Fri Apr 23, 2021 2:37 pm This is a good idea and will work but requires the caveat that on any productive system with quite a few users and concurrency this is going to really explode the tm1server.log

Log size isn't really a factor here, but that is worthy of noting. For some setups it would be a bad call to keep it live all the time in prod.