SSAS Online Training with MDX & DAX - LIVE, Instructor Led

SSAS Online Training at SQL School is carefully designed to involve Datawarehouse Implementation, OLAP Data Modelling, Cube Development, Dimension Modelling, MDX Programing, DAX Expressions, MDX & DAX Reports, Data Mining with Forecasts and Tuning and Debugging operations. Classes are completely practical and interactive with one real-time Case Study.

Pre-requisites: Participant needs to have knowledge on SQL Server T-SQL Queries to join our Real-time Practical MSBI Training Course.

Schedules for SSAS Online Training (LIVE, Interactive)

  Timings (IST) Register
Shedule 1 8:00 AM to 9:30 AM Register
Shedule 2 11:30 AM to 1 PM Register
Shedule 3 8 PM to 9:30 PM Register
 

None of the above schedules work for you? Register @ 24 x 7 Video Training Course Prior knowledge of SQL Server T-SQL mandatory for this MSBI Online Training

Trainer : Mr Sai Phanindra T (11+ Yrs Exp)

Dur : 2.5 Weeks (Mon - Fri).

Total Course Fee : INR 7,000/- (USD 120)


HIGHLIGHTS

Advanced MDX, DAX Data Mining, Forecasts
Real-time Project Resume Guidance
SSAS Certifications Placement Services
 

SSAS LIVE Online Training Course Contents:

SSAS Cube Design - MULTIDIMENSIONAL MODE

SSAS Cube Design - TABULAR MODE

DAY 1: INTRODUCTION TO SSAS & CONFIGURATION

  • Installation and Configuration: SSAS 2014 and SSAS 2016
  • Need for SSAS Component & Tools - Operation Modes
  • Multidimensional Mode : Purpose, Properties & Usage
  • Tabular Mode : Purpose, Properties and Usage (ROLAP)
  • PowerPivot Mode : Purpose, Properties and Usage (Overview)
  • Configuring Multidimensional Mode Instances, Verification
  • Configuring Tabular Modes Instances Purpose and Tests
  • Understanding Multidimensional Databases (OLAP Databases)
  • SSAS Service Accounts & Usage. SQL Browser Service
  • SSDT Tool: SQL Server Data Tools - SSDT / Visual Studio Shell
  • SSDT 2015 Installation and Verification of SSAS Templates
  • SSDT 2013 Installation and Verification of SSAS Templates
  • Understanding SSAS Developer Environment (SSDT) Interface
  • SSAS Online Training - Lab Plan, Resources & Databases
  • Introduction to OLAP Database: Measure Groups, Measures
  • Attributes, Hierarchy Levels and Members. Cube, OLAP DB
  • Need for MDX : Multidimensional Expression Language
  • SSAS Workflow in Real-world. Sources to SSAS to Reports
  • Data Source Configurations & DB Installations for Lab

DAY 2: BASIC CUBE DESIGN WITH SSAS, EXCEL PIVOT

  • Identifying Analysis Entities in OLAP Database, Design Plan
  • Need for OLAP Databases and Cubes For Analysis - OLAP
  • Implementing Kimball & Inmon Methods Of BI - SSAS Design
  • A Realworld Example - Sales Scenario for SSAS. Cube Design
  • Sample Analysis Requirements and Entities - Measure Groups
  • Basic SSAS Database Entities & Usage. Sources, Destinations
  • Working with SQL Server Data Tools (SSDT). SSAS Templates
  • Data Source, Data Source View and Cube Designer Wizard
  • Dimension Wizard and Attributes. BUILD, DEPLOYMENT
  • PROCESS of OLAP Cube. Options and Online Deployment
  • Cube Browsing (Basic Level) using SSMS & ADOMD Client
  • Excel Connections for SSAS Databases and OLAP Cube Access
  • Excel PIVOT Tables and Basic Chart Report Design with SSAS
  • Piechart Reports and Attribute Filters. Usage Statistics
  • Using Microsoft Excel for OLAP Cube Access and Filters
  • Common Deployment Errors : Logon Failures @ OLAP Server
  • OLAP Server Impersonation Account - NT ATHORITY Account
  • OLAP Deployment Warnings and Solutions. Precautions
  • Data Source to SSDT : Issues, Solutions and Data Flow
  • Data Source to OLAP Server : Issues, Solutions and Data Flow
  • OLAP Cube Design and Working : Advantages. Access Methods
  • End to End Implementation of SSAS - Basic Level Example

DAY 3: CUBE DESIGN WITH ATTRIBUTE KEYS, HIERARCHIES

  • Cube Design with Attributes, Keys and Relations. Members
  • Data Source View : Entities, Related Objects, Filters
  • Database Dimensions : Identification and Purpose / Usage
  • Cube Dimensions: Identification and Purpose / Usage
  • Connection Strings for SQL Server Data Sources. Options
  • Named Queries and Calculations in Data Source View (DSV)
  • DSV Entities : Data Explore Options. Data Verifications
  • Expressions in DSV Entities. Named Calculations & Reports
  • Cube as a Subject Oriented OLAP Database Object. Browse
  • Cube Access Methods : Cube Browsers - SSMS and SSDT
  • Cube Access Methods: Excel, SSRS, Power BI, Tableau, etc.
  • MDX Filters (Browser Based Options). Pivot Reports @ MS Excel
  • Conditional Formats and Table Formatting Styles @ MS Excel
  • Cell Styles and Monetary Formatting Options. ODC Connections
  • Enabling Caching. Reusing ODC Connections. Filter Expressions
  • Backend Access to Business Data. Frontend Access to Reports
  • Cube Browser - Differences between SSDT and SSMS Drilldowns
  • Hierarchies : Attribute Levels and Purpose. Easy Access
  • Defining Hierarchies for Easy Access. Drill-down Reports
  • Performance Warnings and Missing Attribute Relations and Keys
  • Composite Attribute Keys, Member Name Columns, Hierarchies
  • Deployment Errors - Duplicate Attribute Keys: Solutions

DAY 4: MDX QUERIES & EXPRESSIONS

  • MDX: Multidimensional Expression Language. Syntax Rules
  • MDX Syntax and Axis Models. Cube Data into ROWS, COLUMNS
  • Practical Advantages of MDX: Reports, Cube Writebacks
  • Additional MDX Usage for Cube Design, KPIs, Actions, Security
  • MDX Queries with Dimension Attributes, Members. Axis Levels
  • MDX Queries on Hierarchies, Levels and Attribute Keys
  • MEMBERS, CHILDREN, ALL MEMBERS: Comparison, Aggregates
  • Reporting Multiple Measures / Attributes. Tuple & Set Values
  • MDX Joins using * and CROSSJOIN. Precautionary Measures
  • MDX ORDER, TOPCOUNT / HEAD, BOTTOMCOUNT/ TAIL
  • MDX Queries with WHERE, EXCEPT, RANGE, Operators
  • NONEMPTY, Multiple Member Values. Limitations with WHERE
  • PARENT and CHILDREN with MDX Hierarchies. Tuple Inverse
  • FILTERS in MDX - CURRENT MEMBER, EMPTY MEMBER
  • FILTER Expressions with AND / OR and LEFT / RIGHT Range
  • LEFT/RIGHT and Pattern Matching with MDX. Empty Axis
  • Exact and Closest Match Search Patterns, Operators in MDX
  • Working with NULL Values, Cascades and Cell Details in MDX
  • MDX Query Batches - GO. ADOMD Client: MDX Query Processing
  • Differences between Cube Browser & MDX Expressions
  • MDX Queries for Formatting Options. Member Name Binding
  • Attribute Keys for Member Value Access. Advantages, Usage
  • MDX in SSMS Vs Cube Browser @ SSMS/SSDT. Null, Empty Axis

DAY 5: CALCULATIONS, TIME BASED ENHANCEMENTS

  • MDX Calculations - Need & Advantages. UI Options
  • Calculations with MDX Scripts. Syntax Verification
  • Calculations Usage and Folders - Cube Browser Usage
  • Relating and Reusing Calculations with MDX Expressions
  • Using Calculations in FILTERS and Search Pattern - MDX
  • Conditional Formats, Expressions. Verification @ MS Excel
  • TIME DIMENSIONS - Purpose and Advantages, Properties
  • Time Keys and Time Attributes - Calendar / Fiscal / ISO
  • Adding Hierarchies and Members to Time Dimensions. Usage
  • Dimension Enhancements - Purpose, Advantage, Usage Scope
  • Cube Enhancements - Purpose, Advantage and Usage Scope
  • Time Type Calculations with MDX Expressions, SCOPE, Formats
  • Identifying Attributes & Hierarchies For Time Enhancements
  • Time Calculations for Attributes & Hierarchies - Differences
  • MDX Scripting for Time Calculations. Verification Techniques
  • Attribute Calculation Sets and MDX Scripts. Grouping Options
  • Browsing Time Calculations in MDX Editor, Cube Browser
  • Time Calculations with User Defined Calculations - Options
  • Attribute Level Calculations and Members - Dimension Level
  • Measure Level Calculations and Granularity - Cube Level
  • Calculation Properties : Type, Scope, Format, Visibility
  • Associated Measure Groups & Parent Hierarchy, Non-empty
  • Pie-Charts & User Defined Measures For Time Calculations
  • Slow running Queries with MDX: Need for Tuning Cubes

DAY 6: PARTITIONS, AGGREGATIONS & STORAGE MODES

  • Partitions : Purpose and Architecture, Tuning Operations
  • Cube Partitions : Storage, Slice Options. Query Conditions
  • Query Binding and Table Binding Options in Partition Design
  • Redesign and Default Partitions. Location / Remote Storage
  • Role of Measure Group Partitions in Cubes - Advantages
  • Aggregations - Purpose and Usage. Predefined Calculations
  • Aggregation Types: Full, Default, None and Unrestricted
  • Aggregation Design with Optimization Levels and Storage
  • Measure Properties and Default Aggregation Types in OLAP
  • Aggregations on Measures, Dimensions and Keys. Scope
  • Linking Aggregations and Partitions. Cube Slicing Options
  • Aggregation Reusability Options - Multiple Partitions
  • Additive and Semi-Additive Measures - Aggregation Options
  • Estimated Row Count Options and Advantages - Query Tuning
  • Minimize Impact of Aggregations on OLAP Cube Processing
  • Storage Modes : Multidimensional, Relational and Hybrid
  • Aggregation Storage & Measure Group Storage: MOLAP/ROLAP
  • MOLAP - Automatic Processing, Scheduled, Medium Latency
  • Low Latency Processing and Custom Scheduling Options
  • Proactive Caching Options and Silence / Override Interval
  • Cache Rebuild & Overwrite Options. Processing with Partitions
  • Choosing Correct Storage Modes for FASMI Rules (MOLAP)
  • Perspectives - Purpose and Scenarios For Invalid Cube Access
  • Dimension Usage Tab for Dimension Relations. End User Access
  • Cube Deployments with Partitions, Aggregations, Perspectives

DAY 7: KEY PERFORMANCE INDICATORS & MDX FUNCTIONS

  • Key Performance Indicators (KPI) - Purpose, Design Options
  • Understanding GOAL, VALUE, STATUS, TREND and WEIGHT
  • Data Driven Goals - Identification and MDX Expressions
  • Variance and Value Computations. Format Options, Scope
  • KPI Organizer and Calculation Tools. Metadata Refresh
  • Associated Measure Groups For KPIs. Time Members (Current)
  • KPI Design with MDX Expressions, Time Based Analysis Options
  • Parent - Child KPIs : Purpose and Design Options. Weights
  • Cube Design Options with KPI Calculations and Functions
  • FORMAT_STRING, MDX Expressions and Member Operators
  • KPI Goal and Status @ PARALLELPERIOD, CLOSINGPERIOD
  • MDX Expressions for additional Cube Design, SESSIONSCOPE
  • MEMBER Names and SOLVE_ORDER Expressions in MDX
  • Parent KPIs with Member Hierarchies, KPI Browser Options
  • Re-using KPIs in OLAP. Excel Pivot Reports with KPI - Drilldown
  • KPIs for Drill-Up, Drill-Down in MS Excel. Pivot Tables
  • Variance Factors and Calculations with MDX Expressions
  • Retrieving & Displaying KPIs. Client Apps @ Multi-Level Access
  • Perspectives and Translations with KPIs, MDX Calculations

DAY 8: TABULAR MODE - CUBE DESIGN and MDX QUERIES

  • Tabular Mode - Importance, Advantages and SSDT Templates
  • Workspace Server Architecture, Configuration Settings
  • Entity Identification Procedures and Design Constraints
  • Data Import Wizard and SQL / ODBC Connection Settings
  • ODC (Office Data Connections) and ROW / Column Filters
  • Named Query Options with Tabular Design, Entity Relations
  • Column Filters and Row Filters. Workspace Import Settings
  • Dimension Identification Technques in Tabular Mode
  • Working with Hierarchies, Attributes and Tabular Grids
  • Marking Time Tables and Date/Time Key Setttings
  • Time & Non-Time Hierarchies with Tabular Mode
  • Aggregated Measures and Measure Groups. KPI Generation
  • KPIs with Static and Dynamic Goals. Status, Trend Factors
  • Issues and Solutions with Static and Dynamic Goals
  • Cube Level Meausure Level Goals, Status. Add/Remove Columns
  • Freezing Columns, Pivot / Unpiviot Operatins. Usage.
  • Partitions - Entity Level Partitions, SQL Scripts, Process
  • Partition Reuse. Perspectives in Tabular Mode - Cube Browser
  • Time Based Hierarchies and Time Keys in Tabular Mode - Usage
  • BUILD and Deployment Options in Tabular Mode. XMLA Scripts
  • Tabular Database Processing - InMemory & Direct Query Modes
  • In-Memory with Direct Query & Direct Query with In-Memory
  • Performance Advantages of Tabular Mode, In-Memory Queries
  • Virtual Cube Access and OLAP Cube Browsing Options
  • MDX Queries and WITH MEMBER Expressions in Tabular Mode
  • MDX Filters - Vertical & Horizontal Access Attributes. MEMBERS
  • Connection Edits and SMDL (Semantic Definition Language)
  • Advanced Cube Design and STAR / SNOWLFLAKE Schemas
  • MDX Predicates and FILTER() Expressions, Nested Queries
  • Querying Tabular Cubes with MDX Joins, SCOPE, TOPPERCENT
  • xVelocity and Direct Query with MDX / DAX. Tuning Options

DAY 9: DAX - SYNTAX and FUNCTIONS

  • DAX - Data Analysis Expressions
  • Purpose of DAX, Syntax Rules
  • Possible Operations and Usage
  • DAX Data Types and Operators
  • DAX Values and Usage Options
  • DAX Functions - Scalar, Statistical
  • Mathematical & Time Functions in DAX
  • Aggregate & Logical Functions in DAX
  • Conversion & Relational Functions in DAX
  • Working with Columns and Measures in DAX
  • Row Context and Filter Context
  • Using DAX for Calcualted Columns
  • Using DAX for Report Measures
  • Using Logical Operators and Conditions
  • DAX Filters and Expressions with Columns
  • DAX Errors and Handling Options
  • DAX Expressions: Excel Versus Power Pivot
  • Pivot Tables and Chart Slicer Functions
  • Funnel Reports and Data Cards.
  • Fetching data from different files
  • Computed Columns & Column Splits
  • Data Navigation Paths and Data Splits
  • Merge Queries and Append Queries
  • Remove columns and split columns
  • Hiding & Moving Tables in Power BI
  • Legends, Values, Labels and Data Fields
  • Adding new Columns and Measures
  • Power Pivot Tables - Creation, Usage
  • Exporting Templates and Publish Options

DAY 10: TABULAR MODE - DAX QUERIES AND EXPRESSIONS

  • DAX: Query Syntax and Purpose. ROW & CONTAINS
  • Defining Measures inside DAX Query. ROLAP Cube Access
  • Using DAX Local Measures in MDX Queries, Drillthrough
  • CALCULATETABLE, FILTER, SUMMARIZE and ADDCOLUMN
  • CROSSJOIN, GENERATE, GENERATEALL & LOOKUPVALUE
  • FILTER in CALCULATE, CALCULATETABLE - Visual Totals
  • TOPRANK, RANKX, RANK.EQ and DAX Filters Expressions
  • Vertipaq Storage - Advantages and OLAP Backend Options
  • Direct Query Mode Settings. Data Modelling in Tabular
  • ENDOFYEAR, ENDOFQUARTER,ENDOFMONTH. KPIs with DAX.
  • FIRSTDATE, LASTDATE, DATESBETWEEN. COUNTALL, ISERROR
  • CLOSINGBALANCEYEAR, DATESYTD, DATESQTD, DATESMTD
  • SAMEPERIOD and PREVIOUSMONTH,QUARTER.
  • RANKX, RANK.EQ, COUNT, COUNTX, RANK, TOPN
  • Optimizing Performance by reducing Memory Usage
  • Row Oriented Databases Vs Column Oriented Databases
  • Security Management with DAX. OLAP Database Roles
  • Entity Roles and Membership of Multiple Roles. Tests
  • DAX Filters for Security. Dynamic Security Scenarios - DAX
  • DAX Functions for Dynamic Security. Row Filter Expressions
  • Tabular Mode Cube Design with ODC Sources - MS Excel
  • Tabular Mode Cube Design with DAT, HTML Sources. Options
  • Advantages and Limitations of Tabular Mode Cubes. Usage
  • Importing Tabular Mode Solutions and Workspace Databases
  • Multidimensional Mode Versus Tabular Mode - Technical
  • Entity Level Partitions Versus Measure Group Partitions
  • Zero Aggregations Versus Usage Based Optimization
  • SubCube and Data Mining Advantages with MOLAP
  • Design and Access, Delivery Advantages with ROLAP

DAY 11: SSAS PROJECT DEPLOYMENT & TOOLS

  • SSAS Deployment Procedures and Tools. BUILD Options
  • Deployment Configuration Files - Usage. Contents & Targets
  • Deployment Options, Settings & Targets: Transaction Settings
  • SSAS Deployment Wizard Tool Usage: Impersonation Settings
  • SSAS Deployment Accounts and Passwords. Security Inherit
  • OLAP Cube Security Roles and Partition Settings (MOLAP)
  • Defining Key Error Logs for OLAP Databases and Cubes
  • Custom Storage and Error Locations for Databases, Cubes
  • Scripting Deployment Procedure. Executing of XMLA Scripts
  • OLAP Database Processing Options - Full, Default, None
  • Cube Processing Options - Full, Default, Data, Index, Clear
  • Post Deployment Operations. Design and Data Verification
  • Scripting OLAP Databases and Cube, Dimensions. Purpose
  • Cloning Options with XMLA. OLAP DB Testing & Executions
  • Partitions Management - SPLIT and MERGE. Aggregations
  • Lazy Aggregations and Purpose. Process Scripts with XMLA
  • Force Commits and Cube Slice Options for Aggregations
  • OLAP Server Security, Database Level & Cell Level Security
  • Managing Security with MDX Expressions (Dynamic Security)
  • Attribute and Cell Visibility. Read Contingent Permissions
  • Processing Options: Sequential, Parallel and Thread Priority
  • DB and Cube Processing Jobs: Scripting with XMLA Commands
  • SQL Agent Jobs and Analysis Services Commands for XMLA
  • SSAS Server Level Audits and Cube, Dim Key Errors - Options

DAY 12: CUBE MANAGEMENT, DATA MINING & TUNING

  • SSAS Database (MOLAP) and Cube Audits - MDX Query Logs
  • Security Audits and Need for Optimizations, DB Audits
  • Usage Based Optimization (UBO): Purpose, Aggregations
  • Usage Based Optimization Options (MOLAP) and Filters
  • Flight Recorder Settings, SSAS Query Log Providers
  • Data Sampling Intervals and MDX Query Tuning Process
  • 100% Aggregations (FULL), CPU and Space Thresholds
  • Lazy Aggregations & UBO in Partitions, Performance Settings
  • Writebacks - Purpose, Settings and Usage. Forecast Updates
  • Writeback Partitions - Cube & Dimension Level, UPDATES
  • Using MDX Expressions and Queries for Writeback. Advantages
  • Writeback Tables. MDX Transactions - COMMIT / ROLLBACK
  • Subcube - Creation, Advantages Usage. MDX Query Plans
  • Subcube - DESCANDANTS, PARENT & MEMBER Functions
  • Data Mining - Purpose, Techniques and Forecast Operations
  • Mining Structures & Mining Models - Storage Architecture
  • CLUSTERING, DECISION TREES, NEURAL NETWORK Models
  • REGRESSION Techniques, Naive Bayes Mining Algorithms
  • Case Tables Identification and Nested Table For Relations
  • Attribute Data Types - DESCRETE & CONTINOUS. Drilldowns
  • Training Sets and Testing Sets for Data Mining Accuracy
  • Dependency Rules, Classification Charts and Lift Chart
  • Mining Algorithms - Score and Prediction Analysis Functions
  • DMX Queries - Forecasts, Prediction Functions, Expressions
  • Mining Model Comparison, DMX and Forecast Reports

DAY 13: REAL-TIME PROJECT and OLAP MODES Comparision

  • Multidimensional OLAP DB Management: Backups, Restores
  • Multidimensional OLAP DB Management: Detach, Attach
  • Multidimensional OLAP DB Management: DB Synchronize
  • Tabular OLAP DB Management: Backups, Restores
  • Tabular OLAP DB Management: Detach, Attach
  • OLAP Database Scripting and Cloning Options. Jobs
  • Cube Processing Jobs and Schedules with SQL Server
  • Comparing SSAS 2012, SSAS 2014 and SSAS 2016
  • Comparing Multidimensional and Tabular Modes
  • XEvents support through SSMS (New in SSAS 2016)
  • DAX performance through Super DAX - DBCC Commands
  • DBCC Commands and MDX for SSAS - 2016

Real-time Project for SSAS

One Real-time Project with 24x7 LIVE Server & Session wise Material and Lab Work to be provided.

Benefits of our SSAS Training Course :

  • Completely Practical and Realtime
  • Theory Material provided in Advance
  • Highly Interactive and Interesting
  • Daily Tasks and Weekly Assignments
  • Certification Guidance and FAQs
  • 24x7 Server Access with Realtime DBs

After the SSAS Online Training course participants should be able to :

  • Design and Understand OLAP Cube Structures
  • Decide to Multidimensional and Tabular Techniques
  • Design MDX Queries and Expressions for Cube Reports
  • Tune OLAP Cube Structures with UBO and Partitions
  • Perform Aggregation Design and KPIs with DAX Expressions
  • Deploy and Implement SSAS OLAP Databases with Data Mining
Our SSAS Online Trainings are completely practical, realtime. Register Today for Free Demo
 

SQL Server, SQL DBA, MSBI DWH Trainings :