Why Upgrade to SQL 2012: When You Should Choose Denali & Main Concepts

1. Why Upgrade to SQL 2012: When You Should Choose Denali & Main Concepts Efi Goldman Solution Expert at Panorama Software

2. Training Plan

3. Agenda • Release Notes • Work environments optimal for SQL 2012 • Different products within SQL 2012 and Pricing • Comparison between SQL Server 2008 R2 and SQL 2012 • Understanding Denali's in-memory engine • Sharepointintegration

4. Release Notes • Was previously called Denali • Current official title is SQL 2012 • Will be released in H1/2012 • Current version is Rc0 possible to download from Microsoft Download center

5. SQL 2012 and Pricing

6. SQL 2012 Editions

7. SQL Server 2008 R2 vs. SQL 2012

8. Differences between editions *! *!

9. In-Memory Engine

10. 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

11. BI Semantic ModelExisting 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

12. BI Semantic Model: Architecture BI Semantic Model Multi- dimensional Data model Tabular Business logic and queries MDX DAX Direct Query VertiPaq MOLAP Data access ROLAP

13. Denali demo

14. 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

15. 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

16. 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

17. 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

18. Data Access and Storage VertiPaq • In-memory column store… typical 10x compression • Brute force memory scans… high performance without tuning… • Basic paging … data volume mostly fits in physical memory MOLAP • Disk based store… typical 3x compression • Disk scans with in-memory subcube caching… aggregation, tuning required • Extensive I/O support… scales to multiple TBs 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…

19. Sharepoint Integration

20. Sharepoint Integration Power Pivot Reporting Services Project Crescent Always On Alerting Data Quality Services (DQS)

21. Panorama Necto Architecture Analysis Services PowerPivot Applications Web Services Databases Flat Files * * End User • - Automated Insights • Social Intelligence • Advanced Analytics Intelligence Layer AIDEN (Automated Intelligence Driving Engine) Modeling, aggregations, semantics and / or and / or Denali Source, operational raw data

22. 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

23. Live Q & A info@panorama.com PanoramaSW Name: Business Intelligence News Panorama Software www.panorama.com/industry-news