TechEd Braindump: Real Time BI with SQL Server 2005 Analysis Services

Speaker: Amir Netz, Richard Tkachuk

  • Analysis Services Proactive Caching
    • Attempting to combine
      • OLAP query performance
      • Real Time data access (as needed)
    • Hands off operation
      • Automatic synchronization with the relational database
      • No more explicit “cube processing”!
  • Technically, caches all share very challenging problem:
    • Coherency – maintaining consistency with the primary source of the data
    • Low Latency – reflecting changes in the primary source in a small amount of time.
  • Proactive Caching Challenges
    • Efficiency
      • How do we not drive the system insane with frequent updates
      • How fast can the caches catch up
    • Performance
      • How to balance between Latency and performance
      • “No free lunch” but you can order a la carte
    • Notifications
      • How does Analysis Services knows that something changed in the RDBMS.
  • Silent Interval
  • You can start a SQL Profiler Trace on Analysis Services 2005. Cool.
  • Balancing Latency and Performance
    • How much REAL in Real Time is needed?
    • Are you willing to accept certain latency for a dramatic improvement of performance?
    • Maximum Latency
  • Achieving Consistent Performance
    • Users are very annoyed when a system behaves inconsistently
      • A consistently slow system is often more usable than a fast system with occasional slowness
    • Consider Automatic MOLAP to ensure consistent performance
      • Automatic MOLAP – never revert to ROLAP
      • Set Maximum Latency to -1
  • Continuously Updated Source systems
    • OLTP systems and trickle-feed DW will always get updated
    • Problem: No time for cache to ever catch up
      • A lot of false starts or Silence Interval never observed
    • Solutions
      • Give up – use Real Time ROLAP
      • Use automatic MOLAP with Silence Override
      • Use auto scheduled MOLAP
  • There will be a white paper describing this process

Disclaimer: These are my notes from my experience at TechEd 2005. I do not guarantee the validity or accuracy of these notes. They are my thoughts and what information I felt was important at the time.