Cube last data pull

[korthal]

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.

[David Usherwood]

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....

[declanr]

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.

[Elessar]

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.

[Wim Gielis]

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

[korthal]

@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.

[macsir]

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

[Alan Kirk]

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.

[PavoGa]

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.

[ykud]

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

[Elessar]

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

[orlando]

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

[gtonkin]

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 will give you details on the cube read, dimensions, time etc.

Code: Select all

13100   [448f]   DEBUG   2021-04-23 12:28:23.883   TM1.ViewArray     cube: [Scenario Locking]
13100   [448f]   DEBUG   2021-04-23 12:28:23.883   TM1.ViewArray     user: [CAMID("pans:u:tm1_gtonkin@xyz.com")]
13100   [448f]   DEBUG   2021-04-23 12:28:23.883   TM1.ViewArray     There are 3 title elements:
13100   [448f]   DEBUG   2021-04-23 12:28:23.883   TM1.ViewArray         [Scenario].[Rolling Forecast]
13100   [448f]   DEBUG   2021-04-23 12:28:23.883   TM1.ViewArray         [Company Code].[1234]
13100   [448f]   DEBUG   2021-04-23 12:28:23.883   TM1.ViewArray         [}Groups].[Balance Sheet Planner]
13100   [448f]   DEBUG   2021-04-23 12:28:23.883   TM1.ViewArray   TM1ViewImpl::STGCreate took 192 microseconds.
13100   [448f]   DEBUG   2021-04-23 12:28:23.883   TM1.ViewArray   View Array Construction succeeded
19512   [448f]   DEBUG   2021-04-23 12:28:25.271   TM1.ViewArray   Beginning View Array Construction for view: NULL (abstraction 0x000000374FEA3568, mdx select)
19512   [448f]   DEBUG   2021-04-23 12:28:25.271   TM1.ViewArray     cube: [}ElementAttributes__S-General Ledger Account]
19512   [448f]   DEBUG   2021-04-23 12:28:25.271   TM1.ViewArray     user: [CAMID("pans:u:tm1_gtonkin@xyz.com")]
19512   [448f]   DEBUG   2021-04-23 12:28:25.271   TM1.ViewArray     There is 1 title element:
19512   [448f]   DEBUG   2021-04-23 12:28:25.271   TM1.ViewArray         [_S-General Ledger Account].[0010000010]
19512   [448f]   DEBUG   2021-04-23 12:28:25.271   TM1.ViewArray   TM1ViewImpl::STGCreate took 128 microseconds.
19512   [448f]   DEBUG   2021-04-23 12:28:25.271   TM1.ViewArray   View Array Construction succeeded

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

[lotsaram]

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

[korthal]

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.

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.

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.

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.

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.