}Stats cubes tools

Ideas and tips for enhancing your TM1 application
Post Reply
Wim Gielis
MVP
Posts: 2527
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

}Stats cubes tools

Post by Wim Gielis » Thu Jun 04, 2020 8:45 pm

Hi all,

Attached is a TI process I wrote to do 2 things:
- if pMode = 0, add elements and rules for the cube }StatsByCube
- if pMode = 1, export data from the cube }StatsByCube

Have a look in the TI process for more details.

With respect to exporting values, the idea is that a chore is run every hour. This export TI process will create a file in a custom folder in the TM1 logging directory (unless you specify your own file and folder).
Each file will have the data of the cube from the PAST hour, at minutes 0 / 5 / 10 / 15 / 20 / 25 / 30 / 35 / 40 / 45 / 50 / 55.
Like that, you can easily merge text files in the folder, over time, and use that combined file in an analysis in Excel (or similar) to show cube statistics over time. Or use Power Query in Excel to merge the files from a folder and let Excel do the heavy lifting for you. Or create a cube to store the values and a fancy dashboard in PAW for reporting/analysis.

The TI process is easily extendable to other }Stats cubes. You can decide to incorporate the 0 values or not.

Added benefits: interesting dates and times functions are used, and all in all, it's good to have a look at it.
It's probably better to have this export process running once an hour instead of once every 5 minutes (even though it's very quick and only treats data, no metadata and I use temporary subsets/views).
TECH_StatsByCube_additions.pro
(9.63 KiB) Downloaded 57 times
Any remark, let me know. A 'thank you' is also appreciated but my experience is that this is seldom done.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

User avatar
PavoGa
Community Contributor
Posts: 437
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: }Stats cubes tools

Post by PavoGa » Mon Jun 08, 2020 7:45 pm

:) Thanks!
Ty
Cleveland, TN

Wim Gielis
MVP
Posts: 2527
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: }Stats cubes tools

Post by Wim Gielis » Tue Jun 09, 2020 8:08 am

You're welcome :)
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Re: }Stats cubes tools

Post by HighKeys » Wed Aug 05, 2020 10:29 am

Thats more then genuis! Got some time to try it, and love it! Thanks for sharing wim!

Wim Gielis
MVP
Posts: 2527
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: }Stats cubes tools

Post by Wim Gielis » Thu Aug 06, 2020 10:58 am

Thanks for the kind comment !
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Post Reply