 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Data Warehousing / February 2006Design opinion?
Hi I am starting the task of creating a data warehouse for my company's OLTP system and we would like to offload reporting to a reporting schema at a data warehouse. We have 2 kinds of reports: operational (used daily and need data just about real time) and analytical (used to determine sales trends, etc). For the analytical reports, I think we can just do a nightly ETL load since the data doesn't need to be too recent. However, I was thinking of using replication to replicate the table data used in the operational reports, capturing the data in stored procedures as they come across the pipe and then performing ETL within the stored procs. That way I think we can have real time data available for reporting. Does anyone have an opinion on this scheme? Thank you I've done this kind of configuration before on SQL 2K. Transactional replication is about the best you can do since log shipping takes the DB offline. We preferred to pause replication during the staging portion of the ETL cycle to avoid blocking and dead locking. This also gave us a consistent point in time snap shot of the source to work from. Using TSQL as an ETL tool is fast but has limitations around data transformation and no access to the bulk load api. Good luck, Danny > Hi > [quoted text clipped - 16 lines] > > Thank you Hello, Timing is everything. I would work on getting your data warehouse overnight processes completed. In a short period of time new replication options will be available in SQL Server 2005. Even the existing options may meet your needs, but with the right timing you may have better options available to you. Depending on what method you use to migrate data to the warehouse (and also the volitility of the OLTP data), this may impact the performance of the OLTP system. Also, once the user expects analysis in real time, any interruption will be perceived as a system malfunction, and thus your maintenance requirements and responsilbility have increased. Do they really need real time analysis? Also, what type of analysis; OLAP? > Hi > [quoted text clipped - 16 lines] > > Thank you |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.