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

Is this even possible? I've been trying to design a BI system on SQL Server 2005 with strict constraints to maintain a 'single version of truth'. One of the hardest things in this system is to provide Real Time data views with historical audit. To make things more exciting, many of the dimensions are slowly changing dimensions.

Proactive Caching in SSAS seems to address the issue of Real Time BI in a good sense. We're actually able to get pretty good performance on a ROLAP engine thanks to this neat feature. However, it seems like we're trying to merge two entirely different things here when we need to have a Real Time Business Intelligence System which is capable of capturing historical changes and do some dimensional versioning. Since proactive caching really works its charm when we do a direct connection to the OLTP relational database, we will be really missing out on the ETL / Cleansing layer. In short, we will have no area where we can do SCD Type II transformation.

There are other ways in which we can use SSIS with dimension destination, but I doubt if this approach will yield good performance and results.

There are probably some other ways in which this can be achieved, such as splitting the system into 2 different sub systems, one to cater for Real Time needs, and the other for historical data analysis. I'm not too happy with this as well, as it takes away the 'single version of truth' away from a Business Intelligence system.

Comments anyone?

Posted on Wednesday, August 29, 2007 7:29 AM Business Intelligence , Architecture | Back to top

Comments on this post: Real Time BI with SCD Type II?

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

Copyright © Nestor | Powered by: