SSAS Classroom Training (with MDX & DAX)

SSAS Classroom Training at SQL School is carefully designed to involve Cube Design, OLAP Data Modelling, Cube Development, MDX Programing, Mining ith Forecasts and Tuning operations. Our SSAS Training Classes are completely practical and interactive with real-time Project.

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

Trainer : Mr. Sai Phanindra T (11+ Yrs EXP). Profile


Basic to Advance MDX Basic to Advance DAX
OLAP Cube Design Tuning & Reporting
Real-time Project Placement Services

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


SSAS Classroom Training Course Contents:


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


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


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


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


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


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


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


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


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


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


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


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
Our SSAS Online Trainings are completely practical, realtime. Register Today for Free Demo

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 Classroom 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

SQL Server, SQL DBA, MSBI DWH Trainings :