WIXLIB

New Features in Analysis Services and the TabularData Stream Protocol for SQL Server 2019Baoming YuLin DongSQL OIPI Test Development Team

Agenda1Tabular Data Stream Protocol‐ MS-TDS Protocol Overview‐ What’s new in MS-TDS Protocol‐ New features’ Demo2Analysis Services Protocols‐ SSAS Protocols Overview‐ What’s new in SSAS Protocols‐ Test Suite Intro and Demo3Resources议程安排

MS-TDS Overview

What is TDSThe Tabular Data Stream (TDS) protocol is an application layer request/response protocol thatfacilitates interaction with a database server and provides for the following:Authentication and channel encryption negotiation.Specification of requests in SQL (including Bulk Insert).Invocation of a stored procedure or user-defined function, also known as a remote procedure call (RPC).The return of data.Transaction manager requests.

Transport LayersThe TDS protocol does not prescribe a specific underlying transport protocol to use on the Internet or onother networks. TDS only presumes a reliable transport that guarantees in-sequence delivery of data.The current version of the TDS protocol has implementations over the following transports:TCP [RFC793]A reliable transportover the VirtualInterface Architecture(VIA) interface[VIA2002]Named Pipes[MSDN-NP]Shared memory[MSDN-TDSENDPT]

Typical Message Sequence (Pre-login)Created connection successfully (Pre-login state)Initial Pre-Login Request with ENCRYPTION ONTLS Exchange 1 RequestTLS Exchange 1 ResponseTLS Exchange 2 RequestTLS Exchange 2 ResponseLogin stateTLS/SSL Transport LayerTLS/SSL Transport LayerInitial Pre-Login Response with ENCRYPTION ON

Typical Message Sequence (Login State)Created connection successfully (Pre-login state)Login7 Request with SPNEGO Exchange 1SPNEGO Exchange 1 ResponseSPNEGO Exchange 2 RequestLogin7 Response (LOGINACK)Post-Login Success State

Typical Message Sequence (Post-Login Success)Post-Login Success State(Server is now ready to accept command from client)SQL Command with Insert BulkTable ResponseSQL Command with Binary Data (Bulk Load Data)Table ResponsePost-Login Success State(Server is now ready to accept command from client)SQL Command with Insert BulkTable ResponseSQL Command with Binary Data (Bulk Load Data)Attention RequestTable Response with attention acknowledgement

What’s new in MS-TDS

MS-TDS latest updates inOct, 2019This document has been updated as follows: Included support for Always Encrypted with secureenclaves. Added information about the new dataclassification feature. Defined support for the client and server to be ableto send and receive UTF-8 encoded data. Specified the client-certificate authentication featurethat provides an authentication alternative whenSQL Server is running on a Linux operating system.

Always Encrypted with secure enclavesAlways Encrypted with secure enclavesIn-place encryption: enablescryptographic operations on sensitivedata without moving the data outsidethe databaseplaintextciphertextEnhancedclient driverRich computations: pattern matchingand range comparisons are supportedinside the secure enclave, enabling abroad range of applicationsplaintextEnclave

① Always Encrypted with secure enclaves② UTF-8 Support③ Data Classification演示 Demo

UTF-8 encoding supportCode range(hexadecimal)Code range (decimal)UTF-8 encoding formatStorage bytes1 with UTF8Storage bytes1 ��1,0231,024–2,047110xxxxx ,38316,384–65,5351110xxxx 10xxxxxx 10xxxxxx3211110xxx 10xxxxxx 2040000–10FFFF2262,144–1,114,1112 With UTF-8 encoding, characters in the ASCII range (000000–00007F) require 1 byte, code points 000080–0007FF require 2bytes, code points 000800–00FFFF require 3 bytes, and code points 0010000–0010FFFF require 4 bytes. With UTF-16 encoding, code points 000000–00FFFF require 2 bytes, and code points 0010000–0010FFFF require 4 bytes.

How to convert to UTF-8?1) Convert column data. Imagine you have a current table that is encoded in UCS-2/UTF-16 (depends on whether asupplementary character enabled collation is used) or non-Unicode:To convert it in-place, run an alter statement, like the following example:2) Copy and Replace. This method involves copying data to a new table where the target column(s) are already in UTF-8, and then replace the oldtable.

① Always Encrypted with secure enclaves② UTF-8 Support③ Data Classification演示 Demo

Data ClassificationClassification tool allows you to tagcolumns stored in a SQL Serverdatabase with pre-defined set ofGDPR related labels.Data Classification and auditingbuilt-in to the engine.

① Always Encrypted with secure enclaves② UTF-8 Support③ Data Classification演示 Demo

SSAS Protocols Overview

Analysis ServiceAnalysis Services is an analytical dataengine (Vertipaq) used in decision supportand business analytics.It provides enterprise-grade semantic data models for business reports and client applicationssuch as Power BI, Excel, Reporting Services reports, and other data visualization tools. AnalysisServices is available in different platforms.SQL Server AnalysisServicesAzure Analysis ServicesPower BI Premium (Preview)

SQL Server Analysis Services(SSAS)APPs & ServicesSQL Server BI PlatformSQL ServerAnalysisServicesSQL ServerIntegrationServicesExcelSharePointSQL ServerDBMSSQL ServerReportingServicesPower BIDataSQL ServerOracleMySQLJSON FileExcel File

What's New in SSAS 2019 Tabular model compatibility level 1500 Calculation groups Queryinterleaving Many-to-manyrelationships Calculation groups in tabular models Governance setting for Power BI cache refreshes Online attach Many-to-many relationships in tabular models Memory settings for resource governance

Calculation GroupsSale Measure 01Sale Measure 01 MTDSale Measure 01 QTDSale Measure 01 YTDSale Measure 02 MTDSales TableSale Measure 02Order TableSale Measure 02QTDSale Measure 02 YTDSale Measure 03 MTDSale Measure 03Sale Measure 03QTDSale Measure 03YTD

Calculation Groups sampleMTDQTDYTDCalculation ItemsCalculation GroupSales TableTableOrder Table

Calculation Groups sample

SSAS Protocols OverviewSQL Server Tools ( such as SSMS, SSDT-BI)Analysis ServerMS-SSASMS-SSAS-TExternal Application

SSAS Protocols OverviewMS-SSASCube1/3MS-SSAS-T1/10Tabular

Typical Message SequenceCreate ConnectionClientAuthenticateNew Session RequestRequest (TCP)AuthenticateResponse(TCP)New SessionResponseDiscover / Execute RequestDiscover / Execute ResponseClose Connection End of Session RequestEnd of Session ResponseServer

OperationsAuthenticate RequestAuthenticate ResponseDiscover RequestClientDiscover ResponseExecute RequestExecute ResponseServer

Structure of SSAS messageScripting LanguageXMLASOAP

Scripting Language tidimensionalTabular110xTabular1200Tabular1400SQL Server2019ASSLASSLTMSLTMSLSQL Server2017ASSLASSLTMSLTMSLSQL Server2016ASSLASSLTMSLTMSLSQL Server2014ASSLASSLNANASQL e

TMSL in XMLATMSL

ASSL in XMLAASSL

What’s new in SSAS Protocols

MS-SSAS latest updates in Oct, 2019 New element added for Cube Complex Type DaxOptimizationMode: To enable DAX optimization. New column added for DBSCHEMA CATALOGS CLIENTCACHEREFRESHPOLICY: To add information aboutoptions for refreshing client application caches. New Request Types added for Discover : DISCOVER MEM STATS DISCOVER DB MEM STATS New Unsubscribe command added: To support termination of an ongoing subscription to Traceevents. Updated the info in Restore/Attach/ImageLoad command To clarify the use of read-write mode for a database.For details, please refer to [MS-SSAS] V20191016

MS-SSAS-T latest updates in Oct, 2019 New Calculation Group Content New CalculationGroup Object New CalculationItem Object New QueryGroup Object added To introduce a query feature that represents a logical groupof Partition and Expression objects.For details, please refer to [MS-SSAS-T] V20191016 CalculationGroup Object CalculationItem Object QueryGroup Object

MS-SSAS-T latest updates in Oct, 2019 Updated property for linguistic metadata To specify linguistic metadata as either XML or JSONcontent. Updated property for Model Object To prevent a measure from having the same name as acolumn in the model. To disable creation of implicit measures so that calculationgroups can be created. Updated property for Measure Object To introduce a query feature that represents a logical groupof Partition and Expression objects Updated property for Table Object To exclude a table from a refresh operation on a model.For details, please refer to [MS-SSAS-T] V20191016

SSAS Objects - erarchyCultureRoleExpression

SSAS Objects - upDimensionAssemblyMining StructureMining ture nCellPermissionRoleDatabasePermissionAggregation

Test Suite Intro and Demo

Test Suite Production WorkflowOpen SpecificationRequirementSpecificationTest Suite(Simulates protocol client)

Test Suite ArchitectureClientTest CaseServerSUTProtocolAdapterSUT ControlAdapterSQL ServerTransport Layer

Test Suite ArchitectureClientTest CaseServerSUTProtocolAdapterSUT ControlAdapterSQL ServerTransport Layer

New QueryGroup Object in MS-SSAS-T演示 Demo

Resources

Protocols: Open Specifications: -openspeclp/3589baea-5b22-48f2-9d43-f5bea4960ddb SQL Server Technical Documents: https://docs.microsoft.com/enus/openspecs/sql server a0816464e MS-TDS: https://docs.microsoft.com/en-us/openspecs/windows ec MS-SSAS: https://docs.microsoft.com/en-us/openspecs/sql server 0c9 MS-SSAS-T: https://docs.microsoft.com/en-us/openspecs/sql server aecd8

SQL Server 2019: Microsoft SQL ql/?view sql-server-ver15 What's New in SQL Server 2019 (15.x): at-s-newin-sql-server-ver15?view sql-server-ver15Analysis Services: Analysis Services documentation: / What's New in SQL Server Analysis Services: /what-s-new-in-sql-server-analysis-services Analysis Services Team Blog: analysis-services/

SQL Server 2012 ASSL NA Analysis Services Scripting Language Tabular Model Scripting Language. TMSL in XMLA TMSL. ASSL in XMLA ASSL. What's new in SSAS Protocols. MS-SSAS latest updates in Oct, 2019 . s