August 14, 2019
Keeping track of both public and private subsets and views within a TM1 or Planning Analytics model is somewhat daunting since there has been no simple administrative function available to accomplish this task.
There are, of course, some “for-sale” tools available as well as various shell commands that offer partial solutions but if you don’t have a budget to purchase or the appropriate access to your TM1 data server so that you can run Command Line (CL) statements, you can accomplish the same thing using 2 simple TurboIntegrator (TI) processes: 1 to generate a list of all subset information and the other to format and present that information.
To get started, first create a TI process and indicate that there is no data source. This will allow you to add code to only the Prolog and Epilog – which is all you’ll need.
In the Prolog, add “loop logic” to iterate through the }Dimensions control dimension and a sub-loop iterating through the “}Clients” control dimension (the sub loop is required to include private subsets).
Here is “the trick”. Within the loop, code the ASCIIOUPUT function to create and write a MS Windows batch (.bat) file of a simple DOS command. The line of code will look like this:
ASCIIOUTPUT(‘s.bat’,’dir /B “‘| Trim(vFN)|’” > “‘|Trim(sD)| ‘subsets.txt”‘);
You can see from the above that we are writing the dir /B command to the file (named “s.bat”) which will then “direct” (using the “ > “ operator) the results of the command to a file named after the dimension (the variable “sD”), concatenated with the name “subsets.txt”, so for example “Accountsubsets.txt”. The variable “vFN” is important. It signifies the name of the folder to execute the DIR command on. Since TM1 stores subsets in folders named consistently with the dimension name and “}subs” we can set the variable as:
vFN = Trim(sD) | ‘}subs’;
For the private subsets, TM1 stores them in a sub folder named for the client, as in “User1\Account}subs”, so in the sub-loop we create our vFN with an extra step:
vFN = vClient | ‘\’ | Trim(vFN);
A portion of the generated .bat file is shown below:
Next, in the Epilog, we need to add a line of code to execute the .bat file that was just created (in the Prolog). To do this, we use the ExecuteCommand function (with the parameter “1” so that the process doesn’t wait for the call back from executing the .bat file):
When the .bat file is executed, it will create a text file for each dimension, listing all of the subsets for that dimension as records in it. Below is an example of the file generated for a Product dimension:
The last thing to do in this process is to copy the loop/sub-loop code from the Prolog section of our process and paste it in here, (after the ExecuteCommand we just added) and change the line with the ASCIIOUTPUT to again use the ExecuteCommand function, but this time to execute our second process (which we will create next; here I called it “TIProcess2”) and pass it the name of the dimension:
ExecuteProcess(‘TIProcess2′, ‘ pDimensionName ‘, Trim(sD));
So now that the first process is complete, we can go ahead and create our second TI process. This process will be defined with a Data Source of “Text” and, you guessed it, will read the text files that were created by executing the .bat file.
Below is the code we need to add to the Prolog of this TI process (“pDimensionName” is a run time parameter of type string that you need to add). The first line of code concatenates “subsets.txt” to the name of the dimension and the string then becomes the explicit Data Source of this process:
grabfle = ” | Trim(pDimensionName) | ‘subsets.txt’;
You can use this information in any way you want, perhaps format it into a web page (one of my favorite formats for presenting this information) or store it in a cube for other reporting. These processes can be run at any time (or on a schedule) to continually audit your model and maintain an “up to date” listing of all of subsets (this same method can be used for documenting public and private cube views!).
The results can also be comingled with other model information, which always makes a great impression and can be very useful. Below is a clip from a web page where I’ve created a “Dimensions Report” adding attribute information, number of elements, the cube(s) the dimension is part of and so on:
Another popular use of this information is a hierarchy showing the subsets (public and private) each client has access to, for each dimension. I won’t go into the code here, but just a few lines can produce something useful like the following:
There is literally an endless amount of enhancements you can make to the above approach to refine how it works and how the results are presented. The approach should also be generic enough to use with any TM1 or Planning Analytics mode.