Create a very large dimension in Cognos TM1 using Turbo Integrator (TI)

There are instances during an implementation where in, there is a need to export out one or more cubes for Support.  However, client has reservation against data being taken out from their premises despite:

• Having signed an NDA (Non Disclosure Agreement)
• Erasing out original data and loading arbitrary data

Recently we ran into such situation.  Over the course of this and the next post, I will cover steps that we took to simulate the cube for Support.

The cube that we wanted to replicate had bunch of dimensions, that we could easily mimic – like Time, Chart of Accounts, Region, Type, Indicators etc.  However, for a one particular dimension it was a daunting task to mimic (at least manually).  It has about 500,000 elements in it.  The number of elements in it, continues to grow month-on-month basis, with a potential of adding roughly couple of thousand elements each month.

Besides being THE largest dimension in our model, entire implementation revolved around it.  Hence it was critical to have it mimicked and also load arbitrary data in couple of cubes using this dimension.

The TI process code is pasted below.  It accepts few parameters, the description is mentioned as well.  We’d need to enter the dimension size (number of leaf level elements), length of each element, whether they are numeric/text/alphanumeric and lastly the dimension name.

 

 This code relies heavily on the random number generation to determine the numbers/characters that go into the element.  So it natural to encounter is the repetition of random numbers in TM1 after 65,536 times.  Check out my previous post on this!

Here we are attempting to create half a million random elements which are made up of only text characters.  As you increase the number to a million or two, there is possibility of elements that are formed are repeated (since random numbers in TM1 start repeating).  In such instances, attempts to create elements upto the parameter specified may run into an infinite loop.  To avoid the possibility running the code forever, I am breaking the execution, if the attempt to create an element goes above 10K the dimension size. 

Turbo Integrator (TI) Code consists of only the prolog tab and here is the actual code:

vi_Siz = IF (pi_Siz = 0, 10000, pi_Siz);
vi_Len = IF (pi_Len = 0, 10, pi_Len);
vs_DimName = ps_Name;
vi_Attempt = 0;
vi_MaxHit  = 0;
vi_MulFac  = 1;
IF (DimensionExists (vs_DimName) > 0);
    # Either destroy the dimension or stop execution, based on your preference
    DimensionDeleteAllElements (vs_DimName);
ELSE;
    DimensionCreate (vs_DimName);
ENDIF;
WHILE (vi_Siz > 0);
    vs_Elem = ”;
    vi_Ctr = 1;
    WHILE (vi_Ctr <= vi_Len);
        # TM1 starts repeating random numbers after 65,536 times.  Hence use a multiplication factor to change the random value
        IF (vi_MaxHit = 64500);
            # This is where you can play around and alter way random numbers are generated
            vi_MulFac = vi_MulFac + Rand() * 2 + Rand ();
            vi_MaxHit = 0;
        ENDIF;
        vi_RandVal = Rand() * vi_MulFac;
        # If dimension elems are only numeric or alpha numeric with 50% of probablity, enter this block
        IF (pi_AlphaNumeric = 1 % (pi_AlphaNumeric = 3 & vi_RandVal < 0.5));
            vi_RandMod = Round (Mod (vi_RandVal * 10, (vi_Len -1)));
            vs_NewLetter = NumberToString (vi_RandMod);
        # If dimension elems are only string or alpha numeric with the other 50% of probablity, enter this block
        ELSEIF (pi_AlphaNumeric = 2 % (pi_AlphaNumeric = 3 & Rand() >= 0.5));
            vi_RandMod = Round (Mod (vi_RandVal * 100, 25));
            vs_NewLetter = Char (65 + vi_RandMod);
        ENDIF;
        vi_Ctr = vi_Ctr + 1;
        vs_Elem = IF (vi_RandVal < 0.5, vs_Elem | vs_NewLetter, vs_NewLetter | vs_Elem);
        vi_MaxHit  = vi_MaxHit + 1;
    END;
    IF (DimIx (vs_DimName, vs_Elem) = 0);
        DimensionElementInsert (vs_DimName, ”, vs_Elem, ‘N’);
        vi_Siz = vi_Siz – 1;
    ENDIF;
    vi_Attempt = vi_Attempt + 1;

    # Stop the loop, if the number of times you have attempted to create elements is about 10,000 higher than the element count
    # You can increase/decrease the number to your preference
    IF (vi_Attempt > pi_Siz + 10000);
        vi_temp = vi_siz;
        vi_Siz = 0;
    ENDIF;
END;

AsciiOutput (GetProcessErrorFileDirectory | ‘sk.txt’, ‘Number of time executed ‘ | NumberToString (vi_Attempt) | ‘, ‘ | NumberToString (vi_temp));

The code an be modified to make the elements begin with a certain character(s) like ‘SKU_’, ‘CC’ etc.  This is what the properties window shows after the code is run and some sample elements.