Only last record in data source loaded via TI process

[dfrench77]

My issue is that only the last row of the selected data range is loaded in the TM1 cube. For example if the source SQL table has 10 rows of data, only the 10th row is loaded.

Below is an example, SQL where the date range is 1/1/11 thru 7/31/11. The last data value is recorded on 7/26/11. All records prior to 7/26/11 are skipped and not loaded.

I have checked the TI settings several times. I believe the issue is with either the SQL Server table or the SQL Script that I am using. Any suggestions or advice that can be provided will be helpful.

I have written a simple SQL script to load data via TI using an ODBC SQL Server data source. Below is the SQL script:

SELECT UnitCd, CurDT,
sum (LaborCostChng) Labor,
sum (MaterialCostChng) Material,
sum (SubcontCostChng) Subcontract,
Sum (POCommitChng) POCommitChng
FROM dbo.MaintWoData
where CurDT >= ‘2011-01-01’
and CurDT <= ‘2011-07-31’
and PlantCD in ('295', '296')
and UnitCd = ‘04’
and WoClass <> '3'
group by UnitCd, CurDT
order by UnitCd, CurDT

The result of the query in the TI preview window is below:
UnitCD CurDT Labor Material Subcontract POCommitChng
04 2011-01-01 00:00.0 -778867.47 -57770.62 -26760.47 0
04 2011-01-02 00:00.0 0 0 0 0
04 2011-01-03 00:00.0 0 0 0 0
04 2011-01-04 00:00.0 20149.98 10591.26 667.5 45126.18
04 2011-01-05 00:00.0 32664.28 42626.26 36998.67 8331.89
04 2011-01-06 00:00.0 30621.05 44221.77 2800.35 26199.54
04 2011-01-07 00:00.0 0 5019.17 19631.9 -15568.88
04 2011-01-08 00:00.0 0 0 0 0
04 2011-01-09 00:00.0 0 0 0 0
04 2011-01-10 00:00.0 54987.33 48258.76 2212.69 15122.71

The variables settings are below:
UnitCd String 00 Element
CurDT String 2011-01-01 00:00:00.000 Other
Labor Numeric -487069.570000 Data
Material 0.000000 Data
Subcontract Numeric 0.000000 Data
POCommitChng Numeric 0.000000 Data
vDate String 01-01 Element Formula
vVersion String Actual Element Formula
vYear String 2011 Element Formula

Dimension maps
UnitCd 00 Units As Is String By Input
vDate 01-01 Days As Is String By Input
vVersion Actual Versions As Is String By Input
vYear 2011 Years As Is Numeric By Input
(Data Variables) Daily Cost Accounts As Is By Input

Data Maps
Labor Labor Numeric -487069.570000
Material Material Numeric 0.000000
Subcontract Sub-contract Numeric 0.000000
POCommitChng Committed Numeric 0.000000

Advanced data tab:
#****Begin: Generated Statements***
CellPutN(Labor,'Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Labor');
CellPutN(Material,'Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Material');
CellPutN(Subcontract,'Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Sub-contract');
CellPutN(POCommitChng,'Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Committed');
#****End: Generated Statements****

[jim wood]

Change your process to accumulate rather than store.

[jim wood]

Just to clarify. You've ignored the only thing (the date) in the query which makes each line unique. It's loading different values to the same variables. Instead of setting the process to accumulate why don't you just remove date from the query?

To be clear this isn't really a TM1 issue.

[dfrench77]

Change your process to accumulate rather than store.

Jim,
I did change the data action from store values to accumulate values and the TI process loaded an accumalated value for the last record value.

Just to clarify. You've ignored the only thing (the date) in the query which makes each line unique. It's loading different values to the same variables. Instead of setting the process to accumulate why don't you just remove date from the query?

To be clear this isn't really a TM1 issue.

Jim,
Currently I am mapping the date to the days variable. However, I did try removing the date range from the where clause in the query and I received the same results.

I agree that this does not appear to be a TM1 TI issue but I thought someone on the forum may have experience with a similar issue.

Thanks for your comments and suggestions.

Desi

[Alan Kirk]

Change your process to accumulate rather than store.

Jim,
I did change the data action from store values to accumulate values and the TI process loaded an accumalated value for the last record value.

I'm not sure what you mean by "an accumalated value for the last record value"; can you clarify that?

Just to clarify. You've ignored the only thing (the date) in the query which makes each line unique. It's loading different values to the same variables. Instead of setting the process to accumulate why don't you just remove date from the query?

To be clear this isn't really a TM1 issue.

Jim,
Currently I am mapping the date to the days variable. However, I did try removing the date range from the where clause in the query and I received the same results.

I agree that this does not appear to be a TM1 TI issue but I thought someone on the forum may have experience with a similar issue.

Possibly no-one has picked up on it before - for I go to great lengths to conceal it - but I for one regard the Maps tab and the generated code as being something which should be embraced with as much enthusiasm as someone who has both the plague and leprosy. It's inflexible, it's fiddly and pedantic in its settings, it borders on useless, and rather than assisting it frequently stands between you and getting the job done.

I think, however, that you should probably post the current code in the data tab so that we can see what code the pillock of a thing has come up with this time, just in case it's something... "unorthodox".

In the meantime I would also suggest that you dip your toes into doing some real coding. In the Data tab, below the generated code, use either the AsciiOutput or TextOutput function (you can find details on both of those in the Reference Guide under "TM1 Turbointegrator Functions") to write the data that is being processed out to a text file, just to ensure that you're getting from your SQL query the values that you expect to get from it. Bear in mind that the data preview grid really isn't all that good for doing that, and it can be misleading; you need to see the data that's actually coming through each row.

[jim wood]

Alan,

The data tab generated code is in the original post. I couldn't see anything wrong with it. It looks like a data problem to me but there we we go. I might not have looked at the thing in enough detail....

Jim.

[jim wood]

I've just noticed that you have formulas for the dates. Could you please post the formulas for these? If they are not on the data tab (when you create the formula you can specify metadata, data or both) it might explain why you are having a problem.

Just to be sure go through each tab and post all the code generated on each,

Jim.

[dfrench77]

I'm not sure what you mean by "an accumalated value for the last record value"; can you clarify that?

Alan,
Here is what I mean regarding accumaulated value for the last record, for example the following query result:

UnitCD CurDT Labor Material Subcontract POCommitChng
04 2011-01-01 00:00:00 -17337.4 -823.96 -11552.77 0
05 2011-01-01 00:00:00 -470918.7 -56695.42 -15207.7 0
06 2011-01-01 00:00:00 -243032.69 0 0 0
07 2011-01-01 00:00:00 -32016.68 -251.24 0 0
08 2011-01-01 00:00:00 -15562 0 0 0
09 2011-01-01 00:00:00 0 0 0 0
10 2011-01-02 00:00:00 0 0 0 0
SUM TOTAL -778867.47 -57,770.62 -26,760.47 0

TI loads the following values in the cube when:
Days Labor Material Subcontract Committed
01-01 $0.00 $0.00 $0.00 $0.00
01-02 ($778,867.47) ($57,770.62) ($26,760.47) $0.00

I've just noticed that you have formulas for the dates. Could you please post the formulas for these? If they are not on the data tab (when you create the formula you can specify metadata, data or both) it might explain why you are having a problem.

Just to be sure go through each tab and post all the code generated on each,

Jim.

Jim,
Here is the formula for vDate is vDate = Subst(CurDT,6,5);

Prolog Code:
#****Begin: Generated Statements***
OldCubeLogChanges = CUBEGETLOGCHANGES('Summary Data_v3');
CUBESETLOGCHANGES('Summary Data_v3', 0);
#****End: Generated Statements****

Metadata Code:
#****Begin: Generated Statements***
vDate=subst(CurDT,6,5);
vVersion='Actual';
vYear=Subst(CurDT,1,4);
#****End: Generated Statements****

Data Code:
#****Begin: Generated Statements***
CellPutN(CellGetN('Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Labor')+Labor,'Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Labor');
CellPutN(CellGetN('Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Material')+Material,'Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Material');
CellPutN(CellGetN('Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Sub-contract')+Subcontract,'Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Sub-contrac
t');
CellPutN(CellGetN('Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Committed')+POCommitChng,'Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Committed');
#****End: Generated Statements****

Epilog Code:
#****Begin: Generated Statements***
CUBESETLOGCHANGES('Summary Data_v3', OldCubeLogChanges);
#****End: Generated Statements****

In the meantime I would also suggest that you dip your toes into doing some real coding. In the Data tab, below the generated code, use either the AsciiOutput or TextOutput function (you can find details on both of those in the Reference Guide under "TM1 Turbointegrator Functions") to write the data that is being processed out to a text file, just to ensure that you're getting from your SQL query the values that you expect to get from it. Bear in mind that the data preview grid really isn't all that good for doing that, and it can be misleading; you need to see the data that's actually coming through each row.

I take this suggestion of creating and output of the data being processed and will update the post with results.

Another interesting factor about the issue is, if I output the query results from SQL Server to a text csv file and use the csv output as the source for TI I get the same results. It is really strange.

[lotsaram]

Metadata Code:
#****Begin: Generated Statements***
vDate=subst(CurDT,6,5);
vVersion='Actual';
vYear=Subst(CurDT,1,4);
#****End: Generated Statements****

Data Code:
#****Begin: Generated Statements***
CellPutN(CellGetN('Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Labor')+Labor,'Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Labor');
CellPutN(CellGetN('Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Material')+Material,'Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Material');
CellPutN(CellGetN('Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Sub-contract')+Subcontract,'Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Sub-contrac
t');
CellPutN(CellGetN('Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Committed')+POCommitChng,'Summary Data_v3',vVersion,vYear,UnitCd,vDate,'Committed');
#****End: Generated Statements****

Seems pretty obvious what the problem is. Had you posted the full code from BOTH the Metadata and Data tabs before then you would have gotten the answer from others sooner I'm sure.

You are using the variables vDate, and vYear in your CellPutN functions on the Data tab however what you are not doing is to update these variables on the Data tab. As these variables are only being updated on the Metadata tab the value they have is the value they got from the LAST RECORD processed on the Metadata tab.

Solution
On the "variables" tab select data tab for where you need these variables. As you aren't doing any metadata updates you shouldn't have any code on the Metadata tab at all.

Better solution
Copy the code between the generated statements start/finish from the Metadata AND Data tabs and paste BOTH below the generated statements finish on the Data Tab. then go back to the "variables" tab and delete the formula based variables and set the "type" of all variables from the data source to "other". This will help you dip your toe into real coding as Alan put it.

[dfrench77]

Lotsaram,

Thanks for shinning the light on the obivious. I was way to deep in the weeds here. Thanks alot to all. I have started to dip my toes in the TI advances coding.

[jim wood]

I did put that in my last post. I know a lot of people don't like the generated code on this forum but I've found when doing some of the more easier tasks it makes more sense to use it, especially when you are handing it over to customer to administer going forward. You just need to know what things you should avoid when setting them up.