Automatically/manually run chore producess different values

[abcuser]

Hi,
I am facing a strange problem in TM1. Processes to create a cube is scheduled (chore) at night. When I check the data from TM1 cube in the morning and compare the data to warehouse I see the data in cube are wrong - they are always little bit bigger that it should be. If I manually run a chore at the morning then there are correct values in cube. This is very strange to me.

In processes of building cube all dimensions are unique to this cube. Dimensions are created with recreate option and cube is emptied with CubeClearData command before new data are loaded into cube. Cube is created with update option. This cube has no rules and no views.

I have written a SQL batch job to check the data in source table (from witch TM1 processes are accessing data to build this cube) every 5 minutes and data are changed once per night and there are NEWER the values that appear after chore is automatically run in the night. Any idea what could be wrong?

My system:
- Cognos BI v8.4.1 fixpack 3 Report Studio as reporting tool accessing cubes
- TM1 v9.5.2 (without fixes) as cube server

P.S. Bellow is process affecting update cube operation.
Thanks

Prolog

Code: Select all

CubeClearData('NAROCILA');

#****Begin: Generated Statements***
OldCubeLogChanges = CUBEGETLOGCHANGES('NAROCILA');
CUBESETLOGCHANGES('NAROCILA', 0);
VIEWZEROOUT('NAROCILA','EmptyCube');
#****End: Generated Statements****

Metadata

Code: Select all

#****Begin: Generated Statements***
#****End: Generated Statements****

Data

Code: Select all

#****Begin: Generated Statements***
CellPutN(NAROCENO,'NAROCILA',STATUS_NAROCILA,STATUS_OBDELAVE_NAROCILA,KOMERCIALIST,STANJE_MATERIALA,STANJE_POVRSINE,SISTEM_IMPOL,NACIN_OBDELAVE,IZDELE
K,KOTE,ZATE,ZLITINA,KUPEC,PREJEMNIK,POREKLO,LME_FORMULA,'NAROČENA KOL');
CellPutN(IZDELANO,'NAROCILA',STATUS_NAROCILA,STATUS_OBDELAVE_NAROCILA,KOMERCIALIST,STANJE_MATERIALA,STANJE_POVRSINE,SISTEM_IMPOL,NACIN_OBDELAVE,IZDELE
K,KOTE,ZATE,ZLITINA,KUPEC,PREJEMNIK,POREKLO,LME_FORMULA,'IZDELANA KOL');
CellPutN(NEIZDELANO,'NAROCILA',STATUS_NAROCILA,STATUS_OBDELAVE_NAROCILA,KOMERCIALIST,STANJE_MATERIALA,STANJE_POVRSINE,SISTEM_IMPOL,NACIN_OBDELAVE,IZDE
LEK,KOTE,ZATE,ZLITINA,KUPEC,PREJEMNIK,POREKLO,LME_FORMULA,'NEIZDELANA KOL');
CellPutN(POTREBNA_SUROVINA,'NAROCILA',STATUS_NAROCILA,STATUS_OBDELAVE_NAROCILA,KOMERCIALIST,STANJE_MATERIALA,STANJE_POVRSINE,SISTEM_IMPOL,NACIN_OBDELA
VE,IZDELEK,KOTE,ZATE,ZLITINA,KUPEC,PREJEMNIK,POREKLO,LME_FORMULA,'POTREBNA SUROVINA');
#****End: Generated Statements****

Epilog

Code: Select all

#****Begin: Generated Statements***
CUBESETLOGCHANGES('NAROCILA', OldCubeLogChanges);
#****End: Generated Statements****

The only process that I can imagine that could influence the cube is and run after above processes (saving all the data after night load of data).
Prolog

Code: Select all

SaveDataAll;

#****Begin: Generated Statements***
#****End: Generated Statements****

[Duncan P]

Just a few questions for clarification :-

Looking at which cells are populated by the overnight chore, is it :-
1. exactly the same set of cells with higher numeric values
2. a larger set of cells but with the same values for those cells populated by the manual run
3. a mixture of both

Have you tried putting a TextOutput row in the Data section to log the values received and then compare them with the batch run from SQL?

Have you checked the log for the period covering the load to check what TM1 thinks it is putting in the cells?

What is the SQL? Is it parameterised and does the process have parameters?

Have you checked that the finish time for the load process in the tm1server.log is after the start time for the SaveDataAll process?

As a last resort turn process logging on with the tm1s-log.properties file.

[abcuser]

Looking at which cells are populated by the overnight chore, is it :-
1. exactly the same set of cells with higher numeric values
2. a larger set of cells but with the same values for those cells populated by the manual run
3. a mixture of both

There should be no manual run of process in the morning only the process at the night. Whole cube is emptied and new data fresh data is loaded into cube (full refresh principle).

What is the SQL? Is it parameterised and does the process have parameters?

No, it is just ordinary SQL without any parameters.

Have you checked that the finish time for the load process in the tm1server.log is after the start time for the SaveDataAll process?

Yes, I have checked.

[abcuser]

Hi,
I have looked into what was most recently changed in cube and found out that change was done in the section of clearing out the data.

I used to have:
1. Process of updating cube with new data, on Cube tab there was "Zero Out Portion" checked.
2. "Edit View" button.
3. In "View Extract" window all was set to default (so marking whole cube).

According to IBM support suggestion this is not needed anymore and can be replaced with:
1. Process of updating cube with new data, on Cube tab there was "Zero Out Portion" unchecked.
2. Advanced | Prolog tab and added command: CubeClearData('my_cube_name');

Now I added back "zero out portion" and created view that holds whole cube and problem is solved. Now Prolog looks like:

Code: Select all

CubeClearData('NAROCILA');

#****Begin: Generated Statements***
OldCubeLogChanges = CUBEGETLOGCHANGES('NAROCILA');
CUBESETLOGCHANGES('NAROCILA', 0);
VIEWZEROOUT('NAROCILA','EmptyCube');
#****End: Generated Statements****

This is strange. I thought CubeClearData removes all the data from the cube. According to the documentation:
http://publib.boulder.ibm.com/infocente ... 78211.html

Code: Select all

CubeClearData.
This clears all of the data in a cube.

This function is much faster than doing an operation such as creating a view to cover the entire cube, and then doing a ViewZeroOut() to zero out the entire cube.

Any suggestion what my be wrong? Is this some kind of bug or something?
Regards