Microsoft Business Intelligence - MSBI Online Training (LIVE, Instructor-Led)

This impeccable MSBI (SQL BI) course is carefully designed for aspiring BI Developers, Consultants and Architects. Program includes basic to advanced Business Intelligence and Data Warehouse concepts on SQL Server Integration Services (SSIS), Analysis Services (SSAS) and Reporting Services (SSRS). Complete practical and realtime training course with 24x7 free LIVE server access and Lab.

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

MSBI Online Training Schedules

Timings (IST) Free Demo Start Date Register
1 8 AM to 9:30 AM Oct 2nd Oct 4th Register
Trainer: Mr. Sai Phanindra T (11+ Yrs EXP). Profile
** If above schedule does not work for you, please find below options :
 

Option 1: MSBI Training Videos - On-demand, 24x7 Access
Option 2: Interactive Training from other experienced faculty


ALL SESSIONS ARE COMPLETELY PRACTICAL, REAL-TIME.
Total Course Fee: 18000/- [USD 300]. DUR - 7 Weeks (Mon-Sat)

MSBI TRAINING DEMO

Highlights : Basic to Adv. ETL Data Warehousing Data Cleaning DAX Basic to Adv. MDX Tabular Mode OLAP Custom Dashboards

MSBI Training (SSIS, SSAS, SSRS) Curriculum:

CHAPTER 1: SSIS INTRODUCTION, INSTALLATION & TOOLS

Need for SQL Server Integration Services (SSIS) & ETL / DWH Advantages of SSIS for Data Loads, ETL, DataWarehouse SSIS Tools : SSDT (SQL Server Data Tools), ETL Wizards SSIS Design / Development & LIVE (Deployment) Environment Understanding Data Warehouse (DWH) Design & ETL Process DWH and ETL Structures, Implementations with MSBI SSIS SSIS ETL Operations for Data Reads, Data Cleansing, DWH Data Warehouse (DWH) Design Principles and Design Plan SSIS 2016 and SSIS 2017 : SSIS Database Installations Understanding SSIS Catalog DB Encryptions and CLR / Startup SSIS Database and Catalog Folders - Purpose, Passwords SSIS 2016, 2017 Configuration and DB Catalog Encryptions SSIS Catalog Database (SSIS DB) : Creation, Verification SSDT Tool: SQL Server Data Tools - SSDT / Visual Studio Shell SSDT 2015 Installation and Verification of SSIS Templates SSDT 2013 Installation and Verification of SSIS Templates SSIS Package Environment and SSDT Project Creation SSIS Solutions and Project Templates - Verifications Version Control Options and SSIS Design Plan - Tools Usage SSIS Online Training - Lab Plan, Resources & Databases

CHAPTER 2: SSIS PACKAGE DESIGN BASICS: ETL

Understanding SSIS Developer Environment (SSDT) Interface Basic Vocabulary - SSIS, ETL, DWH, Data Flow, Data Buffer Control Flow Tasks - Architecture, Purpose and Usage Data Flow Tasks - Architecture, Purpose and Usage Creating SSIS Packages For Basic Data Flow Operations SSIS Solutions, Projects and Package Creation. Tasks Need For Data Pipelines & Connections in Data Flow Tasks Basic Data Extraction Drivers / Providers with Data Flow Using OLE DB and SQL Server Connections - Usage SSIS Package Creation Process - Using Control Flow Items SSPI Interface Connections, Data Source, Initial Catalog Using DTSX Files for SSIS Package Execution. Audits SSIS Execution Context, SSIS Package Errors and Logs Using Source Assistants, Destination Assistants in SSIS DAT File Imports, Data Flow Options, Adding Annotations Common SSIS Package Errors & Solutions in Real-time SSIS Project Configuration Options - Debugging, Bit Config SSIS 64 Bit and 32 Bit Configuration Settings and Options

CHAPTER 3: MERGE, UNION ALL, DATA CONVERSIONS

Understanding ETL and DWH Implementations - Strategies Bulk Load Operations and Data Import Options in SSIS Bypass Prepare and Execution Options with SQL Task Historical Data Loads (Type II) and Incremental Updates Debugging Controls in SSIS - Variants and Limitations Data Flow Debugging Controls and Data Viewer Options Synchronous & Asynchronous Transformations, Data Loads Row and Partial Blocking Transformations. Buffer Reuse Fully Blocking Transformations - Buffer Reuse Restrictions Examples to Avoid Fully Blocking Transformation in SSIS Advanced Options and Input / Output with Excel Sources Excel Connections, Sheets, & Memory References - Usage MERGE Transformation and UNION ALL Transformation SORT Transformation, NOSORT Options, Advanced Sort Data Conversion Transformation and SSIS Expressions Data Flow Transformation: Conditional Split, Expressions Transactions & Batch Scoped Data Loads with SSIS Packages Comparing Regular Loads and Fast loads in ETL / DW

CHAPTER 4: PIVOT, FUZZY LOOKUP, TRANSACTIONS

Importing Legacy Data, Need for Data Cleansing, Formatting PIVOT Transformation, Data Reads, Connection Assistant Pivot Usage Values - Purpose and Implementation. Key Values Lineage ID in SSIS - Purpose, Usage. Column Mappings Data Exchange Operations with Pivot Keys and Pivot Values Using Data Viewer (Debugging) for Data Transfer Verifications Debugging with Detach/Attach, Memory Buffer & Row Counts Fuzzy Lookup Transformation, Reference Table Connections Exact, Fuzzy and Nomatch Data Cleansing with Conditional Split Index Creation, Maintenance for Faster Lookup Transformation Data Conversion Transformation - Usage. Derived Columns Data Conversion Transformation - NVARHAR, VARCHAR Data Explicit Data Type Conversions and Usage. Error Redirections SSIS Package/Project Conversions - Process and Options Text Qualifiers and Data Loads with Flat File Sources (.dat) SSIS Expressions and Package Debugging, Break Points Transaction Options For SSIS Executables - Package Level Precedence Constraints - Success/Failure/Completion Settings Parallel and Sequential Task Executions. Options, Usability

CHAPTER 5: SSIS with ETL & DWH. SCD TYPE 1, TYPE 2

Sales Scenario for OLTP Database to Historical Data Loads Introduction to Datawarehouse (DWH) - Purpose, Usage, Access Dimension Tables, Fact Tables and Relations, Inferred Members TYPE1 and TYPE2 Changes. ETL Implementation Techniques Implementing Type I Changes (SCD) for DWH in Sales Scenario Dimensional Table Design for DWH using SCD - Type 1, Type 2 Data Loads with Legacy Files (DAT, TXT, CSV), Data Marts Surrogate Keys and Alternate Business Keys - Purpose, Usage Identity Columns, Key Values in DWH. Business Key Limitations Changes to Fixed Attributes, Changing Attributes. Observations Changes to Historical Attributes. Inferred Member Updated ETL Date Fields, Row Status Values and Transformations Naming Conventions For SSIS Entities, Options & Reusability Designing Dimensions for DW - Incremental, Historical Loads OLE DB Connections for Incremental Loads in Datawarehouse Identity Property and Attribute Key Types in SCD, Limitations Historical Attributes and Data Delta Operations, Identification SSIS SCD Transformation Limitations and Real-time Issues SCD Transformation with OLE-DB Command Transformation

CHAPTER 6: FOR LOOP, FOR EACH LOOP, CONTAINERS

Threshold Values Selection, Search Delimiters and Options Data Pipeline with _Similarity & _Confidence Columns ForEach Loop Container. File Level Connections, Variables Defining Variables for Connections. DFT and Control Flow Dynamic Connections with Variables. For Loop Container Connection Iterations, Connection Fetch and Index Mapping Local and Global Connections. Naming Conventions Post ETL, Derived Column and Fuzzy Transformations SSIS Configurations, Usage. Dynamic Connection Values SSIS Package Level Rollbacks and Restart Execution Options Checkpoint Files and SSIS Logging Options - Package, Tasks Checkpoint Options - Advantages, Usage and Limitations Dynamic Connection Managers - Precedence Constraints FailPackageOnFailure and Checkpoint Property Usage Transaction Property : REQUIRED / (NOT)SUPPORTED Transaction Property Versus CHECKPOINT Files, Usage Parent - Child Package Design in SSIS, Parameter Binding Master Packages and Child Packages, Derived Columns Defining Local and Global Variables / Parameters Parameter Bindings with Parent - Child Packages in SSIS

CHAPTER 7: CHECKSUM TRANSFORMATION @ DWH DESIGN

Need for Checksum Transformation in ETL Data Loads Configuring Checksum Transformation : 2014, 2016 Versions Checksum Transformation Logic & Parity Checks (CRC) Generating Checksum Values @ Type I, Type II ETL Changes DWH Dimension Tables With Checksum Transformation Working with Parity Bits, Conditional Lookup Transformation Row Redirection Options & Lookup Match, NonMatch Output OLE DB Command: SQL Statements and Input Parameters Parameter Mapping and Dynamic SQL for Row Updates Cache Transformation with Memory Loads, CAW Files (VM) Memory Connections and Reuse. Lookup with Cache Options Tuning Lookup Transformation: Caching & Index Options FULL CACHE : Usage Options. NO CACHE: Usage Options PARTIAL CACHE : Precautions. Data Splits, Fast Loads Cache Connection Manager, Performance Tuning @ ETL Pre-ETL Data Load Operations with Memory Connections Lookup with NOCACHE Options. Advantages, Precautions Understanding Dependent Data Flow Tasks and Usage SSIS Package Internal Parameters and Query Updates OLEDB Command with Conditional Splits, Multi Row Updates

CHAPTER 8: XML DATA, LOGGING, ERROR HANDLING

System.Data.SQLClient Managed Providers: Usage Options Dynamic Connections and Loops with SSIS Expressions SSIS Expressions with Variables For File Specifications For Each Expressions - Dynamic File Types, Directories Variable Mapping with Indexed Connections. Performance XML Queries, Options @ Sub Queries. XML Data Imports RAW Files - DTSPipeline Wrapper & RAW Adapters Row Sampling and Percentage Sampling Options SSIS Logging - Text Files and sysssislog Tables @ Audits Package Logging and Container Events for Execution SSIS Logging Options and Connections. Verifications SSIS Data Profiler Tool, ADO.NET Connections and XML SQL Data Profile Viewer Tool - Usage with XML Files Nullability Checks and Fast Load Options @ Row Size ADO.NET Connections for SQL Profiler Tool, Options Time-Out Options for Quick Profile, Candidate Keys Value and Length Distribution, Surrogate Key Detection XML File Imports for SQL Profiler Viewer Tool, Options

CHAPTER 9: FACT TABLE DESIGN, FACT LOADS (DWH)

Fact Table - Design and Implementation Rules, ER Model Fact Table Design with STAR and SNOWFLAKE Schemas Time Dimensions - Purpose, Usage and ETL Date / Time Generating Time Dimensions for ETL, Load Identification Linking Time Dimensions to Fact Loads, Lookup Operations Caching Options in Lookup. Dimension Loads, Key Lookups Parent-Child Packages for Dimension,Fact Table Loads Master Package Design: Dimension, Fact Load Integration Inferred Members and Status for NULLs in Dimensions Parameter Mapping for ETL Updates, Component Properties ETL Load Date IDs & Expressions for Dimension Keys Error Handling & Event Handling with Master Packages Project Connections, Parameters in Master-Child Packages Key Based Dimension LOOKUPs and Indexes. Fast Loads SCD Implementations with Legacy Data. ETL Load Types Fact Load Design for Initial Loads, Incremental Updates End-to-End Data Warehouse Design & .NET Scripts STAR & SNOWFLAKE Schemas for DWH - Data Modeling

CHAPTER 10 : SSIS CUSTOMIZATION WITH SCRIPT TASK, .NET

Script Task - Purpose and Working in SSIS Control Flow Script Task - Usage with VB.NET Programs. Compilations Script Task - Usage with C#.NET Programs. Compilations Variables and Parameters with SSIS Script Task. Usage Read Only and Read Write Variables. Expressions, Mapping Namespace Options, Customizations : System.IO, DTS Using VB.NET, C#.NET Scripting Programs with Parameters Data Flow Limitations : Solutions with .NET Scripting SSIS Variables and Parameters - Mapping Expressions Understanding Control Flow - Sequence Containers, Usage Using Control Flow File System Tasks and Limitations Using .Net Scripting for SQL Server Data Reads/Writes SQLDataAdapters & System.Data.SQLClient Connections Script Task for Data Writes and Row Level Formatting Adding DTS Packages to SSIS Projects (Data Tools) Using Variables and Parameters for SSIS Script Task Script Component in Data Flow Task - Usage Options Script Component - Input, Output and Transformations Using Script Task for Control Flow, Data Flow Limitations

CHAPTER 11: SSIS PROJECT DEPLOYMENT, UPGRADES

SSISDB Catalog Deployments - Process, ISPAC File Structure SSIS Package Builds, Verification Techniques, Scripts SSIS Project Deployment Wizard : Targets and Logging SSIS Package Deployments - Need, Options and Tools SSIS DB Catalog Folders - Creation, Usage. Properties SSIS DB Catalog Projects For Project Deployments SSIS Package Executions - Scripts, Reports, Messages Package Validations - Scripts, Reports, 32/64 bit Options Package Configurations - Parameter Management, Scripts SSIS Package Jobs from SQL Server Agent. SSIS Job Steps SSIS Job Schedules and Notifications: Windows Event Logs SQLISPackage130 Events and Error Logs. Package Monitoring Package Security Management - SSISDB Logins and Users ssis_admin, ssis_failover_monitoring_agent, ssis_logreader Folder Level and Project Level Security Implementation Execute/Write/Folder Security Content Management Roles Business Intelligence Development Studio: SSIS BIDS Project Migration Utilities in SSIS, SSIS Project Passwords Importing ISPAC Files, SSIS Projects. Re-Engineering

CHAPTER 12-13: SSIS REAL-TIME PROJECT @ ECOMMERCE

Advanced Connection Options - XML, DT_NTEXT Stream XML Connections & Usage. Reading, Operating XML Data Column Statistics and SSIS Data Profile Viewer Tool Direct Data Loads and Data Stage Operations for DWH DBs DML Audits using Change Data Capture (SQL Server) Understanding CDC Tables with SQL Server Connections DML Audits: Inserts, Deletes, Values before/after Updates CDC Source Connections - Usage, ADO.NET Integration CDC Control Flow Tasks and CDC State Tasks. Values INITIAL LOAD START / END. PROCESSING RANGES SSIS CDC Source Connections with Net Support Changes Adding DTS, Older SSIS Packages & Package Upgrades Creating Packages, SMO Connection with BIDS. Project Builds Command-Line Deployments For SSIS, Execution Utilities Advantages of SSIS ETL/DW Procedures and Data Audits SSIS Packages for Database Migrations - Online/Offline Database Copy & Move Options. Pre, Post Migration Tasks Package Refresh Options, Package Upgrade Properties Using State Variables and Controls. Storing States

CHAPTER 1: MULTIDIMENSIONAL MODE - CONFIG

Installation and Configuration: SSAS 2014, 2016, 2017 Need for SSAS Component & Tools - Operation Modes Multidimensional Mode : Purpose, Properties & Usage Configuring Multidimensional Mode Instances, Verification 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 SSAS Components and DO-MD Client. Server and Client Arch" Data Source Configurations & DB Installations for Lab

CHAPTER 2: BASIC CUBE DESIGN WITH SSAS

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 Realworld Example - Sales Scenario for SSAS. Cube Design Sample Analysis Requirements and Entities - Measure Groups Basic SSAS Database Entities. Sources, Destinations Working with SQL Server Data Tools (SSDT). BI Templates Data Source, Data Source View and Cube Designer Wizard Dimension Wizard and Attributes. BUILD, DEPLOYMENT PROCESS of OLAP Cube. Options and Online Deployment Excel Connections for SSAS 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 Deployment Warnings and Solutions. Impersonaltion Data Source to SSDT : Issues, Solutions and Data Flow OLAP Cube Design and Working : Access Methods End to End Implementation of SSAS in Real-world

CHAPTER 3: CUBE DESIGN - 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 Options). Pivot Reports @ MS Excel Enabling Caching. Reusing Connections. Filter Expressions Backend Access to Business Data. Frontend Access to Reports Cube Browser - SSDT and SSMS Drilldown Differences Hierarchies : Attribute Levels and Purpose. Easy Access Defining Hierarchies for Easy Access. Drill-down Reports Performance Warnings and Attribute Relations and Keys Composite Attribute Keys, Member Name Columns, Hierarchies Deployment Errors - Duplicate Attribute Keys: Solutions

CHAPTER 4: MDX QUERIES & EXPRESSIONS

MDX: Multidimensional Expression Language. Syntax Rules MDX Syntax and Axis. Cube Data into ROWS, COLUMNS Practical Advantages of MDX: Reports, Cube Writebacks Additional MDX Usage for Cube Design, KPIs and Actions MDX Queries with Dimension Attributes, Members. Axis Levels MDX Queries on Hierarchies, Levels and Attribute Keys MEMBERS, CHILDREN, ALL MEMBERS: 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. 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 Cube Browser in SSMS Vs SSDT. Null, Empty Axis

CHAPTER 5: CALCULATIONS, TIME ENHANCEMENTS with MDX

MDX Calculations - Need & Advantages. UI Options Calculations with MDX Scripts. Syntax Verification Calculations Usage and Folders - Cube Browser Usage 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 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 Using Calculations in FILTERS and Search Pattern - MDX Slow running Queries with MDX: Need for Tuning Cubes

CHAPTER 6: PARTITIONS, AGGREGATIONS, STORAGE

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

CHAPTER 7: KEY PERFORMANCE INDICATORS & MDX

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

CHAPTER 8: TABULAR MODE CONFIG, CUBE DESIGN

Tabular Mode : Purpose, Properties and Usage (ROLAP) PowerPivot Mode : Purpose, Properties and Usage Configuring Tabular Modes Instances Purpose and Tests Tabular Mode - Importance, Advantages. SSDT Templates Workspace Server Architecture, Configuration Settings Entity Identification Procedures and Design Constraints Data Import Wizard and SQL / ODBC Connection Settings Column Filters and Row Filters. Workspace Import Settings Dimension Identification Technques in Tabular Mode Working with Hierarchies, Attributes and Tabular Grids Aggregated Measures & Measure Groups. KPI Generation KPIs with Static and Dynamic Goals. Status, Trend Factors Measure and Measure Group Identification Techniques In-Memory and Direct Query Option for Cube Process Direct Query with In-Memory Settings. Vicer Versa Performance Advantages of Tabular Mode, In-Memory Integrated Workspace and Dedicated Workspace - Usage

CHAPTER 9: TABULAR CUBE DESIGN - IN DETAIL

Named Query Options with Tabular Design, Entity Relations Marking Time Tables and Date/Time Key Setttings Time & Non-Time Hierarchies with Tabular Mode Time Based Hierarchies and Keys in Tabular Mode - Usage Partitions - Entity Level Partitions, SQL Scripts, Process Entity Conditions with Partions. Cloning Options, Reuse Freezing Columns, Pivot / Unpiviot Operatins. Usage. Connection Edits, SMDL (Semantic Definition Language) Cube Design and STAR / SNOWLFLAKE Schemas Virtual Cube Access and OLAP Cube Browsing Options Cube Level Meausure Goals, Status. Add/Remove Columns ODC (Office Data Connections) and ROW / Column Filters Partition & Perspectives in Tabular Mode. Cube Browser Goal Identification For KPIs. Static & Dynamic Goals Issues and Solutions with Static and Dynamic Goals KPI Design with Remote Measures and Abstract Values Build & Deployment Options. Transactional Deploy BUILD & Deployment in Tabular Mode. XMLA Scripts Tabular Processing - InMemory & Direct Query Modes

CHAPTER 10: DAX - FUNDAMENTALS & SYNTAX

Purpose of Data Analysis Expresssions (DAX) Scope of Usage with DAX. Usabilty Options DAX Context : Row Context and Filter Context DAX Entities : Calculated Columns and Measures DAX Data Types : Numeric, Boolean, Variant, Currency Datetime Data Tye with DAX. Comparison with Excel DAX Operators & Symbols. Usage. Operator Priority Parenthesis, Comparison, Arthmetic, Text, Logic DAX Functions and Types: Table Valued Functions Filter, Aggregation and Time Intelligence Functions Information Functions, Logical, Parent-Child Functions Statistical and Text Functions. Formulas and Queries Syntax Requirements with DAX. Differences with Excel Naming Conventions and DAX Format Representation Working with Special Characters in Table Names Calcualted COlumns and Calculated Measures with DAX Attribute / Column Scope with DAX - Examples Measure / Column Scope with DAX - Examples DAX Expressions with Measures and Columns DAX for Query Extraction, Data Mashup Operations

CHAPTER 11: TABULAR MODE - DAX EXPRESSIONS *

Possible Operations and Usage. Definig Measures DAX Functions with Scalar, Statistical Values DAX Functions For Time Intelligence Calculations Using DAX for Calculated Columns, KPI Measures Logical Operators and DAX Conditions in Tabular DAX Filters and Expressions with Entity Columns Calculate Options - Auto & Manual Calculations Using DAX Local Measures in MDX Queries, Drillthrough CALCULATETABLE, FILTER, SUMMARIZE, ADDCOLUMN CROSSJOIN, GENERATE and GENERATEALL ADDCOLUMNS,  MAXX, MAXA, DISTICTCOUNT CALCULATETABLE, FILTER, SUMMARIZE CROSSJOIN, GENERATE and GENERATEALL Vertipaq Storage - Advantages and OLAP Backend Options Direct Query Mode Settings. Data Modelling in Tabular ENDOFYEAR, ENDOFQUARTER and ENDOFMONTH FIRSTDATE, LASTDATE, DATESBETWEEN, COUNTALL CLOSINGBALANCEYEAR, DATESYTD, DATESQTD SAMEPERIOD and PREVIOUSMONTH, DATESMTD TOPCOUNT, TOPCOUNTX, SUM, SMUX in DAX Freeze, Hiding / Moving Tables in Tabular Mode Power Query in Excel Versus Tabular Mode Tranformations & Time Based Calculations in ROLAP

CHAPTER 12: TABULAR & MOLAP DEPLOYMENTS

SSAS Project Builds and SOlution Builds Deployment Configuration Files & Contents SSAS Deployment Wziard and XMLA Scripts Partition Management and Schedules (OLAP Jobs) Storage Modes in MOLAP and ROLAP 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 Usage Based Optimization (UBO): Aggregations Storage Modes : Multidimensional, Relational and Hybrid 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 Security Roles with MDX and DAX In-Memory Options and Deployment Scripts

CHAPTER 13: REAL-TIME PROJECT : MOLAP, TABULAR

Writeback Partitions - Cube & Dimension Updates MDX Expressions, Queries for Writeback. Advantages Writeback & MDX Transactions - COMMIT / ROLLBACK Subcube - Creation, Advantages. MDX Query Plans Subcube - DESCANDANTS, PARENT, MEMBER Functions Data Mining - Techniques and Forecast Operations Mining Structures & Mining Models - Storage CLUSTERING, DECISION TREES, NEURAL NETWORK REGRESSION Techniques, Naive Bayes Mining Algorithms Case Tables Identification and Nested Table For Relations Attribute Data Types - DESCRETE & CONTINOUS Training Sets, Testing Sets for Data Mining Accuracy Dependency Rules, Classification Charts and Lift Chart Mining Algorithms - Score and Prediction Analysis DMX Queries - Forecasts, Prediction Expressions Mining Model Comparison, DMX & Forecast Reports XEvents support through SSMS, DBCC Commands DAX performance with Super DAX - DBCC Commands

CHAPTER 1: SSRS INSTALLATION, CONFIGURATION

Need For Reporting Solutions (SSRS) and Tools Reporting Engine Architecture and Report Databases SSRS Installation, Tools and Database Configuration Process SSRS 2016 : Installation and Report Databases SSRS 2014 : Installation and Report Databases Report Server URL - Purpose, Test Connection, Usage Report Manager URL - Purpose, Test Connection, Usage Report Portal URL in SSRS 2016 - Usage, Web Service Three-Phase Report Life Cycle in Reports (End-End) Report Server Database and Report TempDB Configuration Virtual Directories (VD) in Web Services - Settings, Options Communication Ports and Data Encryption For Web Portal URL Report Design and Work Flow Diagram - Lab Plan, Data Sources Report Design with Report Designer & Report Builder Tools Report Builder Versus Report Designer - Basic Differences SSDT (VS): SQL Server Data Tools Installation, Verification SSDT 2015 and SSDT 2013 Installation, Test Connection SSRS 2016 Report Portals and Accessibility

CHAPTER 2: REPORT DESIGN - I

Working with Visual Studio - SQL Server Data Tools Report Templates and Basic Usage. Project, Solution Designing Basic Reports - Understanding Entities Working with Report Project Wizard - Usage, Reports Defining Data Source Connections, Source Databases Query Designer Options, Query Builder & Import Options Table Reports and Matrix Reports with Report Wizard Layout and Format Options - Drilldown Reports, Blocks Stepped Reports and Multi Field Drilldown Options Report Project Template - Designing Dataset, Reports Report Toolbox and ReportData Options, Textbox Usage Table Headers, Formatting Options, MATH Expressions Alternate Row Colors, Using Report Globals, Expressions Report Formatting Styles, Expressions and Reusability Reporting Functions: IIF, Format, Ceiling, Round, Concat Alternate Row Colors and Dynamic Formatting Options Textbox Properties - Date & Time Formats, Numbers, Styles Report Timeout Options and Tool Box Options, Report Data Initial Catalog For Report Sources, Static/Dynamic Properties Query Timeouts and Connection Properties - Scaleout

CHAPTER 3: REPORT DESIGN - II

Grouping Operations: Row Groups, Column Groups Table Report - Row Groups, Parent - Child Groups Adding Groups to Existing Rows and New Rows Group Headers and Group Footers - Usage, Sub Totals Group Field Visibility and Toggle Options @ Parent Row Group Properties, Header/Footer Properties Column Groups for Table Reports, Advanced Options Repeat-On-New-Page Options and Report Properties Column Groups for Matrix Reports, Data Grouping Drill-down Reports using Row Groups and Visibility Drill-down Reports using Column Groups, Visibility Table Reports Vs Matrix Reports - Technical Differences Adding Images to Reports. Tooltip and Size Options Report Headers and Report Footers - Globals, Expressions Merging Report Fields, Sub Totals and Grand Totals Group Edits, Removing Group Totals, Adding Group Headers Column Groups - Advance Mode - Fixed Attributes, Fields Repeating Header on Every Page & RepeatPage Options Working with Report Globals for Metadata, Report Properties Report Properties and Report Page Options with Scripts

CHAPTER 4: REPORT DESIGN - III

Report Parameters - Purpose, Benefits and Usage Creating Parameters using Dataset Query Conditions Creating Parameters using Report Properties, Report Data Single Value Parameters and Multi Value Parameters Dynamic Parameter Values, Dependency Options, Queries Defining Datasets with Parameters, Dynamic Conditions Dataset Links to Parameters, Value Options from List, Query Multi-Value Parameter Options: Data Types, Null, Empty Values Reports using Multiple Data Sets. Performance Issues Advanced Options : Auto Refresh, Manual Refresh Options Dataset Filters, Toolbox Filters - Purpose and Usage Filters versus Parameters - Differences with Performance Using Filter Condition at Dataset Level, Toolbox Level Data Type Conversions with Filters, Expressions & Computations Deciding Parameters and Filters - When to use which, why? SSRS Expressions and Global Fields For Report Parameters JOIN, FIELDS, LABEL and Formatting Options with Parameters Linking Report Parameters to Dataset Parameters and Filters

CHAPTER 5: REPORT DESIGN - IV

Chart Reports - Design Options, Properties and Usage Series Values Selection - with / without Category Groups Report Categories with Series Groups - Differences, Usage Data Visualization Types: Trend & Discrete Chart Reports Clustered / NonClustered Legend Attributes. Position Options Series Labels : Properties, Number Formatting Options, Visibility Series Actions : Series Properties, Multi - Valued Parameters Report Actions : URL / Reports, Setting for Report Filters Axis Values - Possible Issues with Multiple Axis Items, Solutions Dashboard Creations, Usage. Multiple Chart Areas, Legends Dashboard Exports with Filters (Static, Parameterized) Chart Series Properties and Options, Report Markers & Grid Values Chart Areas with Multiple Toolbox Items, Properties & Limitations 3-Dimensional Report Options - Properties, Visibility Options Range & Pie Charts, Line Reports, Data Bars, Area Charts Report Actions with Parameter Values - Report Filter Joins Exploring Toolbox Filters with Type Conversions & Expressions Common Report Errors with DataSet / Report Filters, Solutions Using Shared DataSources and Shared DataSets - Advantages Data Bar Reports and Sparkline Reports - Stacked Reports - Usage Multiseries Charts, Dynamic Chart Size, Axis Display Control

CHAPTER 6: REPORT DESIGN - V

Gauge Reports: Purpose and End User Access Options Gauge Report Types - Radial and Linear Gauges Adding Data to Gauge Report - Default Indicators Gauge Data Versus Chart Data - Pointer Options Scale Properties: Maximum, Minimum Dynamic Ranges Browser Compatibility and Offline Report Access Gauge Properties, Gauge Panel Properties and Filters Master Reports and Detailed Reports - Sub Reports Scale - Properties, Values and Label Options Pointers - Properties, Scale Limits, Format Options Ranges & Labels, Report Items, Properties, Needle Options Adding Multiple Gauges - Dashboard Design, Dynamic Size Parameterized Gauge Reports - Dataset Level and Filters Indicator Reports : Value and State Expressions Indicator Reports and Gauge Panel Options with Filters 3D PieCharts and Aggregations with Excel Reports ODBC Data Source Connection Reuse & Worksheets Dynamic Report Parameters and Filter Expressions Rendering Format Limitations & Real-world Considerations

CHAPTER 7: REPORT DESIGN - VI

Report Builder Tool - 2016, 2014, 2008 R2 Installation Installation Verification Options, msi File Configuration DataSource and DataSet Creation with Report Builder Local Report Specific Entities and RDL File Creations Entity Browsing Options and Query Design Options Dataset Design with Parameters and Filters. Usage Options Query Designer Options with Report Builder, Table Selection Column Aggregates and Auto Group BY. Query Edit Options Adhoc Reports Design with Tabilx Wizard - Report Layouts Report Design - Sub Totals, Drill-down & Column Groups Chart Report @ Report Builder - Line Reports & Options Dataset Design with Parameters and Filters. Usage Options Line Reports and XValue Options using Report Builder Tool Trend Analysis and Continuous Data Reports - OLTP / DWH Map Reports - Map Layers, Map Report Items and Options Map Gallery, ESRI Share Files and SQL Server Spatial data Defining Geo Spatial Data for Business Analysis Dashboards Working with Polygon, Tile, Line and Point Map Layers Map Visualization Options and Bubble Map Reports Usage Data Fields, Display Labels and Visualization Indicators

CHAPTER 8: REPORT DEPLOYMENT & SUBSCRIPTIONS

Report Deployment Options - Report Builds, Config Files Data Source, Data Set Folders, Report Server URL Targets Report Deployment - UI & Command Line. RSKey Config Report Server URL and Report Manager (Web Portal) URL Report Home, Site Settings & Properties. Report Settings Data Sources: Properties, Security Options, Access Options DataSets: Properties, Security Options, Dependent Items Reports: Properties, Security Options, Edits, Downloads Security Roles: Content Manager, Browser, Report Builder MyReports Role, Report Publisher Role. Report User/Groups Report Linking and Default/Hidden Parameter Options Report Tuning: Caching Options, Cache Refresh - Cautions Report Tuning: Snapshot Options and Snapshot Schedules Report Tuning: Off-the-Shelf Reports and Linked Reports Local and Global Schedules, History Management, Jobs Report Builder Connections and Reusability Options Report Parts and RS Storage Options. Paginated Reports Report Delivery & Schedules, File Share Subscriptions Accessing and Controlling Reports, Email Subscriptions Encryption Keys & RS Config Files, RS Execution Accounts

CHAPTER 9: CUBE REPORTS, POWER BI INTEGRATION

Scale-Out Deployments and Remote Report Databases Remote Report Database Connections, Recovery Options Scale-Out Joins and Waits. Report Configuration Keys Multi-Report Server Configurations, Execution Accounts Report Designer Versus Report Builder - Advantages Cube Reports with SSRS - MDX Queries. Advantages Cube Reports with MDX Parameters & Filters. Performance Cube Reports with MDX Expressions, YTD, QTD Calculations Reports with SSAS KPIs and Cube Aggregations - MOLAP Cube Reports with Tabular Data Sources. Hierarchy Levels Sub Reports, Dashboards, Action Reports with Report Builder Drill-Up and Drill-Down Reports, Drill Through Reports Report Actions @ SSAS 2016 OLAP Databases. Limitations Power BI Reports with SSRS - Desktop / Mobile Editions Power BI PBX Reports with RDL Sources, Vice Versa Power BI Reports with SSRS and SSAS Connections, Options Power View and Power Pivot Extensions, Usage Examples New SSRS Tuning Features in 2016, Report Migrations

 
Participants may join for complete MSBI (SSIS, SSAS, SSRS) Course or for individual modules. Practical, Real-time.
 
New batch for MSBI Online Training (LIVE, Instructor Led) start every 15 days. Register Today
All Classes are Instructor-Led & LIVE. Completely Practical and Real-time with Study Material, Session Wise Tasks and 24x7 LIVE Server.
 

Job-Oriented Real-time Training @ SQL School Training Institute - Trainer: Mr. Sai Phanindra T

 
 
 
 
Register Today Other Popular Courses: SQL DBA Training, MSBI Training, SSIS Training, SSAS Training, SSRS Training [+] More Courses