What's New in Microsoft SQL Server Code-Named "Denali" for SQL Server Analysis Services and PowerPivot

1. DBI206 What's New in Microsoft SQL Server Code-Named "Denali" for SQL Server Analysis Services and PowerPivot T.K. Anand, Ashvini Sharma SQL Server Business Intelligence Microsoft Corporation

2. Analysis Services: Today Broad adoption “Customers in the Magic Quadrant survey report that their Microsoft average deployment sizes are now larger than any other vendor in the survey in terms of users.” “Use of OLAP functionality by Microsoft customers is more than double that for the rest of the survey respondents.” Source: Gartner Magic Quadrant for BI Platforms, 2011 Large ecosystem "Wide availability of skills is among the top reasons customers select Microsoft over competing vendors.” Source: Gartner Magic Quadrant for BI Platforms, 2011 Highest rated infrastructure and development tools “Microsoft customers rate its BI platform infrastructure and development tools among the highest compared to other vendors, and a higher percentage of customers use them extensively.” Source: Gartner Magic Quadrant for BI Platforms, 2011

3. Analysis Services: Tomorrow Build on the strengths and success of Analysis Services and expand its reach to a much broader user base Embrace the relational data model – well understood by developers and IT Pros Analysis Services Vision BI Semantic Model Bring together the relational and multidimensional models under a single unified BI platform – best of both worlds! Provide flexibility in the platform to suit the diverse needs of BI applications

4. BI Semantic Model BI Semantic Model One Model for all End User Experiences Data model Client ToolsAnalytics, Reports, Scorecards, Dashboards, Custom Apps Business logic and queries Data access Team BI Personal BI Organizational BI Analysis Services PowerPivot for SharePoint PowerPivot for Excel Data Sources Databases, LOB Applications, OData Feeds, Spreadsheets, Text Files

5. BI Semantic Model demo

6. BI Semantic ModelWhat about existing Analysis Services applications? Existing applications Based on Unified Dimensional Model Existing applications Based on Unified Dimensional Model Existing applications Every UDM becomes a BI Semantic Model New applications New technology options “Denali”

7. BI Semantic Model: Architecture Third-party applications Reporting Services SharePoint Insights Excel PowerPivot BI Semantic Model Multi- dimensional Data model Tabular Business logic and queries MDX DAX Direct Query Data access ROLAP VertiPaq MOLAP Databases LOB Applications Files OData Feeds Cloud Services

8. BI Semantic Model: ScenariosCrescent over a Sales model End User Model Developer BI Semantic Model Data model Tabular Business logic and queries DAX Data access VertiPaq SQL Server Dynamics CRM

9. BI Semantic Model: ScenariosExcel over a Sales model End User Model Developer BI Semantic Model Multi- dimensional Data model Tabular Business logic and queries MDX DAX Data access VertiPaq SQL Server Dynamics CRM

10. BI Semantic Model: ScenariosExcel over a Finance model End User Model Developer BI Semantic Model Data model Multi-dimensional Business logic and queries MDX Data access MOLAP Oracle SAP

11. BI Semantic Model: ScenariosCrescent over a Finance model End User Model Developer BI Semantic Model Multi- dimensional Data model Tabular Business logic and queries DAX MDX Data access MOLAP Oracle SAP

12. Denali in Action… • Professional tools • BI Development Studio in Visual Studio 2010 shell (“Juneau”) • Multi-dimensional and tabular projects • Management Studio updates • Powershell support • End user tools • Excel, PowerPivot • Crescent • Rich model capabilities • Hierarchies, KPIs, perspectives, parent-child • DAX calculated columns & measures • DAX queries, MDX drillthrough • Row level security • Performance & scalability • MOLAP dimension scale (>4GB string store) • VertiPaq performance, partitioning • DirectQuery over SQL Server with VertiPaq (Apollo) index demo

13. VertiPaq for high performance, MOLAP for mission critical scale DirectQuery and ROLAP for real-time access to data sources State-of-the-art compression algorithms Scales to largest enterprise servers BI Semantic Model • Rich data modeling capabilities • Sophisticated business logic using MDX and DAX • Fine-grained security – row/cell level • Enterprise capabilities – multi-language and perspectives • Multi-dimensional and tabular modeling experiences • MDX and DAX for business logic and queries • Cached and passthrough storage modes • Choice of end-user BI tools Flexibility Richness Scalability

14. How Should I Build my Model? • Depends on the application needs for each layer • Data model • Business logic • Data access & storage • Two Visual Studio (BIDS) project types in Denali • Multidimensional project – with MDX and MOLAP/ROLAP • Tabular project – with DAX and VertiPaq/DirectQuery • Project types could change post-Denali • VertiPaqin multidimensional projects, MDX scripts in tabular projects… • Based on customer feedback

15. Data Model Tabular • Familiar model, easier to build, faster time to solution • Advanced concepts (parent-child, many-to-many) not available natively in the model… need calculations to simulate these • Easy to wrap a model over a raw database or warehouse for reporting & analytics Multidimensional • Sophisticated model, higher learning curve • Advanced concepts baked into the model and optimized (parent-child, many-to-many, attribute relationships, key vs. name, etc.) • Ideally suited for OLAP type apps (e.g. planning, budgeting, forecasting) that need the power of the multidimensional model

16. Business Logic DAX • Based on Excel formulas and relational concepts – easy to get started • Complex solutions require steeper learning curve – row/filter context, Calculate, etc. • Calculated columns enable new scenarios, however no named sets or calc members MDX • Based on understanding of multidimensional concepts – higher initial learning curve • Complex solutions require steeper learning curve – CurrentMember, overwrite semantics, etc. • Ideally suited for apps that need the power of multidimensional calculations – scopes, assignments, calc members

17. Data Access and Storage VertiPaq • In-memory column store… typical 10x compression • Brute force memory scans… high performance by default… no tuning required • Basic paging support… data volume mostly limited to physical memory MOLAP • Disk based store… typical 3x compression • Disk scans with in-memory subcube caching… aggregation tuning required • Extensive paging support… data volumes can scale to multiple terabytes DirectQuery • Passes through DAX queries & calculations… fully exploits backend database capabilities • No support for MDX queries… no support for data sources other than SQL Server (in Denali) ROLAP • Passes through fact table requests… not recommended for large dimension tables • Supports most relational data sources… no support for aggregations except SQL Server indexed views

18. Analysis Services Architecture SharePoint Internet Explorer BI Development Studio Project Juneau Excel Services Reporting Services xlsx PowerPivot for Excel Analysis Services BI Semantic Model PowerPivot for SharePoint (Analysis Services) Excel xlsx Third Party Apps Personal BI Team BI Organizational BI

19. Summary BI Semantic Model One Model for all End User Experiences Data model Client Tools Analytics, Reports, Scorecards, Dashboards, Custom Apps Business logic and queries Data access Team BI Personal BI Organizational BI Analysis Services PowerPivot for SharePoint PowerPivot for Excel Data Sources Databases, LOB Applications, OData Feeds, Spreadsheets, Text Files Flexibility Richness Scalability

20. Analysis Services Maestro Program • Deep-dive level 500 course on SSAS 2008 R2 • Intense training spread across five days • Think Microsoft Certified Masters for SQL Server • Prepared by SQLCAT, top industry experts and SSAS Team • “This was by far the best SSAS training/conference I attended ever….” • Apply for the V1.2 Courses • SSAS Maestro Program – Redmond, WA – June 13-17 • SSAS Maestro Program – Madrid, Spain – July 18-22

21. What’s Next? • Follow, Tweet and Enter to win an Xbox Kinect Bundle • GAME ON! Join us at the top of every hour at the BI booth to compete in the Crescent Puzzle Challenge and Win Prizes • Sign up to be notified when the next CTP is available at: microsoft.com/sqlserver Join the Conversation @MicrosoftBI /MicrosoftBI

22. Resources • Connect. Share. Discuss. http://northamerica.msteched.com Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers http://microsoft.com/technet http://microsoft.com/msdn

23. Complete an evaluation on CommNet and enter to win!

25. © 2011 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.