Transcript Model complex data easily with SQL Server 2016 Analysis Services

Review the most important Analysis Services
Tabular enhancements in SQL Server 2016.
Showcase the new scripting and
programmability options to automate tedious
repetitive modelling tasks and accelerate
solution delivery.
CLIENT TOOLS
BI SEMANTIC MODEL
Queries
Data model
Business logic
Data access
DATA SOURCES
Use SQL Server
data tools for
Visual Studio to
create BI semantic
models
Non empty calculation
CLIENT TOOLS
Fewer scans to improve query execution.
Fusion
Multiple measures from the same table
combined into a single
storage engine query.
Grouping sets
For measures
BI SEMANTIC
MODEL at multiple granularities,
a single query is sent at the lowest level
Queries
and the rest derived from the lowest level.
Redundant join elimination
Data
modelquery to the storage engine
A single
returns both the dimension columns
Business logic
and the measure values.
Strict evaluation
of IF/SWITCH
Data access
A branch whose condition is false will
no longer result in storage engine queries.
Evaluate an expression once and
DATA SOURCES
use the results many times.
Use SQL Server
data tools for
Visual Studio to
create BI semantic
models
CLIENT TOOLS
High-Performance Data Import
Enhanced performance through
parallel processing of partitions.
Database Consistency Checker
Check for corruption issues on
database
or individual objects.
BI SEMANTIC
MODEL
Full xEvents Support
Queries
Diagnose problems with xEvents,
a light-weight tracing and performance
Data model
monitoring system in SSMS.
Business logic
Tabular 1200 models rely on an entirely
refactored
Data
access tabular metadata stack.
DATA SOURCES
Use SQL Server
data tools for
Visual Studio to
create BI semantic
models
CLIENT TOOLS
High-Performance DirectQuery
Eliminate redundant source queries, use
better joins, and push calculations
to the source as much as possible.
Allow Row-level securities to be defined.
BI SEMANTIC MODEL
Calculated Columns
Support tables with
Queries
intra-row calculated columns.
model
More DataData
Sources
Support Oracle, Teradata, and APS
Business
logic to SQL Server RDBMS.
in addition
Broad Availability across BI Tools
Data access
Support common analytical tools
with MDX queries, namely Excel
(and others).
DATA SOURCES
Use SQL Server
data tools for
Visual Studio to
create BI semantic
models
Simplified Setup Experience
CLIENT TOOLS
SQL Server and BI Tools consolidated in
SSDT for Visual Studio 2015.
Improved DAX Formula Editing
Syntax coloring, multiline and indentation,
and support for comments in measures.
On a tabular
BI SEMANTIC
MODEL1200 model, SSDT will
automatically update any measures
Queries
referencing a renamed column or table.
Data
model
Create
multiple project configurations
using Visual Studio configuration manager
to deploy to different targets.
SimplifiedData
Team
Development
access
For tabular 1200 models, Model.bim file
is based on JSON-based TMSL making
it easier to identify and consolidate
data model changes.
DATA SOURCES
Save Incomplete Measures
Save incomplete DAX expressions and
continue at your convencience.
Business logic
Use SQL Server
data tools for
Visual Studio to
create BI semantic
models
CLIENT TOOLS
Metadata changes only impact
affected objects.
Access to 50+ new DAX functions
and support for variables.
Bi-Directional Crossfiltering
Powerful bi-directional relationships
BI SEMANTIC MODEL
support BI patterns like M:M.
CalculatedQueries
and Pasted Tables
Use DAX to create tables dynamically.
Data model
Display Folders
Display folders for columns and measures.
Business logic
Localization
of Metadata
Ability to view semantic models in
Data access
client language.
Simplified Scripting and Programming
Support for new scripting language (TMSL).
Support for new object model (TOM).
DATA SOURCES
Use SQL Server
data tools for
Visual Studio to
create BI semantic
models
 Tabular Model Scripting Language (TMSL)
Tabular scripting language
{
"name": "Demo Model",
"description": "Demo model description",
"culture": "en-US",
"dataSources": [
....
],
"tables": [
{
"name": "Customer",
"description": "Customer information.",
"columns": [
{
"name": "ID",
"dataType": "int64",
"sourceColumn": "customer_id",
"sortByColumn": "Last Name"
},
•
Tabular Object Model (TOM)
Database
public void RefreshTable()
{
var server = new Server();
server.Connect(ServerConnectionString);
//Connect to the server.
Database Db = server.Databases["TMDB"];
//Connect to the DB
Model m = Db.Model; //Get the model
//Mark the Sales table to be refreshed.
m.Tables["Sales"].RequestRefresh(RefreshType.Full);
m.SaveChanges(); //Commit the changes
}
Model
Table(s)
Column(s)
Measure(s)
 Pasted and other calculated tables and measures
 Problem Solved!
http://download.microsoft.com/download/2/7/8/2782DF953E0D-40CD-BFC8-749A2882E109/Bidirectional%20crossfiltering%20in%20Analysis%20Services%202016%20and%20Po
wer%20BI.docx
 Export – Translate - Import
"cultures": [
{
"name": "de-DE",
"translations": {
"model": {
"name": "Model",
"translatedCaption": "Datenmodell",
"translatedDescription": "",
"tables": [
{
"name": "Customer",
"translatedCaption": "Kunde",
"translatedDescription": "",
"columns": [
{
"name": "FirstName",
"translatedCaption": "Vorname",
"translatedDescription": ""
},
https://github.com/Kjonge/TabularTranslator
 Export from 1103 – Transform – Apply to 1200 using Windows PowerShell
http://www.kapacity.dk/migrating-ssas-tabular-models-to-sqlserver-2016-translations-and-display-folders/
 Go beyond the SSDT developer experience
From the author (Daniel
Otykier): “Thanks to the new
Tabular Object Model in
Compatibility Level 1200, it
turns out to be very easy to
modify the metadata of a
Tabular model. Through the
Tabular API, these changes can
be done either in a Model.bim
file, or directly to a Tabular
database that has been
deployed to a server.”
http://www.kapacity.dk/a-new-way-to-work-with-sql-server-tabularmodels-the-tabular-editor/?lang=en
www.microsoft.com/itprocareercenter
www.microsoft.com/itprocloudessentials
www.microsoft.com/mechanics
https://techcommunity.microsoft.com
http://myignite.microsoft.com
https://aka.ms/ignite.mobileapp