Geeks With Blogs
Random Musing Putting it together one piece at a time...

While there have many takes and talks about Business Intelligence 2.0, which is really BI working in a SOA environment, the technology behind Business Intelligence hasn't seems to mature to the state where we can really have real time performance without sacrificing some traditional BI systems characteristics. SQL Server 2005 did a great job in getting us closer to Real Time Business Intelligence through the introduction of Proactive Caching as well as SSIS's dimensional destination. However, things get tricky when we try to set up a system with the following requirements

  • Real Time data
  • Historical archives
  • Large SCD Dimensions
  • Very Large Data Warehouses

Part of my consulting work includes designing architectures for Business Intelligence and SOA systems. After some considerable research on 'Best Practices', the options are basically between these 2

UDM to connect directly into the OLTP relational database and into SSAS

While this will enable us to be truly real time with proactive caching, there is a big catch which cannot be ignored. It is obvious that if a system is set up this way, we will have the following issues to worry about

  • No data cleansing and data quality check
  • No dimension versioning with SCD Type II
  • No surrogate keys
  • Unable to hold historical data unless OLTP system archives them
  • Slower queries

This almost contradict the entire philosophy behind having a Business Intelligence system as we're basically going through a big round to connect for traditional reporting with the advantage of being able to connect multiple data sources in the UDM for 'single version of truth'

UDM to connect into Datawarehouse and into SSAS

While this sounds like a good idea, present technologies haven't gotten us to a comfortable level with this arrangement yet. What this mean is that we will not be able to achieve a real 'Real Time' scenario. The shortcomings are as follows

  • Will ETL take too long to process and hence unable to catch up?
  • Will there be competition on hardware resource resulting from ETL and SSAS processing competing with each other, and hence increasing costs due to the need to have separate installations of each?
  • Real Time scope will have to be 'lengthen'
  • More complex system development and maintenance due to the need to store historical partitions and current partitions



While there is really no ideal solution to this (yet, I hope someone might correct me here!). There are a few possible workarounds so that you can get your job done. You can either

  • Adapt option 1 if the requirements allows (which is quite likely)
  • Split the project into 2 sub projects
    • Real-time system without historical audit and X% tolerance for unclean data
    • Historical data warehouse with ability to report of reports coming from the Real-time system.
  • Adapt option 2 and work with your project sponsors and stakeholders on the definition of 'Real-time'
    • Work on the slightly more complex environment and try to simplify it as much as you can

I'm actually more inclined towards the last option as in my experience; it is easier to justify system limitations to business users, then trying to explain why data doesn't reconcile with 2 different systems. I hope this blog entry will give someone out there with some insight with the available options currently.

Posted on Thursday, August 30, 2007 4:58 PM Business Intelligence , Architecture | Back to top

Comments on this post: Implementation of Real Time Business Intelligence

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Nestor | Powered by: