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 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 blow that. Note as well the “Not Defined” member, which we identify as a blank substitute in the TI.

How to a Create a Virtual Dimension

Model Configuration

Check the TM1 Configuration file (tm1s.cfg) is configured to allow virtual dimensions. To do this add the following entry to tm1s.cfg:

EnableNewHierarchyCreation=T

Attributes

Ensure the relevant attribute for the virtual dimension exists, otherwise create it.

TI Process

Develop 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)

Detailed Explanation

These steps are detailed below.

Download the Zip File here

Check if a Heirarchy 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 );
ENDIF;

Create Hierarchy

We then want to create the hierarchy (virtual dimension) using the CreateHierarchyByAttribute function.:

CreateHierarchyByAttribute ( DimName, sAttrName , sBlankSubstitute , RootName );

where:

  • DimName – the dimension where it contain 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);

where:

  • 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):

Remember you can get a copy this TI by just adding your details below.

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.

If you like this post, please spread the love…

One comment on “How to Create Virtual Dimensions in TM1/Planning Analytics

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.