Archives of the TeradataForum

Message Posted: Thu, 05 Dec 2002 @ 17:03:42 GMT

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

Well, just to give you an idea of what we're doing in our site :

We have a 1.3 TB 4 Nodes system (we'll increase our disk space soon). We also have a separate ETL box (Sun) and we use Genio 4.7 (currently migrating to 5.0.3) for 99% of the ETL work. We use CONTROL-M for scheduling. We use Cognos and many other tools to do queries. We also run the NCR CRM application.

How we solve your type of problems :

1. Load the master and detail table to Teradata and do the proper outer join.

2. We have a multiple set of tables for each incoming file : Temporary, staging, error and holding. Some of them, of course do not apply to your environment. The data moves from one to another table based on business rules (simulating states for the records) It will be extremely difficult to handle this environment without the help of an ETL tool with the Impact Analysis feature. We use the tool to generate HTML files and send them via email to the responsible and business users at the end of the process.

3. Load all the 5-7 transactions and join them together. Again, this is pretty easy with an ETL tool that will allow you develop the transformation process and then generate the perfect SQL code for Teradata.

4. You have some many options here. What we do for some sources is load control files. Then, we use the data loaded to generate new control numbers that we compare against the control file.. We also have error, exception and threshold control in each step,

Now, things that I don't like to do in Teradata :

1. IsNumber(), IsDate(). Teradata doesn't have anything like that and of course, you can create complex expressions or join your table with a "valid domain table". I'm wondering why Teradata doesn't have this simple functions. Anyway, the new version of the ETL tool I'm using now, is capable of read a file, apply more complex Row level transformations and (internally) using inmods, send the data to Teradata using a Fastload script. As you can see, now we can do almost any type of row transformation without problems.

... I've realized that was the only one.

Because we have web users, reading data from our database 24x7, we can not afford the delete+insert strategy to do the updates. We do the updates in place, using only SQL. It's slow, mostly because the secondary indexes in the tables, but it's our best option by now and the users can afford to have an ETL session running an update during 4 hours if that doesn't interfere with the CRM application ,the web users and the regular traffic.

My last project was migrating Retail Analysis system from Oracle to Teradata. The process was as simple as loading some data and generating a star schema. We reduced the time from 3 days to less than 3 hours. However, because we need to make changes in reports and existing processes, we're exporting the "transformed" data back to Oracle by now. That's why I don't believe Oracle can do any transformation better than Teradata (except the IsSomething stuff)

By the way, 3% of the time, we need to code something specific outside the ETL tol and run it as a macro.

Jose Lora

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