How to handle TM1 load errors - No parent hierarchy and multiple parents’ hierarchy

Check list of all posts

Context

There are different methods to load data into TM1 cube, listed as follows:

1. Load it from data warehouse, or database. This method has no any problem in TM1, as all data validation is done in relational database side.
2. Load data from TM1 forms. Normal, dimension is setup before data entry, user can use pick list to input data. Therefore, this method has no problem with data quality either.
3. Load data from database, or files, but all tables and files are not validated. One typical problem is that some of Customers are not in dimension yet during loading Sales data.

This document is intended to resolve the problem of the 3rd method above.

Load approach

During loading sales data into Cognos database, some of Customer and SKU are not in Cognos yet. There are three options to handle this problem:

1. Hold the load until we resolve missed SKU and Customer. We could build this process, but it will result in a big effort.

2. Let the load go with warning. This is NOT an option, as the loaded data is NOT consistent with your source.

3. Insert an element into dimension, let sales load go through. For example, one customer is not available in dimension, and then the customer will be added into customer dimension. However, the hierarchy is missed.  

The cost effective solution is the 3rd approach. When load data into Cognos TM1, we can use Update to load data into TM1 cube. Sample as below

After load additional sales for new Customer 2 and Product 2

Then Sales data will be loaded even Customer 2 and Product 2 are not in dimension

Product group 2

Problems: No parent hierarchy and multiple parents hierarchy

However, we cannot simply leave customer and product dimension as loaded. We need to add parents to make dimension rollup. Otherwise data could be missed.

At the same time, we could experience problem when loading dimensions to have multiple parents. If a customer or SKU is assigned to two different parents, then your data will be double counted.  By the way, this problem is a generic problem and very difficult to debug, which could waste a big effort each time.

To sum the problem, there are two major issues:  1) no hierarchy and 2) multiple parent hierarchy;

Solution

The solution is to build a TM1 process to validate these two cases.

Create exception handling cube to hold errors

Assume that product hierarchy is listed as follows

Then we can create a cube to host all exceptions as below

Use TI process to populate exceptions into exception handling cube

vCheckDimensions='#Customer#Product#';

vExcludedTopNodes='#All Customers#Total Product#';

CubeClearData('Admin Exception');

vExceptionNo = 1;

#****Validate No parent hierachy****

d=1;

While(d<=Dimsiz('}Dimensions'));

  vDim=Dimnm('}Dimensions',d);

  If(Scan('#' | vDim | '#',vCheckDimensions)<>0 & (Subst(vDim,1,1) @<> '}'));

     e=1;

     While(e<=Dimsiz(vDim));

        vElem=Dimnm(vDim,e);

        If(Scan('#' | vElem | '#',vExcludedTopNodes)=0 & ElparN(vDim,vElem)=0);

            CellPutS('No parent hierachy', 'Admin Exception', NumberToString(vExceptionNo), 'Exception Kind');

           CellPutS( vDim,                       'Admin Exception', NumberToString(vExceptionNo), 'Exception Dimension');

           CellPutS( vElem,                     'Admin Exception',  NumberToString(vExceptionNo), 'Exception Element');

           vExceptionNo = vExceptionNo+1;

         EndIf;

        e=e+1;

     End;

  EndIf;

  d=d+1;

End;

#****Validate Multiple parent hierachy****

d=1;

While(d<=Dimsiz('}Dimensions'));

  vDim=Dimnm('}Dimensions',d);

  If(Scan('#' | vDim | '#',vCheckDimensions)<>0 & (Subst(vDim,1,1) @<> '}'));

     e=1;

     While(e<=Dimsiz(vDim));

        vElem=Dimnm(vDim,e);

        If(ElparN(vDim,vElem)>1);

           vParents='';

           p=1;

           While(p<=ElparN(vDim,vElem));

              vParent=Elpar(vDim,vElem,p);

              vParents=vParents|';'|vParent;

              p=p+1;

           End;

           CellPutS('Multiple parent hierachy', 'Admin Exception', NumberToString(vExceptionNo), 'Exception Kind');

           CellPutS( vDim,                               'Admin Exception', NumberToString(vExceptionNo), 'Exception Dimension');

           CellPutS( vElem,                             'Admin Exception',  NumberToString(vExceptionNo), 'Exception Element');

           CellPutS(NumberToString(ElparN(vDim,vElem)) | ' Parents: '  | Subst(vParents,Long(';')+1,Long(vParents)),

                                                                  'Admin Exception',  NumberToString(vExceptionNo), 'Exception Detail');

           vExceptionNo = vExceptionNo+1;

        EndIf;

        e=e+1;

     End;

  EndIf;

  d=d+1;

End;

Use form to report exceptions

We can either use TM1 web, or other client such as portal allow users to access the exceptions. If needed, we can build another forms to update dimension accordlingly.