}Stats cubes tools

Ideas and tips for enhancing your TM1 application
Post Reply
Wim Gielis
MVP
Posts: 2467
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 25 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: Selecting elements in the Subset Editor quickly

User avatar
PavoGa
Community Contributor
Posts: 430
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: 2467
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: Selecting elements in the Subset Editor quickly

Post Reply