As a TM1 Admin have you ever wished for user maintained public subsets? User maintained public subsets puts the onus for maintaining said subsets back in the hands of the users, a welcome respite for an TM1 Admins attempting to accommodate a multitude of user requests.
Note: In order to avoid a proliferation of user maintained public subsets, the following assumes the subset to be maintained is already in existence.
Create a cube using the three dimensions shown below:
The qSubset Dimension contains element names which correspond to user maintained public subset names in other dimensions.
Example: The Element names above (qSubset Dimension) correspond to subset names on the Dimensions (BUDUSDU and Iterations) below.
Create a Picklist (text) Attribute that corresponds to the pool of elements from which a user can select for inclusion in their user maintained public subsets.
Create a second Text Attribute to link the qSubset Element Name to the Dimension in which it would be found.
The Slots Dimension is a listing of place holder elements. Sequential numbers tend to work best. Ensure the minimum number of elements is greater than or equal to the maximum number of elements in the largest/longest user maintained public subset.
Generic Input is a Dimension with a string value element or placeholder.
Updating User Maintained Public Subsets:
Create two Turbo Integrator Processes, one to load the user maintained public subsets and one to update the user maintained public subsets.
Turbo Integrator Process qSubset_Load:
- Data Source is none.
- There are no variables.
- Create a pSubset string Parameter.
Creating the View:
You are creating a view which will contain the elements of the user maintained public subsets.
- Cube Name is the name of the cube you just created.
- View Name is the name of the view you will be creating.
- Dimension Name is the name of the Dimension whose elements are the user maintained subset names i.e. qSubset.
- Subset Name is the name of the subset you will be creating. In the example below, zTI_ is concatenated with the pSubset parameter to denote a. the Subset is used in a Turbo Integrator process and b. which Dimension Subset the Turbo Integrator process will use.
- Subset Element Insert inserts the value from [parameter] pSubset when the Turbo Integrator Process is executed.
After the View is created with the subset name is cleared.
Populating the View:
- pCount is the number of elements in the user created public subset that will be maintained. The Dimension attribute created above tells the Turbo Integrator process to which Dimension (e.g. BUDUSDU or Iterations) the pSubset parameter entry belongs.
- Set pIndex to one.
- pElement is the name of the element in the pSubset Subset being processed.
- Cycle through the elements in the pSubset Subset
Determine which row/cell in zSubset_Creation is empty
- Count is the number elements in the place holder dimension i.e. the Slots Dimension.
- Set Index to one.
- Element is the Index number converted to a string (as noted above sequential number works best in the Slots Dimension).
- Check if the cell in zSubset_Creation corresponding to pSubset (subset name), element (row number) and string is blank.
- If the Cell is blank, populate it with the pElement.
- Set Index equal to Count.
- Increase the Index by one. If Index is equal to Count, increasing it by one will stop the While Loop; if less than Count, the search for an open cell to populate continues.
- Increase pIndex by 1 to process the next element in pSubset.
Destroy the View
Destroy the Subset
Turbo Integrator Process qSubset_Update:
- Data Source is Cube View
- Variables are as follows:
qDimension is the name of the dimension to which the subset to be updated belongs.
- Create a pSubset string Parameter
Create a view that houses the elements of the user maintained public subset loaded in the qSubset_Load Turbo Integrator Process.
Cube Name is the name of the cube.
View Name is the name of the view indicated in the Data Source Name.
Dimension Name is the name of the Dimension (e.g. BUDUSDU or Iterations) whose elements are the user maintained subset names i.e. qSubset.
Subset Name is the name of the subset you will be creating. In the example below, zTI_ is concatenated with the pSubset parameter to denote a. the Subset is used in a Turbo Integrator process and b. which Dimension Subset the Turbo Integrator process will use.
qDimension is the name of the Dimension (e.g. BUDUSDU or Iterations) which contains the actual user maintained public subset.
Delete all elements in the user maintained public subset [so that it can be repopulated].
Insert SValue [from the Data Source Name Cube View) into the qDimension (e.g. BUDUSDU or Iterations) pSubset.
Convert the Slot number to numeric to keep the subset order intact.
Clear the View.
Destroy the View.
Destroy the Subset.
End User Perspective:
Tie it all together for the users by creating a websheet.
Cube and Generic Input rows do not need to be visible to the users.
Hopefully this technique will save you time and endear you to your end users. At Lodestar Solutions our consultants and coaches teach you time saving tips. Hence, we empower you to deliver
Faster, Cheaper Analytics
Finally, if you like our tips, consider joining our Lodestar Club to get access to our member only library of educational videos. If you would like additional help on your TM1 project please contact at firstname.lastname@example.org.