Re: Normalization ?

Naran,

I think you are on the right track with your Database A and Database B.

The process that brings Database B up to date will have to be carefully written, so as to take into account any update issues that normalization would have rendered moot.

In addition, you will want to carefully consider whether any natural keys you use in the OLTP system should be replaced by surrogate keys in the Reporting system. Also, there are cases where and update in the OLTP system will cause an insert in the Reporting system.

Example: a price change. The old row is kept in the price dimension, because any facts that relate to the old price need to be kept in context. But new facts that relate to the new price need to be kept in a different context. So both rows are needed. But all this is presented in more detail, and better explained, in Kimball's book.

Dimensional modeling is not difficult, but it is deep. Not since E-R modeling have I seen something with so many implications for the way I think.

--
Regards,
    David Cressey
    www.dcressey.com