Summation of Data while executing TI Process

[hsulakhe]

Hi,

I have to upload a set of data through TI using the CSV upload route, in the data the chances of rows having the same dimension element is high but the data points would be different.

Normally in a TI upload, the process overwrites the similar data and the last data point overwritten is displayed in the cube as the process logic is row by row upload.

Is their any way i can sum the similar data points while i am uploading the file in TI.

E.g is Upload of stocks data for a month where the date is not an dimension to create uniqueness for data upload.

Appreciate your insight on this.

Regards

Harshal

[Alan Kirk]

I have to upload a set of data through TI using the CSV upload route, in the data the chances of rows having the same dimension element is high but the data points would be different.

Normally in a TI upload, the process overwrites the similar data and the last data point overwritten is displayed in the cube as the process logic is row by row upload.

Is their any way i can sum the similar data points while i am uploading the file in TI.

E.g is Upload of stocks data for a month where the date is not an dimension to create uniqueness for data upload.

I understand what you're getting at, but it IS important to understand that a data point is a data point and, by definition, unique. In the example that you gave at the end of your post, I imagine that you have a "Months" dimension in the cube, but not a "Dates" dimension. If you had one row which represented a value on 15 June, and one which represented a value on 16 June (with all other element selections being the same), these would not be distinct data points for the purposes of the cube. Both would be for the "June" element's data point.

(I'm not sure that "stocks" is a good example of this; I'm not sure whether you mean inventory or shares, but in either case those would have distinct values for each day; they wouldn't be added together. (Unless of course you're talking about share volumes rather than prices, since those do aggregate.))

In any case, aggregating is a really easy process. In the Data tab, you simply write a CellGetN function to get the existing value from the cube, add it to the value from your .csv data source, then write the total of the two back to the cube using a CellPutN function.

[hsulakhe]

Hi Alan,

Thanks for the post. Just to clarify when i say Stocks it means Inventories and even if i specify dates it would not satsfy my purpose as for a given date also i have multiple entires against the element of Inventory code.

Regards

Harshal

[Alan Kirk]

Hi Alan,

Thanks for the post. Just to clarify when i say Stocks it means Inventories and even if i specify dates it would not satsfy my purpose as for a given date also i have multiple entires against the element of Inventory code.

OK; well, as long as you aren't double counting, the CellGetN -> add together -> CellPutN method will still work for all rows which relate to the same combination of elements in your cube.

[hsulakhe]

Hi Alan,

Thanks for the suggestions. It worked. Appreciate your help.

Regards

Harshal

[Martin Ryan]

Apologies if I'm stating the obvious, but if you run the process again, make sure you run a ViewZeroOut in the Prolog first, so that you don't end up double (or triple, or quadruple, or...) counting your data.

Martin

[Alan Kirk]

Apologies if I'm stating the obvious, but if you run the process again, make sure you run a ViewZeroOut in the Prolog first, so that you don't end up double (or triple, or quadruple, or...) counting your data.

Ah yes, I should have mentioned that.

Of course that's an issue regardless of whether the data is being aggregated, since you can't be certain that the second (third, etc) data file will set any data points that had a value in the first load back to zero. (In fact, they usually won't since data sources tend to be "zero suppressed".)

[hsulakhe]

Hi Martin & Alan,

I realised it the hard way that the data was double counting but we also do not want to Zero Out hence we are going to restrict the number of uploads to say once a month and rest manipulations would be manual by users. Trying to use this workaround outside the system .

Again appreciate your posts on the same.

Regards

Harshal