Steps to be done before ETL part

[sumeshabraham]

Hi All,

I worked on a multple technology project which had 2-3 modules which used DataStage as ETL tool.The project as a whole was not a Data Warehousing project. So I'd no practical exposure to Facts, Dimensions,Schemas(Only theoritical knowledge) ...etc. I was using the ETL tool for meeting the requirements. Recently when I faced an Interview, the interviewer was interested in asking only the DW designing and he never asked anything about the tool and his opinion was that a guy who is very good know-how of the practical things of DW can get on with any ETL. I couldn't clear it eventhough I was very confident in the tool.

I would appreciate if you can give me suggestions on how to overcome this problem, the main areas to focus in the practical side of DW (Before ETL comes into picture)and please tell me briefly the steps in a typical DW project?

Thanks a lot,
Sumesh

 

[johnherman]

Dimensional Modeling, aka star and snowflake schemas for relational databases, are the database designs usually used in DW. Then ETL is used to transform the data and load those dimensional structures.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[Hans63]

Dimensional modelling, as JohnHerman pointed out is one thing which you have to do before ETL.
Another thing is to get a design of all transformations you will need. How do you compose fact A from your available sources, how do you derive the customer name from your sources, when not all sources have the same structure (not every source system will have a separate field for storing initials, not all will separate first and last name)

However: before both of these steps, you need to have some analysis: what are the facts and dimensions you will need to meet your requirements?
And you need some source-system analysis: Is all wanted data available in the source system, and if so: where do you get it?

 

[sumeshabraham]

Hi John & Hans

Thanks for the clarifications.

 

[Bobje]

oracle has some nice docs on this:

http://www.oracle.com/pls/db102/portal.portal_db?selected=6

datawarehousing guide will give you a lot of info.