Hi All,

Currently we are implementing an ETL Process via DTS. There is some
confusion regarding Staging Area.

As far as my understanding is concerned, i feel that the Staging Area
(SA) is the area where where i _EXPLICITLY_ store data from my legacy
system, the reason to use a SA can or can not be any of the following :

1) Handle the Updates to Datawarehouse
2) Increase the efficiecny of the process
3) [ANY REASON]


I just woundering that if any of you can help me out in clearing my
uderstanding about the staging area. If there are any Online Articles
or BOOKS, then please do refer it to.

Thanks,
Mansoor

Mansoor,

A Staging Area could be a Server or a Database based on the Context.

You can restore a copy of the OLTP database on the Staging Server(can also
be the server
on which the Reporting database runs). In another case, we used to BCP out
the required OLTP tables
from the OLTP_DBName DB and BCP in the same tables on a OLTP_DBNameStaging
DB and
then run the load script to build the DataWarehouse/Reporting DB.

Gopi

"Muhammad Mansoor" <mmansoor (AT) gmail (DOT) com> wrote

What about the practices that are usually done for ETL Process. ??
Gopinath R wrote:

What about the practices that are usually done for ETL Process. ??
Gopinath R wrote:

There are many takes on this and I suggest a decent ETL book would be a good
purchase to find out more.

Perhaps the simplest is where you really do ELT.

The staging table mirrors the structure of the source, even if that is RMDBs
or file or other based. Load data straight into staging table. This is teh E
and L. You then perform the T part and transform data into your data store
and/or warehouse structures.


--
Darren Green
http://www.sqldts.com

"Muhammad Mansoor" <mmansoor (AT) gmail (DOT) com> wrote

I would agree with Darren. ELT is commonly mistaken or ELT. I am guilty of
it myself especially with things like Text files.
The staging area for me is the intermediate staging of the Source System
data. I only do this if the repective SQL laguage is not powerful enough
to handle doing the right selection and transoforming itself in mid flight.

Take text files for instance !



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

Hmm...

The steps that i had understood are as :

1) Extract from Source System.
2) Load in to Intermediate Area (Staging Area) [Explicitly store in
staging area]
3) Transform and the Load in to Target Warehouse.

Correct ???

Mansoor,

The process for ETL depends. You might not even need a ETL tool based on
your requirement.

In our case we did not need one as the OLTP databases and DSS databases were
SQLServer databases.

In one case we used T-SQL batch file and the other case we used Stored
Procedures to transform and load the
data (Facts and Dimensions ?). I believe ETL tool is required only when the
data sources are on different
platforms (one in SQLServer and the other in Oracle).

Even if the databases are on remote servers one could use Linked Server
(instead of ETL tool) to connect
to the remote server.

Thanks,
Gopi



"Muhammad Mansoor" <mmansoor (AT) gmail (DOT) com> wrote

Thanks, i got the answer