SQL Server 2005 Analysis Services & Reporting Services for the developer

1. SQL Server 2005 Analysis Services & Reporting Services for the developer Eric Nelson Application Architect http://blogs.msdn.com/ericnel UK ISV and SQL Server 2005

2. Agenda • Assumes no BI knowledge or exposure to SQL 2000 Analysis Services • But don’t panic if you already know BI well • Short Overview of BI • A lot on Analysis Services • But not covering data mining • A little on what is new in Reporting Services

3. What is Business Intelligence all about anyway?

4. Who cares – I must have it  • Growing market and a huge market - $5b in 2005 • Check out http://www.olapreport.com/market.htm • 2 years back very few ISVs I engaged were talking BI • Now over 50% are talking about adding BI to their application – and many of these are already doing it… Converting Data into Insight

5. 1/2: Basic Multidimensional Modelling • Basic elements are measures, dimensions, and schema • Measures: • The performance indicators that you want to evaluate. Rule of thumb: can you aggregate it? • E.g. volume, sales, headcount and cost • Dimensions: • Query/report “by” something • E.g. by month, by sales region, by department • Can be “hierarchies” e.g. year, month, week, day • Values within levels are “members” e.g. 2004, 2005 • Dimensions have attributes. Similar to columns on a table. E.g. Customer may have Name, Age, Gender, City

6. 2/2: Basic Multidimensional Modelling • Schema: • Measures and Dimensions can be modelled as a “star schema” • Fact table – column per measure and per dimension

7. SQL Server 2005 BI Overview

8. SQL Server 2005 Business Intelligence Development Tools BI Development Studio Management Tools Management Studio Reporting Services Analysis Services Integration Services SQL Server

9. 1. Integration Services • CompleteRewrite! • We learnt a lot from DTS  • Enterprise ETL platform • High performance • High scale • Trustworthy and reliable • Best in class usability • Rich development environment • Source control • Visual debugging of control flow and data • Great range of transforms out-of-the-box • Highly extensible • Custom tasks • Custom enumerations • Custom transformations • Custom data sources

10. 2. Analysis Services • Major improvements over 2000 • Introduces the Unified Dimensional Model • Best of relational reporting and OLAP Analysis • Pro-active caching • Bringing the best of MOLAP to ROLAP • Advanced Business Intelligence • KPIs, Data Mining, MDX scripts, translations, currency, stored procedures… • Web services for thin client footprint • Native XML/A with great support in the industry • Great MDX Support

11. 3. Reporting Services • Introduced with SQL Server 2000 • Open, extensible enterprise reporting solution • Report authoring, management, delivery • Office System integration • VS.NET hosted Report Designer • SQL Server 2005 enhancements • Integration with AS, IS and management tools • More easily “embeddable” • Improved report interactivity • Additional Report Designer for end users

12. A “typical” path to BI

13. Application SQL Server Reporting Services Reports Application Data Adding Reporting • Well understood model • Simple with good tool support • But… • Reports are “set in stone” • Reports can cripple OLTP work • Reports can take a long time to complete • Reports need to painfully navigate 3NF • Data may be “lost” over time

14. Application Reporting Application Data ‘OLTP’ Application Data ‘Reporting’ Reporting against the operational store has its problems… • In high performance, multi-user scenarios • The schema is optimized for insert, update and delete, not for reporting • You should keep reporting queries separate from OLTP ‘queries’ for performance reasons ? ‘ETL’ IS

15. But… some reports will take a very long time to run or be very hard to code with SQL and still “set in stone” • Cubes provide multidimensional analysis • Allows dynamic investigation – “slicing and dicing” • Handles multiple dimensions gracefully • Cubes capture complex business analytics • KPIs – key performance indicators • Cubes allow MDX queries • MDX is a query language specifically for working with cubes • Cubes make queries/reports quick • Pre-aggregate Reporting Excel 3rd Party Application Analysis Services Application Data ‘OLTP’ DW (Optional) IS (Optional)

16. Pre-Aggregation • Consider a simple scenario: • “I want the average annual sales for each of our regions by looking at annual sales over the last 10 years” • That’s one value for each region • 10 regions. 10 offices per region. Average 100 orders per day per office. Each order is for on average 10 items. • So that is 10 values • The data is held in an OLTP system currently • The value of a sale is held in order line items • To associate a line item to a region need to link via order header to the salesperson to office to region • Order header includes the date • How many tables? • How many rows to aggregate over?

17. Pre-Aggregation • Tables? 5 • How many rows to aggregate over? 300 million rows! • 100 offices x 100 orders x 10 items x 300 days x10 years • Plus complex joining through header etc • And we want an answer of just 10 numbers (one per region)… • How improve in OLTP? • Denomarlise? • Pre-aggregate? But at what level? • Into order header? 30 million rows • Add a new annual sales table with a row per year and a column per region? 10 rows  • But what if they then want same answer by office? 300 million rows  • What about with Analysis Services? • It just does a great job for you 

18. Analysis Services

19. Introducing the Unified Dimensional Model (UDM) • Contains the schema, security, calculations, caching… • It is the simple view that end users will be comfortable with • Combines the best of traditional OLAP … • Performance • Rich calculations • End-user model • With the best of traditional Relational based reporting • Real time & Detail level data • Complex schema • Simplified management • The UDM is an important architectural change

20. MOLAP OLAP Browser (1) MOLAP OLAP versus Reporting OLAP Browser (2) Datamart Reporting Tool (3) Datamart Reporting Tool (2) Duplicate Models Duplicate Data Enterprise BI – A Messy Reality Data Source Data Model Tool DW Reporting Tool (1)

21. OLAP Browser (1) OLAP Browser (2) Datamart Datamart Enterprise BI – With A UDM Data Source Data Model Tool MOLAP MOLAP UDM Reporting Tool (2) Reporting Tool (1) DW BI Applications

22. OLAP Browser (1) OLAP Browser (2) Datamart Datamart Enterprise BI – With A UDM Data Source Data Model Tool MOLAP MOLAP UDM Reporting Tool (2) Reporting Tool (1) DW BI Applications A single dimensional model for all OLAP analysis and Relational reporting needs

23. OLAP Browser (1) OLAP Browser (2) Datamart Datamart Analysis ServicesScaleable, high performance UDM Server Data Source Tool MOLAP Analysis Services MOLAP UDM XML/A or ODBO Reporting Tool (2) Reporting Tool (1) DW BI Applications • Optimized SQL to all major remote database management systems (RDBMS) platforms • Most scaleable OLAP store • OLE DB and XML/A APIs • UDM automatically becomes Web Service • API supported by all major BI vendors

24. OLAP Browser (1) OLAP Browser (2) Datamart Datamart Notifications Analysis ServicesTransparent MOLAP Caching Data Source Tool MOLAP Analysis Services MOLAP UDM XML/A or ODBO Reporting Tool (2) Reporting Tool (1) Cache DW BI Applications • “Proactive Caching” – Automatic MOLAP cache creation and management • MOLAP becomes transparent – no need to manage an OLAP store any more • Relational reporting enjoys MOLAP like performance

25. OLAP Browser (1) OLAP Browser (2) Datamart Datamart Analysis ServicesData Marts are Virtualized Data Source Tool Analysis Services UDM XML/A or ODBO Reporting Tool (2) Reporting Tool (1) Cache DW BI Applications • UDMs provide “subject area centric” view of the DW • “Perspectives” feature allow user/group specific view of the same data • High performance ensured by the Proactive Cache

26. BI Development Studio to create our first UDMUse ETL to extract from Operational Data Store Create a DSV for a virtual star schema Create a Cube Query using BI Development Studio Query using Reporting Services Query using Excel Demo

27. SIDEBAR 1/2: How different is it? • How many clicks would it take to build the same Foodmart2003 Cube in Analysis Services 2000 and 2005?

28. Creating the Sales and Warehouse cubes in Foodmart SIDEBAR 2/2: Very Different 

29. And finally - options to query the UDM • Custom applications • Fire MDX at the UDM using XML/A • Or “Send queries at your cube via SOAP from any client”  • Fire MDX at the UDM using ADOMD.NET • Others… • Microsoft Clients • BI Development Studio • Reporting Services can generate MDX • Office Web Components http://www.microsoft.com/downloads/details.aspx?FamilyID=7287252c-402e-4f72-97a5-e0fd290d4b76&DisplayLang=en • Excel Pivot Tables http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_XLOWCOlap.asp • Excel Add In for BI* http://www.microsoft.com/office/solutions/accelerators/exceladdin/default.mspx • 3rd Party Clients • E.g. Proclarity http://www.proclarity.com/yellowstone/

30. Reporting Services

31. Themes for 2005 • Core Product Enhancements • Extend features based on feedback from SQL2K version • Incremental enhancements, performance and scalability • Tighter Integration • Strengthen SQL Server end-to-end BI scenarios • Tighter integration with Office and SharePoint • Developer Focus • Increased integration for the Visual Studio developer • Freely redistributable Windows and ASP.NET controls • Empowering End Users • Report Builder enables end-user reporting scenarios

32. Multi Value Parameters Core Product Enhancements Date Picker

33. Small ActiveX control for Print Preview Floating Headers Similar to Excel Splitters On Table and matrix Interactive Sort without re-query Core Product Enhancements

34. Report Explorer provides browsing of server Report Viewer used to view reports Parts can be connected or used standalone Works in both SPS and WSS Tighter Integration with Sharepoint

35. MDX and data mining query builders Tighter Integration with Analysis Services

36. Single point of management for all SQL Server components Superset of Report Manager functions Tighter Integration with Management Studio

37. Enhanced Expression Editor Custom Report Items Create controls Developer Focus on Productivity

38. Controls make it easy to embed reporting functionality into applications Windows Forms (rich client) and Web Forms (ASP.NET) control Local processing mode (no server) or connected server mode Developer Focus on Embedding

39. Empowering End Users with a new Report Designer • Report Builder - A new ad-hoc report design tool for Reporting Services • Targeted at Business Users • Developers/IT define a model to hide complexity • Complements the Visual Studio Report Designer

40. Based on familiar Microsoft Office UI Reports built via report templates (table, matrix, chart, etc.) ClickOnce application deployed from the Report Server Createnew ormodify existing reports Finished reports can be saved on the server Report Builder Client

41. Hosted in Visual Studio (new project type) For editing models used by Report Builder Auto-generation of models from relational and analytical data sources Report Builder Model Designer

42. Summary • Adding BI to your application adds value • Help your users make better decisions, faster • Adding BI to your application is cost effective • Powerful BI components in SQL Server • Full ETL Capabilities • Integration Services • Including Oracle to DB2  • Powerful OLAP • Analysis Services • Direct to relational or against a DW/DM • Powerful Reporting • Against any RDBMS • Two great designers

43. Resources • Whitepapers/Ariticles • White Paper: “Introduction to the Unified Dimensional Model (UDM)” by Paul Saunders, July 2004http://msdn.microsoft.com/sql/2005/2005articles/default.aspx?pull=/library/en-us/dnsql90/html/introtoudm.asp • Introduction to OLAP in SQL Server 2005http://www.devx.com/dbzone/Article/21410 • Comparison of AS2000 and AS2005http://www.devx.com/dbzone/Article/21539 • Querying KPIs from ASP.NET http://www.mosha.com/msolap/articles/kpiquerying.htm • Migrating from 2000 to 2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/mmsqls2.mspx • Webcasts: • Report Builder linked from http://blogs.msdn.com/ericnel/archive/2004/11/11/255724.aspx • Analysis Services http://msevents.microsoft.com/cui/eventdetail.aspx?eventid=1032263432&culture=en-us • Reporting Services http://msevents.microsoft.com/cui/eventdetail.aspx?eventid=1032263305&culture=en-us • Newsgroups • Microsoft.private.sqlserver_adomd • Microsoft.public.data.xmlanalysis • Microsoft.public.sqlserver.dts • Microsoft.public.sqlserver.olap • Blogs • Development lead for Analysis Services http://www.mosha.com/msolap/ • Reed Jacobson on Analysis Services http://sqljunkies.com/weblog/hitachiconsulting/ • Donald Farmer PM for Integration Services http://sqljunkies.com/weblog/donald%20farmer/ • Data Mining • http://www.sqlserverdatamining.com • Books • No AS2005 books as yet and AS2000 books are “irrelevant”  • Hitchhikers Guide to SQL Server 2000 Reporting Services – excellent!

44. © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only.MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

45. © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only.MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

46. Appendix • Some useful bits and bobs

47. Some key elements of UDM…(by no means all of them!)

48. Security • End-user Model • Translations • Actions • KPIs … Calculations Scope(Customer.Country.USA, *); Sales = 2; End Scope; Customer ID (PK) Name Age Order Order# (PK) CustomerID DueDate • Basic Dimensional Model • Cubes and Dimensions • Storage/caching policies Data Source View Components of a UDM

49. Customer ID Name Customer ID Name Quota CustomerID Quantity Multiple Data Sources • DSV can contain tables drawn from multiple heterogenous data sources • ‘Primary’ data source must be SQL Server • OLE DB • Microsoft® SQL Server™ 7.0, Microsoft® SQL Server™ 2000, and Microsoft® SQL Server™ 2005) • Oracle (8.0, 9.0, 10.0) • IBM DB2 (8.1) • NCR TeraData (4.1, 5.0) • Microsoft® Jet (4.0) • Managed Providers • SQL Server • Oracle DSV Quota CustomerID Quantity

50. Translations • Supports global enterprises • Allows translations of Metadata and Data