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. For example, you could add virtual dimensions for Customer Type and Customer State to a physical Customer dimension and then be able to analyse all three (Customer Type, Customer State virtual dimensions and the physical Customer dim) as if the two virtual are physical dimensions.

For a full explanation of virtual dimensions, sometimes called hierarchies, please see this post.

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 it:

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)

These steps are detailed below.

Check if a Heirarchy Exists

First we want to test the existence of the Hierarchy using. To do this we use:

HierarchyExists ( DimName , sHierName );

If it does exist, then destroy the hierarchy using:

HierarchyDestroy ( DimName , sHierName );

So the whole portion of the TI would look like this:

DimName = 'Customer';

sAttrName = 'Customer State’;

sHierName = sAttrName;

IF ( HierarchyExists ( DimName , sHierName ) = 1);

  HierarchyDestroy ( DimName , sHierName );

ENDIF;

Create Hierarchy

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

CreateHierarchyByAttribute ( DimName, Attribute , BlankSubstitute , RootName );

where:

  • DimName – the dimension where it contain the attribute and is also the dimension where the hierarchy will be created.
  • Attribute – the attribute to create the hierarchy from.
  • BlankSubstitute – where it collects the dimension leaves that without an attributes value – OPTIONAL.
  • RootName – overrides the root element name which by default is named after the attribute – 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, HierName, Element, Group);

where:

  • SecLvl – the security level to be assigned from None, Read, Write, Reserve, Lock, Admin.
  • DimName – the physical dimension
  • HierName – the hierarchy to be updated
  • Element – the element which you are assigning security
  • Group – the security group which you are assigning a level

The full code for this loop is:

sAttributesCube = '}ElementAttributes_DimensionName';

sSecLvl = 'WRITE';

nEls = DIMSIZ ( DimName );

nCount = 1;

WHILE ( nCount <= nEls );

    sEl = DIMNM ( DimName, nCount );

    IF ( ELLEV ( DimName, sEL) =0 ) ;

         sElement = CellGetS(sAttributesCube, sEl, sHierName );

         IF ( sElement @= '' % sElement @= ' ' );

            sElement = 'Others';

         ENDIF;

    nGroups = DIMSIZ('}Groups');

    nCount1 = 1;

    WHILE ( nCount1 <= nGroups );

       sGroup = DIMNM('}Groups', nCount1);

       HierarchyElementSecurityPut ( sSecLvl , DimName, sHierName, sElement, sGroup);

       nCount1 = nCount1 + 1;

    END;

    ENDIF;

    nCount = nCount+1;

END;

Execute 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…

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