Documenting TM1

Documenting TM1 admin Tue, 2019-11-19 08:57
Categories

A closer look at chores

A closer look at chores admin Tue, 2019-11-19 08:58

if you ever loaded a .cho file in an editor this is what you would expect:

534,8
530,yyyymmddhhmmss ------ date/time of the first run
531,dddhhmmss ------ frequency
532,p ------ number p of processes to run
13,16
6,"process name"
560,0
13,16
533,x ------ x=1 active/ x=0 inactive

In the 9.1 series it is possible to see from the Server Explorer which chores are active from the chores menu.
However this is not the case in the 9.0 series, also it is not possible to see when and how often the chores are running unless you deactivate them first and edit them. Not quite convenient to say the least.
From the specs above, it is easy to set rules for a parser and deliver all that information in a simple report.
So the perl script attached below is doing just that: listing all chores on your server, their date/time of execution, frequency and activity status.

Procedure to follow:

  1. Install perl
  2. Save chores.pl in a folder
  3. Doubleclick on chores.pl
  4. A window opens, enter the path to your TM1 server data folder there
  5. Open resulting file chores.txt created in the same folder as chores.pl

Result:

ACT /     date-time     /    frequency   / chore name
 X   2005/08/15 04:55:00 007d 00h 00m 00s currentweek
 X   2007/04/28 05:00:00 001d 00h 00m 00s DailyS
 X   2007/05/30 05:50:00 001d 00h 00m 00s DAILY_UPDATE
 X   2007/05/30 05:40:00 001d 00h 00m 00s DAILY_S_UPDATE
 X   2005/08/13 20:00:05 007d 00h 00m 00s eweek
 X   2006/04/06 07:30:00 001d 00h 00m 00s a_Daily
 X   2007/05/30 06:05:00 001d 00h 00m 00s SaveDataAll
 X   2007/05/28 05:20:00 007d 00h 00m 00s WEEKLY BUILD
 X   2005/05/15 21:00:00 007d 00h 00m 00s weeklystock
     2007/05/28 05:30:00 007d 00h 00m 00s WEEKLY_LOAD
Categories

A closer look at subsets

A closer look at subsets admin Tue, 2019-11-19 09:02

if you ever loaded a .sub file (subset) in an editor this is the format you would expect:

283,2 start
11,yyyymmddhhmmss creation date
274,"string" name of the alias to display
18,0 ?
275,d d = number of characters of the MDX expression stored on the next line
278,0 ?
281,b b = 0 or 1 "expand above" trigger
270,d d = number of elements in the subset followed by the list of these elements, this also represents the set of elements of {TM1SubsetBasis()} if you have an MDX expression attached

These .sub files are stored in cube}subs folders for public subsets or user/cube}subs for private subsets.

Often a source of discrepancy in views and reports is the use of static subsets. For example a view was created a while ago, displaying a bunch of customers, but since then new customers got added in the system and they will not appear in that view unless they are manually added to the static subset.

Based on the details above, one could search for all non-MDX/static subsets (wingrep regexp search 275,$ in all .sub files) and identify which might actually need to be made dynamic in order to keep up with slowly changing dimensions.

Categories

Beam me up Scotty: 3D Animated TM1 Data Flow

Beam me up Scotty: 3D Animated TM1 Data Flow admin Tue, 2019-11-19 09:05

Explore the structure of your TM1 system through the Skyrails 3D interface:

If you do not have flash, you can have a look at some screenshots
/!\ WARNING: your eyeballs may pop out!

This is basically the same as the previous work with graphviz, except this time it is pushed to 3D, animated and interactive.

So the visualisation engine Skyrails is developed by Ph.D. student Yose Widjaja.
I only wrote the TM1 parser and associated Skyrails script to port a high level view of the TM1 Data flow into the Skyrails realm.

How to proceed:

  • Download and unzip skyrails beta 2nd build
  • Download and unzip TM1skyrails.zip (attachment below) in the skyraildist2 folder
  • In the skyraildist2 folder, doubleclick TM1skyrails.pl (you will need perl installed unless someone wants to provide a compiled .exe of the script with the PAR module)
  • Enter the path to (a copy of) your TM1 Data folder
  • Skyrails window opens, click on the "folder" icon and click TM1
  • If you don't want to install perl, you can still enjoy a preview of the Planning Sample that comes out of the box. Just double-click on raex.exe.

w,s,a,d keys to move the camera

Quick legend:
orange -- cube
blue -- process
light cyan -- file
red -- ODBC source
green sphere -- probably reference to an object that does not exists (anymore)
green edge: intercube rule flow
red edge: process (CellGet/CellPut) floww

Changelog and Downloads:

1.0
1.1 a few mouse gestures added (right click on a node then follow instructions) to get planar (like graphviz) and spherical representations.
1.2 - edges color coded, see legend above
- animated arrows
- gestures to display different flows (no flow/rules only/processes only/all flow)

 

 

 

Categories

Dimensions updates mapping

Dimensions updates mapping admin Tue, 2019-11-19 14:18

When faced with a large "undocumented" TM1 server, it might become hard to see how dimensions are being updated.

The following perl/graphviz script creates a graph to display which processes are updating dimensions.

The script dimflow.pl is looking for functions updating dimensions (DimensionElementInsert, DimensionCreate...) in .pro files in the TM1 datafolder and maps it all together.
Unfortunately it does not take into account manual editing of dimensions.

This is the result:

Dimensions Flow

Legend:
processes = red rectangles
dimensions = blue bubbles

The above screenshot is probably a good example of why such map can be useful: you can see immediately that several processes are updating the same dimensions.
It might be necessary to have several processes feeding a dimension, though it will be good to review these processes to make sure they are not redundant or conflicting.

Procedure to follow:

  1. Install perl and graphviz
  2. Download the script below and rename it to .pl extension
  3. Doubleclick on it
  4. Enter the path to your TM1 Data folder (\\servername\datafolder)
  5. This will create 2 files "dim.dot" and "dim.gif" in the same folder as the perl script
  6. Open dim.gif with any browser / picture editor
Categories

Duplicate Elements in Rollups

Duplicate Elements in Rollups admin Thu, 2019-11-21 14:12

Large nested rollups can lead to elements being counted twice or more within a rollup.
The following process helps you to find, across all dimensions of your system, all rollups that contain elements consolidated more than once under the rollup.

The code isn't the cleanest and there are probably better methods to achieve the same result so don't hesitate to edit the page or comment.

It proceeds like this:

loop through all dimensions
  loop through all rollups of a dimension
    if(number of distinct elements in rollup <> number of elements in rollup)
      then loop through all alphanumerically sorted elements of the rollup
        duplicates are found next to each other

The process takes less than 30 sec to run on a 64bit 9.1.4 system with 212 dimensions, one of the dimensions holds more than a 1000 consolidations and some "decent" nesting (9 levels).

/!\ This process will not work with some TM1 versions because of the MDX function DISTINCT, it has been successfully tested on v. 9.1.4.
DISTINCT does not work properly when applied to expanded rollups on v. 9.0.3

You can just copy/paste the following code in the Prolog window of a process.
Just edit the first line to dump the results where you would like to see them.

/!\ The weight of the duplicated elements is also shown, just in case identical elements cancel each other out with opposite weights.

report = 'D:\duplicate.elements.csv'; #config the line above. #caveat: empty or consolidations-only dimensions will make this process fail, because empty subsets cannot be created allrollups = '}All rollups'; subset = '}compare'; maxDim = DimSiz('}Dimensions'); i = 1; #for each dimension while (i <= maxDim); vDim = DIMNM('}Dimensions',i); #test if there are any rollups #asciioutput(report, vDim); SubsetDestroy(vDim, allrollups); SubsetCreatebyMDX(allrollups,'{TM1FILTERBYLEVEL( {TM1SUBSETALL([' | vDim | '])},0)}'); If(SubsetGetSize(vDim,allrollups) <> DimSiz(vDim)); #get a list of all rollups SubsetDestroy(vDim, allrollups); SubsetCreatebyMDX(allrollups,'{EXCEPT(TM1SUBSETALL([' | vDim | ']), TM1FILTERBYLEVEL( {TM1SUBSETALL([' | vDim | '])},0))}'); maxRolls = SubsetGetSize(vDim,allrollups); rollup = 1; #for each rollup in that dimension while (rollup <= maxRolls); Consolidation = SubsetGetElementName(vDim, allrollups, rollup); If(SubsetExists(vDim, subset) = 1); SubsetDestroy(vDim, subset); Endif; SubsetCreatebyMDX(subset, '{DISTINCT(TM1DRILLDOWNMEMBER( {[' | vDim | '].[' | Consolidation | ']}, ALL, RECURSIVE))}' ); Distinct = SubsetGetSize(vDim, subset); SubsetDestroy(vDim, subset); SubsetCreatebyMDX(subset, '{TM1DRILLDOWNMEMBER( {[' | vDim | '].[' | Consolidation | ']}, ALL, RECURSIVE)}' ); All = SubsetGetSize(vDim, subset); if(All <> Distinct); SubsetDestroy(vDim, subset); #alphasort consolidation elements so duplicates are next to eachother and a 1-pass is enough to find them all SubsetCreatebyMDX(subset, '{TM1SORT( {TM1DRILLDOWNMEMBER( {[' | vDim | '].[' | Consolidation | ']}, ALL, RECURSIVE)},ASC) }' ); maxElem = SubsetGetSize(vDim, subset); prevE = ''; nelem = 1; while(nelem <= maxElem); element = SubsetGetElementName(vDim, subset, nelem); #if 2+ identical elements next to each other then these are the duplicates If(prevE @= element); AsciiOutput(report,'',vDim,Consolidation,element,NumberToString(ELWEIGHT(vDim,Consolidation,element))); Endif; prevE = element; nelem = nelem + 1; end; Endif; rollup = rollup + 1; end; SubsetDestroy(vDim, allrollups); If(SubsetExists(vDim, subset) = 1); SubsetDestroy(vDim, subset); Endif; Endif; i = i + 1; end;

Graphing TM1 Data Flow

Graphing TM1 Data Flow admin Tue, 2019-11-19 14:26

This is the new version of genflow.pl a little parser written in perl that will create a input file for graphviz from your TM1 .pro and .rux files then generate a graph of the data flow in your TM1 server...

TM1 Data Flow

(the image has been cropped and scaled down for display, the original image is actually readable)

TM1 Data Flow legend

Legend

ellipses = cubes, rectangles = processes
red = cellget, blue = cellput, green = inter-cube rule

Procedure to follow:

  1. Install perl and graphviz
  2. Put the genflow perl script in any folder, make sure it has the .pl extension (not txt)
  3. Doubleclick on it
  4. Enter the path to your TM1 Data folder such as: \\servername\datafolder where \\servername\datafolder is the full file path to your TM1 data folder
  5. Hit return and wait until the window disappears
  6. This creates 2 files: "flow.dot" and "flow.gif" in the same folder as the perl script
  7. Open "flow.gif" in any browser or picture editor

Changelog

1.4:
.display import view names along the edges
.display zeroout views
.sources differentiated by shape

1.3:
.CellPut parsing fix
.cubes/processes names displayed 'as is'

This is still quite experimental but this could be useful to view at a glance high-level interactions between your inputs, processes, cubes and rules.

Categories

Indexing subsets

Indexing subsets admin Tue, 2019-11-19 14:44

Maintaining subsets on your server might be problematic. For example you wanted to delete an old subset that you found out to be incorrect and your server replied this:

Delete Subset failed popup

This is not quite helpful, as it does not say which views are affected and need to be corrected.

Worse is that, as Admin, you can delete any public subset as long as it is not being used in a public view. If it is used in a user's private view, it will be deleted anyway and that private view might become invalid or just won't load.

In order to remediate to these issues, I wrote this perl script indexsubset.pl that will:

  • Index all your subsets, including users' subsets.
  • Display all unused subsets (i.e. not attached to any existing views)

From the index, you can find out right away in which views a given subset is used.

I suppose the same could be achieved through the TM1 API though you would have to log as every user in turn in order to get a full index of all subsets.

Run from a DOS shell: perl indexsubset.pl \\path\to\TM1\server > mysubsets.txt

Categories

Processes running history

Processes running history admin Tue, 2019-11-19 14:49

On a large undocumented and mature TM1 server you might find yourself with a lot of processes and you wonder how many of them are still in use or the last time they got run.

The loganalysis.pl script answers these questions for you.

One could take a look at the creation/modification time of the processes in the TM1 Data folder however you would have to sit through pages of the tms1msg.log to get the history of a given process which is what the script below does.

Procedure to follow for TM1 9.0 or 8.x

  1. Install perl
  2. Save loganalysis.pl in a folder
  3. Stop your TM1 service (necessary to beat the windows lock on tm1smsg.log)
  4. Copy the tm1smsg.log into the same folder where loganalysis.pl is
  5. Start your TM1 service
  6. Double click loganalysis.pl

 

Procedure to follow for TM1 9.1 and above

  1. Install perl
  2. Save loganalysis.pl in a folder
  3. Copy the tm1server.log into the same folder where loganalysis.pl is
  4. Double click loganalysis.pl

That should display the newly created processes.txt in notepad and that should look like the following:

First, all processes sorted by name and the last run time, user and how many times it ran.

processes by name:
2005load run 2006/02/09 15:02:33 user Admin [x2]
ADMIN - Unused Dimensions run 2006/04/26 14:02:58 user Admin [x1]
Branch Rates Update run 2006/10/19 15:23:29 user Admin [x1]
BrandAnalysisUpdate run 2005/04/11 08:09:13 user Admin [x33]
....

Second, all processes sorted by last run time, user and how many times it ran.

processes by last run: 
2005/04/11 08:09:13 user Admin ran BrandAnalysisUpdate [x33]
2005/04/11 10:26:29 user Admin ran LoadDelivery [x1]
2005/04/19 08:44:22 user Admin ran UpdateAntStockage [x19]
2005/04/26 14:18:17 user Admin ran weeklyodbc [x1]
2005/05/12 08:34:16 user Admin ran stock [x1]
2005/05/12 08:37:59 user Admin ran receipts [x1]
....

 

Categories

The case against single children

The case against single children admin Tue, 2019-11-19 14:55

I came across hierarchies holding a single child.
While creating a consolidation over only 1 element might make sense in some hierarchies, some people just use consolidations as an alternative to aliases.
Either they just don't know they exist or they come from an age when TM1 did not have aliases yet.

The following process will help you identify all the "single child" elements in your system.
This effectively loops through all elements of all dimensions of your system, so this could be reused to carry out other checks.

#where to report the results
Report = '\\tm1server\reports\single_children.csv';

#get number of dimensions on that system
TotalDim = Dimsiz('}Dimensions');

#loop through all dimensions
i = 1;
While (i <= TotalDim);
  ThisDim = DIMNM('}Dimensions',i);

  #foreach dimension
  #loop through all their elements 
  j = 1;
  While (j <= Dimsiz(ThisDim));
    Element = DIMNM(ThisDim,j);
    #report the parent if it has only 1 child  
    If( ELCOMPN(ThisDim, Element) = 1 );
      AsciiOutput(Report,ThisDim,Element,ELCOMP(ThisDim,Element,1));
    Endif;
    #report if consolidation has no child!!!
    If( ELCOMPN(ThisDim, Element) = 0 & ELLEV(Thisdim, Element) > 0 );
      single = single + 1;
      AsciiOutput(Report,ThisDim,DIMNM(ThisDim,j),'NO CHILD!!');
    Endif;
  j = j + 1;
  End;
  i = i + 1; 
End;
Categories