Slide 1

Whats New in Microsoft SQL Server 2012 for Business Intelligence Bryan Smith brysmi@microsoft.com

Slide 2

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

Slide 3

Rapid data discovery Managed self-service BI Credible, consistent data Complete DW solutions Rapid data discovery Managed self-service BI Credible, consistent data Complete DW solutions Scale on demand Fast time to market Extend any data, anywhere Optimized productivity Scale on demand Fast time to market Extend any data, anywhere Optimized productivity Required 9s & protection Blazing-fast performance Organizational compliance Peace of mind Required 9s & protection Blazing-fast performance Organizational compliance Peace of mind MISSION CRITICAL CONFIDENCE CLOUD ON YOUR TERMS BREAKTHROUGH INSIGHT Unlock new insights with pervasive data discovery across the organization Create business solutions fast, on your terms across server & private or public cloud Enable mission critical performance & availability at an appropriate TCO

Slide 4

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

Slide 5

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

Slide 6

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

Slide 7

Analytic Functions CUME_DIST PERCENT_RANK PERCENTILE_CONT PERCENTILE_DISC FIRST_VALUE LAST_VALUE LEAD LAG

Slide 8

PERCENTILE_CONT

Slide 9

SQL Server 2012 SELECT DISTINCT Name as DepartmentName, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Rate) OVER (PARTITION BY Name) AS Median FROM EmployeeRates; 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;

Slide 10

Statistical Semantic Search

Slide 11

Spatial Enhancements

Slide 12

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

Slide 13

AlwaysOn Readable Secondaries Virtual Network Name

Slide 14

Columnstore Indexes DateCustomerProductQuantityAmount April 1, 2011Mr. SmithABC15.00 April 1, 2011Ms. JohnsonABC420.00 April 1, 2011Mr. WilliamsXYZ16.50 April 1, 2011Ms. JonesJKL28.00 April 2, 2011Mr. BrownABC15.00 April 2, 2011Ms. DavisMNO220.00 April 2, 2011Mr. MillerJKL520.00 April 2, 2011Mr. SmithABC1050.00 April 3, 2011Ms. WilsonPQR26.00 April 3, 2011Ms. MooreABC420.00 April 3, 2011Mr. TaylorMNO990.00 April 3, 2011Mr. SmithJKL14.00 April 3, 2011Ms. ThomasABC1050.00 SELECT Date, SUM(Amount) FROM FactSales WHERE Product='ABC' GROUP BY Date April 1, 2011Mr. Smith ABC 15.00 April 1, 2011Ms. Johnson ABC 420.00 April 1, 2011Mr. Williams XYZ 16.50 April 1, 2011Ms. Jones JKL 28.00

Slide 15

DateCustomerProductQuantityAmount April 1, 2011Mr. SmithABC15.00 April 1, 2011Ms. JohnsonABC420.00 April 1, 2011Mr. WilliamsXYZ16.50 April 1, 2011Ms. JonesJKL28.00 April 2, 2011Mr. BrownABC15.00 April 2, 2011Ms. DavisMNO220.00 April 2, 2011Mr. MillerJKL520.00 April 2, 2011Mr. SmithABC1050.00 April 3, 2011Ms. WilsonPQR26.00 April 3, 2011Ms. MooreABC420.00 April 3, 2011Mr. TaylorMNO990.00 April 3, 2011Mr. SmithJKL14.00 April 3, 2011Ms. ThomasABC1050.00 April 1, 2011 April 2, 2011 April 3, 2011 ABC XYZ JKL ABC MNO ABC XYZ MNO PQR JKL ABC SELECT Date, SUM(Amount) FROM FactSales WHERE Product='ABC' GROUP BY Date Columnstore Indexes 5.00 20.00 6.50 8.00 5.00 20.00 50.00 6.00 20.00 90.00 4.00 50.00

Slide 16

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

Slide 17

Fast Track & PDW

Slide 18

Analysis Services MultidimensionalTabular PowerPivotMultidimensional Data Model Deployment Mode

Slide 19

Data Models Multidimensional Tabular 2008 R22010

Slide 20

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

Slide 21

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 deved tabular models

Slide 22

Feature Comparisons MultidimensionalTabularPowerPivot ActionsYesNo AggregationsYes Calculated MeasuresYes Custom AssembliesYesNo Custom RollupsYesNo Distinct CountYesYes (via DAX) DrillthroughYesNoYes (detail opens in separate worksheet) HierarchiesYes KPIsYes Linked objectsYesNoYes (linked tables) Many-to-many relationshipsYesNo Parent-child HierarchiesYesYes (via DAX) PartitionsYes No PerspectivesYes Semi-additive MeasuresYes TranslationsYesNo User-defined HierarchiesYes WritebackYesNo

Slide 23

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

Slide 24

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

Slide 25

Data Alerts

Slide 26

Power View

Slide 27

Integration Services Usability Enhancements New Tasks & Transforms Management Catalog

Slide 28

Usability Enhancements Copy/paste Undo/redo Annotations New icons

Slide 29

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

Slide 30

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

Slide 31

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

Slide 32

Change Data Capture: Control Flow

Slide 33

Change Data Capture: Data Flow

Slide 34

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

Slide 35

Data Quality Services

Slide 36

Master Data Services

Slide 37

Project Codename Barcelona

Slide 38

Big Data Hive Linux Windows Windows Azure Java JavaScript.NET ODBC for Hive Reporting Services Excel & PowerPivot Connectors Analysis Services

Slide 39

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

Slide 40

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

Slide 41

Slide 42

SQL Rally

Slide 43

Slide 44

Slide 45

Slide 46

Slide 47

Slide 48

PERCENT_DISC

Slide 49

PERCENTILE_DISC SQL Server 2012 SELECT DISTINCT Name as DepartmentName, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Rate) OVER (PARTITION BY Name) AS Median FROM EmployeeRates; 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, convert(float,y.Rate) as Rate FROM ( SELECT Name, ROUND((MAX(RowId)-1)/2 + 0.5,0) as MedianId FROM Rates GROUP BY Name ) x INNER JOIN Rates y ON x.Name = y.Name AND x.MedianId=y.RowId;

Slide 50

Multidimensional Enhancements Scale & Performance Scalable String Storage for MOLAP DistinctCount Performance Improvements in ROLAP Administration Resource Usage Class for Command End & Query End Events Locks Acquired, Locks Released & Locks Waiting Trace Events PowerShell cmdlets for AMO Applies to both Multidimensional and Tabular deployments Development SSDT/BIDS in Visual Studio 2010

Slide 51

Tabular Enhancements Modeling KPIs, Hierarchies & Perspectives Image Support New DAX Functions Performance & Scale Large Table Support & Partitioning DirectQuery Mode for SQL Server Sources Memory Paging Security Security Roles Row Level Security

Slide 52

Tabular Enhancements Development Enhanced Excel Add-In (PowerPivot Deployment) SSDT/BIDS in Visual Studio 2010 (Tabular Deployment) Administration PowerPivot Configuration Tool (PowerPivot Deployment) Automatic Workbook Upgrade (PowerPivot Deployment) PowerShell cmdlets for AMO (Tabular Deployment) PowerShell for PowerPivot (PowerPivot Deployment) SharePoint Configuration & Server Health Rules (PowerPivot Deployment) SSMS Administration (Tabular Deployment)

Slide 53

New DAX Functions USERELATIONSHIP DISTINCTCOUNT CROSSJOIN, GENERATE & GENERATEALL TOPN, RANK.EQ & RANKX STDEV.S, STDEV.P, STDEVX.S & STDEVX.P VAR.P, VARX.S, VARX.S & VARX.P SUMMARIZE, ROW & ADDCOLUMNS LOOKUPVALUE & CONTAINS PATH, PATHCONTAINS, PATHITEM, PATHITEMREVERSE & PATHLENGTH SWITCH ALLSELECTED, ISCROSSFILTERED, ISFILTERED, HASONEVALUE, HASONEFILTER & FILTERS CURRENCY