SAS EG as an ETL tool

[deleted_user]

Hi,
I was wondering if anyone has used SAS Enterprise Guide as an ETL(Extract Transform and Load) tool in order to build a data warehouse and what his/her experience with that was.

Thank you

[Doc_Duke]

EGuide is nice for organizing the pieces and doing the diagnostic reports, but I find that I still end up writing a good bit of code for the transform part.

[Shane_Gibson]

We use a combination of EG and DI Studio.

We use EG as our exploration tool as it is just faster to use for this.

But we use DI for the building of the ETL code, its better at doing repeatable ETL tasks, and for creating ETL flows (with LSF), promotion etc.

DI 4.2 has also come a long way to providing some of the flexibility that EG offers as well.

Of course DI does cost more.....

[Patrick]

Hi

ETL for a data warehouse? If this warehouse has more than a few tables then it's clearly DI Studio.

That's the specialised SAS tool to build and maintain ETL jobs.

I agree 150% with Shane's statements - especially when it comes to DIS 4.2.

Purchasing DIS might cause some initial cost both in license fees and training costs. But once developers are up to speed this investment is paid back quite fast in shorter and more stable development cycles and in easier maintainable jobs.

The current project I'm working in includes a big ETL implementation with DIS 4.2. All developers are on senior level but I'm convinced we wouldn't have stood a chance to meet the deadlines using EG instead of DIS for our ETL's.

HTH
Patrick

[Bennya]

I think the answer needs to be in perspective. It really depends on what kind of "data warehouse" we are talking about.

For an enterprise level, true data warehouse, there's no question that a proper ETL is a must.

For a small "data warehouse" with 1 or 2 star schema and 1-2 fact tables with a few dimensions, where no table is bigger than a few thousand records, it's not even a "data warehouse" per se. On top of that, if we only have about a few hundred records a day to deal with, I would think implementing a full-blown ETL tool is an major overkill.

SAS EG should be more than sufficient - maybe it's already an overkill because for that simple "data warehouse", one can just write a few SQL codes to achieve all that "ETL". I would think the volume of the data coupled with the complexity of the data and the transformation should be the deciding factors.