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

HIGHLIGHTS

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.

ALL SESSIONS ARE COMPLETELY PRACTICAL, REAL-TIME WITH MATERIAL & LAB WORK. Course Fee: 4000/- DUR - 2.5 Weeks (Mon-Fri)

SSAS Classroom Training Course Contents:

Basic SSAS Development & Cube Design

Advanced SSAS Development & Deployment

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 7: KEY PERFORMANCE INDICATORS & MDX

  • Key Performance Indicators (KPI) - Purpose, Design Options
  • GOAL, VALUE, STATUS, TREND and WEIGHT
  • Data Driven Goals - Identification and MDX Expressions
  • Variance and Value Computations. Format Options, Scope
  • KPI Organizer, Calculations. MDX Expressions, Time Analysis
  • 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
  • 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,Usage Options
  • Role Playing Dimensions, Fact and Factless Dimensions
  • Referenced Dimensions and Conformed Dimensions - Usage
  • Degenerate, Junk and Hierarchical Dimensions - Usage

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 8: 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 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
  • 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. SSAS Server Level Audits
  • 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 @ XMLA Commands
  • SQL Agent Jobs and Analysis Services Commands for XMLA

DAY 3: CUBE DESIGN WITH ATTRIBUTES, 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). Pivot Reports @ MS Excel
  • Conditional Formats, 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 & 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 9: TABULAR MODE - CUBE DESIGN, 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
  • Named Query Options with Tabular Design, Entity Relations
  • Dimension Identification: Hierarchies, Attributes, Members
  • Time & Non-Time Hierarchies with Tabular Mode. Keys
  • Aggregated Measures and Measure Groups. KPI Generation
  • KPIs with Static and Dynamic Goals. Status, Trend Factors
  • Partitions - Entity Level Partitions, SQL Scripts, Process
  • Partition Reuse. Perspectives in Tabular Mode - Cube Browser
  • Time Based Hierarchies and Keys in Tabular Mode - Usage
  • 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, Browsing Options. MDX 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 @ MDX Joins, SCOPE, TOPPERCENT
  • xVelocity and Direct Query with MDX / DAX. Tuning Options

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
  • 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 10: TABULAR MODE: DAX, OLAP CUBES

  • DAX: Query Syntax and Data Types. Comparision with Excel
  • Defining Measures inside DAX Query. ROLAP Cube Access
  • Using DAX Local Measures in MDX Queries, Drillthrough
  • Adding New Columns, DAX Comparisions and Calcualtions
  • DAX Operators, Freezing Columns, Concatenation Options
  • IF and Member Expressions. Hiding / Changing Columns
  • Vertipaq Storage - Advantages and OLAP Backend Options
  • Optimizing Performance: In Memory Query Processing
  • Direct Query & In Memory with Direct Query Processing
  • Tabular Mode Cube Design: Manual/Automated Calculations
  • Security Management with DAX. OLAP Database Roles
  • DAX Filters for Security. Dynamic Security Scenarios - DAX
  • Tabular Mode Cube Design with ODC Sources - PowerPivot
  • Cude Design with PowerPivot Mode using SSDT & Workspace
  • Advantages and Limitations of Tabular Mode Cubes. Usage
  • Importing Tabular Mode Solutions and Workspace Databases
  • Entity Level Partitions Versus Measure Group Partitions
  • Scripting Options with Tabular Mode. Cloning & Processing
  • OLAP Backups and Restores. Detach - Attach Process
  • Data Process & Read Access Security Tests with Excel
  • OLAP Database Upgrades. Process Recalculation, Clear
  • DAX Filters & Security Roles. XMLA Scripts. Cube Imports

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 11: 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 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 MOLAP, Performance Settings
  • Writebacks - Purpose, Settings and Usage. Forecasts
  • Writeback Partitions - Cube & Dimension Level, UPDATES
  • Using MDX Expressions, Queries for Writeback. Advantages
  • Writeback. MDX Transactions - COMMIT, DESCANDANTSK
  • Subcube - Creation, Advantages Usage. MDX Query Plans
  • 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 Mode Versus Tabular Mode - Technical

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
  • Measure Properties and Default Aggregation Types in OLAP
  • Linking Aggregations and Partitions. Cube Slicing Options
  • Aggregation Reusability Options - Multiple Partitions
  • Additive and Semi-Additive Measures - Aggregation Options
  • 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
  • Perspectives - Purpose and Scenarios For Invalid Cube Access
  • Dimension Usage Tab for Dimension Relations. End User Access
  • Cube Deployments with Partitions, Aggregations

DAY 12: REAL-TIME PROJECT, SSAS MODE COMPARISIONS

  • 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

* Above curriculum applicable for registrations from October 26th, 2017.
Our SSAS Classroom 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 :