1 / 46

What’s New in Microsoft SQL Server 2012 for Business Intelligence

What’s New in Microsoft SQL Server 2012 for Business Intelligence. Bryan Smith brysmi@microsoft.com. Online Operation Enhancements. Contained Database Authentication. AlwaysOn. Reliable Secondaries. Multi-site Clustering. SQL Server Express LocalDB. Windows Server Core Support.

penn
Download Presentation

What’s New in Microsoft SQL Server 2012 for Business Intelligence

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. What’s New inMicrosoft SQL Server 2012for Business Intelligence Bryan Smithbrysmi@microsoft.com

  2. Online Operation Enhancements Contained Database Authentication AlwaysOn Reliable Secondaries Multi-site Clustering SQL Server Express LocalDB Windows Server Core Support Statistical Semantic Search PHP & Java Connectivity FileTable Unstructured Data Performance ColumnStore Index Extended Events Enhancements AlwaysOn Connection Director Multiple Secondaries PowerShell 2.0 Support Reporting Alerts Database Recovery Advisor SQL Server Data Tools PowerPivot Enhancements Resource Governor Enhancements T-SQL Debugger Enhancements Audit Filtering Power View Full Globe Spatial Flexible Failover Policy Unstructured Data Performance CDC Support for SSIS BI Semantic Model Audit Resilience Distributed Replay New SSIS Design Surface User-defined Audit SSMS to Windows Azure Platform Data Quality Services FTS Support for Czech and Greek 15k Partitions Master Data Management Excel Add-in Full-Text Search Performance HA for StreamInsight Availability Groups SSIS Troubleshooting Ad Hoc Reporting Default Scheme for Windows Groups Spatial 2D Support ODBC Driver for Linux SharePoint Active Directory Support SSIS Package Management SQL Audit for All Editions T-SQL Enhancements

  3. Online Operation Enhancements Contained Database Authentication AlwaysOn Reliable Secondaries Multi-site Clustering SQL Server Express LocalDB Windows Server Core Support Statistical Semantic Search PHP & Java Connectivity FileTable Unstructured Data Performance ColumnStore Index Extended Events Enhancements AlwaysOn Connection Director Multiple Secondaries PowerShell 2.0 Support Reporting Alerts Database Recovery Advisor SQL Server Data Tools PowerPivot Enhancements Resource Governor Enhancements T-SQL Debugger Enhancements Audit Filtering Power View Full Globe Spatial Flexible Failover Policy Unstructured Data Performance CDC Support for SSIS BI Semantic Model Audit Resilience Distributed Replay New SSIS Design Surface User-defined Audit SSMS to Windows Azure Platform Data Quality Services FTS Support for Czech and Greek 15k Partitions Master Data Management Excel Add-in Full-Text Search Performance HA for StreamInsight Availability Groups SSIS Troubleshooting Ad Hoc Reporting Default Scheme for Windows Groups Spatial 2D Support ODBC Driver for Linux SharePoint Active Directory Support SSIS Package Management SQL Audit for All Editions T-SQL Enhancements

  4. Database Engine Analysis Services Reporting Services Integration Services Master Data Services Data Quality Services StreamInsight

  5. Database Engine • T-SQL Analytic Functions • Statistical Semantic Search • Spatial Enhancements • Partitioning • Online Operations • Readable Secondaries • Columnstore Indexes

  6. Analytic Functions • CUME_DIST • PERCENT_RANK • PERCENTILE_CONT • PERCENTILE_DISC • FIRST_VALUE • LAST_VALUE • LEAD • LAG

  7. PERCENTILE_CONT

  8. PERCENTILE_CONT SQL Server 2012 Pre-SQL Server 2012 WITH Rates AS ( SELECT Name, Rate, ROW_NUMBER() OVER ( PARTITION BY Name ORDER BY RATE) AS RowId FROM EmployeeRates ) SELECT y.Name, AVG(convert(float,y.Rate)) as Rate FROM ( SELECT Name, (MAX(RowId)+1)/2 as LowerBound, ROUND(0.5+(MAX(RowId)/2),0) as UpperBound FROM Rates GROUP BY Name ) x INNER JOIN Rates y ON x.Name= y.Name AND (x.LowerBound=y.RowId OR x.UpperBound=y.RowId) GROUP BY y.Name; SELECT DISTINCT Name as DepartmentName, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Rate) OVER (PARTITION BY Name) AS Median FROM EmployeeRates;

  9. Statistical Semantic Search

  10. Spatial Enhancements • Whole globe support • Improved precision • Support for curvilinear objects • Index tuning visualizations

  11. Partitioning & Online Operations • 15,000 partitions support finer-grain sliding-window data management • Adding non-nullable fields with defaults now metadata operation • Rebuilding & reorganizing indexes on tables with BLOBs now online operation

  12. AlwaysOn Readable Secondaries Virtual Network Name

  13. Columnstore Indexes SELECT Date, SUM(Amount) FROM FactSalesWHEREProduct='ABC'GROUP BY Date April 1, 2011 Mr. Smith ABC 1 5.00 April 1, 2011 Ms. Johnson ABC 4 20.00 April 1, 2011 Mr. Williams XYZ 1 6.50 April 1, 2011 Ms. Jones 2 8.00 JKL

  14. Columnstore Indexes SELECT Date, SUM(Amount) FROM FactSalesWHEREProduct='ABC'GROUP BY Date 5.00 April 1, 2011 ABC 20.00 April 1, 2011 ABC 6.50 April 1, 2011 XYZ 8.00 April 2, 2011 JKL 5.00 April 2, 2011 ABC 20.00 April 2, 2011 MNO 20.00 April 2, 2011 ABC 50.00 April 3, 2011 XYZ 6.00 April 3, 2011 MNO 20.00 April 3, 2011 PQR 90.00 April 3, 2011 JKL 4.00 April 3, 2011 JKL 50.00 April 3, 2011 ABC April 3, 2011

  15. Columnstore Indexes • Secondary index using xVelocity storage • Supports traditional data types • Read-only in 2012 RTM • Use partition-swapping in ETL

  16. Fast Track & PDW

  17. Analysis Services Multidimensional DeploymentMode Tabular PowerPivot Multidimensional Tabular DataModel

  18. Data Models Multidimensional 2008 R2 2010 Tabular

  19. Data Models Multidimensional Tabular Data as rows & columns Less metadata-driven Wider range of data sources IT or business development Direct support for Power View • Data as points • More metadata-driven • Traditional DW platforms • IT-only development • Indirect support for Power View

  20. Deployment Modes • Multidimensional • Supports the traditional multidimensional model • Tabular • Large-scale server-only deployment of tabular model • PowerPivot • Formerly known as SharePoint Integrated mode • Small-scale deployment for client dev’ed tabular models

  21. Feature Comparisons

  22. Reporting Services • Excel & Word Rendering • SharePoint Integration • Data Alerts • Power View

  23. SharePoint Integrated Mode • Easier configuration • Claims-based authen • SP backup & recovery • User logging service

  24. Data Alerts

  25. Power View

  26. Integration Services • Usability Enhancements • New Tasks & Transforms • Management Catalog

  27. Usability Enhancements • Copy/paste • Undo/redo • Annotations • New icons

  28. Designer Experience • Data Flow • Data flow grouping • Data flow mappings • Type conversions • Script transform debugging • Source & destination wizards • General • Shared connection managers • Package parameters • Autosave & recovery • Simplified XML for difference analysis

  29. Functionality • Data Quality Services Integration • Change Data Capture Task & Transforms • Works with SQL Server & Oracle (with Oracle Log Miner)

  30. Change Data Capture • CDC Control Task (Control Flow) • c • Retrieves & stores CDC state • CDC Source (Data Flow) • c • Retrieves data from source using CDC • CDC Splitter (Data Flow) • x • Splits pipeline based on source operation

  31. Change Data Capture: Control Flow

  32. Change Data Capture: Data Flow

  33. Management • Project-based deployment to catalog database • Package-level security • Provides versioning and centralized logging • Maintains centralized configurations • Package management, execution, & monitoring through SSMS, T-SQL, & PowerShell • Advanced data flow troubleshooting with data taps • Package performance tracking through DMVs • Built-in reporting & dashboard

  34. Data Quality Services

  35. Master Data Services

  36. Project Codename Barcelona

  37. Big Data ReportingServices AnalysisServices Excel &PowerPivot ODBC for Hive Hive Connectors Linux Java Windows JavaScript Windows Azure .NET

  38. Certifications • Microsoft Certified Technical Specialist (MCTS) • Microsoft Certified IT Professional (MCITP) • Data Platform • 70-465: Designing Database Solutions with Microsoft SQL Server 2012 • 70-464: Developing Microsoft SQL Server 2012 Databases • 70-463: Implementing Data Warehouses with Microsoft SQL Server 2012 • 70-462: Administering a Microsoft SQL Server 2012 Database • 70-461: Querying Microsoft SQL Server • Business Intelligence • 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012 • 70-466: Implementing Data Models & Reports with Microsoft SQL Server 2012 • 70-463: Implementing Data Warehouses with Microsoft SQL Server 2012 • 40-462: Administering a Microsoft SQL Server 2012 Database • 70-461: Querying Microsoft SQL Server • Microsoft Certified Master • Data Platform • Business Intelligence

  39. Community Events • SQL Saturday #107 – Houston, TX • PASS SQL Rally – Dallas, TX • Microsoft TechEd – Orlando, FL • SQL Saturday #125 – Oklahoma City, OK • PASS Summit – Seattle, WA

  40. SQL Rally

More Related