MSBI - Vinay Tech House

Home / Business Intelligence Courses / MSBI

MSBI Training

This course covers SSIS, SSRS, SSAS, DQS, MDS, DAX, and MDX. We Provide Detailed Materials, Practice Softwares, Software Installation Documents and Daily Class Videos. Adequate knowledge on SQL provided. Includes Two Real-Time projects & Multiple Case Studies

Classroom | Online | Fast Track | Self Paced

Course Highlights

Live Projects

Placement Assistance

Expert Trainers

Premium Training

Live Video Share

Real-Time Case Studies

Frequently Asked Questions

What is MSBI and What are it's uses?

MSBI ( Microsoft Business Intelligence) is a SSBI ( Self Service Business Intelligence) Suite which generates effective Reports and Dashboards from data using Multiple Tools.

a) SSIS (SQL Server Integration Services): To Gather Data, Transform Data and Load Data
b) SSAS ( SQL Server Analysis Services): To Create Semantic Models by Converting Normal Data into Decision Making Data
c) SSRS (SQL Server Reporting Services): Create Reports and Dashboards from the Data and Semantic Models.
d) DQS (Data Quality Services): Ensure the Quality of data by profiling, scrubbing and matching.

Is MSBI easy to learn?

It is easy to learn because of Low-code components and flexible interfaces.

Does MSBI have a market?

Yes, Microsoft is the leader in Analytics and Business Intelligence since 16 years. It is providing its wonderful support to the current cutting edge processes and technologies in multiple versions. And On-Premises and Cloud Power BI and Cloud Azure BI providing other BI supportive services beside MSBI. Kindly visit the following links to know more about the Microsoft BI Market & Leadership

- https://powerbi.microsoft.com/en-us/blog/microsoft-named-a-leader-in-the-2022-gartner-magic-quadrant-for-analytics-and-bi-platforms/

- https://powerbi.microsoft.com/en-my/blog/microsoft-named-a-leader-in-the-forrester-wave-augmented-bi-platforms-q3-2021/

What if I miss a training session?

No need to worry, daily videos are in the class room folder and materials in the materials folder. You can watch and practice and also reach out to the trainer for queries.

What type of study materials and videos we will get and how long they are available?

You will get all materials after a week of your joining which you can download and use. Daily live class recordings will be placed in a folder and you will have access for maximum 12 months. Lab practice documents and required Power BI credentials provided for practice till one year.

Quick Enquiry

Why Join Vinay Tech

Why join Vinay Tech

In-Depth Curriculum

Our educational program aims to comprehensively cover all & important points & aspects of the subject

Course Specialized Trainers

Our Trainers are high experienced industry experts with their teaching methods designed for their particular course

10+ Years of Experience

We’ve come a long way teaching from hundreds to thousands, so we use our experience in guiding students in the right path

Students Favourite

Vinay Tech House is prefered by many students as their favourite, rating as the best in teaching

Curriculum

MSBI Content, Data Warehouse, Data Model Fundamentals & Project Content

IT Data Fundamentals & MSBI Existence

Data, Data Availability in IT
Data, Data Availability in IT
IT data storage areas
Datawarehouse, BI definitions and layers
MSBI existence in IT, Product services
MSBI Tools and Components
MSBI Opportunities

MSBI and Competitiveness

What are BI, MSBI, and SSBI? Importance in the current Enterprise?
Competitive advantages
MSBI vs Other BI tools
MSBI 2016 Vs Others (2014, 2012, 2008, 2005)
Hardware & Software requirements
Instances and installation methods

Tools & Utilities to work with MSBI

SSDT, BIDS, SSMS, DQS Client / Server / Installer
Sqlserver Profiler, Deployment Wizards
Command Line Utilities
a) DTUTIL b) DTUTILEXEC c) RS RSCONFIG d) ASCMD etc…

Data warehousing fundamentals

Data definitions (Data, Information, BI, Mining, ODS, DSS, Analysis, OLTP, OLAP, OLCP)
Dwh Characteristics, Principles
Dwh approaches (Top down/Bottom up)
Dwh Life Cycle steps
Data Mart & Types
Data Processing Types (OLAP)
Granularity and various granularities
Diff between OLTP and OLAP

Dimensional Modeling Fundamentals

Dimension, Dimension Table & types
Fact, Fact Table & measures types
Fact less fact table
Various schemas (Star, Snow Flake, Galaxy & Hybrid )
Surrogate key and usages in real time
Model, Business, Conceptual, Logical, Physical Data Models

SQL Server Integration Services [ SSIS ( Basic-Advanced ): 15 hrs ]: 2016 & 2014

SSIS in Real-Time projects (DWH and OLTP)

SSIS resources in the BI and Non BI projects
SSIS collaboration with other DWH tools
SSIS strengths than contemporary ETL tools
SSIS installation (SSDT, SSMS) and observation
SSIS real time projects flow and explanation
SSIS Development / Support responsibilities
SSIS Testing, Deployment, Versioning, Scheduling, Defect Handling
Normalization vs demoralization in DWH
Dimensional Model (Facts & Dimensions)

SSIS-2016 Logical & Physical Architectures

What is SSIS? Detailed logical architecture and components
SSIS-2016 newly added features, disconnected features, enhancement and break down features
SSIS strengths than contemporary ETL tools
SSIS Practical architecture, Solution, Project and Package creations and executions
Connection Managers, Shared Data Sources,
Parameters(2012,2014,2016)
Discussion on new Features in SSIS 2016
Balanced Data Distributor,
OData v4 data sources & and Bug Fixes
Data Feed Publishing Components

SSIS resources and responsibilities ETL operations in SSIS (Data Flow Task) (Extract, Transform and Load)

Objective of Dataflow and process flow
In memory model and performance tips
Aggregate, Data Conversion, Derived column, Character Map, Audit
Merge, Merge Join, Union All, Sort
Multicast, Conditional Split, Oledb command
Lookup, Fuzzy lookup, Copy column
Pivot, Un pivot, Term Extract, Term Lookup
CDC, Data Cleansing, Script Component
Checksum transformation (2014) real usage

Implementing SCDs, CDC in DWH using restarts, transactions and data tracking

Using Check points to configure restarts and preserve operations
Transactions for historical and business evaluations
Implementing Slowly Changing Dimensions in Data warehouse using No, Full and Partial chches
history tracking of dimension (Type1)
Full history track and methods (Type2)
Partial history track and need (Type3)
Manual Implementation of SCDs, Incremental (Delta) Loading
Change data capture in data warehouse tables for the events insert, update and delete on OLTP

Versioning and Scheduling in DWH projects

SSIS versioning, other versioning tools
SSIS Scheduling, other versioning tools
Jobs, Proxy and Sqlserver agent account

SSIS ETL, Windows and Sql Server Admin tasks (Control Flow Tasks and Operations)

Looping usages (For Each loop, For loop) and various scenarios (Files, Datasets, XMLs, Excels)
Transactional operations and Parallel processing implementation using Sequence container
Using File System to move, copy, create folders
Execute Sql for Single, Multi SQL, Procedure, functions with Various parameters and implementing DML, DDL statements
Working on Master, Child Packages using Execute Package task and variables
Expression task for evaluations, iterations
Using Script task for customized operations
Execute process task for Zip, Exe Operations
Profiling and cleansing the data using Data Profiler, DQS cleansing operations
Controlling tasks using Precedence Constraints

Data Loading, Fact and Dimensions load

Direct load and components (Truncate & Load)
Fast load implementation in SSIS
Incremental load in warehouse tables
Wizards, Tools, Tasks for different types of load

Debugging, Logging and Event handling

Trouble shooting and Bottlenecks identification using DataViewer, BreakPoint, Hit count debug methods
Real time Logging, usages and observations
Log Providers and Catalog Logging
Implementing Basic, Verbose and Performance
Event vs Error Handling, real time handling

Command Line Utilities Utilization

Various environments operations of packages using a) DTUTIL b)DTEXECUI c) DTEXEC

Configurations and Parameters in DWH

Various ways of configuring SSIS packages (Legacy)
User interactions to the packages for incremental load
Parameters and environments in Catalog Database

Security, Deployment in Real Rime projects

Security in various ways (Password protection & Role based security), DBA role in security
U Package deployment using manifest file (File System, Sqlserver deployment)
Project deployment using Catalog database (SSDB) and explaining various features

Bottlenecks, Performance Tuning

Production issues in real time, other pkg issues
Bottleneck, various ways to identify bottlenecks
Tuning Source, Destination, Transformation,Packageand System level
Data pipeline optimization, in memory model
Execution trees, memory buffer optimization

SSAS

SSAS in Real-Time projects (DWH & BI)

SSAS resources in the BI projects
SSAS collaboration with other DWH tools
SSAS strengths than contemporary tools
SSAS installation (SSDT, SSMS) and observation
SSAS real time projects flow and explanation
SSAS Development / Support responsibilities, SSAS Processing
SSAS Testing, Deployment, Versioning, Scheduling and Defect Handling
Normalization vs demoralization in DWH
Dimensional Model (Facts, Dim) process

BISM (Business Intelligence Semantic Model) role in analysis and data mining

SSAS 2016, 2014 and 2012 discussions
Installation of SSAS, Analysis Modes
Multidimensional model and real time usage
Tabular mode and real time usage
Power Pivot and real time usage
Over view of SSBI,Multidimensional Model, Tabular Model and Power Pivot
Power Pivot for Excel and Share Point
What is OLAP, Types of OLAP (MOLAP, ROLAP, DOLAP, HOLAP)
Sample project, Modeling, Designing & Solution

Multi-dimensional model (Cube Data mart) creation in real time with various settings

Developing Analysis Services Multi Dimension Corporate Solution with SSDT tools, Command lines.
Data source view creation
Add / Remove tables
Design / Diagram panes
Named calculations
Named Queries
Relationships
Identifying dimensions with types (Date, Time, Regular, Scenario etc…)
Creating, Deploying and Processing of cubes

Kimball approach of Facts, Dimensions add / removal (configuring measures and dimensions)

Working with Measures, Measure Groups,Members and Dimensions in data warehouse
Maintaining measures in analytical format using various properties
Configuring Dimensions, taking all attributes, composite keys, Duplicate Attribute Keys
Discretization feature, Sort order
Hierarchies, levels, members, performance improvement features implementation
Linking dimensions , gathering/ collecting calculations, KPIs and actions

Semantic operations on Cube and its functionalities in DWH

Writing various calculations using MDX
Preparing script with multiple members
Pros and cons of various members
Implement Key Performance Indicators for effective report analysis
Pull model analysis with Actions
Implementing Perspectives / Translations to restrict visibility and for multi-language translation

Effective relationships (Dim & Fact)

Working with Dimension Usage Wizard
Real time usages of various relationships (Regular, Referenced, Fact etc…)

Parallel processing of data using Fact Partitions and various ways of processing

Implementing Partitions (Table and Query)
Usage of various storage modes
Automatic processing using Proactive Caching
Cube, Fact and Dimension table processing (Full, Clear, Data, Incremental, Add, Index, Update, Default etc…)

Faster retrieval of data using Aggregates design and Usage based optimization

Working with Designing Aggregations
Normal Mode and advanced mode
Usage based optimization

Working with MDX and components

What is MDX, MDX Fundamentals
Identifiers, Expressions & Literals
Data Types & Operators
Functions (twelve types of functions)
MDX language reference DDL, DML etc…
MDX multi-function operators
Sub cube expressions
Member, Measure, Tuple, Set features
Advanced Time and Date functions for cumulative preceding and following analysis
MDX Querying against OLAP Cube

Data Analysis Extensions [DAX]

Need of DAX in tabular models, Excel
Expressions in DAX
Identifiers, Literals and Operators
Functions and various types of functions

SSAS Deployment, real time methods

BIDS / SSDT deployment
SSAS deployment wizard
XMLA method of deployment XMLA method of deployment
Backup and Restore
SSAS synchronization wizard
AMO objects (Analysis Services Management Objects)

SSAS Cube and MDX Performance tuning

Production issues while cube processing
Memory, Time and threads issue at MDX
SSAS Database and Cube Audits - Query Logs
Security Audits and Need for Optimizations
DB Audits, Usage Based Optimization
Aggregations, Usage Based Optimization Options (MOLAP) and Filters
Partitions & Query binding
Named Calculations, Queries and Named Sets
Flight Recorder Settings, Query Logs
Data Sampling Intervals and MDX Query Tuning Process
100% Aggregations (FULL), CPU and Space Thresholds
Proactive cache solution for fresh update
Lazy Aggregations & UBO in Partitions, Performance Settings
Writeback option to save OLTP analysis
Writeback Partitions - Cube & Dim, UPDATES
Using MDX Expressions and Queries
Writeback Tables, MDX Transactions - COMMIT / ROLLBACK
Subcube - Creation, Advantages Usage. MDX Query Plans
Subcube - DESCANDANTS, PARENT & MEMBER Functions
SSAS threads, taking required number of threads
SSAS Trace Monitoring and Sql server Profiler

SSAS-TABULAR MODEL CONCEPTS

Workspace Database, Workspace Server, Direct Query, Backup to disk
Installation steps, error mechanisms and error rectification process
Creating a tabular model and setting model properties
Adding data to the model, renaming tables, filtering columns
Rename Columns
Monitoring relationships, Providing relationships
Create Hierarchies
Create partitions
Create perspectives
Create Roles
Create KPIs
Deployment
Using in Excel Power Pivot and PowerView

SQL Server Reporting Services [ SSRS ( Basic-Advanced ): 15 hrs ]: 2016 & 2014

SSRS in real-time projects (DWH-BI, OLTP)

SSRS resources in the BI and OLTP projects
SSRS various visualizations for analysis
SSRS and general Report Life Cycle Steps
SSRS collaboration with other DWH tools
SSRS strengths than contemporary tools
SSRS installation (SSDT, SSMS) and observation
SSRS real time projects flow and explanation
SSRS Development / Renderer responsibilities, SSRS Subscriptions
SSRS Publishing, and real time scheduling

SSRS Architecture and Components

SSRS 2016, 2014 and 2012 discussions
SSRS Reporting Components (Report Server, Report Manager, and Databases etc…)
SSRS Models (Push and Pull)
SSRS report store and retrieve flow

SSRS Administration (Tools and Configuration files) and Management

RS Manager Configuration Tool
Configuration Files for install and configure
SharePoint mode of explanation & install
Create / modify Report manager/ Server / Database Creation and Monitoring
Start / Stop report server

Working with various Report facilities

Report Server Wizard
Report Server Project
Report Builder
Power View
Dashboard

Report Design, Sub Reports & Groups

Designing Wizard based Reports
Custom Reports and Report Groups
Column groups and row groups
Child column and row groups
Row Drilldown and Column Grouping
Advanced and Static Options in SSRS
Chart Based Reports and Images
Using SSRS Expressions & Global Fields

OLTP and OLAP Reports in SSRS

Table Reports, Tablix Properties
Gauge Reports, Indicator Reports
Pie Charts, Bars and Spark Lines
Report Testing and Auto Refresh
Map Reports and Report Dashboads
Multiple Chart Areas in Dashboards
Report Dashboard & Limitations
Report Scorecards with SQL Queries
Sparkline, Databars in Reports
Lines, PlaceHolder usages in reports
Images acquisition in the reports
Exporting RDL Reports in SSRS

Excel, Map, Cube, XML Reports

Excel reporting with DAX
XML reporting with / without XSD
Map Reports and Stored Procedures
Indicators 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
Sub Reports versus Report Actions
Accessing and Controlling Reports

Report Model, Builder & Power View

Report Model and Features
Model and DataSets use 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
Power Pivot & PowerView Addins
Power View for RDLX Reports
Understanding Data Models
Tables, Charts and Data Bars
SparkLines and Data-Mashup
Report Builder Vs PowerView
New Report Builder Extensions
Report Builder Mobile Edition

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 Upgradations
Scale-out Deployments in SSRS

Share point server, Power View with SSRS

Managing RDL and RDLX Reports
Comparing PowerView and Report Builder
Drillthrough Report Management Options
Migrating Reports with SSRS and Excel
Migrating Reports from 2012,2014 to 2016

Working with report layout and design

Sub totals /Full totals
Page Breaks
Page Footer/Header
Table Header/Footer
Sorting
Grouping
Applying Colors, Fonts etc…

Dataset features and performance tips

Shared dataset usage
Table, Query, Procedure options
Import from File, Refresh Fields, Time Out
Calculated fields, Query Fields
Filter usage in real time
Parameters in the dataset

Working with Report Definition Language (RDL)

Identifiers, Operators, Expressions
Functions (Built in, Date, Mathematical and String)
Report, Group variables with expressions

Drill Down, Drill through Analysis and Parameterization

Parameterized Reports (a. Non Queried b. Queried c. Multivalued d. Cascading e. NO parameters)
Dynamic Report Parameters and Filters
Dependent Parameters & Usage
Multi Valued Nullable Parameters
Report Filters - Options and Types
Toolbox Filters and Limitations
Sub Reports and Report Actions
Parent-Child Report Parameters
Jump from one report to another using drill through
Hierarchical analysis using drill down

Report Manager administrative features

New folders, Data Sources, Folder Security
Web site options, schedules, security
Linking reports, Download and Replace of reports based on CR documents
Maintaining history of reports
Report security
Report Parts in Report Builder

Report processing, rendering performance tuning tips

Report issues at production
Publishing and Executing Reports
Observing Execution Logs, Execution log views
Creating cached Instances, cache refreshment
Creating snapshots
Live report features
Subscription reports (Normal, Data driven)
SSRS components optimization

Data Quality Services [ DQ- 4 hrs ]: 2016, 2014 & 2012 features

Data profiling versus DQS Data profiling versus DQS

INSTALLING Data Quality Services 2012
BUILDING A KNOWLEDGE BASE
BUILDING KNOWLEDGE DISCOVERY IN DQS
Data Cleansing in DQS
Building a Matching Policy in DQS
Matching Projects in DQS
Activity Monitoring ,Configuration & Security in DQS

Projects Covered

Project - 01

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Project - 02