4

I have done a fair number of traditional data warehouse implementations where the loads were done in a batch-oriented manner, i.e. the data is refreshed nightly or, at most, every few hours. I now face a challenge of creating a system, where the data in the data warehouse needs to be maintained close to real-time (a few minutes delay is OK, but no more than that). I have done a lot of reading and it seems that getting close to real-time has been a trend in DW over the last few years. However, I am having trouble finding specific examples and concrete information about the available tools, which support this kind of "trickle-feed" ETL. It seems that the right tool would be able to read the database transaction log and send those changes over to the data warehouse, while allowing to do some data transformations in-flight. Does anybody has experience with real-time data warehousing and can recommend a good tool or point out a good reading on this subject.

Here are a couple of relevant links: http://proc.conisar.org/2012/pdf/2214.pdf http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=37&cad=rja&uact=8&ved=0CGQQFjAGOB4&url=http%3A%2F%2Fwww.researchgate.net%2Fpublication%2F226219087_Near_Real_Time_ETL%2Ffile%2F79e4150b23b3aca5aa.pdf&ei=G_dOU8HXI4iy2wW954GoDw&usg=AFQjCNHZVJuxfmuqwjrdi10oxM8v51WcNA&sig2=6OI-xoIz9b0mH_hzITBVrQ

Thank you!

P.S. I work in a Microsoft shop, so the source databases are on Microsoft SQL Server. I do have a good handle on SSIS, but it doesn't seem to fit here.

1

If you have the Enterprise version of SQL Server, then I would recommend doing this using Change Data Capture (CDC). This is much easier and cleaner than setting up triggers to watch for changed data. Plus it reads the transaction log for changes and then logs those changes in system tables.

You can use SSIS for your ETL. If you have 2012 installed, there are some very handy CDC components available. However you could also use regular components like the OLE DB source and query the built in CDC functions that SQL Server installs when CDC is enabled.

I have used CDC and SSIS to build an integration that is keeping 2 systems in sync while we are transitioning from one to another and it runs every 10 minutes without issue. The key is to keep your SSIS packages as light weight as possible. This means moving lookups away from full cache and other things like that. Ideally, the more often the job runs, the less data you are transmitting each run.

One other thing to consider if you are worried about putting stain on your source system would be look into seeing up an Availability Group read only replica for your source in asynchronous mode. This would allow you to offload your reads from your OLTP database. This does require Enterprise Edition and 2012 or higher though.

| improve this answer | |
  • Thank you for your comment! I did look into CDC and it seems appealing. My one issue with CDC is that it introduces and "extra" step, meaning: from Transaction Log into CDC tables from where I need to read the results and populate my ultimate destination. I am sure there are ETL tools which allow to read logs and go straight to the destination (while performing necessary transformations on the fly). This seems to be the main idea of the articles I've been reading. I suspect that market leaders like Informatica might be able to do this, but I have no personal experience, hence my question. – SQL_Guy Apr 18 '14 at 17:04
  • Gotcha, I understand. I guess it all depends on your users' definition of "real time". I do think using CDC and SSIS you can get data moved in a few minutes, but faster than that would definitely require a different approach. – njkroes Apr 18 '14 at 17:07
0

I had to build something like this recently and I eneded up using an AlwaysOn Availability Group to create read-only replicas on the databases that I needed. If Enterprise is not an option then log shipping can do the job but it will put your datawarehouse further behind and your ETL would need to be able to deal with the restores to the log shipped replicas.

From these replicas I had a custom ETL process that made heavy use of MERGE statements that ran every 5 minutes as a SQL Agent job to pump data new data into the datawarehouse.

You could have this run at smaller intervals depending on the amount of data you have to process.

If your records are datetime stamped then the only thing that should slow this process down over the months and years would be a big increase in transactions. All ETLs will suffer like this in some way if the transactions increase but there are many improvements that can made to scale-out the deployment.

| improve this answer | |
0

Depending on the volume, if you use an affordable high-speed tool for data integration like IRI CoSort (which can use either bulk-extracted/loaded flat-file data or ODBC connections), you'll be near real-time anyway (with its speed, and without in-DB transforms). To be closer to real time per the article, you can 'regulate flow' via CDC in a data- (rather than log-)centric way using the same tool to select rows after a certain date, join to find inserts/updates/deletes, perform multiple kinds of mappings, and feed table, file and report targets at once. It's a 4GL and Eclipse approach that's faster than SSIS and cheaper than Informatica et al.

| improve this answer | |
0

Check out Attunity Replicate and Golden Gate from Oracle.

Other native options include using service broker or SSIS with WMI polling.

If you have SQL Enterprise however CDC is would be the best option in terms of maintainability.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.