Archives of the TeradataForum

Message Posted: Mon, 13 Oct 2003 @ 12:04:50 GMT

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

Hi All,

I'm fairly new with Teradata and we are attempting to execute a comparative evaluation by replicating an existing Oracle Data Mart. To make a fair comparison, we intend to replicate the system exactly as it is for now, from feed file load to transformation and so on. Besides most production reports are already pointed to the existing table structures.

The current Oracle system is like this. I have 45 flat files that load directly into 45 tables which serve as my ODS. This is done using a Pro-C program. Using another Pro-C program, the data from the ODS is consolidated into 1 fact table and 11 dimension tables which is the target data mart (DM). Yes, the existing Oracle system is in start schema.

The real issue is not in replicating the load. To do the initial population, we simply dumped the existing tables (ODS and DM) to flat files and used FastLoad to transfer them to Teradata. The incremental loads from the feed files to the ODS is also not an issue as we used MultiLoad for these. The challenge that I have is in transferring the data from the Teradata ODS to the Teradata DM because my dimension tables are slowly changing over time (e.g. customer type description) and I need to keep the historical changes. This was achieved in Oracle through a Pro-C program that retrieves the data from the ODS and them populates the fact table one by one. Before each insert into the fact table I check the equivalent dimension rows against the dimension tables. If the row does NOT exist, I generate a new dimension key which is a numeric surrogate key that is basically a sequence number. I insert the dimension row with the new dimension key, I add the new dimension key also to the fact table row and then do an insert. If the row in the dimension table does exist, I check if all the fields are the same. If there is at least one field whose value has changed, I expire the current row in the dimension table by updating the end_date field of that row with sysdate. I then generate a new dimension key (the surrogate key) and then do the insert as before.

This was relatively easy to do in Pro-C. My question is, what facility to I use in Teradata? Better yet, is there a special technique in Teradata for handling something like this? I was thinking of creating a dummy table first with all the facts and dimensions then using FastExport to dump it to file. From there I was thinking of using MultiLoad to populate the DM tables. Problem is, Multiload does not seem to support mathematical functions. How do I increment the dimension key then?

Anyhow, any help would be very much appreciated. Thanks.

Cheers,

Ricky

Ricky Ramos

Remax International Inc.

Web: www.remax.com.ph

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