Chapter 1: SSIS INTRO, INSTALLATION

• Integration Services (SSIS) & ETL / DWH
• SSIS for Data Loads, ETL, Warehouse
• SSDT : SQL Server Data Tools
• SSIS Development, LIVE (Deployment)
• Data Warehouse Design & ETL Process
• DWH and ETL Structures Implementation
• SSIS ETL for Data Reads, Data Cleansing
• Data Warehouse (DWH) Design Principles
• SSIS 2019, 2017 : SSIS DB Installations
• SSIS Database, Catalog Folders, Storage
• SSIS Catalog Database (SSIS DB) Creation
• SQL Server Data Tools – SSDT / Visual Studio
• SSDT Installation and Catalog Verification
• SSIS, ETL, DWH, Data Flow, Data Buffer
• SSIS Package Environment, SSDT Projects
• SSIS & ETL Training – Lab Plan, Resources

Chapter 2: SSIS ETL PACKAGES: BASICS

• Control Flow Tasks Architecture, Purpose
• Data Flow Tasks Architecture, Purpose
• SSIS Packages @ Basic Data Flow, ETL
• SSIS Projects and Package Creation
• Data Pipelines in Data Flow Tasks
• SSIS Packages Execution Process
• Data Flow Objects, OLE DB Connections
• SSIS Package Creation – Control Flow
• DTSX Files for Package Execution
• SSIS Execution, Package Errors & Logs
• SSIS Transformation: Conditional Split
• Excel Connection and Memory Reference
• Source and Destination Assistants
• DAT File Imports and Annotations
• SSIS Project Configurations, Debugging
• SSIS 64 Bit, 32 Bit Configurations

Chapter 3: MERGE & FUZZY LOOKUP

• MERGE and UNION ALL Transformations
• SORT, NOSORT and Advanced Sort
• Synchronous, Asynchronous Tfns
• Row, Partial Blocking Transformations
• Fully Blocking Transformations – Buffers
• Avoiding Fully Blocking Transformation
• Bulk Load Operations, SSIS Data Imports
• IsSorted & SortKey Position Options
• SSIS Package Performance & Resources
• Data Conversion Expressions
• Fuzzy Lookup Transformation, References
• Nomatch Cleansing @ Conditional Split
• Index Creations, Lookup Transformation
• Data Conversion, Derived Columns
• Varchar, Nvarchar, Error Redirections
• Threshold Values, Search Delimiters
• _Similarity, _Confidence Columns

Chapter 4: SSIS CHECKPOINT & PIVOT

• Execute SQL Task and OLE DB Queries
• Transaction Options For SSIS Executables
• Precedence – Success/Failure/Completion
• SSIS Package Rollbacks Execution Options
• Checkpoints Purpose with Data Flow Tasks
• Checkpoint Files and SSIS Logging Tasks
• Transactions with Checkpoint File in SSIS
• Checkpoint Option Advantages, Limitations
• FailPackageOnFailure, Checkpoint Property
• REQUIRED/SUPPORT/ NOTSUPPORTED
• Transaction Property, CHECKPOINT Files
• Legacy Data, Data Cleansing, Formatting
• Denormalization, Keys. Need for OLTP
• PIVOT Transformation, Connection Assistant
• Pivot Usage – Implementation. Key Values
• Lineage ID – Purpose. Data Mappings
• Lineage IDs for Column Mapping, Pivot Keys
• SSIS Input Columns and Mappings
• Data Viewer : Data Transfer Verification
• Data Type Conversions, Error Redirection

Chapter 5: EVENTS, LOOPS, EXPRESSIONS

• SSIS Package Events, Validation, Execution
• PreExecution, Progress, Cleanup Events
• SSIS Events, Errors/Warnings/Information
• Configuring sysssislog System Tables
• Debugging : Data Viewers and Breakpoints
• ForEach Loop Container. File Connections
• Variables For Linking DFT, Control Flow
• Dynamic Connections with Variables
• Iterations, Fetch, Index Mapping
• SSIS Expressions for ETL and DWH
• FOR LOOP Expressions in SSIS
• Init/EvalExpression, AssignExpression
• SSIS Expressions, Functions, Values
• Data Insertions, Data Serializations
• Counter Values, Variables & Parameters
• SSIS for OS Level Operations, Loops
• Execute SQL Task : Return Values

Chapter 6: SSIS with ETL, Warehouse (DWH)

• OLTP Database : Historical Data Loads
• Data Warehouse (DWH) Purpose, Usage
• Dimensions, Attributes, Members Types
• Dimension Tables, Fact Tables Design
• TYPE1 and TYPE2 ETL Implementation
• SCD Type1, Type 2 for DWH in Sales
• Inferred Members and Legacy Loads
• Initial Data Loads with Data Marts
• Business Keys & non Identity Columns
• Surrogate Keys, Alternate Business Keys
• Cascading OLTP / Stage to DWH Rows
• Fixed Attributes, Changing Attributes
• Historical Attributes. Inferred Updates
• ETL Date, Row Status Transformations
• Attribute Key Types in SCD, Limitations
• Historical Attributes and Data Delta
• SSIS Connection Assistants – Reuse
• SCD Transformations in Real-time

Chapter 7: Checksum & DWH Design

• Checksum Transformation in ETL Loads
• Configuring Checksum: SSIS 2019, 2017
• Transformation Logic, Parity Checks CRC
• Checksum For Type I, Type II ETL DWH
• DWH Dimension Tables With Checksum
• Lookup Transformation, Row Redirection
• OLE DB Command and Input Parameters
• Parameter Mapping, Dynamic Updates
• Cache Transformation CAW Memory Files
• Memory Connection Lookup with Cache
• Tuning Lookup: Caching, Index Options
• Pre-ETL Activities, DB Recovery Models
• FULL/ PARTIAL CACHE & NOCACHE
• Performance Tuning and Pre-ETL Loads
• Dependent Data Flow Tasks, Post ETL
• Internal Parameters and Query Updates
• Cache Allocation Options with ETL DWH

Chapter 8: CDC Transformations for DWH

• DML Audits with Change Data Capture
• CDC Tables with SQL Server Connections
• CDC Connections & ADO.NET Integration
• CDC Control Flow and CDC State Values
• INITIAL LOADS & PRCESSING RANGE
• State Variables, Net Changes, Logging
• Initial & Incremental Dimension Loads
• Dynamic CDC Control, OLEDB Command
• Internal Parameters and Usage Options
• Parameter Mapping For ETL Type1, Type2
• Integrating Control Flow for CDC @ ETL
• CDC Splitter – Row Inserts, Updates
• CDC Precautions, Input & Output Range
• Derived Column Transformations with CDC
• Limitations of ADO.NET Connections
• Master Child Packages,Parameter Binding
• Package Passwords, Project Parameters
• Project Configuration Options(32,64 bit)

Chapter 9: Fact Table Design, DWH Loads

• Fact Table – Design and ER Model
• DWH : STAR & SNOWFLAKE Schemas
• Time Dimensions and ETL Date / Time
• Link Time Dimension to Facts, Lookups
• Parent-Child Packages for Fact Loads
• Inferred Members for NULL Dimensions
• SCD Wizard for DWH Fact Table Design
• Parameter Mapping for Incr Updates
• ETL Load IDs – Dimension Attributes
• Error Handling, Event Handling in SSIS
• Text Qualifiers with Flat File Sources
• Fact Load Design for Initial, Incr Loads
• End-to-End DWH Design Implementation
• Direct Data Loads and Staging Tables
• Fact Table Staging and Incr Updates

Chapter 10: DWH MIGRATIONS, SCRIPT Task

• DataWarehouse Migrations with SSIS
• Using SSIS Containers, Db Integrity Task
• Pre-Database Migration Task Precautions
• Online and Offline DB Transfer Options
• Copy / Move with DWH Migrations
• SMTP : Simple Mail Transfer Protocol
• SQL Server Agent and Package Events
• Data Profiling with ADO.NET Connectors
• XML Files & SSIS Data Profiler Tool
• Script Task – Working in SSIS Control Flow
• Script Task – VB.NET Program Compilations
• Variables, Parameters with SSIS Script Task
• Read Only, Read Write Variable Expressions
• Expressions and Debugging, Break Points
• Variables, Parameters Mapping Expressions
• File System Tasks and Limitations
• SQLDataAdapters & System.Data.SQLClient

Chapter 11: SSIS DEPLOYMENTS, UPGRADES

• SSISDB Catalog Deployments, ISPAC Files
• Package Builds, Verification, Scripts
• Project Deployment Wizard Targets, Logs
• DB Catalog Folders & Projects Creation
• Package Executions – Scripts, Reports
• Package Validations, 32/64 bit Options
• Configurations & Parameter Management
• Package Jobs @ SQL Agent. Job Steps
• Job Schedules and Notifications Event Logs
• Package Security – SSISDB Logins, Users
• Folder Level and Project Level Security
• Project Migration Utilities, Upgrades
• Package Imports, Exports with ISPAC Files
• Command-Line Deployment,Execution Utility
• Package Execution & Validation Reports
• Package Versions and Restores, Rollbacks

Chapter 1: SSAS INTRO, CONFIGURATION

• Installation, Configuration of SSAS
• SSAS Component & – Operational Modes
• Multidimensional Mode : Properties, Usage
• Tabular Mode Purpose : Properties (ROLAP)
• PowerPivot Mode & Usage (Overview)
• Multidimensional Mode Instance Verification
• SSAS and SQL Browser Service Accounts
• SQL Server Data Tools / Visual Studio
• Developer Environment (SSDT) Interface
• SSAS Training Lab Plan, Resources
• OLAP Databases, Cubes For Analysis
• MDX: Multidimensional Expression Language
• DAX: Data Analysis Expression Language
• SSAS Architecture : XMLA and DMX
• SSAS Workflow and Sources in Real-world
• Data Source Configuration, DB Installations

Chapter 2: CUBE DESIGN WITH SSAS, EXCEL

• Cube Design with SQL Server Data Tools
• OLAP Data Source, Data Source View
• Measure Groups, Measures, Members
• Identifying Dimensions and Attributes
• Cube Design : Cube Wizard, Dimensions
• Add Attributes. Deployment, Cube Access
• OLAP Cube Process. Online Deployment
• Cube Browsing using SSMS, SSDT Tools
• Excel Connections for SSAS Cubes
• OLAP Cube Access, Pivot Charts
• Excel Pivot Tables, Chart Report Design
• Piechart Reports & Attribute Filters
• Common Deployment Errors : Solutions
• OLAP Server Impersonation – Settings
• OLAP Deployment Warnings, Solutions
• End to End Implementation of SSAS

Chapter 3: HIERARCHIES, MDX – LEVEL 1

• Data Source Views Named Calculations
• Named Querie, Dimension Attributes
• Explore Data with Data Source View
• Dimension Types : Dimension, Entity Level
• Cube Dimension on Entity Relations
• Hierarchies in Multidimensional Cube
• Grouping Attributes in Dimensions
• Testing Hierarchies : SSMS Cube Browser
• Testing Hierarchies : SSDT Cube Browser
• Multidimensional Expression Language
• MDX Queries Syntax, MDX Expressions
• MDX Axis Models. Cube into Rows
• Advantages of MDX: Reports
• MDX Queries with Attributes, Members
• MDX Queries on Hierarchies, Keys
• Members, Children, All Members
• SELECT in MDX with CROSSJOIN

Chapter 4: CALCULATIONS, MDX – LEVEL 2

• MDX Queries with WHERE, Except, Range
• NonEmpty Function, Multi-Member Values
• Parent, Children with MDX Hierarchies
• ORDER Function in MDX, Binary Sorts
• TOPCOUNT / HEAD, BOTTOMCOUNT
• CURRENT MEMBER, EMPTY MEMBER
• Filter Expressions with AND / OR
• Filter with and LEFT / RIGHT Range
• MDX Query Batches – GO Statement
• Limitations @ WHERE, Tuple Inverse
• ADOMD Client : MDX Query Processing
• MDX Calculations – Creation and Scope
• Calculations with MDX : Measure Level
• Calculations with MDX : Attribute Level
• Time Calculations with MDX Scripts
• TIME DIMENSIONS – Purpose, Advantages
• Time Attributes – Calendar / Fiscal
• BI Enhancements : Advantage, Usage Scope
• Time Enhancement Attributes, Hierarchies
• MDX Functions: YoY, YTD, QTD, MTD,

Chapter 5: PARTITIONS & AGGREGATIONS

• Partitions : Architecture, Tuning
• Storage, Slicing. Query Conditions
• Query and Table Binding in Partition
• Aggregations – Predefined Calculations
• Full, Default, None and Unrestricted
• Measure, Default Aggregations in OLAP
• Linking Aggregations and Partitions
• Additive & Semi-Additive Measures
• Storage Modes : Multidimensional
• Aggregation, Measure Group Storage
• Automatic, Scheduled, Medium Latency
• Low Latency and Custom Scheduling
• Proactive Caching, Silence Interval
• Cache Rebuild and Processing
• Perspectives – Purpose, Scenarios
• Dimension Usage for OLAP Relations
• Translations : Creation, Real-time Use

Chapter 6: KPIs, OLAP CUBE DEPLOYMENTS

• Key Performance Indicators (KPI) Design
• MDX GOAL, VALUE, STATUS & TREND
• Variance Computations. Format Options
• KPI Organizer, MDX Expressions
• FORMAT_STRING and MDX Operators
• MEMBER, SOLVE_ORDER Expressions
• Parent KPIs with MDX Hierarchies
• KPI Browser and KPI Conditions
• Drill-Up and Drill-Down in Excel
• SSAS Deployment Build, Configuration
• SSAS Deployment Options and Settings
• Deployment Targets: Transaction Options
• Deployment Wizard : Impersonation
• Deployment Accounts, Password Security
• OLAP Cube Security Roles, Partitions
• Key Error Logs and Error Locations
• Scripting Deployment. XMLA Scripts
• Processing Options – Full, Default

Chapter 7: TABULAR CUBE DESIGN

• SSAS Tabular Mode : Purpose, Usage
• SSAS Tabular Mode Server Installation
• Tabular Mode Server and DB Architecture
• Tabular Mode Advantages with Data Sources
• Tabular Mode : SSDT, Power Query, DAX
• Power Query, DAX for OLAP Cube Design
• In-Memory Vertipaq Storage – Performance
• Source Data Access Flexibility with ROLAP
• Business Intelligence Semantic Model
• Tabular Mode : Developing Data Models
• Workspace Server : Integrated, Dedicated
• Workspace Server and Integrated Options
• Compatibility Levels : Tabular Solutions
• Cube Design with SQL Databases : Imports
• Workflow with Tabular Mode Cube Design
• Data Sources, SSDT, Tabular Mode Design
• SSAS OLAP Environment, Cube Reports

Chapter 8: TABULAR MODE CUBES, DAX – 1

• Cube Design with SQL Server Databases
• Data Imports, Workspace Server Processing
• Identifying Tables (Entities) and Dimensions
• Attributes and Members and Relationships
• Measure Groups and Aggregated Measures
• Grid and Diagram Formats. Process Options
• BUILD, DEPLOY with Integrated Workspace
• Cube Browsing : “Analyze in Excel” Reports
• Tabular Mode Cube Design and Hierarchies
• DAX – User Interface and Data Types
• DAX Usage : DAX Queries, Basic Examples
• DAX Expressions and Real-time Usage
• DAX Aggregated Measures in DAX, Syntax
• Hierarchies and Levels in Cube Design
• Relations : Active,Inactive Relations
• Tabular Mode Cube : SSDT Imports
• Model Options : Process, ReCalculate
• Data Loads and Tabular Mode Explorer

Chapter 9: TABULAR MODE – DAX LEVEL 2

• KPIs (Key Performance Indicators) & Use
• Partitions in Tabular Mode Cube Design
• Using Power Query for Partition Design
• Power Query Expressions and Data Filters
• Import Data Options and ETL Operations
• Defining Measures with DAX Expressions
• Defining Perspectives for Cube Access
• Data Expression Language (DAX) Basics
• DAX Usage : Columns & Measures in SSAS
• Auto-generated Expressions in DAX, Usage
• Member Representations in DAX Queries
• DAX Functions, Expressions, Real-time Use
• Standard and Time Intelligence Functions
• DAX FILTER(), CALCULATE() Operations
• Time Dimension and YTD(), QTD(), MTD()

Chapter 10: OLAP DATABASE MANAGEMENT

• OLAP Backups – Multidimensional, Tabular
• OLAP Restores – Multidimensional, Tabular
• Detach, Attach Operations with OLAP DBs
• OLAP Database Processing, XMLA Scripts
• Cube Processing Jobs with SQL Agent
• OLAP DB Scripting with XMLA, Cloning
• OLAP DB Security Roles – MDX, DAX
• Partition Management – Split, Merge
• Cube Audits, Usage Based Optimization
• Cube Writebacks. Cube Updates with MDX
• Tabular Mode Cube Processing Options
• Direct Query and In Memory Processing
• In-Memory with Direct Query Processing
• Data Mining: Decision Trees, Clustering Alg
• Training, Testing Sets. Lift Charts & DMX
• Dimension Types: Role Playing, Degenerate
• Multidimensional – Tabular Comparisons

Chapter 11: Real-time Project for SSAS

• Working with SQL Server Data Sources
• Data Modeling and Relation Management
• Creating Bridging Tables and References
• Identifying Measures and Measure Groups
• Identifyig Attributes and Dimensions
• Adding Hierarchies and Attribute Relations
• Time Intelligence and BI Enhancements
• Cube Calculations with MDX / DAX
• Defining KPIs, Perspectives, Roles
• Cube Deployment Options with MS OLAP
• Adding Hierarchies and Attribute Relations
• Time Intelligence and BI Enhancements
• Cube Calculations with MDX / DAX
• Defining KPIs, Perspectives, Roles
• Cube Deployment & End user Cube Access
• DAX Queries in MS Excel (Excel Analyzer)

Chapter 1: SSRS INTRO, INSTALLATION

• Reporting Operations and Report Types
• Paginated Reports, Interactive Reports
• Analytical Reports & Mobile Reports
• Reporting Solutions (SSRS) and Tools
• Report Engine Architecture, Databases
• SSRS Report Server Installation
• Report Databases in SSRS and Usage
• Web Service URL : Connections, Usage
• Web Portal URL : Connections, Usage
• ReportServerDB, TempDB Configuration
• SQL Server Data Tools (SSDT)
• Report Builder, Mobile Report Publisher
• Report Design : Lab Plan, Data Sources
• 3-Phase Report Life Cycle (End-End)
• Report Builder Versus Report Designer
• Report Server, Web Service Integration

Chapter 2: BASIC REPORT DESIGN

• Working with SQL Server Data Tools
• Report Templates and Project, Solution
• Basic Reports – Understanding Entities
• Report Project Wizard Usage, Reports
• Data Source Connections and Databases
• Query Designer, Query Builder, Imports
• Table, Matrix Reports with Report Wizard
• Layout, Format – Drilldown Reports, Blocks
• Stepped Reports, Multi Field Drilldowns
• Report Template – Datasets & Reports
• Table Headers & Formatting Expressions
• Alternate Row Colors, Global Expressions
• Formatting Styles, Expressions, Reusability
• Expressions: IIF,Format,Ceiling,Round
• Textbox Properties: Date Format, Numbers
• Report Sources, Static/Dynamic Properties

Chapter 3: GROUPING, REPORT PARAMETERS

• Grouping : Row Groups, Column Groups
• Row Groups, Parent – Child Groups
• Adding Groups to Existing & New Rows
• Group Headers & Footers, Sub Totals
• Field Visibility, Toggle with Parent
• Row Group, Header/Footer Properties
• Column Groups for Table Report, Options
• Drill-down Report, Row Groups, Visibility
• Column Group Advance Mode. Fixed Values
• Repeating Column Headers on Every Page
• Creating Parameters, Dataset Conditions
• Single Value and Multi Value Parameters
• Dynamic Parameters, Dependency Queries
• SSRS Parameters with Dynamic Conditions
• Dataset Links to Parameters, List Values
• SSRS Expressions, Global Fields, Values
• Advanced Options : Auto / Manual Refresh

Chapter 4: CHARTS, DASHBOARDS, FITLERS

• Chart Reports – Design, Properties
• Series Values and Category Groups
• Report Categories with Series Groups
• Report Category Types and Differences
• Visualizations: Trend, Discrete Chart
• Clustered, Non Clustered Attributes
• Series Labels: Properties, Formatting
• Series Actions: Multi – Valued Parameters
• Report Actions: URL, Report Filters
• Dashboards : Creation and Real-time Use
• Multiple Chart Areas, Legends in Charts
• Dashboard Exports and Report Filters
• Static and Parameterized Report Filters
• Series, Markers Chart Areas, Limitations
• 3-Dimensional Report Properties, Visibility
• Range Charts, Data Bars, Area Charts
• Report Actions with Parameters, Joins
• Dataset and Toolbox Filters, Bookmarks
• Filters Vs Parameters – Difference
• Filter Conditions in Dataset, Toolbox

Chapter 5: EXPRESSIONS, SHARED DATASETS

• Shared Data Sources, Shared DataSets
• Date-Time Expressions with RDL Files
• FORMAT Function in SSRS, Parameters
• Data Type Conversions, Int / String Types
• String Functions, Page Breaks in SSRS
• LOOKUP Function, Dataset Joins in SSRS
• Field Value Replacement with Datasets
• Using LIST Item from SSRS Toolbox
• Field Expressions and Field Properties
• #VALX, #VALY, #PERCENT, #SERIES
• #LABEL, #AXISLABEL, #LEGENDTEXT
• 3D Pie Charts, Funnel and Tree Map
• 3D Funnel, Sunburst, Shape Charts
• Doughnut, Pyramid, 3D Pyramid Reports
• Parameterized Gauge Reports – Filters
• Indicators : Value, State Expressions
• RDL Expressions, Custom Functions

Chapter 6: REPORT BUILDER, GAUGES

• Report Builder Installation & Usage
• Differences with Report Designer Tool
• Data Source Creation with Report Builder
• Dataset Creation with Report Builder
• Dataset Design with Parameters, Filters
• Query Designer with Report Builder
• Toolbox Items Insertion and Properties
• Column Aggregates, Auto Group By Edits
• Adhoc Reports with Column Groups
• Dynamic Row Colors, Report Expression
• Gauge Reports: End User Access
• Report Types – Radial, Linear Gauges
• Indicators, Pointers, Scale Ranges
• Browser Compatibility, Offline Reports
• Gauge, Gauge Panel Properties, Filters
• Scale Properties, Values, Label Options
• Ranges & Labels, Items, Needle Options
• Parameterized Gauge Reports, Datasets

Chapter 7: REPORT BUILDER, MAP REPORTS

• Map Reports – Map Layers and Map Items
• Map Gallery – ESRI Share Files (Geo Data)
• SQL Server Data Sources, Geo Spatial data
• Business Analysis Dashboards For Maps
• Polygon, Tile, Line and Point Map Layer
• Map Visualization and Bubble Map Reports
• Data Fields, Labels, Visualization Indicators
• Fields to Visualize, Color Rules and Labels
• Editing Report Builder Reports in Designer
• SSRS Deployment: Report Designer Reports
• SSRS Deployment: Report Builder Reports
• Report Deployment – Builds, Config Files
• Webservice URL, Webportal URL Access
• Data Source, Data Set Folders, Report URL
• Deployment of Shared Data Sources
• Deployment of Shared Datasets, Reports
• Report Manager Uploads for RDL Files

Chapter 8: REPORT MANAGEMENT

• Data Source Management, Subscriptions
• Dependant Items, Security Operations
• Edit Shared Data Sources in Web Portal
• Shared Data Source Enable and Hide
• Connection Types, Edits and Security
• Shared Dataset Operations: Report Edits
• Data Preview, Downloads, Link Reports
• Report Security: Browser Role, User Access
• Content Management, My Reports, Publisher
• Report Builder, Report Definitions, Uploads
• Report Tuning: Caching, Rebuilds, Refresh
• Report Tuning: Report Snapshot, Schedules
• Subscriptions: Standard and Data Driven
• Email and File share Subscriptions in SSRS
• Schedules and Report Delivery Options
• Report Server Settings, Shared Schedules
• Report Timeout, Report Parts and Publish
• Report Builder Sub Reports, Report Parts

Chapter 9: MOBILE, KPI, CUBE REPORTS

• Mobile Reports : Creation & Usage
• Excel and Report Server Sources
• Working with Mobile Report Publisher
• Elements Layout: Master, Tablet
• Grids and Color Palette. Deployment
• RSMobile Formats: Uploads, Downloads
• Shared Dataset in Report Builder Tool
• KPI Reports: Design from Web Portal
• KPI: Value, Goal, Status, Trend
• KPI Visuals: Bar, Line, Step, Area
• Custom URLs and Mobile Reports
• Cube Reports with SSAS MDX, DAX
• SSRS Cube Reports with Parameters
• MDX Default Parameters, Options
• SSAS OLAP Cube Actions with SSRS

Chapter 10: PROJECT WORK in SSRS

• SQL Server Data Sources and Datasets
• Designing RDL [Paginated], Expressions
• Chart Reports, Line Reports, Options
• Dataset with Parameters and Filters
• Trend Analysis, Continuous Data Reports
• Data Bar Reports and Stacked Reports
• Multi-series Charts, Dynamic Chart Size
• Axis Display Control : Paginated Reports
• Parameters and Filters – When to use which
• Complete Project Solution & Explanation
• Work Flow Operations and Report Types
• Report Design, Builds, Report Deployment
• Report Edits, Data Source Changes to Azure
• Azure SQL Database for SSRS DB Source
• Report Management, Security, Subscriptions
• Report Tuning, Caching, Snapshot Options
• Project FAQs and Explanations for Resume

End to End Implementation (Real-time)

• End to End MSBI Implementation Process
• Project Requirements and SDLC Life Cycle
• Database Design and Entity Selection
• Understanding OLTP Databases, Relations
• Design of DWH : Data Warehouse Database
• SCD Techniques for ETL, Dimension Loads
• Fact Loads, STAR / SNOWFLAKE Schemas
• DWH Database Limitations for Analysis
• OLAP Databases for Data Analysis
• Cube Design and Operational Modes
• Mode Selection and Capacity Planning
• Tabular Mode OLAP Cube: Advantages
• Excel Analysis and Reports with MDX
• Using DAX for Modeling, Cube Reports
• Paginated Reports with OLAP, DWH
• MSBI Limitations, Need for Azure BI

Ch 1 : POWER BI BASICS

• Power BI Job Roles in Real-time
• Power BI Data Analyst Job Roles
• Business Analyst – Job Roles
• Power BI Developer – Job Roles
• Power BI for Data Scientists
• Comparing MSBI and Power BI
• Comparing Tableau and Power BI
• MCSA 70-778, MCSA 70-779 Exam
• Types of Reports in Real-World
• Interactive & Paginated Reports
• Analytical & Mobile Reports
• Data Sources Types in Power BI
• Power BI Licensing Plans – Types
• Power BI Training : Lab Plan
• Power BI Dev & Prod Environments

Ch 2: BASIC REPORT DESIGN

• Power BI Desktop Installation
• Data Sources & Visual Types
• Canvas, Visualizations and Fields
• Get Data and Memory Tables
• In-Memory xvelocity Database
• Table and Tree Map Visuals
• Format Button and Data Labels
• Legend, Category and Grid
• PBIX and PBIT File Formats
• Visual Interaction, Data Points
• Disabling Visual Interactions
• Edit Interactions – Format Options
• SPOTLIGHT & FOCUSMODE
• CSV and PDF Exports. Tooltips
• Power BI EcoSystem, Architecture

Ch 3 : Visual Sync, Grouping

• Slicer Visual : Real-time Usage
• Orientation, Selection Properties
• Single & Multi Select, CTRL Options
• Slicer : Number, Text and Date Data
• Slicer List and Slicer Dropdowns
• Visual Sync Limitations with Slicer
• Disabling Slicers,Clear Selections
• Grouping : Real-time Use, Examples
• List Grouping and Binning Options
• Grouping Static / Fixed Data Values
• Grouping Dynamic / Changing Data
• Bin Size and Bin Limits (Max, Min)
• Bin Count and Grouping Options
• Grouping Binned Data, Classification

Ch 4 : Hierarchies, Filters

• Creating Hierarchies in Power BI
• Independent Drill-Down Options
• Dependant Drill-Down Options
• Conditional Drilldowns, Data Points
• Drill Up Buttons and Operations
• Expand & Show Next Level Options
• Dynamic Data Drills Limitations
• Show Data and See Records
• Filters : Types and Usage in Real-time
• Visual Filter, Page Filter, Report Filter
• Basic, Advanced and TOP N Filters
• Category and Summary Level Filters
• DrillThru Filters, Drill Thru Reports
• Keep All Filters” Options in DrillThru
• CrossReport Filters, Include, Exclude

Ch 5 : Bookmarks, Azure, Modeling

• Drill-thru Filters, Page Navigations
• Bookmarks : Real-time Usage
• Bookmarks for Visual Filters
• Bookmarks for Page Navigations
• Selection Pane with Bookmarks
• Buttons, Images with Actions
• Buttons, Actions and Text URLs
• Bookmarks View & Selection Pane
• OLTP Databases, Big Data Sources
• Azure Database Access, Reports
• Import & Direct Query with Power BI
• SQL Queries and Enter Data
• Data Modeling : Currency, Relations
• Summary, Format, Synonyms
• Web View & Mobile View in PBI

Ch 6 : Visualization Properties

• Stacked Charts and Clustered Charts
• Line Charts, Area Charts, Bar Charts
• 100% Stacked Bar & Column Charts
• Map Visuals: Tree, Filled, Bubble
• Cards, Funnel, Table, Matrix
• Scatter Chart : Play Axis, Labels
• Series Clusters & Selections
• Waterfall Chart and ArcGIS Maps
• Infographics, Icons and Labels
• Color Saturation, Sentiment Colors
• Column Series, Column Axis in Lines
• Join Types : Round, Bevel, Miter
• Shapes, Markers, Axis, Plot Area
• Display Units,Data Colors,Shapes
• Series, Custom Series and Legends

Ch 7 : POWER QUERY LEVEL 1

• Power Query M Language Purpose
• Power Query Architecture and ETL
• Data Types, Literals and Values
• Power Query Transformation Types
• Table & Column Transformations
• Text & Number Transformations
• Date, Time and Structured Data
• List, Record and Table Structures
• let, source, in statements @ M Lang
• Power Query Functions, Parameters
• Invoke Functions, Execution Results
• Get Data, Table Creations and Edit
• Merge and Append Transformations
• Join Kinds, Advanced Editor, Apply
• ETL Operations with Power Query

Ch 8 : POWER QUERY LEVEL 2

• Query Duplicate, Query Reference
• Group By and Advanced Options
• Aggregations with Power Query
• Transpose, Header Row Promotion
• Reverse Rows and Row Count
• Data Type Changes & Detection
• Replace Columns: Text, NonText
• Replace Nulls: Fill Up, Fill Down
• PIVOT, UNPIVOT Transformations
• Move Column and Split Column
• Extract, Format and Numbers
• Date & Time Transformations
• Deriving Year, Quarter, Month, Day
• Add Column : Query Expressions
• Query Step Inserts and Step Edits

Ch 9 : POWER QUERY LEVEL 3

• Creating Parameters in Power Query
• Parameter Data Types, Default Lists
• Static/Dynamic Lists For Parameters
• Removing Columns and Duplicates
• Convert Tables to List Queries
• Linking Parameters to Queries
• Testing Parameters and PBI Canvas
• Multi-Valued Parameter Lists
• Creating Lists in Power Query
• Converting Lists to Table Data
• Advanced Edits and Parameters
• Data Type Conversions, Expressions
• Columns From Examples, Indexes
• Conditional Columns, Expressions

Ch 10 : DAX Functions – Level 1

• DAX : Importance in Real-time
• Real-world usage of Excel, DAX
• DAX Architecture, Entity Sets
• DAX Data Types, Syntax Rules
• DAX Measures and Calculations
• ROW Context and Filter Context
• DAX Operators, Special Characters
• DAX Functions, Types in Real-time
• Vertipaq Engine, DAX Cheat Sheet
• Creating, Using Measures with DAX
• Creating, Using Columns with DAX
• Quick Measures and Summaries
• Validation Errors, Runtime Errors
• SUM, AVERAGEX, KEEPFILTERS
• Dynamic Expressions, IF in DAX

Ch 11 : DAX Functions – Level 2

• Data Modeling Options in DAX
• Detecting Relations for DAX
• Using Calculated Columns in DAX
• Using Aggregated Measures in DAX
• Working with Facts & Measures
• Modeling : Missing Relations
• Modeling : Relation Management
• CALCULATE Function Conditions
• CALCULATE & ALL Member Scope
• RELATED & COUNTROWS in DAX
• Entity Sets and Slicing in DAX
• Dynamic Expressions, RETURN
• Date, Time and Text Functions
• Logical, Mathematical Functions
• Running Total & EARLIER Function

Ch 12 : DAX FUNCTIONS Level 3

• 1:1, 1:M and M:1 Relations
• Connection with CSV, MS Access
• AVERAGEX and AVERAGE in DAX
• KEEPFILTERS and CALCUALTE
• COUNTROWS, RELATED, DIVIDE
• PARALLELPERIOD, DATEDADD
• CALCULATE & PREVIOUSMONTH
• USERELATIONSHIP, DAX Variables
• TOTALYTD , TOTALQTD
• DIVIDE, CALCULATE, Conditions
• IF..ELSE..THEN Statement
• SELECTEDVALUE, FORMAT
• SUM, DATEDIFF Examples in DAX
• TODAY, DATE, DAY with DAX
• Time Intelligence Functions – DAX

Ch 13 : POWER BI CLOUD – 1

• Power BI Service Architecture
• Power BI Cloud Components, Use
• App Workspaces, Report Publish
• Reports & Related Datasets Cloud
• Creating New Reports in Cloud
• Report Publish and Report Uploads
• Dashboards Creation and Usage
• Adding Tiles to Dashboards
• Pining Visuals and Report Pages
• Visual Pin Actions in Dashboards
• LIVE Page Interaction in Dashboard
• Adding Media: Images, Custom Links
• Adding Chs and Embed Links
• API Data Sources, Streaming Data
• Streaming Dataset Tiles (REST API)

Ch 14 : POWER BI CLOUD – 2

• Dashboards Actions,Report Actions
• DataSet Actions: Create Report
• Share, Metrics and Exports
• Mobile View & Dashboard Themes
• Q & A [Cortana] and Pin Visuals
• Export, Subscribe, Subscribe
• Favorite, Insights, Embed Code
• Featured Dashboards and Refresh
• Gateways Configuration, PBI Service
• Gateway Types, Cloud Connections
• Gateway Clusters, Add Data Sources
• Data Refresh : Manual, Automatic
• PBIEngw Service, ODG Logs, Audits
• DataFlows, Power Query Expressions
• Adding Entities and JSON Files

Ch 15 : EXCEL & RLS

• Import and Upload Options in Excel
• Excel Workbooks and Dashboards
• Datasets in Excel and Dashboards
• Using Excel Analyzer in Power BI
• Using Excel Publisher in PBI Cloud
• Excel Workbooks, PINS in Power BI
• Excel ODC Connections, Power Pivot
• Row Level Security (RLS) with DAX
• Need for RLS in Power BI Cloud
• Data Modeling in Power BI Desktop
• DAX Roles Creation and Testing
• Adding Power BI Users to Roles
• Custom Visualizations in Cloud
• Histogram,Gantt Chart,Infographics

Ch 16 : Report Server, RDL

• Need for Report Server in PROD
• Install, Configure Report Server
• Report Server DB, Temp Database
• Webservice URL, Webportal URL
• Creating Hybrid Cloud with Power BI
• Using Power BI DesktopRS
• Uploading Interactive Reports
• Report Builder For Report Server
• Report Builder For Power BI Cloud
• Designing Paginated Reports (RDL)
• Deploy to Power BI Report Server
• Data Source Connections, Report
• Power BI Report Server to Cloud
• Tenant IDs Generation and Use
• Mobile Report Publisher, Usage

Ch 17: MSBI Integrations

• Power BI with SQL Server Source
• Power BI with SQL Data Warehouse
• Power BI with SSAS OLAP Server
• Power BI with Azure SQL DB Source
• Power BI with Azure SQL Warehouse
• Power BI with Azure Analysis Server
• Power BI with SSRS (RDL) Reports
• Power BI Report Builder Tool
• Installation & Configuration
• Paginated Reports Design, Use
• Data Sources, Datasets, RDL
• Report Publish (RDL) to Cloud
• Report Verifications, Data Sync
• Interactive Vs Paginated Reports
• Creating, Managing Alerts in Cloud

Ch 18 : REAL-TIME PROJECT

• Project Requirement Analysis
• Implementing SDLC Phases
• Requirement Gathering, FSA

Phase 1:

• PBIX Report Design
• Visualizations, Properties
• Analytics and Formating

Phase 2:

• Data Modeling, Power Query
• Dynamic Connections, Azure DB
• Parameters and M Lang Scripts

Phase 3:

• DAX Requirements, Analysis
• Cloud and Report Server
• Project FAQs and Solutions