TM1 User Maintained Public Subsets

3/6/2017

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:

User Maintained Public Subset

The qSubset Dimension contains element names which correspond to user maintained public subset names in other dimensions.

User Maintained Public Subsets

Example:  The Element names above (qSubset Dimension) correspond to subset names on the Dimensions (BUDUSDU and Iterations) below.

User Maintained Public Subsets

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.

User Maintained Public Subsets

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.

User Maintained Public Subsets

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.

User Maintained Public Subsets

Prolog Tab:
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.

User Maintained Public Subsets

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.

User Maintained Public Subset

Epilog Tab:

Destroy the View

Destroy the Subset

User Maintained Public Subsets

Turbo Integrator Process qSubset_Update:
  • Data Source is Cube View

User Maintained Public Subseets

  • Variables are as follows:

User Maintained Public Subsets

qDimension:

qDimension is the name of the dimension to which the subset to be updated belongs.

User Maintained Public Subsets

  • Create a pSubset string Parameter

User Maintained Public Subsets

Prolog Tab:

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].

User Maintained Public Subsets

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.

User Maintained Public Subsets

Epilog Tab:

Clear the View.

Destroy the View.

Destroy the Subset.

User Maintained Public Subsets

End User Perspective:

Tie it all together for the users by creating a websheet.

User Maintained Public Subsets

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 services@lodestarsolutions.com.

Recreating TM1 Alternate Hierarchies

2/8/2017

Cognos TM1 allows alternate hierarchies which, from a user perspective, is greatly appreciated but from a TM1 Admin perspective, probably not so much.  The official hierarchy may be maintained from a data warehouse or general ledger while TM1 Alternate Hierarchies may be maintained from user defined data or attributes.  Also, TM1 Alternate hierarchies may be subjected to frequent changes e.g. territories assigned to managers.  And when multiple hierarchies are in use, maintaining or rebuilding one hierarchy without jeopardizing the remaining hierarchies can be tricky.  So, you need to create a Turbo Integrator Process to remove all children (including other parents) from a designated parent.

The following example is generic and can be used with any dimension and any parent within the dimension. Also, the rebuild of the hierarchy is not part of this TI Process.

The Data Source of this TI Process is None. (When rebuilding the hierarchy, select the appropriate Data Source.)

TM1 Alternate Hierarchies

There are no Variables or Maps in this generic TI Process. (If rebuilding, designate your Variables and Maps accordingly.)

Parameters:
TM1 Alternate Hierarchies

pDimension allows the Turbo Integrator Process to be generic and used with any dimension. (If rebuilding the hierarchy, pDimension can be omitted and the name of the dimension can be hard coded into the TI Process.)

pParent  is the Top Level Parent from which you want to remove all children. Even if this Turbo Integrator Process will be Dimension specific, skipping this parameter is not recommended since even a Top Level Parent can change. (You can populate the Default Value with the Top Level Parent.)

pYN is an optional parameter to determine if any parent elements below pParent should be deleted.

Parameter Check:

This is optional code to determine if the Parameters entered are valid. Dimension and Top Parent values must exist; pYN must be populated.

TM1 Alternate Hierarchies

Parent/child relationships may be used in more than one hierarchy.  In the example shown below, 1st Half and 2nd Half exist in both the TOTAL YEAR and YEAR hierarchies.

TM1 Alternate Hierarchies

If a parent/child relationship exists in the hierarchy with which you are working as well as another hierarchy, the relationship must be removed intact from the hierarchy with which you are working.

  • Determine the Level of pParent.
  • Set pLevelIndex to one less than the pParent pLevel.  You are looking for the children of pParent so the level of any children would be less than pParent.  So, start at the top and work your way down.  If you were to start at the bottom, you could potentially break other hierarchies as it would be difficult to know at what level the parent/child relationship starts.
  • Cycle through the elements in pDimension.
    • Does the level of the element equal the current index level?
    • Is pParent an ancestor of the element?
    • Does the element have more than one parent?
TM1 Alternate Hierarchies
Multiple Parents:

If the [pDimEle] element has more than one parent, the assumption is it belongs to more than one hierarchy.

  • Set the pLevelIndex2 to one higher than the current pLevelIndex.  You need to determine to which parent of the hierarchy the element with multiple parents belongs.
  • Cycle through the elements in pDimension.
    • Does the level of the element equal the current pLevelIndex2?
    • Is pParent an ancestor of the element?
  • Delete the element with multiple parents from the current parent.  (DeleteDirect is used so that, if needed, the dimension can be rebuilt as part of the TI Process.)
Tm1 Alternate Hierarchies

Because you only need the level above the current pLevelIndex, there is no need to increment pLevelIndex2.

  • pLevelIndex is decreased by one each time.  You need to work your way DOWN the levels of pParent.
Tm1 Alternate Hierarchies
Removing  Elements from TM1 Alternate Hierarchies:

Once all parent/child relationships that exist in other hierarchies are removed, the remaining relationships/elements can be removed from your hierarchy.

  • Reset pLevel to the Level of pParent.
  • Set the LevelIndex to 1.  You are working from the bottom UP.
  • Cycle through the elements in pDimension.
    • Does the Level of the element equal the current LevelIndex?
    • Is pParent an ancestor of the element or does the element equal pParent?
TM1 Alternate Hierarchy
  • Determine the number of Children of the parent element.
  • Determine the Child based on the pChildCount.
  • Delete the Child from the parent element.  (DeleteDirect is used so that the child is removed immediately.)
  • pChildCount is decreased by one each time as the number of children decreases each time one is removed.
TM1 Alternate Hierarchies
  • If the element type is "C" and the number of children is zero and pYN parameter is set to "Y", delete the parent element.
TM1 Alternate Hierarchies
Tm1 Alternate Hierarchies

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 members only library of education videos. If you would like additional help on your TM1 project please contact us at Services@lodestarsolutions.com.

X