Cognos TM1 Rand() function - it is NOT quite random after all !

Let me start the post with a Dilbert cartoon on Random number generator :)

Ok, so we have Rand() function in Cognos TM1 to generate random numbers,

1. Which can be used in Rules as well as in Turbo Integrator (TI)
2. Which is uniformly distributed between 0 and 1
3. That generates a decimal with 9 digit precision (theoretically up to a billion combination of numbers)
4. Whose seed is generated at the start of TM1 server

While I was analyzing the random number distribution, I noticed certain odd behavior.  Upon narrowing down the possible culprit, what I found out is that, IBM Cognos TM1 Rand () function can generate up to 65,536 unique numbers.  Thereafter it starts repeating the numbers.  To test this behavior, enter below piece of code in the prolog of a TI process and execute it.

i = 1; iMax = 65536; WHILE ( i <= iMax *2 );
    IF (i <= iMax);
        vFile = 'sk1.txt';
    ELSE;
        vFile = 'sk2.txt';
    ENDIF;
    vFile = GetProcessErrorFileDirectory | vFile ;
    AsciiOutput (vFile, NumberToString (Rand()) );
    i = i + 1; END;

It will generate 2 files where in

• sk1.txt captures 1st to 65,536 Rand() calls
• sk2.txt captures 65,537 onwards all the way to 131,072 Rand() calls

A screen grab showing the first several lines of these 2 files is below:

Once a TM1 server is ready then you can get up to 65,536 unique random numbers.  After that it is no longer random, it is predictable (with 100% accuracy). Besides, this number is on the server basis (not user basis)
Now let's modify the code slightly to prove the point.

vFile = 'sk3.txt';
vFile = GetProcessErrorFileDirectory | vFile ;
AsciiOutput (vFile, 'Code run by: ' | TM1User () );
i = 1;
iMax = 65536;
WHILE ( i <= iMax);
AsciiOutput (vFile, NumberToString (Rand()) );
i = i + 1;
END;

Run this process.  It will now create sk3.txt.  Log out from architect and log back in with a different user.  Modify the file name to sk4.txt, save and run the process

vFile = 'sk4.txt';

Let's compare sk3.txt with sk4.txt

 The random numbers generated between the 2 users are exactly the same i.e., the unique times IBM Cognos TM1 can generate random numbers is 65,536 and it is system wide (irrespective of users).  If the TM1 service associated with the server is restarted, then the slate is wiped clean.

Read IBM Cognos TM1 Data in IBM SPSS Modeler

One of the new feature added in SPSS Modeler 16, is the support of reading data from IBM Cognos TM1 and exporting data to it.  In this post we will look at:

1. Prerequisite for reading data from IBM Cognos TM1 into IBM SPSS Modeler
2. Reading data from IBM Cognos TM1 cube into IBM SPSS Modeler

We can therefore bring in the Enterprise Planning (or other data from IBM Cognos TM1) functionality into the Predictive Analytics capabilities of SPSS.  This brings in synergy between the two products.

Prerequisites

1. SPSS Modeler must be ver 16 or above and IBM Cognos TM1 needs to be 10.2 or higher
2. Before you begin working with IBM Cognos TM1 in SPSS Modeler (either for import or export), you will need to copy following three processes (.pro files) from IBM SPSS Modeler <Modeler install directory>/ext/bin/pasw.tm1/scripts to the DATA directory of your IBM Cognos TM1 server.  To do this, stop the Cognos TM1 service, copy these files and start the Cognos TM1 service
   • ExportToSPSS.pro
   • ImportFromSPSS.pro
   • SPSSCreateNewMeasures.pro
3. If you have multiple TM1 servers on a given TM1 Admin Host, then you'd need to copy these 3 .pro files to all those DATA directories, with which you want to connect in IBM SPSS Modeler
4. If you have done TM1 integration before applying IBM SPSS Modeler 16.0 FP1, then after FP1 is applied, you will need to copy the 3 .pro files all over again ... i.e. you will need to repeat the step #2.. Please read this IBM KB article
5. Measure dimension needs to be defined on the IBM CognosTM1 cube, from which you are importing data.  To do this, right click on the cube, choose properties and pick the measure dimension
   • There must be at least one public view defined on the cube.  Private views cannot be read from IBM SPSS Modeler
6. Per IBM Knowledge Center, the data that needs to be read must be in UTF 8 format

Read data from IBM Cognos TM1

Please take few minutes to read and follow the prerequisites.  After you have done, you are all set to get data from powerful in-memory database into your predictive analytics tool.

In your stream, click on the Sources and place the IBM Cognos TM1 Import palette on your canvas.  Double click on the palette and set its properties.  First enter the PM hub URL and click on Connect

In the TM1 Server drop down there will be list of TM1 servers, pick the one where the cube resides.  For our test purposes, we will use Planning Sample.  Click on Login, enter the credentials and click Ok.  Notice the list of IBM Cognos TM1 Cubes in shown in SPSS as well as in Architect.

A test cube, which I created "SK_Cube1" is missing in the SPSS list.  There is no measure dimension defined for this cube.  Consequently it does NOT appear in the cube list within SPSS Modeler (as mentioned in #5 prerequisite).  We will go ahead and define a measure for this cube.  It can be either done by right clicking on the cube or entering the suitable text in }CubeProperties cube.  Once you are done, click on refresh in SPSS Modeler

Take a look at the screen below.

• In the blue box on right side, it shows the views available on this cube as well as the sample data entered from TM1 Architect.  Observe that there are Public Views available.  "View 1" is a private view
• In the red box on left side, there are views listed under the cube in SPSS Modeler.  Since there are no public views available, you cannot work with this TM1 cube, yet

We will open the private view and save it as Public View.  Then click refresh in SPSS Modeler.  Select the view and click on Import.  Then click on Preview, to see the sample data imported.  Voila!

       

When you hit the Preview button, it is at this stage "ExportoSPSS" process is executed.  If this process is not present in your TM1 server, you will get error right away.