Archives of the TeradataForum

Message Posted: Tue, 02 Mar 2004 @ 20:43:20 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

This is still a work in progress, but I thought I'd supply a quick update of how this process is working today for anyone who's interested.

We have 30 countries (actually a few more now) that send us daily financial files. In addition to the daily financial files, each country can send month-end (restatement of the entire month) files at any time during the first 10 business days of the following month (note that they can send us monthly financial files at nearly any time of day - with record counts ranging from the thousands to the millions and we can receive multiple files from the same country in the same day as well). Yes, it sounds more like an application than a data warehouse - but those are the requirements.

We were not able to find a solution that would allow us to perform simultaneous restatements (deletes/inserts) into the target table (same target for all countries). As expected, we failed miserably in getting locking to function at a low enough level to where we could perform simultaneous deletes on the source table by month and country in order to re-load monthly data. This was regardless of distribution and various index lay-outs it was only processing one at a time.

The only thing that saved us in this case was that the monthly re-statement data in only needed to be integrated with the daily data once per week. We switched the monthly loads to go to a separate table for monthly only (the customer's query the monthly table to determine if they need to re-state the month again rather than the large target table, which is more efficient also) and continued to let the daily loads go into the standard target. Once per week, the monthly target is merged back in with the daily target and cleared so that the cubes get a full image of the month and the monthly table stays as small as possible.

We still run into contention problems from time to time, but not nearly as bad as in the past. The long term plan may have us splitting each country up into its own table(s) and creating views or merge processes to provide "the big picture".

I don't know if this provides any value to anyone other than if you are thinking about some way to get a row-level lock in Teradata to do mass simultaneous transformations on the same table at the same time like we were, but it had been an interesting process to work through to this point.

If we run into anything "earth shattering" on the final implementation, I'll report back with more detail for those who might be interested.

	<Prev	Next>		<<First	<Prev	Next>	Last>>