MS-BI online training

SQL Server Analysis Services(SSAS)
Course Duration : 15 Hrs

SSAS Installation and Configuration

  • Introduction to SSAS 2012 & 2014
  • Multidimensional Mode Configuration
  • Tabular Mode Configuration, In-Memory
  • Testing SSAS Workspace Servers in SSDT
  • Understanding Real-world Databases
  • Need for OLAP Databases and Mining
  • Need for Cubes and Faster Reports
  • Identifying OLAP / Analysis Entities
  • Kimball and Inmon Methods – BI Design
  • SSAS Usage and Target Access Methods
  • Column Storage and Row Storage Options

Basic SSAS Entities & Usage

  • Data Source and Data Source Views
  • Computed Columns and Attributes
  • Building Relationships For Cubes
  • Identifying Facts and Dimensions
  • Identifying Measurable Aggregates
  • Identifying Attributes and Hierarchies
  • Deciding Cube (MOLAP) Schema
  • Named Queries and Relations in DSV
  • Database Dimensions from DSV Entities
  • Cube Dimensions from DSV Relations
  • Cube & Database Dimensions – Issues

Dimension Wizard and Attributes

  • Creating Multi-Dimensional Entities
  • Attributes and Key Member Names
  • Cube Dimension Templates and Options
  • Applying Time Based Dimensions
  • Dimension Hierarchies and Limitations
  • Cube Attribute Relations and Uses
  • Composite Attribute Keys in OLAP Cubes
  • Building Attribute Levels and Hierarchies
  • Duplicate Attribute Key Errors
  • Common Deployment and Security Errors
  • Deployment Warnings and Solutions
  • Real-world Deployment Strategies
  • Deployment Challenges (Issues), Precautions

Cube Wizard and Relations

  • Cube Design – Regular Templates
  • Cube Measures and Dimension Keys
  • Additive and Semi Additive Measures
  • Cube Customization Options and MDX
  • Calculations and MDX Members
  • MDX Scripts for Member Calculations
  • Cube Calculation Sets and MDX Scripts
  • BI Enhancements and MDX in MOLAP
  • Time Based Calculations with MDX

Dimension Types & KPI

  • Role Playing Dimensions and MDX
  • Fact and Factless Dimensions
  • Referenced Dimensions & Usage
  • Conformed Dimensions & Usage
  • Degenerate Dimensions & Usage
  • Junk & Hierarchical Dimensions
  • Dimension Usage and Granularity
  • Building Fact Less Dimensions
  • Static and Dynamic KPIs with MDX
  • Parent and Child KPIs with MDX
  • Defining KPIs and Goals using MDX
  • Perspectives using MDX Expressions
  • SSAS Actions with MDX and XMLA

SSAS Partitions and Aggregations

  • Local and Remote Cube Partitions
  • Storage Modes – MOLAP, ROLAP, HOLAP
  • Aggregations and Proactive Caching
  • Tuning Cube Partition Processing
  • Row Estimations and Aggregations
  • Assigning Aggregations, XMLA Scripts
  • Usage Based Optimization (UBO) Options
  • UBO Limitations and MDX Query Filters
  • Reusing Aggregation Scripts & Cache Options
  • Proactive Caching Limitations in OLAP
MDX Functions, FILTERS and JOINS

  • MDX Functions & Conditional JOINS
  • Complex MDX Queries & FILTERS
  • CROSSAPPLY versus MDX JOINS
  • CROSSAPPLY and MDX Query Filters
  • Cube and Dimension Write backs
  • MDX with Roleplaying Dimensions
  • MDX Transactions & Limitations

Data Mining and Forecast

  • Need for Data Mining Structures
  • Data Mining Algorithms and Rules
  • DMX Queries and Forecast Reports
  • Data Mining Scope and DMX Ranks

Tabular Mode Cube Design

  • Entity Identification Procedures
  • Workspace Configuration Settings
  • In-Memory Processing Options
  • Advance Cube Design Scenarios
  • Aggregated Measures with KPIs
  • Partitioned Dimensions & Usage
  • Conditional MDX Scripts & Options
  • Hierarchical MDX in Tabular Mode
  • MDX Filters and Joins in Tabular Mode

Deployment (Tabular & Multidimensional)

  • Deployment Options and Pre-requisites
  • Cube Deployment Process and Tools
  • SSAS Deployment Wizard Tool & Errors
  • Storage Locations – DB, Cube, Partitions
  • Creating OLAP Databases, Scripts
  • Processing Options and Schedules
  • Managing Deployment Process (OLAP)
  • SSAS Database and Cube/Mining Audits
  • MDX Query Log Tables in DB Engine
  • Flight Recorder Options and Sampling
  • Managing SSAS Log Providers
  • SSAS Flight Logging Options

OLAP Database Management

  • OLAP & Tabular Database Backups
  • OLAP Compressions and Encryptions
  • Backups, Restores and Passwords
  • Scheduling Backups and Jobs
  • OLAP DB Synchronizations and XMLA
  • Partitions – Splits and Merge Options
  • Managing Partitions & Aggregations

Usage Based Optimization (UBO)

  • Need for Optimization and Audits
  • Designing Aggregations using UBO
  • Aggregations For Optimization Levels
  • Audit Query Filters and Reuse Options
  • OLAP Restore Options and XMLA

Using OLAP Structures

  • Cube Analysis with Excel Tables
  • Excel Pivots and Refresh Options
  • Configuring ODC (Office) Connections
  • Prediction Functions and Analysis
  • DMX and MDX Queries Interfaces

Managing Security with MDX

  • SSAS Security Roles and Scope
  • Object Level Access and MDX
  • Item Level Security and DAX
  • Dynamic Cube Security with MDX
  • Security Audits and Query Logs

Schedules and SSAS Upgrades

  • Using SSIS for OLAP Management
  • MDX and OLAP Operations in SSIS
  • Scheduling SSAS Operations (XMLA)
  • OLAP Command Job Steps and Working
  • OLAP Query Job Steps and Working
  • Comparing SSAS 2012 & 2014
SQL Server Integration Services(SSIS)
Course Duration : 15 Hrs

Introduction to SSIS & Data Warehouse

  • Introduction to MSBI Tool and SSIS
  • Need for SSIS and ETL / DWH Entities
  • Data Warehouse Design and SSIS
  • DW Components and SSIS Tools
  • Configuring SSIS Tools and Options
  • SSIS Configuration and Catalog DB
  • Control Flow Tasks Architecture
  • Data Flow Tasks and ETL Architecture
  • Data Pipelines and Data Buffers

Basic ETL Entities in SSIS

  • SSIS Data Types and Data Conversions
  • SSIS Working and Control Flow Options
  • SSIS Local and Global Variables – Usage
  • Dynamic Precedence Constraints
  • Dynamic Connection Managers in DFT
  • Data Flow Transformations and Usage
  • Handling ETL Enumerations in SSIS
  • ETL with Loops & Indexed Connections
  • Fast Load Options in OLE-DB Connections

SSIS Expressions & Package Debugging

  • Conditional Precedence Expressions
  • SSIS Parameters Usage and Variables
  • Control Flow Breakpoints – Usage
  • Data Flow Data Viewers and Audits
  • SSIS Expressions &Debugging Options
  • Debugging SSIS Packages & Limitations
  • Debugging Procedures in Real-time

Data Flow Transformations and ETL

  • Row and Column Transformations
  • Combining and Splitting Data
  • Performing SQL Database Operations
  • MERGE Vs UNION ALL Transformations
  • Lookup, Fuzzy Lookup and Grouping
  • SORT Vs AGGREGATE Transformations
  • OLE-DB Command Usage & Parameters
  • Derived Columns & Data Conversions
  • SSIS Parameter Mappings & Options
  • Tabular Data Stream & Commit Size
  • Table Locks & Batch Calculations

SSIS Customizations with .NET

  • SSIS SCRIPT Task (.NET) and Coding
  • Scripts for Control Flow Limitations
  • Scripts for Data Flow Limitations
  • ADO Connections and DataAdapters
  • Custom Code Handling in SSIS
  • Script Tasks & Notify Operators
  • SSIS Expressions and Iterations

SCD Transformation – ETL / DW

  • Data Analytics and Column Profiling
  • Data Sampling and Data Delta
  • Type I and Type II SCD Examples
  • Dimension Table Design, Surrogate Keys
  • SCD – Business Keys and Attributes
  • SCD – Historical Attributes and Status
  • Event Notifications and Emails
  • ETL – Partial and Full Cache Options
  • Fact Table Loads and ETL Load IDs
  • PIVOT, UNPIVOT and WebService T/Fs
  • TDS Sizing and Memory Options

Checksum Operations for ETL/DW

  • CHECKSUM Transformationin DW
  • CHECKSUM Transformation Logic, Installation
  • Using CHECKSUM for ETL and DWH
  • Working with Derived Parity Options
  • Partial and Full Cache Options
  • SSIS Transactions & Isolation Levels
  • Checksum for Type 1 and Type 2
  • SSIS Checkpoints and Limitations
  • Connection Options and Retaining Stats
  • Tuning Techniques  with Checksum T/F
  • Practical Issues with Checksum – Alternatives
Master – Child Packages & Fact Data Loads

  • Configuring Master Packages, Parameters
  • Configuring Child Packages, Parameters
  • Parameter Bindings, Execute Process
  • Using  CDC Transformation and Controls
  • CDC Source and CDC Splitter Options
  • CDC State Options and Usage
  • Using CDC for Type I & Type II ETL Loads
  • CDC Connections – Performance Advantages
  • OLE DB Transformation and Parameters
  • ADO.Net Connection Limitations in SSIS
  • Solutions to Data Delta Options
  • Need for Fact Tables and Techniques
  • Dimensions to Fact Table Relations in DWH
  • Fact Table Design and Data Load Techniques
  • Dimension to Fact ETL Operations
  • Generating Time Dimensions for DWH with ETL
  • Master Child Packaging for Fact Loads

SSIS – Events, Errors and Logging

  • SSIS Package Events and Audits
  • Package Level Event Handling Options
  • SSIS Event Bubbling Escalations
  • SSIS Logging & Project Connections
  • Windows Event Logs and SSIS Jobs
  • SQL Server Logging and Audit Tables
  • Global Connections and Project Audits
  • Logging Fuzzy Data Loads and ETL
  • Need For XML Connections & ADO
  • XML Connections & BLOB Data
  • Using SSIS Data Profile Viewer Task
  • Reusing SSIS Pipeline Execution Plans

Deployment Procedures

  • Project Builds & Bit Level Options
  • Creating ISPAC Files : SSIS 2014, 2012
  • Securing SSIS Manifest Files (PROD)
  • 64 bit and 32 bit Configurations
  • Validation & Verification Techniques
  • Configuration Options and Connections
  • Control Loops and Indexed Connections
  • Migrating ISPAC Files – Options
  • Package Backups and Package Passwords
  • Project Level Passwords and Encryption

SSIS Project Deployments& Security

  • SSIS Deployment and SSIS Catalog DB
  • SSIS Catalog Project Files and Options
  • Security – Logins, Users, SSISAdmin Roles
  • SSIS Catalog Folders in SSIS Database
  • Post Deployments and Connections
  • Package Configurations, Parameters
  • SSIS Package Validations and Reports
  • SSIS Package Executions and Reports
  • Checksum Management and Encryptions
  • SSIS Package Executions with T-SQL Scripts
  • Tuning Deployment and Configurations
  • SSIS Package Performance Reports
  • SSIS Package Security Options
  • Encrypting SSIS Catalog Packages
  • SSIS Package Store and SSISDB
  • Security Audits Reports and Limitations
  • SSIS Folder Level Security Options
  • Project Level Security and SSIS Roles
  • Container and Package Logging

SSIS Package Upgrades

  • SSIS Project Migration Utilities
  • Importing ISPAC and Manifest Files
  • SSIS Project Import Wizard – Usage
  • Upgrading Packages & Configurations
  • Command-Line Deployments & Options
  • Package Backup Procedures & Options
  • Scheduling Packages (Jobs), Operators
  • Upgrading SSIS 2012 to SSIS 2014
  • MSDB and SSIS Catalog DB Files
  • Comparing SSIS 2012 and 2014
  • SSIS ETL and DW – Comparisons
SQL Server Reporting Services(SSRS)
Course Duration : 15 Hrs

Introduction to SSRS & Usage

  • SSRS Architecture and Tools
  • Three-Phase Report Life Cycle
  • Reporting Services Configurations
  • Report Server Database and TempDB
  • SSRS 2012 and Web Service URLs
  • SSRS 2014 and Web Service URLs
  • Communication Ports & Encryptions
  • Execution Accounts and Usage
  • Report Designer Usage and Scope
  • Report Builder Usage and Scope
  • Report Manager and Options
  • RSCONFIG Tool and Limitations

Report Planning and Design

  • SSRS Report Authoring Options
  • Data Sources (RDS) Connections
  • Credentials and Security Options
  • Toolbox Items and Dataset Options
  • Table Reports and Matrix Reports
  • Report Images, Textbox, Global Fields
  • Dynamic Expressions and Options
  • Reporting Functions and Formatting
  • Report Headers and Report Footers
  • Enterprise and Ad-hoc Report Design

Report DataSets and Joins

  • Working with Multiple DataSets
  • Joining Local and Shared Datasets
  • Configuring Dynamic DataSets
  • Working with Remote Datasets
  • Reusing XML and Shared Datasets
  • Dataset Import Options in RDL

Report Design and SubReports

  • Designing Wizard based Reports
  • Custom Reports and Report Groups
  • Row Drilldown and Column Grouping
  • Advanced and Static Options in SSRS
  • Chart Based Reports and Images
  • Using SSRS Expressions & Global Fields
  • Sub Report Options with Parameters

Report Parameters & Usage

  • Report Parameters & Properties
  • Dynamic Report Parameters and Filters
  • Dependent Parameters & Usage
  • Multi Valued Null Parameters
  • Report Filters – Options and Types
  • Toolbox Filters and Limitations
  • Sub Reports and Report Actions
  • Parent-Child Report Parameters

Enterprise Reports in SSRS

  • Gauge Reports, Indicator Reports
  • Pie Charts, Bars and Spark Lines
  • Rectangle Reports and List Reports
  • Report Testing and Auto Refresh
  • Map Reports and Report Dash Boards
  • Multiple Chart Areas in Dashboards
  • Report Dashboard & Limitations
  • Report Scorecards with SQL Queries
  • Exporting RDL Reports in SSRS
  • LOOKUP Functions – Limitations
  • Report Parts and Sub Reports
  • Sub Reports versus Report Actions
  • Accessing and Controlling Reports
  • Map Reports and Stored Procedures
  • Understanding Layouts and Map Links
Power View with Report Builder Reports

  • Power Pivot & PowerView Add ins
  • Power View for RDLX Reports
  • Understanding Data Models
  • Tables, Charts and Data Bars
  • Sparkline and Data-Mashup
  • Report Builder Vs Power View
  • Managing RDL and RDLX Reports
  • Comparing PowerView and Report Builder

Cube Reports in SSRS

  • Indicators Usage with Map Reports
  • Designing Reports using OLAP DBs
  • Identifying KPIs and Measures
  • MDX Queries with Cube Reports
  • MDX Filters with Sub Reports
  • Scorecards with MDX and DAX
  • Working with Parameterized SPs for Reports

Report Builder Reports

  • Configuring Report Builder for Offline Use
  • Designing DataSets in Report Builder
  • Chart and Map Wizard in Report Builder
  • Report Parts and Web Services
  • Report Designer Vs Report Builder
  • Publishing Report Parts with RB
  • Stored Procedures with Report Builder
  • Report Downloads and Edit Options
  • Report Builder – Limitations

Report Management Options

  • Report Manager Configurations
  • SSRS Report Builds and Deployment
  • Managing Data Source Security
  • Exporting and Importing Reports
  • Snapshot Management & Schedules
  • ReportServerTempdb Usage & Tuning
  • Linked Reports & Null Parameters
  • Report Caching and Memory Refresh
  • Managing Reporting Engine & Tuning
  • Report Execution Audits & Events
  • Tuning SSRS Reports & Memory Options

Subscriptions and Report Builder

  • Global and Shared Schedules
  • Report Subscriptions and Advantages
  • Email and File Share Subscriptions
  • Creating Standard Subscriptions
  • Creating Data-Driven Subscriptions
  • Data Source Credential Store Options
  • Subscription & Logging Options
  • Report Uploads and Downloads
  • Report Sites and Parameter Options
  • Report Builder Connections & Caching
  • Report Builder Reports to RDL Entries

Report Security and Upgrades

  • Data Source Security Credentials
  • Report Server Security Levels
  • Security Options and Report Users
  • Content Manager and My Reports
  • Cloning Report Roles and Schedules
  • Report Browser and Report Publisher
  • Report (RDL) Configuration Options
  • Managing Configurations Files
  • RSConfig & Encryption (snk) Keys
  • SSRS Reports Upgradation Options
  • Scale-out Deployments in SSRS
  • RS Execution Accounts and Identity

Online Training Enquiry