Any thoughts on my specific requirement?

General

Any thoughts on my specific requirement?

Hi All,

    This is my first post and I am excited !!!. I am new to Teradata tools. I worked almost 8 years in SQL Server and Oracle but not on Teradata(only T-SQL).

I recently moved to a new company and working for a specific business team. IT Department provided us DataLab (TD 14) for us for any adhoc requirements. This department already has access to SQL Server and they generate reports using few denormalized tables from this SQL Server. I am not sure why they never IT to build these tables in DW.

Now, I was asked to move them to Teradata Datalab. Sorry for being detailed.

Here is the requirement:

1) Everyday, we get 5 excel files in specific folder

2) Move these data to 5 Staging Tables

3) Populate the 3 De-normalized tables from these 5 Staging tables using some Stored Procedures which does all the transformations..

4) Delete the data from these 5 Staging Tables

5) Schedule this process daily to be automatic

In SQL Server, we used SSIS ETL tool and also SQL Server Agent Job to do this but how can this be done in Teradata with access to Teradata Load Utilities tools (MLOAD,FastLOAD..)

Any Suggestions to get this approach working automatically daily once?

Thanks,

nath

5 REPLIES
Senior Apprentice

Re: Any thoughts on my specific requirement?

Hi Nath,

what's your SSIS release?

You should be able to modify your existing SSIS workflow to load to TD instead of SS, there's a MS Connector for TD at http://www.microsoft.com/en-us/download/details.aspx?id=29283

Those Stored Procedures are SS SPs? Depending on the complexity you can probably rewrite them as TD SPs or even TD Macros.

Re: Any thoughts on my specific requirement?

Thanks Dieter. They are planning to remove the SQL Server. As our team is maintaining this enviornment without IT team involvement, they are not willing to do the required migration or support for us. They only provided us Teradata Datalab.

So, I cannot use SSIS. By the way, it was on SQL Server 2008R2.

SP's are not complex. I can say simple to medium complex and I can easly rewrite them in TD.

Only concern is:

How to automate

1) the process of importing excel data into TD Staging tables (a dump ) daily

2) Run the SP's to do the calculations,transformations on Staging Tables data and populate them in denormalized tables.

I do not want to do them manually everyday morning to get latest data in to these tables. I started looking through the tools FASTLOAD (for staging dump from excel) and other utilities..

But as I am new to these tools and requirement is not complex, I thought of posting in forum for any better approaches.

Senior Apprentice

Re: Any thoughts on my specific requirement?

I can think of two ways to get Excel data into TD using:

  1. TD's OLE DB Access Module (to extract from Excel via a OLE DB provider) within a FastLoad, this is covered in the Teradata Tools and Utilities Access Module Reference manual
  2. MS's Excel ODBC driver within a TPT Load (I would prefer this because FastLoad is a legacy load tool and you can call the SPs within the TPT script (in TPT14))

For the scheduling part:

There's no built in scheduler in TD because most customers already have an enterprise scheduler like Control-M. You can use whatever you want, e.g. Windows Task Scheduler or the at command.

Re: Any thoughts on my specific requirement?

Thanks Dieter. I just confirmed that they can provide data in CSV file. If I am correct, we can create a single TPT Script which does

1) inmport of data from CSV/excel to Staging tables

2) Call Stored Procedures to do transformations and populate on Target tables

3) Delete the data from Staging Tables

4) Schedule this TPT Script through Windows Task Scheduler to run at specified time

Soory to ask this. But do you have any sample TPT Scripts whcih does something similar like importing data from flat files and calling SP's...Else, I will look thorugh the documentation and sample online examples to understand TPT utility tool

Senior Apprentice

Re: Any thoughts on my specific requirement?

You'll find some examples in the "Teradata Parallel Transporter User Guide" plus matching scripts in the C:\Program Files\Teradata\Client\xx.xx\Teradata Parallel Transporter\sample directory.

Regarding SPs, I never tried those in TPT, but using the DDL operator should work, despite the name it supports any(?) stand alone SQL statement