Data load not loading couple rows

[kaazimraza]

Hi all -

I have again ran into a weird scenario, as like my previous post. I am loading data into a cube through TI. Its product data having standard sales & revenue stats. The weird behavior is that its leaving out 2/3 rows for some of the products for some months. There's no logic as such within the TI besides mapping target dimensions with data source columns etc. Data source is a CSV ha1 enving around 46000 rows as its summarized data.

Any idea why TI or TM1 engine is doing like this? I know its a very vague question, but I don't have as such details b/c there's no logic whatsoever within the TI. Moreover, had it been loading all stats incorrectly I'd have suspected something going fishy maybe at the data source or on TM end, however, its acting weirdly for about 2% to 5% of the entire data set.

Thanks.

[Alan Kirk]

I have again ran into a weird scenario, as like my previous post. I am loading data into a cube through TI. Its product data having standard sales & revenue stats. The weird behavior is that its leaving out 2/3 rows for some of the products for some months. There's no logic as such within the TI besides mapping target dimensions with data source columns etc. Data source is a CSV ha1 enving around 46000 rows as its summarized data.

Any idea why TI or TM1 engine is doing like this? I know its a very vague question, but I don't have as such details b/c there's no logic whatsoever within the TI. Moreover, had it been loading all stats incorrectly I'd have suspected something going fishy maybe at the data source or on TM end, however, its acting weirdly for about 2% to 5% of the entire data set.

My initial suspicion would fall on the source data, particularly if it's coming from a relational database, more particularly if it includes some kind of descriptive field which might have some kind of non-visible end of file marker (or other control characters which have the same effect) embedded in it.

The approach that I would take would be to use Ascii / Text Outputs to determine the last row loaded. I'd then extract that row and a couple on either side from the data file and put them into something like Notepad ++ which can show you any extraneous characters.

[kaazimraza]

Hi Alan -

Thanks for the reply. I am currently comparing the CSV and the rows that have been loaded into TM1 to identify the missing rows first and then inspect them.

Thanks.

[rmackenzie]

The weird behavior is that its leaving out 2/3 rows for some of the products for some months. There's no logic as such within the TI besides mapping target dimensions with data source columns etc..

Have you checked if you are accumulating to the target cells? This is a setting in the TI 'wizard' that you should see somewhere in the 'Maps' tab.

If you have multiple rows that map to the same cube intersection you may only be seeing the result of the last row. It's a common issue even with 'summarised' data sources. E.g.

Product.....Region.....Month....Sales$
---------------------------------------------
P1...........NSW.......Jan........100
P1...........NSW.......Jan........200
P1...........NSW.......Jan........300
P2...........NSW.......Jan........100
P2...........VIC.........Jan........100

In the example only the 3rd, 4th and 5th rows load if you aren't accumulating into the cells - you'd see $300 for P1/ NSW instead of $600.

[kaazimraza]

Hey thanks for the reply. I agree to you point, but in that case I should have gotten the last row's result, whereas my result set is missing only a percentage worth of data.

My data goes something like this

ProductCode TotalSales TotalLossToCompetition
001-------------------5-------------------1
001-------------------4-------------------0
004-------------------5-------------------0
004-------------------5-------------------1
001-------------------4-------------------2

From above data, i am getting 10 for 004 which is correct, but for Product 001, i am getting 9. so either the 2nd or last row is not being picked up. Interestingly, TotalLossToCompetition accumulates to proper results .

Thanks.

KR.

[AmbPin]

How are you splitting up each line into individual fields?

[kaazimraza]

How are you splitting up each line into individual fields?

By simply defining the maps. So TotalSales from source CSV is mapped to one of the members in measure dimensions, that is it. No calculation what so ever.

[Steve Rowe]

Don't forget that if youa re ignoring any fields in the input you will usually create duplicate rows that means if you are not accumulating your data you will get the incorrect result.

Set your TI up to accumulate the data and see if you still have an issue.
HTH