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
|
