Real-Time Business Intelligence with SQL Server 2005 Analysis Services

1. Real-Time Business Intelligence with SQL Server 2005 Analysis Services

2. What Will We Cover? • What are the barriers to real-time business intelligence (BI)? • How can Microsoft SQL Server 2005 Analysis Services be used to make BI more real-time?

3. Helpful Experience • Understanding of Analysis Services • Experience with SQL Server • Experience with Reporting Level 300

4. Agenda • Addressing Challenges of Real-Time Business Intelligence • Delivering Real-Time Business Intelligence

5. Difficulties of Real-Time BI POS OLTP

6. Difficulties of Real-Time BI SSIS POS OLTP Cleanse and Enrich DW POS OLTP

7. Difficulties of Real-Time BI SSIS POS OLTP Cleanse and Enrich DW UDM Staging Cube POS OLTP Validation UDM Production Cube

8. Barriers to Real-Time BI

9. Barriers to Real-Time BI

10. Barriers to Real-Time BI

11. Barriers to Real-Time BI

12. Barriers to Real-Time BI

13. More Barriers to Real-Time BI

14. More Barriers to Real-Time BI

15. More Barriers to Real-Time BI

16. Pushing Data into UDM SSIS UDM Production Cube POS OLTP Cleanse and Enrich POS OLTP Data can be pushed directly into a Unified Dimensional Model

17. Pushing Data into UDM SSIS UDM Production Cube POS OLTP Cleanse and Enrich POS OLTP Data can be pushed directly into a Unified Dimensional Model SQL Server 2005 Integration Services processing transforms

18. Pushing Data into UDM SSIS UDM Production Cube POS OLTP Cleanse and Enrich POS OLTP Data can be pushed directly into a Unified Dimensional Model SQL Server 2005 Integration Services processing transforms Includes fact and dimension tables

19. demonstration Demo • Linking Integration Services (SSIS) and Analysis Services Directly • View an SSIS Package • Run an SSIS Package • Browse the Updated Cube

20. Agenda • Addressing Challenges of Real-Time Business Intelligence • Delivering Real-Time Business Intelligence

21. Updating with Trickle Feeds SSIS UDM Production Cube POS OLTP Cleanse and Enrich POS OLTP Trickle feeds can get data directly into the UDM

22. Updating with Trickle Feeds SSIS UDM Production Cube POS OLTP Cleanse and Enrich POS OLTP Trickle feeds can get data directly into the UDM Integration Services updates the cube every few minutes

23. Building the Cube Directly SSIS UDM Production Cube POS OLTP Cleanse and Enrich POS OLTP UDM can combine data from multiple sources

24. Building the Cube Directly SSIS UDM Production Cube POS OLTP Cleanse and Enrich POS OLTP UDM can combine data from multiple sources One of the underlying sources must be SQL Server

25. Building the Cube Directly SSIS UDM Production Cube POS OLTP Cleanse and Enrich POS OLTP UDM can combine data from multiple sources One of the underlying sources must be SQL Server Not applicable for all scenarios

26. Continuously Changing Data Problem How to handle updated data Source data might be continually changing How to ensure consistency during processing

27. Continuously Changing Data Solution Problem How to handle updated data Source data might be continually changing How to ensure consistency during processing Use Snapshot Isolation

28. Proactive Caching • Policy-based management • Has source data changed? • When to refresh? • How to answer queries during refresh

29. Proactive Caching • Policy-based management • Has source data changed? • When to refresh? • How to answer queries during refresh • Proactive caching combines • OLAP query performance • Real-time data access as needed

30. Proactive Caching • Policy-based management • Has source data changed? • When to refresh? • How to answer queries during refresh • Proactive caching combines • OLAP query performance • Real-time data access as needed • No more explicit “cube processing”

31. Proactive Caching – An Example UDM MOLAP Cache MDX OLTP Analysis Services

32. Proactive Caching – An Example UDM POS MOLAP Cache Events SQL OLTP Analysis Services

33. Proactive Caching – An Example UDM New Version MOLAP Cache Data OLTP Analysis Services

34. Using Policies to Refresh the Cache UDM UDM Production Cube POS OLTP POS OLTP Policy-based refresh of the cache

35. demonstration Demo • Using MOLAP and Reverting to ROLAP when Latency Exceeded • View Partition Settings • Cause Latency • Revert to ROLAP

36. Proactive Caching Challenges • Efficiency • How to avoid overloading Analysis Services with frequent updates • How fast can the caches catch up?

37. Proactive Caching Challenges • Efficiency • How to avoid overloading Analysis Services with frequent updates • How fast can the caches catch up? • Performance • How to balance between latency and performance

38. Proactive Caching Challenges • Efficiency • How to avoid overloading Analysis Services with frequent updates • How fast can the caches catch up? • Performance • How to balance between latency and performance • Notifications • Is the cache refreshed on change or periodically? • How does AS know that the RDBMS has changed?

39. Policy Settings

40. Policy Settings

41. Policy Settings

42. Policy Settings

43. Scaling Up Problem How to handle large quantities of data Re-creating the whole cache on every change is expensive

44. Scaling Up Solution Problem How to handle large quantities of data Re-creating the whole cache on every change is expensive Use ROLAP Use partitions Use incremental cache updates to add data

45. demonstration Demo • Using Automatic MOLAP with Polling Queries and Incremental Refresh • View Cube Settings • View Reports • Add New Data

46. Session Summary • Real-time business intelligence possible in SQL Server 2005 • New technologies • New types of architectures/applications

47. For More Information Visit TechNet atwww.microsoft.com/technet Visit the following site for additional information www.microsoft.com/technet/sql-09

48. Microsoft Press Publications For the latest titles, visit www.microsoft.com/learning/books/itpro/

49. Non-Microsoft Publications These books can be purchased at all major bookstores and online retailers

50. Training Resources For training information and availability:www.microsoft.com/learning