Fetching Data from OLTP to BI database
This is potentially a very tricky question to give a quick answer to, but at a very high level I would say it depends on your reports. If your reports are defined and as you mention are already running against the OLTP database, then it probably makes sense to copy the schema for your new BI database and simply change the report connections. However, if your reporting requirements go beyond this and you regular ad hoc report requests coming through, or even users wanting to create their own reports, then you may want to look into Dimensional Modelling for your BI database.
As mentioned above, you need a way to identify your source records if you want to capture deltas. Datetimes for Insert and Update, ensuring the source system keeps them up to date, are a good way to pick up any changes since the last run. If you go down the Dimensional Modelling route, you'll also need to put the records through an ETL process, rather than simply copying/replicating them in some way.
My preferred method for straightforward replication is the service broker, using triggers on insert/update/delete to replicate the changes to the target database. Once set up, it's very robust and works asynchronously so doesn't directly impact the OLTP load.
.
social.technet.microsoft.com/Forums
