IBM Cognos TM1 Import source in SPSS reads incorrect data from TM1 Cube - How to fix it?

Last week, I published a post on reading IBM Cognos TM1 data into SPSS Modeler, using the newly available source palette within SPSS Modeler called "IBM Cognos TM1 Import".  One of my colleagues posed a question, where in she was getting incorrect results using Cognos TM1 cube as source in SPSS.  Last digit of the number gets dropped off in SPSS Modeler!

If you are on SPSS Modeler ver 17, there is no need to fret.  This is seen only in SPSS Modeler Ver 16.  There is a well documented KB article on IBM Site - "IBM Cognos TM1 Import source node is reading continuous data incorrectly" (Reference #:1683459).  The article tells us to modify the Process ExportToSPSS to fix the issue

Value = if(VALUE_IS_STRING = 1, SVALUE, NumberToStringEx(NVALUE,'#####0.0##', '.', ','));

This post describes how to make this change in the process.  There are 3 ways to implement this fix:

1. Use IBM Cognos TM1 Architect
2. Use IBM Cognos TM1 Performance Modeler
3. Using Text Editor

Text Editor

You would really need to know what you are doing here.  There are numbers present in the beginning of every line, which TM1 system uses to parse out the code in a TI.  A wrong modification will result in unexpected behavior of the program, including errors.  I would not recommend this approach!

TM1 Performance Modeler

By far the easiest way to fix the code, especially in the current scenario.  I will explain more about using Architect and the problems faced, later.  If you open TM1 Performance Modeler, locate the TI process, double click, edit and save.  That's it.  You are ready to use the modified code in SPSS Modeler.
While the change is easy and swift to make, adding an extra line of code in TM1 Performance Modeler increased the file size from 55KB to 126KB !

TM1 Architect

If you open TM1 Architect, logon to the server, locate the TI and when you double click it, you get 2 error messages right away.  See the screens below:

The reason these messages pop up is because, this TI process uses IBM Cognos TM1 Cube View as data source and there is no cube view defined for it.:

If you click on Variables tab,you will notice there are 128 variables present.  So we will need to create a cube with 127 dimensions (yes that's right 127 dimensions, not 128).  128th variable will be the value (SValue or NValue) of the measure.

Using Architect to modify the process is not straightforward and will involve little bit of workaround.  I will explain that in detail here.  Below are the steps that need to be taken:

• Create a cube with 127 dimensions

To help you create a cube with 127 dimensions, I have created a TI process.  At this time of writing I am not sure, how to attach a file (other than media) to the blog.  Therefore I am pasting the code below.  In this process, couple of parameters are defined and the code exists only in the Prolog tab.

vs_CubeName = pCubeName;
vi_MaxDims = 127;
IF (CubeExists (vs_CubeName) > 0);
    ItemReject ('Cube already exists. Quitting program with error');
    ProcessError;
ENDIF;
WHILE (vi_MaxDims > 0);
    vs_DimName = pDimName | NumberToStringEx (vi_MaxDims, '000', '', '');
    IF (DimensionExists (vs_DimName) = 0);
        DimensionCreate (vs_DimName);
        DimensionElementInsert (vs_DimName, '', vs_DimName, 'N');
    ENDIF;
    vi_MaxDims = vi_MaxDims - 1;
END;
CubeCreate (vs_CubeName,
    pDimName | '001', pDimName | '002', pDimName | '003', pDimName | '004', pDimName | '005', pDimName | '006', pDimName | '007', pDimName | '008', pDimName | '009', pDimName | '010', pDimName | '011', pDimName | '012', pDimName | '013', pDimName | '014', pDimName | '015', pDimName | '016', pDimName | '017', pDimName | '018', pDimName | '019', pDimName | '020', pDimName | '021', pDimName | '022', pDimName | '023', pDimName | '024', pDimName | '025', pDimName | '026', pDimName | '027', pDimName | '028', pDimName | '029', pDimName | '030', pDimName | '031', pDimName | '032', pDimName | '033', pDimName | '034', pDimName | '035', pDimName | '036', pDimName | '037', pDimName | '038', pDimName | '039', pDimName | '040', pDimName | '041', pDimName | '042', pDimName | '043', pDimName | '044', pDimName | '045', pDimName | '046', pDimName | '047', pDimName | '048', pDimName | '049', pDimName | '050', pDimName | '051', pDimName | '052', pDimName | '053', pDimName | '054', pDimName | '055', pDimName | '056', pDimName | '057
', pDimName | '058', pDimName | '059', pDimName | '060', pDimName | '061', pDimName | '062', pDimName | '063', pDimName | '064', pDimName | '065', pDimName | '066', pDimName | '067', pDimName | '068', pDimName | '069', pDimName | '070', pDimName | '071', pDimName | '072', pDimName | '073', pDimName | '074', pDimName | '075', pDimName | '076', pDimName | '077', pDimName | '078', pDimName | '079', pDimName | '080', pDimName | '081', pDimName | '082', pDimName | '083', pDimName | '084', pDimName | '085', pDimName | '086', pDimName | '087', pDimName | '088', pDimName | '089', pDimName | '090', pDimName | '091', pDimName | '092', pDimName | '093',
pDimName | '094', pDimName | '095', pDimName | '096', pDimName | '097', pDimName | '098', pDimName | '099', pDimName | '100', pDimName | '101', pDimName | '102', pDimName | '103', pDimName | '104', pDimName | '105', pDimName | '106', pDimName | '107', pDimName | '108', pDimName | '109', pDimName | '110', pDimName | '111', pDimName | '112', pDimName | '113', pDimName | '114', pDimName | '115', pDimName | '116', pDimName | '117', pDimName | '118', pDimName | '119', pDimName | '120', pDimName | '121', pDimName | '122', pDimName | '123', pDimName | '124', pDimName | '125', pDimName | '126', pDimName | '127');
ViewCreate (vs_CubeName, 'All');

• Save and run the TI Process.  You will now see the cube name you supplied in parameter created and has 127 dimensions
• Map the data source of the process to use a view on this 127 dimension cube

To do this, open the ExportToSPSS process in Architect.  Click Ok on any errors you get.  In DataSource Tab, click on Browse and select the cube you created and choose "All" as the view

After this, click on the Variables tab, you will be prompted with couple of options.  Choose the one highlighted

You can now go to the Data Tab, do the modification as recommended in the KB Article.  Comment out the old line and add the new line and save the process.  Ensure that preview in the IBM Cognos TM1 Import Palette runs correctly.

# vValue = if(VALUE_IS_STRING = 1, SVALUE, NumberToString(NVALUE));
vValue = if(VALUE_IS_STRING = 1, SVALUE, NumberToStringEx(NVALUE,'#####0.0##', '.', ','));

Closing Thoughts

• If you are on IBM SPSS Modeler Ver 17, you will not face the issue of last digit being dropped
• Modifying the code through TM1 Performance Modeler is fastest; but it increases the file size by 2.5 times.  We will need a file compare utility to figure out what has been additionally added by TM1 Performance Modeler
• Using TM1 Architect is clean, however you will need to follow certain steps, before you are ready to make the change
• In Ver 17, the file is named slightly different and the code difference between ver 16 and 17 is huge.  See below screen

• As mentioned in my last blog, if you upgrade to a newer version of SPSS, there is need to copy these 3 files all over again to the various DATA directory of the TM1 server you are working with