SSAS 2016 Multidimensional and Tabular Model Training Sylabus

SSAS 2016 Multidimensional and Tabular Model

Total Duration: 4 days

Prerequisites: Strong querying skills with SQL Server or any other databases is desired. Maintaining Databases.

SSAS 2016 Multidimensional MODEL

Business Intelligence Overview

Introduction to Business Intelligence Data Warehousing Concepts Purpose of Data Warehousing Warehouse Design and Intent Structure of Dimensional Database Star Schema

Snowflake Schema Fact Tables Dimension Tables
Building Cubes

Exploring Business Intelligence Development Studio Components of Analysis Services Project

Creating a Cube

Using Cube Creation Wizard without Data Source Under Standing UDM

Data Sources and Data Source Views

Working with Cube Structure in Cube Designer Processing and Browsing a Cube

Deploying Cubes

Creating Dimension Designs

Data warehouse Structure Building Standard Dimension Creating Data Source Views Modifying Dimension Attributes Deploying Dimensions

Working with Time Dimensions Working With Role Playing Dimensions
Working With Parent – Child Dimensions Summarizing Non Leaf Level Members Creating Custom Hierarchies

Attribute and Attribute Relationships

Understanding the essence of using Attribute relationships Using Attribute Relationships to benefit Performance Setting Attribute Relationship Types

Rigid Flexible
Designing Measure Groups and Measures

Adding Measure Groups to Cube Measure Groups and Measures Properties

Creating Cubes based on finance and accounting domain Account Dimension
Measure Behaviors Additive
Non – Additive Semi – Additive Simple Calculations
Conditional Formatting Adding Calculations to Cube
Cube Storage Architecture

Understanding Partitions Creating Partitions

Usage of Partitions and Internals Partition Designing Considerations
Understanding and Creating Aggregation Designs Inspecting Aggregations and Internals
Optimizing Aggregations Maintaining and Using Query Logs Usage Based Optimization
Dimension Relationships

Dimension Relationships Regular and Referenced

Implementing complex analytics using Relationships Fact Relationship
Using Many – To – Many Relationships

MDX

Tuple Based Calculations

Creating and using MDX Expressions in Calculations Querying with MDX

Working with Basic MDX Queries Designing Custom Members Filtering and Sorting Data Attributes
Hierarchies Named Sets
Calculated Members Calculation Sequence
Key Performance Indicators MDX based KPI’s PARALLELPERIOD
Extending Dimensions
Using Perspectives

Adding and Browsing Translations

Managing SSAS
Configuring SSAS

Backing Up an SSAS Database Restoring an SSAS Database Processing a Cube

Configuring Roles, Groups, and Permissions
User Authentication

Granting Administrative Access Granting User Access

Granting Permissions to Roles

SSAS 2016 Tabular Model

Introduction To Microsoft SQL Server Analysis Services

Overview of SQL Server Analysis Services Installing Analysis Services

What is Data Warehouse? What is MSBI?

Difference between OLTP and OLAP

OLAP Modelling

Understand Basic OLAP Modeling (star schema) Understand Dimensional Modeling (stars and snowflakes) Understand Measure (fact) and Cube Modeling

Implementing a Tabular Model Database Providing users with analytics via xVelocity Why Tabular

Comparing Multidimensional with Tabular Comparing DAX with MDX

Service Status Tabular Instance

Introduction to SSAS Tabular Introduction to SSAS Tabular Creating a Project Relationship and measures Measures and their usage Advantages of Tabular model Limitations

Tabular Model Basics

Tabular – Development Server Workspace Server configuration SSAS Configuration management Project, Model – Properties

Migrating Power Pivot Model to tabular

Power Pivot – Overview

Usage and Limitations of Power Pivot Steps to Migrate for Tabular Model Points to consider

Tabular Model – Design

Creating new model

Importing data from different sources Maintain relationship across data sources measures and their usage

calculated columns Deployment

Manage Tabular Model using SSMS Managing Server Properties Managing Cube properties Explore – Cube

Connecting To SQL Server

Creating and using connections Filtering rows

Filtering columns

Creating Calculated Columns

Creating a Data Model

Hiding from client tools Renaming tables/columns Marking Date table

Creating Relationships Diagram view Viewing Relationship Creating relationships

Generating Measures Creating measures Using SUM

Using SUMX, Using AVERAGEX

Impact Of Hirarchies Creating a hierarchy Viewing hierarchies Parent-child hierarchies

Working With KPIs

The parts of a KPI

Absolute and relative measures Creating KPI

Viewing KPIs in Excel Viewing KPIs on server

Perspectives

Creating perspectives Using in Excel

Using On server

Processing and Partitions  How processing works Types of processing Advantage of Partitions Creating partitions Processing single Partition

Processing Multiple Partition Monitoring Partition Processing time

DAX Editors

DAX Editor DAX Studio

Pros and cons of each

DAX Queries

The CALCULATETABLE function Query Output the Basics Evaluate

Filters  Grouping Basics SUMMARIZE

Working with Inline Measures Adding columns

Order By

Security

How security works Creating roles Filtering rows by role

Filtering using DAX Operator Filtering Using DAX Function

SQL Server 2016 Tabular Model Overview

Improved modeling performance for tabular 1200 models Project templates added for tabular 1200 models in SSDT Display folders

Translations in Tabular Model

Parallel processing for multiple table partitions in tabular models

Analysis Services Execute DDL Task Accept TMSL( tabular model scripting Language)