Consultant Analyses the business deeper to come up with star-schema design and further ETL load design,
Working as datawarehouse consultant most important task is to fix granularity of fact across dimensions to be analysed in FACT-DIMENSION Star schema design.
Granularity depends on business requirement and key drivers for business to be analysed for having its impact on Topline and Bottomline of Company. For Clinical Research key driver is No. of patient Enrolled, For banking key driver is cost of adding new customer,
Now patient is analyzed across geography dimension, against time dimension. But at what level of Granularity.
(#no of patient, day) OR
(#no of patient, year) OR
(#no of patient, hour)
This depends on business need and level of criticality to time. For Stock trading Every second is crucial but not for clinical trails but if trial involve enrollment of large public it may required a drill down to per day figure in BI reports hence provisions must be there in star schema.
Besides this The Other task per day can be taken based on stage of project
https://sandyclassic.wordpress.com/2014/02/19/a-day-in-life-of-datawarehouse-architect-part-1/
For datawarehouse Engineer involved with task the day look like
https://sandyclassic.wordpress.com/2014/02/19/a-day-in-life-of-datawarehousing-engineer/
For Unstructured data analysis you can look at
https://sandyclassic.wordpress.com/2011/10/19/hadoop-its-relation-to-new-architecture-enterprise-datawarehouse/
Then data Transformation are applied for
Example in Informatica and SSIS:
Two Sets of documents are There LLD and HLD to look at what needs transformation to be applied.
Like in Informatica Transformation Types are :
http://www.techtiks.com/informatica/beginners-guide/transformations/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
Sandyclassic 