Cube last data pull
-
- Posts: 3
- Joined: Wed Apr 21, 2021 7:47 pm
- OLAP Product: PAW/PAX
- Version: PAW/PAX
- Excel Version: 365
Cube last data pull
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 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.
-
- Site Admin
- Posts: 1457
- Joined: Wed May 28, 2008 9:09 am
Re: Cube last data pull
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....
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Cube last data pull
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.
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.
Declan Rodger
- Elessar
- Community Contributor
- Posts: 344
- Joined: Mon Nov 21, 2011 12:33 pm
- OLAP Product: PA 2
- Version: 2.0.9
- Excel Version: 2016
- Contact:
Re: Cube last data pull
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.
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.
Best regards, Alexander Dvoynev
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
-
- MVP
- Posts: 3121
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Cube last data pull
That's a good piece of lateral thinking
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 3
- Joined: Wed Apr 21, 2021 7:47 pm
- OLAP Product: PAW/PAX
- Version: PAW/PAX
- Excel Version: 365
Re: Cube last data pull
@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'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
- MVP
- Posts: 785
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: Cube last data pull
I would expect IBM provide value for getting last access timestamp for a cube via REST API in the future.
- Alan Kirk
- Site Admin
- Posts: 6608
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Cube last data pull
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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
- PavoGa
- MVP
- Posts: 617
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: Cube last data pull
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
- Community Contributor
- Posts: 344
- Joined: Mon Nov 21, 2011 12:33 pm
- OLAP Product: PA 2
- Version: 2.0.9
- Excel Version: 2016
- Contact:
Re: Cube last data pull
Yeah, good point, I didn't think about this...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
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
Best regards, Alexander Dvoynev
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
- orlando
- Community Contributor
- Posts: 167
- Joined: Fri Aug 04, 2017 8:27 am
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: Office 365
Re: Cube last data pull
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
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
- MVP
- Posts: 1202
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Cube last data pull
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.
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
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
-
- MVP
- Posts: 3657
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Cube last data pull
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.loggtonkin 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
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- Posts: 3
- Joined: Wed Apr 21, 2021 7:47 pm
- OLAP Product: PAW/PAX
- Version: PAW/PAX
- Excel Version: 365
Re: Cube last data pull
I'm still waiting on Monospaced font in the TI editor.
To me, that would indicate the the cubes are being used as a resource, at least somewhere.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
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.
I'd probably use Find in Files from N++. It's a good notion.gtonkin wrote: ↑Fri Apr 23, 2021 12:32 pm You could try using the TM1 Loggers e.g.
You could read and parse the text file into TM1, Excel etc.Code: Select all
...
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.