How to load data into Audit Cube incrementally

[tawadeamit]

Hi,

Per requirement we need to capture below information so that we can have load history data availabel in TM1 cube.
- Source System Code
- Target TM1 Table Code
- Financial Year Quarter
- Extracted As Of Financial Period
- Load Date Time
- Latest Load Indicator
- Load Type

I have created TM1 cube - "SSC_LOAD_ACTIVITY_LOG" having two dimensions
- INDEX (Having Serial Numers like 1,2,3....100)
- LOAD_ACTIVITY_LOG_DETAIL (having below elements)
- Source System Code
- Target TM1 Table Code
- Financial Year Quarter
- Extracted As Of Financial Period
- Load Date Time
- Load Type

I have created TI and have variables to capure the required information. In this TI am using CellPutN/ CellPutS to load data into cube - "SSC_LOAD_ACTIVITY_LOG"

I am able to transfer data on my first run. but when I re-run it all my records got updated for 1st element in INDEX dimension i.e "1", I desperately need your expert opinion how to load data incrementally so that each time I run my TI it will laod to next element in INDEX dimension so that we can have record for dataload updates.

Thanks in advance!!

[declanr]

It would help if you posted your code but something along the lines of the below should work:

Code: Select all

iCount = 1;
sRecord = NumberToString ( iCount );
sSourceSystem = CellGetS ( sAuditCube, sRecord, 'Source System Code' );
While ( sSourceSystem @<> '' );
        iCount = iCount + 1;
        sRecord = NumberToString ( iCount );
        sSourceSystem = CellGetS ( sAuditCube, sRecord, 'Source System Code' );
End;

Then just load to the sRecord you have left at the end as that is the first empty record.

Or you could have created your record dimension as being empty to start with and just add an extra element each time you run the process (via a dimsix + 1) then load to that element.

[tawadeamit]

It would help if you posted your code but something along the lines of the below should work:

Code: Select all

iCount = 1;
sRecord = NumberToString ( iCount );
sSourceSystem = CellGetS ( sAuditCube, sRecord, 'Source System Code' );
While ( sSourceSystem @<> '' );
        iCount = iCount + 1;
        sRecord = NumberToString ( iCount );
        sSourceSystem = CellGetS ( sAuditCube, sRecord, 'Source System Code' );
End;

Then just load to the sRecord you have left at the end as that is the first empty record.

Or you could have created your record dimension as being empty to start with and just add an extra element each time you run the process (via a dimsix + 1) then load to that element.

Thanks for quick response!!

Please refer belwo code that I am using currently ..

INDEX_FLAG=CellGetN('SSC_LOAD_ACTIVITY_LOG', NumberToString(SSC_INDEX),'INDEX_FLAG');
INDEX_FLAG=CellGetN('SSC_LOAD_ACTIVITY_LOG', NumberToString(SSC_INDEX),'SSC_INDEX');

IF(SSC_INDEX =INDEX_FLAG );
#IF(SSC_INDEX=1);

CellPutS(vSOURCE_SYSTEM_CODE, 'SSC_LOAD_ACTIVITY_LOG', NumberToString(SSC_INDEX),'Source System Code');
CellPutS(vTARGET_TM1_TABLE_CODE, 'SSC_LOAD_ACTIVITY_LOG', NumberToString(SSC_INDEX),'Target TM1 Table Code');
CellPutS(vFINANCIAL_YEAR_QUARTER, 'SSC_LOAD_ACTIVITY_LOG', NumberToString(SSC_INDEX),'Financial Year Quarter');
CellPutS(vEXTRACTED_AS_OF_FINANCIAL_PERIOD, 'SSC_LOAD_ACTIVITY_LOG', NumberToString(SSC_INDEX),'Extracted As Of Financial Period');
CellPutS(vLOAD_DATE_TIME, 'SSC_LOAD_ACTIVITY_LOG', NumberToString(SSC_INDEX),'Load Date Time');
CellPutS(vLATEST_LOAD_INDICATOR, 'SSC_LOAD_ACTIVITY_LOG', NumberToString(SSC_INDEX),'Latest Load Indicator');
CellPutS(vLOAD_TYPE, 'SSC_LOAD_ACTIVITY_LOG', NumberToString(SSC_INDEX),'Load Type');
INDEX_FLAG= INDEX_FLAG+1;
CellPutN(INDEX_FLAG, 'SSC_LOAD_ACTIVITY_LOG', NumberToString(SSC_INDEX),'INDEX_FLAG');

SSC_INDEX=SSC_INDEX+1;
CellPutN(SSC_INDEX, 'SSC_LOAD_ACTIVITY_LOG', NumberToString(SSC_INDEX),'SSC_INDEX');

ENDif

Kindly help me understand where I need to incorporate code given by you.

[Wim Gielis]

Using Declan's code:

Code: Select all

iCount = 1;
sRecord = NumberToString ( iCount );
sSourceSystem = CellGetS ( sAuditCube, sRecord, 'Source System Code' );
While ( sSourceSystem @<> '' );
        iCount = iCount + 1;
        sRecord = NumberToString ( iCount );
        sSourceSystem = CellGetS ( sAuditCube, sRecord, 'Source System Code' );
End;

CellPutS(vSOURCE_SYSTEM_CODE, 'SSC_LOAD_ACTIVITY_LOG', sRecord, 'Source System Code');
# extend here for other measures

By the way, what is this supposed to be?

Code: Select all

INDEX_FLAG=CellGetN('SSC_LOAD_ACTIVITY_LOG', NumberToString(SSC_INDEX),'INDEX_FLAG');
INDEX_FLAG=CellGetN('SSC_LOAD_ACTIVITY_LOG', NumberToString(SSC_INDEX),'SSC_INDEX');

IF(SSC_INDEX =INDEX_FLAG );

You fill a variable: good. Then you fill that variable again, and thus overwrite its contents. Why? You need 2 different variables for that.

[tawadeamit]

Using Declan's code:

Code: Select all

iCount = 1;
sRecord = NumberToString ( iCount );
sSourceSystem = CellGetS ( sAuditCube, sRecord, 'Source System Code' );
While ( sSourceSystem @<> '' );
        iCount = iCount + 1;
        sRecord = NumberToString ( iCount );
        sSourceSystem = CellGetS ( sAuditCube, sRecord, 'Source System Code' );
End;

CellPutS(vSOURCE_SYSTEM_CODE, 'SSC_LOAD_ACTIVITY_LOG', sRecord, 'Source System Code');
# extend here for other measures

By the way, what is this supposed to be?

Code: Select all

INDEX_FLAG=CellGetN('SSC_LOAD_ACTIVITY_LOG', NumberToString(SSC_INDEX),'INDEX_FLAG');
INDEX_FLAG=CellGetN('SSC_LOAD_ACTIVITY_LOG', NumberToString(SSC_INDEX),'SSC_INDEX');

IF(SSC_INDEX =INDEX_FLAG );

You fill a variable: good. Then you fill that variable again, and thus overwrite its contents. Why? You need 2 different variables for that.

Thanks a Lot. It works like a magic. . You are absolutely right. I have removed additional code.

I need one more help. When I load "Latest Load Indicator" I need to set flag as a "Y" and for all previous reocrds it should become "N" so that looking at Audit log cube user can pick Row having "Latest Load Indicator" as "Y" is the latest record.

[tawadeamit]

It would help if you posted your code but something along the lines of the below should work:

Code: Select all

iCount = 1;
sRecord = NumberToString ( iCount );
sSourceSystem = CellGetS ( sAuditCube, sRecord, 'Source System Code' );
While ( sSourceSystem @<> '' );
        iCount = iCount + 1;
        sRecord = NumberToString ( iCount );
        sSourceSystem = CellGetS ( sAuditCube, sRecord, 'Source System Code' );
End;

Then just load to the sRecord you have left at the end as that is the first empty record.

Or you could have created your record dimension as being empty to start with and just add an extra element each time you run the process (via a dimsix + 1) then load to that element.

Thanks a Lot. It works like a magic.