All, is it a good idea to have the data moved from OLTP to a datawarehouse, on the daily basis? What are the pros and cons of the situation handling this kind of situation from the database or the implementation standpoint?
Hi Katie, Daily would be ideal as the data is not too outdated. SSIS packages overnight when the system is at it's quitest is a good bet. Not sure what you mean by situation handling but presume you mean in terms of error handling? If so the best thing to do is set a sql job for each of the packages to run and to notify you when it fails, this way first thing you can look into any issues the very next day or re-direct to different office for analysis/troubleshooting. Jamal
Hi Katie, I agree with Jamal, but I would like to add that a lot of this is going to depend on the end users requirements. If they only need the data to be updated once a week, then you might be adding contention that may not be needed. By no means am I saying that this would be the case for sure, just saying that there are a lot of aspects to consider.