Hello TM1 enthousiasts! Here is a new article.

Recently I was challenged in a project to work with sandbox data from users. Meaning that they had the requirement to copy their sandbox data to a separate version (or a different cube could have been an option). Sandboxes in TM1 are privately owned delta values with respect to the base data in the cube (that every entitled user can see or change). A user needs WRITE access to a slice of data in order to be able to use the sandbox functionality.

All in all, the management of sandbox data is not easy. Bedrock processes do help but they require the user to know the name of their sandbox. It would be easier for the user to select the sandbox name from a subset on a dimension. It appears that we cannot do this in the process parameters with a member list.

In summary: we need to know all sandboxes for a given user. Let the user select one. And to know in which cubes the sandbox in particular has data.

That lead me to thinking that having an overview of all sandboxes in a TM1 model would be very useful information. For instance, is this functionality used? By whom? How many of them exist? That is why I wrote the generic TI process here. The process is written for TM1 V11. In TM1 V12 not only is the management of sandboxes done differently, direct TM1 REST API calls will make it possible to skip the step of text file amusements (like I had to do).

Here is the TI process code to play with it:

https://community.ibm.com/community/user/viewdocument/tm1-sandbox-overview?CommunityKey=8fde0600-e22b-4178-acf5-bf4eda43146b

The output can be any or all of the 3 following types:

• A CSV text file
• A control dimension
• A control cube

When the default values are not changed, the output values will be as mentioned above. Changing these values is dead easy with the constant values in the Prolog tab.

The output will be similar to the below pictures. In the second picture we see a dimension of unique combinations: concatenations of 1-2-3 dimension elements that are also visible in the cube. This makes it easy to just filter on the sandboxes of 1 particular user. All permutations of the 3 variables exist and elements are prefixed to enforce uniqueness.

The Default view and subsets in the first picture are created on the fly with the process.

What you can learn from the TI process, includes the following:

• create MDX views and subsets
• show recursion
• add rules to a cube
• formatting measures with 0 values invisible
• a process without a data source and still it has data source variables
• append text to a csv file without overwriting
• interact with the TM1 data directory folders
• loop over files with WildcardFileSearch() in the cleanest possible way
• global string variables
• the Sign() function to come to a boolean 1/0 variable
• log information to the TM1 server log in a convenient way
• descriptive names for the users (}Clients elements)
• Direct metadata functions to conveniently avoid metadata locking and avoid duplicating code
• the Expand() function
• much more to learn from...