A day in Life of datawarehousing Engineer Part-1

Datawarehousing consists of three main area :
1. ETL(data migration, data cleansing, data scrubbing, data loading )
2. Datawarehouse design
3. Business Intelligence (BI) Reporting infrastructure.
BI
Read These Two part article for BI
https://sandyclassic.wordpress.com/2014/01/26/a-day-in-life-of-bi-engineer-part-2/
– https://sandyclassic.wordpress.com/2014/01/26/a-day-in-life-of-business-intelligence-engineer/
And Architect
https://sandyclassic.wordpress.com/2014/02/02/a-day-in-life-of-business-intelligence-bi-architect-part-1/
Design : Now Coming to part 2 (is generally work of Datawarehouse architect)
Read Some details More would be covered in future articles
https://sandyclassic.wordpress.com/2013/07/02/data-warehousing-business-intelligence-and-cloud-computing/
Part 1: ETL Engineer:
Most common task of ETL (Extract data- Transform data -Load to target).
Most Common ETL Tool being
Independent Tool: Informatica, IBM data stage, Ab-initio, Terradata ETL utilities,
Tool within ERP: SAP BIW ABAP based transformations , LSMW, peoplesoft EPM (internally uses other tools though).
Tool within Databases: Oracle SQL loader, Teradata ETL utilities,(Tricle pump, multi-load, fast load),
Microsoft BI Stack with SQL server had : SSIS SQL server integration services.
Cloud based Tool: Apache Hadoop Hive datawarehouse ( here requirement is different from un-structured realtime data analysis.
First data modelling have to completed to have level of granularity to represent requirements of business key drivers.
Once datawarehouse Structure in completed to ascertain level of granularity required.
The data loading Cycle Starts With:
Extraction from desperate data sources in Stagging area
On Stagging area data is cleansed.
Then data Transformation are applied for
Example in Informatica and SSIS:
https://sandyclassic.wordpress.com/2014/01/15/eaten-tv-from-partly-eaten-apple-part-2-artificial-intelligence/
Two Sets of documents are There LLD and HLD to look at what needs transformation to be applied.
Like in Informatica Transformation Types are :

Informatica Transformation Types


Look at all transformations available in Informatica version 9
http://www.folkstalk.com/2011/12/transformations-in-informatica-9.html
These can be customized according to logic required.
Next step is Loading to datawarehouse dimension tables  and then to Fact table.
Read: https://sandyclassic.wordpress.com/2014/02/06/coke-vs-pepsi-of-datawarehousing-etl-vs-elt/
And more
https://sandyclassic.wordpress.com/2013/07/02/data-warehousing-business-intelligence-and-cloud-computing/

 

8 Comments

  1. Pingback: A day in Life of datawarehousing Engineer Part-2 | sandyclassic

  2. Pingback: A day in life of datawarehouse Consultant | sandyclassic

  3. Pingback: A day in Life of ETL Consultant | sandyclassic

  4. Pingback: A day in Life of ETL Consultant | Business Intelligence Technology Trends

  5. Pingback: A day in life of datawarehouse Consultant | Business Intelligence Technology Trends

  6. Pingback: A day in Life of ETL Consultant | Datawarehouse View

  7. Pingback: A day in life of datawarehouse Consultant | Datawarehouse View

  8. Pingback: A day in Life of datawarehousing Engineer Part-2 | Datawarehouse View

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s