How to Create Virtual Dimensions in TM1/Planning Analytics
Virtual Dimensions in TM1 and Planning Analytics allow you to use Attributes to create additional analytic capability. Here we briefly discuss what they are and then what you need to do to create them with code samples.
What are Virtual Dimensions
Virtual dimensions, or hierarchies, add an analytic capability to TM1 cubes without changing the physical structure of those cubes by building structures from attributes that can be used just like physical dimensions. For example, you could have attributes on a Customer dimension for Customer Type and Customer State and then add virtual dimensions for Customer Type and Customer State and then be able to analyse all three (Customer Type, Customer State virtual dimensions and the physical Customer dimension) as if the two virtual are physical dimensions.
For a full explanation of virtual dimensions, sometimes called hierarchies, please see this post.
Here is an example of an Employee State hierarchy on an Employee dimension. We will build this below. You can see the State hierarchy created as part of the Employee dimension and then the members of the hierarchy below that. Note as well the “Not Defined” member, which we identify as a blank substitute in the TI.
How to Create a Virtual Dimension
Check the TM1 Configuration file (tm1s.cfg) is configured to allow virtual dimensions. To do this add the following entry to tm1s.cfg:
Ensure the relevant attribute for the virtual dimension exists, otherwise create it.
Develop a TI process that does the following:
- Test if a hierarchy exists and if it does, then destroy it,
- Create the hierarchy, and
- Assign security to it (if required)
Check if a Hierarchy Exists
This entire TI runs on the Prolog.
First, we want to test the existence of the Hierarchy using. To do this we use:
HierarchyExists ( DimName , sHierName ) = 1;
This test returns 1 if the hierarchy exists. Then, if it does exist, then destroy the hierarchy using:
HierarchyDestroy ( DimName , sHierName );
So the whole portion of the TI would look like this:
DimName = 'Employee';
sAttrName = 'State';
sHierName = 'Employee State';
sBlankSubstitute = 'Not Defined';
sRootName = 'All Employee Stated';
IF ( HierarchyExists ( DimName , sAttrName ) = 1);
HierarchyDestroy ( DimName , sAttrName );
We then want to create the hierarchy (virtual dimension) using the CreateHierarchyByAttribute function.:
CreateHierarchyByAttribute ( DimName, sAttrName , sBlankSubstitute , RootName );
- DimName – the dimension where it contains the attribute and is also the dimension where the hierarchy will be created.
- sAttrName – the attribute to create the hierarchy from.
- sBlankSubstitute – where it collects the dimension leaves that without an attributes value – OPTIONAL.
- sRootName – overrides the root element name which by default is named after the attribute. This is the very top of the hierarchy, like “All Customer States”, for example – OPTIONAL.
Assign Security (if required)
If you have element level security on the physical dimension you are creating the virtual on, then you will need to assign security to the new hierarchy. To do this we use the function called HierarchyElementSecurityPut to loop through user groups to assign proper security level (eg. READ, WRITE). The syntax of this is:
HierarchyElementSecurityPut ( SecLvl , DimName, sHierName, Element, Group);
- SecLvl – the security level to be assigned from None, Read, Write, Reserve, Lock, Admin.
- DimName – the physical dimension
- sHierName – the hierarchy to be updated
- Element – the element which you are assigning security
- Group – the security group which you are assigning a level
Full Code of TI to add a Virtual Dimension or Hierarchy
The full code for this TI is (with the security stuff hashed out):
Test the TI Process to Create the Virtual Dimension
The final step is to execute the TI developed with the functions above to create the Virtual Dimension.
Analysis using the Virtual Dimension is possible after the TI is executed. They are visible under the physical dimension as a hierarchy in Planning Analytics for Excel (PAX) or Planning Analytics Workspace.