This is probably one of the hottest topics (and has been) in the BI field. A few years ago while working for a major online mortgage company I lead the implementation and design of a very large scale ETL real-time solution that I designed based upon SOA architecture principals. The designs were translated into SQL 2005 Service Broker functionality and overall gave us the kind of ETL speeds we were looking for (up to hundreds of records processed per second). I was not the lead on the SSAS side (my very good friend and colleague, Dan Meyers was) but I believe those cube partitions did use MOLAP and reprocessed a current partition every so often (we also ran an ODS,thus the warrenting of realtime ETL), higher latency than the actual ETL loads. With SSAS Proactive Caching and the ROLAP storage mode you can obtain real-time cubes. In addition, SSIS can directly pipe data into SSAS partitions. There are literally about 4/5 major real-time MSFTBI solutions to enabling end-to-end real-time solutions.

The Service Broker ETL approach was highly controversial to say the least, however several in the upper echelons of the SQL Product group at MSFT were quite impressed with my 'crazy design'. Some would argue that SSIS is the ONLY ETL tool in the MSFT stack? Really, then you obviously have never created ETL Solutions prior to such tools being available? Ever heard of script-based ETL? And I do agree with alot of folks out there that continously executing a DTS/SSIS packages is TOO MUCH overhead for processing a variable amount of records thoughout the day, this is why SOA is huge! Service oriented archtiectures are designed to process/communicate with individual messages.

It certainly did not win me any awards but I was able to meet someone quite special to me out of the deal!  (no it was not Dan mentioned above LOL)

So...what do you think? Is real-time BI worth the effort? Certainly it is difficult to troubleshoot but if the business requires real-time operational reporting then what? *and directly 'hitting' the OLTP server(s) for data is NOT AN ANSWER! Alot of experts in my field provide a blanket response such as "If the business requires real-time then the problem is with the business". I HAVE TO DISAGREE with that statement! If I started up a widget factory I know for certain (especially given that I understand the capabilities of modern-day IT) that I would want real-time BI for my operational reports! How many widgets are getting scraped as of this moment? Is there a problem with a certain 'line' on the factory floor? I'm seeing more and more widgets labeld as BAD in my real-time operational BI system(s)!

The key to doing exceptionally well in the business intelligence field is to put yourself in the shoes of the business owners/executives! This is the highest-order bit in BI. So what do you think? Was I really dropped on my head too often as a child?

For more information on Real-Time Business Intelligence Solutions visit our (ScalabilityExperts's) BI Consulting homepage .