Build your first Analysis Services
(Tabular Model) <not a Cube>!
(using the tutorials on docs.microsoft.com)
[email protected]
Why Analysis Services?
Problem statements:
“We don’t have per-user security on database x, y, or z”
“We want to secure our data”
“We want the business users to see THEIR data”
“ETL is hard/slow”
“We need real time and historical data”
Analysis Services Overview
Analysis Services (SSAS) is an online analytical data engine used
in decision support and business analytics
It provides analytical data for business reports and client
applications such as Reporting Services reports, Power BI, Excel,
and other third-party data visualization tools
SSAS supports two modelling modes—multidimensional and
tabular
Analysis Services Overview
One Semantic Model - Two Ways to Develop
Tabular models Multidimensional models
Tables and relationships Dimensions and measure groups
Fast by design with in-memory Highly scalable and mature
Easy to get started and simple Feature rich and complex
Analysis Services Architectural Overview
CLIENT TOOLS
BI SEMANTIC MODEL
Queries Use SQL Server
Data Tools for
Data model Visual Studio to
Business logic create BI
semantic models
Data access
DATA SOURCES
Modeling types
Type Modeling description Released
Tabular Relational modeling constructs SQL Server 2012 and later
(model, tables, columns). Internally, metadata is
inherited from OLAP modeling constructs (cubes, (compatibility levels 1050 -
1
dimensions, measures). Code and script use OLAP 1103)
metadata.
Tabular in SQL Server 2016 Relational modeling constructs SQL Server 2016 (compatibility
(model, tables, columns), articulated in tabular
metadata object definitions in Tabular Model Scripting level 1200)
Language (TMSL) and Tabular Object Model
(TOM) code.
Tabular in SQL Server 2017 Relational modeling constructs SQL Server 2017 (compatibility
(model, tables, columns), articulated in tabular
metadata object definitions in Tabular Model Scripting level 1400)
Language (TMSL) and Tabular Object Model
(TOM) code.
Multidimensional OLAP modeling constructs SQL Server 2000 and later
(cubes, dimensions, measures).
Power Pivot Originally an add-in, but now via Excel and Power Pivot or
fully integrated into Excel. Power BI Desktop
Visual modeling only, over an internal Tabular
infrastructure.
Tabular models in SQL Server 2016 - Overview
DimOrganization
New rich modeling capabilities
OrganizationKey
ParnentOrganization
PercentageOfOwn…
OrganizationName
CurrencyKey
DimDepartmentGr…
DimDepartmentGroupKey…
ParentDepartmentGr…
DepartmentGroupNa… Improved DirectQuery
FactFinance
FinanceKey
DateKey
OrganizationKey
DepartmentGroup…
ScenarioKey
DimAccount
Performance enhancements
AccountKey
ParenAccountKey
AccountCodeAlter…
ParenAccountCod…
AccountDescription
DimAccount
ScenarioKey
ScenarioName
Development
Scale and manage
How do we get started with SSAS?
Take a look at the
Analysis Services Tabular adventure works tutorial:
http://tinyurl.com/SSASTAB
(https://docs.microsoft.com/en-us/sql/analysis-services/tabular-
modeling-adventure-works-tutorial)
“This tutorial provides lessons on how to create an Analysis Services tabular
model at the 1200 compatibility level by using SQL Server Data Tools (SSDT),
and deploy your model to an Analysis Services server on-premises or in Azure.”
Nomenclature in the Tabular Analysis Services world
Tables and Columns – Data imported or queried from data sources
Hierarchy - metadata that define relationships between two or more columns in a table.
Calculations – using DAX, aggregate, filter, extend, combine, and secure that data.
Measure - a calculation created using a DAX formula for use in a reporting client. Measures are
evaluated based on fields, filters, and slicers users select in the reporting client application.
KPI - used to gauge performance of a value, defined by a Base measure, against a Target value, also
defined by a measure or by an absolute value.
Perspective - Perspectives, in tabular models, define viewable subsets of a model that provide focused,
business-specific, or application-specific viewpoints of the model.
Partition - Partitions divide a table into logical parts. Each partition can then be processed (Refreshed)
independent of other partitions.
So, what are we learning?
a. How to create a new tabular model project in SSDT (SQL Server Developer Tools)
b. How to import data from a SQL Server relational database into an Analysis
Services tabular model project.
c. How to create and manage relationships between tables in the model.
d. How to create and manage calculations, measures, and Key Performance
Indicators that help users analyze model data.
e. (Homework) Create and manage perspectives and hierarchies that help users more
easily browse model data by providing business and application specific viewpoints.
f. (Homework) Create partitions that divide table data into smaller logical parts that
can be processed independent from other partitions.
g. (Homework) How to secure model objects and data using roles with user members.
h. How to deploy a tabular model to an Analysis Services server on-premises or in
Azure.
You will need the following prerequisites:
The latest version of SQL Server Data Tools (SSDT). Get the latest version.
or… VS2017 with the Analysis Services add-in also works
The latest version of SQL Server Management Studio. Get the latest version.
A client application such as Power BI Desktop or Microsoft Excel.
A SQL Server instance with the Adventure Works DW 2014 sample database. This sample
database includes the data necessary to complete this tutorial. Get the latest version.
An Azure Analysis Services or SQL Server 2016 or later Analysis Services instance to
deploy your model to. Sign up for a free Azure Analysis Services trial.
DEMO
Demo of Analysis Services Tabular Model creation and deployment
Create
Pull in data
Report on data
Resources
Tutorial on Analysis Services
http://tinyurl.com/SSASTAB
MSDN: SQL Server Analysis Services
https://msdn.microsoft.com/en-us/library/bb522607.aspx
Analysis Services and PowerPivot Team Blog
https://blogs.msdn.microsoft.com/analysisservices/
YouTube video of Azure Analysis Services
https://www.youtube.com/watch?v=44I48ufKhOs
EDx course on Analysis Services: 8 hours of content and hands-on labs
https://www.edx.org/course/sql-server-analysis-services-developing-microsoft-dat225x-0#!
Appendix A:
Enterprise-grade
Analysis Services
Analysis Services themes for SQL Server 2016
Improved productivity and performance
Increased
productivity
Scale and manage
Analysis Services Overview
Analysis Services (SSAS) is an online analytical data engine used in decision support and
business analytics
It provides analytical data for business reports and client applications such as Reporting
Services reports, Power BI, Excel, and other third-party data visualization tools
SSAS supports two modelling modes—multidimensional and tabular
The multidimensional mode also includes a data mining engine
Analysis Services Overview
One Semantic Model - Two Ways to Develop
Tabular models Multidimensional models
Tables and relationships Dimensions and measure groups
Fast by design with in-memory Highly scalable and mature
Easy to get started and simple Feature rich and complex
Analysis Services Architectural Overview
CLIENT TOOLS
BI SEMANTIC MODEL
Queries Use SQL Server
Data Tools for
Data model
Visual Studio to
Business logic create BI semantic
models
Data access
DATA SOURCES
Multidimensional in SQL Server 2016 - Overview
Improvements in manageability, functionality,
and performance
Scale and manage
Tabular models in SQL Server 2016 - Overview
DimOrganization
New rich modeling capabilities
OrganizationKey
ParnentOrganization
PercentageOfOwn…
OrganizationName
CurrencyKey
DimDepartmentGr…
DimDepartmentGroupKey…
ParentDepartmentGr…
DepartmentGroupNa… Improved DirectQuery
FactFinance
FinanceKey
DateKey
OrganizationKey
DepartmentGroup…
ScenarioKey
DimAccount
Performance enhancements
AccountKey
ParenAccountKey
AccountCodeAlter…
ParenAccountCod…
AccountDescription
DimAccount
ScenarioKey
ScenarioName
Development
Scale and manage
New SSAS Features (Tabular and Multidimensional)
Analysis Services Management Objects (AMO) updates
AMO has been refactored to include a second assembly Microsoft.AnalysisServices.Core
This assembly includes common classes across SSAS modes: e.g. server, database, role
No breaking changes, but new application references should use Microsoft.AnalysisServices.Core
DBCC for Analysis Services runs internally to detect potential data corruption issues on
database load
Can be run on demand if an administrator suspects problems with the data or model
DBCC runs different checks depending on whether the model is tabular or multidimensional
New SSAS Features (Tabular and Multidimensional)
Extended Events (xEvents) can now be managed and monitored within SSMS
Live data streams can be monitored in real time, keeping session data loaded in memory for faster
analysis
Data streams can also be saved to a file for offline analysis
In SSMS, it is now possible to add computer accounts as database Administrators
Custom authentication in SQL Server 2016
New security feature for tabular and MOLAP
EUN=Effectiveusername
Admin
ActAs=AnyString
Admin SQL Server
Analysis Services
CustomData
Scale and manage
New SSAS Features - Tabular
While most new tabular features are available only with SSAS 2016 models (compatibility
level 1200), all tabular models can benefit from:
Parallel processing of multiple table partitions
Support for Visual Studio Configuration manager
Improved DAX formula editing (multi-line, tabs, comments)
Formula fixup (renaming objects will automatically update formula references)
Saving incomplete measures (all measures must be complete to deploy the project)
New behaviors with DAX variables
New DAX functions (~59)
New SSAS Features – More than 50 new DAX Functions
Examples:
Date and Time
CALENDAR
CALENDARAUTO
DATEDIFF
Math and Trig
SIN, COS, TAN, etc.
EVEN, ODD, EXP, ISO.CEILING, MROUND
Statistical
MEDIAN, MEDIANX, PERCENTILE.EXC, PERCENITLEX.EXC
New SSAS Features – Model Development
New 1200 model development features:
Calculated tables
Bi-directional cross filters
Display folders, to organize model elements into logical groups
Translations, to view models in a preferred language
DirectQuery enhancements
Improved SSDT modeling performance, thanks to TMSL (introduced later)
New SSAS Features – Calculated Tables
Define calculated tables to add new tables to the model, based on existing data from
other tables
Defined by using DAX
Calculated tables are generally best for intermediate calculations of data stored in the model, rather
than calculated on the fly
Calculated tables appear in Relationship view, and relationships can be defined with other tables
Calculated tables are recalculated in the same circumstances as calculated columns
New SSAS Features – Bi-Directional Cross Filters
Bi-directional cross filtering allows developers to define how filter context propagates
between related tables
Bi-directional filters on both sides of a bridging table will solve the “many-to-many” problem—without
the need to write DAX formulas
New SSAS Features – DirectQuery in Tabular
DirectQuery enhancements:
Direct access without moving data
New data sources:
Microsoft Analytics Platform System (APS)
Oracle
DAX Query
Teradata
Support for common analytical tools with MDX
queries MDX Query
Improved query generation, resulting in faster
performance
New SSAS Features – DirectQuery in Tabular
DirectQuery enhancements [Continued]:
Row-level security, defined by model roles with DAX filters
Calculated columns (but not calculated tables), but note:
In certain known cases, the same formula can return different results from a cached model compared
to a DirectQuery model
These differences are a consequence of the semantic differences between the in-memory analytics
engine and the underlying data source, queried with SQL
Sample partitions, enabling in-memory data to be cached to facilitate model development
New SSAS Features – DirectQuery in Tabular
DirectQuery benefits:
Data is guaranteed to be up-to-date, with no extra management overhead of having to maintain a
separate copy of the data
Data sets can be larger than the memory capacity of the SSAS server
DirectQuery can take advantage of provider-side query acceleration, such as that provided by xVelocity
memory optimized column indexes
Security can be enforced by the back-end database, by using row-level security features from the back-
end database—or alternatively, by using row-level security in the model
SSAS performs optimizations to ensure that the query plan for the query executed against the back-
end database will be as efficient as possible
New SSAS Features – Tabular Object Model (TOM)
The Tabular Object Model (TOM) is part of
AMO
Database
With TOM, developers can now use familiar tabular
concepts, rather than multidimensional concepts
Model
This promotes simpler, and more readable, code when
developing against tabular models
Table(s)
Column(s)
Measure(s)
New SSAS Features – Tabular Object Model (TOM)
Tabular Object Model Example:
public void ReprocessTable(string cnnString, string dbName, string tableName)
{
var server = new Server();
server.Connect(cnnString);
// Connect to the server
Database db = server.Databases[dbName];
// Connect to the database
Model = db.Model;
// Reprocess the table
model.Tables[tableName].RequestRefresh(RefreshType.Full);
model.SaveChanges(); // Commit the changes
}
New SSAS Features – Tabular Model Scripting Language
(TMSL)
The Tabular Model Scripting Language (TMSL) allows for simplified scripting and
development for tabular models
This new language describes and manages objects with JSON
SSMS database commands can produce TMSL scripts, including:
Create, Alter, Delete, Backup, Restore, Attach, and Detach
Changes to the model now only affect a single object instead of having to map everything to
multidimensional objects—this makes metadata operations very fast
As metadata changes are now localized in the script, it allows for simple code merges
New SSAS Features – Tabular Model Scripting Language
The script to represent the schema of the model was developed together with the Power BI team, with
the goal to have feature parity with the Power BI APIs to allow reusability between products
SSAS PowerShell cmdlet Invoke-ASCmd accepts tabular model scripting language commands
{ {
"refresh": { "backup": {
"type": "full", "database": "Sales Analysis",
"objects": [ "file": "SalesAnalysis.abf",
{ "password": "Pass@word1",
"database": "Sales Analysis", "allowOverwrite": false,
"table": "Reseller Sales" "applyCompression": true
} }
] }
}
}
New SSAS Features – Multidimensional Improvements
Distinct count ROLAP optimizations for DB2 and Oracle data sources
Drill-through multi-selection support with Excel 2016
Excel query optimizations
Excel PivotTables are now optimized when totals and subtotals are turned off
In this case, better optimized MDX queries are generating, delivering performance benefits for both
MOLAP and tabular models
Deprecated SSAS Features
Not supported in the next major release of SQL Server:
Remote partitions
Remote linked measure groups
Dimensional writeback
Linked dimensions
Not supported in future releases of SQL Server:
SQL Server table notifications for proactive caching (use polling instead)
Session and local cubes (no replacement)
Tabular model 1100 and 1103 compatibility levels (use 1200)
SQL Server Profiler for trace capture (use xEvents) and replay (no replacement)
Discontinued SSAS Features
CalculationPassValue() MDX function (deprecated SSAS 2005)
CalculationCurrentPass() MDX function (deprecated SSAS 2005)
NON_EMPTY_BEHAVIOR query optimizer hint (deprecated SSAS 2008)
COM assemblies (deprecated SSAS 2008)
CELL_EVALUATION_LIST intrinsic cell property (deprecated SSAS 2005)
Upgrading to SSAS 2016
Upgrading to SSAS 2016 can be achieved by:
Server instance upgrade
Database upgrade
After upgrading, it is recommended to run the new DBCC for Analysis Services
Upgrading to SSAS 2016 – Server Instance Upgrade
A SSAS instance upgrade can achieved by:
In-place upgrade
Side-by-side upgrade
Supported for SQL Server 2008, or later
An instance upgrade cannot change the server mode
The compatibility levels of databases that are attached to a given instance remain the
same, unless manually changed
Metadata and binary data is compatible between the two versions, and so there is no
need to re-process databases
Upgrading to SSAS 2016 – Database Upgrade
Prior SSAS database versions can be hosted on SSAS 2016
The following compatibility levels can been deployed to SSAS 2016:
Mode Level Version
Multidimensional 1050 SQL Server 2005, 2008, 2008 R2
Multidimensional 1100 SQL Server 2012, or later
Tabular 1100 SQL Server 2012
Tabular 1103 SQL Server 2014
Tabular 1200 SQL Server 2016
Upgrading to SSAS 2016 – Database Upgrade
To upgrade a non-DirectQuery database, upgrade the SSDT project, and then deploy to
the upgraded server
DirectQuery databases cannot be upgraded in-place
To upgrade DirectQuery databases:
In SSDT, turn off DirectQuery mode
Set the compatibility level to 1200
Turn DirectQuery mode back on
Deploy to the upgraded server
Upgrading to SSAS 2016 – Database Upgrade
Tabular databases benefit the most from an upgrade to SSAS 2016:
Take advantage of new developer features
The revised DirectQuery mode at compatibility 1200 delivers better performance, calculated columns,
and row-level security enforced by SSAS roles
Tabular model metadata uses native terminology for object definitions, and TMSL is used to script
database operations
Supported SSAS Features by Edition
SSAS 2016 is supported by Enterprise, Standard and Developer editions
Maximum compute capacity per instance:
Enterprise and Developer: Operating system maximum
Standard: Limited to lesser of 4 sockets or 24 cores
Maximum memory per instance:
Enterprise and Developer: Operating system maximum
Standard: Tabular: 16GB, and Multidimensional: 64GB
Standard edition limits AlwaysOn failover clusters to two nodes
Supported SSAS Features by Edition
Features supported only by Enterprise edition:
Scalable shared databases
Synchronize databases
Power Pivot for SharePoint
Only standard data mining algorithms and tools are supported in Standard edition
BI Semantic Model features are limited by edition
Supported SSAS Features by Edition
Standard edition does not support:
Perspectives
Multiple partitions
DirectQuery storage modes
Supported SSAS Features by Edition
Standard edition does not support:
Semi-additive measures (except LastChild)
Perspectives
Writeback dimensions *
Linked measures and dimensions *
Unlimited partitions (supports up to three)
Proactive caching
Push-mode processing
Direct writeback
Measure expressions
* Deprecated features
Summary
Seventh release of Analysis Services
Numerous new features and enhancements deliver improved development and
management capabilities
Upgrade to SSAS 2016 is easily accomplished
Upgrading databases to SSAS 2016 is also easily accomplished by setting project compatibility levels,
and redeploying
DirectQuery enhancements deliver many benefits for real time access to SQL Server,
APS, Oracle and Teradata data sources
Resources
MSDN: SQL Server Analysis Services
https://msdn.microsoft.com/en-us/library/bb522607.aspx
MSDN: What’s New in Analysis Services
Describes a complete list of new DAX functions
https://msdn.microsoft.com/en-us/library/bb522628.aspx
MSDN: Features Supported by the Editions of SQL Server 2016
https://msdn.microsoft.com/en-us/library/cc645993.aspx?#SSAS
Analysis Services and PowerPivot Team Blog
https://blogs.msdn.microsoft.com/analysisservices/
Youtube video of Azure Analysis Services
https://www.youtube.com/watch?v=44I48ufKhOs
EDx course on Analysis Services: 8 hours of content and hands-on labs
https://www.edx.org/course/sql-server-analysis-services-developing-microsoft-dat225x-0#!
