How to load source data into a cube with multiline dimension

[Katheleen]

Hi All,

I am a newbie to TM1. I need to load data from source file to a cube with line numbers. Appreciate any suggestions.

Below is a simplified cube.
dim1= 'Country'
dim2 = 'Product'
dim3 = 'Lines' ( elements are from 1, 2....100)
dim4 = 'Measure' (elements are 'Opportunity Name', 'Revenue Amount')

One product has multiple opportunities. Opportunity Name can not be used as dimension as each country uses their own opportunity names and changes frequently. Each data load should wipe out the previous loaded data. Source data format is as below. There is no line number in the source data file.
NZ Product001 Opp-AYR 100000
NZ Product001 Opp-BND 190000
NZ Product001 Opp-ZHL 500000

The data should be loaded starting from line 1 for each country and product.
line 1 ....Opp-AYR 100000
line 2 ....Opp-BND 190000
line 3 ....Opp-ZHL 500000

I used below code and only line 1 data and 2 data went in correctly. line 3 to 100 got copied from line 2. I believe that is due to looping through the lines till 100.
index = 1;
rowCnt = DIMSIZ('Lines');
WHILE ( rowCnt<index);
CellputS(OpportunityName, tCube, Country, Product, DIMNM('Lines',index), 'Opportunity Name');
CellputN(Amount, tCube, Country, Product, DIMNM('Lines',index), 'Revenue Amount');
index = index +1;
END;

Thanks in advance,
Katheleen

[garry cook]

index = 1;
rowCnt = DIMSIZ('Lines');
WHILE ( rowCnt<index);

is the wrong way round on the WHILE statement - rowCnt = 100 (from your example) so 100 is never lower than 1.

[JDLove]

Hi Katheleen

As mentioned your While statement is the wrong way around, also I would suggest you zero out the target cube view in the TI first.

[Katheleen]

Hi JDLove and Garry,

Sorry for taking long to reply to you guys due to unfortunate accident. Thanks for pointing out the mistake. I have been doing some test and managed to load data into the cube by the multiline dimension elements. However, when my country is a dimension the data in first country loads correctly in sequence but another country data starting from the next line where the first country ends. Below is my code.

I tried using if condition to filter source country @= country in dimension but no data being written to the cube. Appreciate if you can advise me how to restart the line counter or start from the next available line for each country.

dim1=country
dim2= lines
dim3= measure ( product, opportunity, value)
source data
NZ Product001 Opp-AYR 100000
NZ Product001 Opp-BND 190000
AU Product001 Opp-ANT 800000
AU Product001 Opp-ZHA 300000

Loaded Data in 'NZ'
1....Product001 Opp-AYR 10000
2....Product001 Opp-BND 190000

loaded Data in 'AU'
3....Product001 Opp-ANT 800000
4....Product001 Opp-ZHA 300000

Note: vLine =0; is in Prolog to move the lines for each record. Otherwise data is only written to line 1
IF ((PRODUCT @<>'') );
compare ='a';
WHILE ( compare@<>'');
vLine = vLine+1;
IF ( CellGetS(sCube, Country,NumbertoString(vLine), 'Product')@='');
CellPutS(PRODUCT, Country,NumbertoString(vLine), 'Product');
CellPutS(OPPORTUNITY, Country,NumbertoString(vLine), 'Opportunity');
CellPutN(Value ,Country, NumbertoString(vLine), 'Value');
ENDIF;
compare ='';
END;
ELSE;
ProcessQuit;
ENDIF;

[declanr]

Hi JDLove and Garry,

Sorry for taking long to reply to you guys due to unfortunate accident. Thanks for pointing out the mistake. I have been doing some test and managed to load data into the cube by the multiline dimension elements. However, when my country is a dimension the data in first country loads correctly in sequence but another country data starting from the next line where the first country ends. Below is my code.

I tried using if condition to filter source country @= country in dimension but no data being written to the cube. Appreciate if you can advise me how to restart the line counter or start from the next available line for each country.

dim1=country
dim2= lines
dim3= measure ( product, opportunity, value)
source data
NZ Product001 Opp-AYR 100000
NZ Product001 Opp-BND 190000
AU Product001 Opp-ANT 800000
AU Product001 Opp-ZHA 300000

Loaded Data in 'NZ'
1....Product001 Opp-AYR 10000
2....Product001 Opp-BND 190000

loaded Data in 'AU'
3....Product001 Opp-ANT 800000
4....Product001 Opp-ZHA 300000

Note: vLine =0; is in Prolog to move the lines for each record. Otherwise data is only written to line 1
IF ((PRODUCT @<>'') );
compare ='a';
WHILE ( compare@<>'');
vLine = vLine+1;
IF ( CellGetS(sCube, Country,NumbertoString(vLine), 'Product')@='');
CellPutS(PRODUCT, Country,NumbertoString(vLine), 'Product');
CellPutS(OPPORTUNITY, Country,NumbertoString(vLine), 'Opportunity');
CellPutN(Value ,Country, NumbertoString(vLine), 'Value');
ENDIF;
compare ='';
END;
ELSE;
ProcessQuit;
ENDIF;

Not sure if I completely followed that but I interpreted your issue as being around the fact that the "line" isn't reset to 1 when you encounter a new country.

If so you could simply add in:

IF ( CellGetN ( Cube, Country, NumberToString (vLine), 'Value' ) = 0 );
vLine = 1;
Else;
vLine = vLine + 1;
EndIf;


You get the concept, so depending where you put that you may actually want to swap the CellGetN with a CellGetS on 'Product' or you may place it elsewhere in the code and actually set vLine to stay as what it already is etc.

[Katheleen]

Hi Declanr,

Thanks for your advice. It is working correctly.