Hi Vijay,

there has been a lot of things that I have learnt since I wrote that ODS article and last year I wrote a data warehousing book, http://www.amazon.co.uk/dp/1590599314/

My current view is that we don't have to have an ODS to build a data warehouse. So regarding the do and don'ts, the top of the list would probably be "only build the ODS if you need one". The dimensional data warehouse should contain the data at the lowest level/grain. But in practice this is not always the case. 95% of the case it is applicable, but there are certain cases where it's not practical and we had to source operational lower level reporting from ODS.

The second DO is probably the grain, i.e. must be the same as the source business system, i.e. should be the lowest possible level, don't aggregate.

3rd DO: should be in 3rd (or more) normal form. It shouldn't be in 2nd or 1st. And it definitely shouldn't be in dimensional model.

4th DO: have a internal ODS key/ID, rather than relying on the source system key.

5th DO: do have a DQ (Data Quality), i.e. create data firewall between the source system and ODS

DON'T: don't store history, only keep the current version. If we need to do snapshoting and SCD, it is better to do this on the dimensional Data Warehouse.

DON'T: don't copy the structure of the source system as is, but organize into proper 3rd NF.

For incremental load is we can use identity column or timestamp column. I wrote the incremental loading here: http://www.sqlservercentral.com/articles/Design/dataextractionmethodspart1/2356/

Please don't hesitate to come back with any question, I'll be glad to assist.

Regards,

Vincent

PS. You don't have to follow the above do and don't as it is, your situation could be different. So they are just for your consideration. Good luck with your project.