Microsoft Business Intelligence – MSBI

MSBI is an BI tool which help the end users to get a visual designed and user-friendly reports. The developers with knowledge of SQL, databases can learn this tool

The course agenda will include:

  • Software installation and real time environment setup
  • Hands on training with real time Expert
  • Real time scenarios
  • Assistance after course completion also

   Business Intelligence/Data warehousing Overview

  • Business intelligence and features
  • Components of MSBI

    SSIS -12

    SSRS -14            

    SSAS -14     

SQL Server Integration Services (SSIS)

Introduction to SQL Server Integration Services

  • Introduction to ETL
  • SSIS package Lifecycle
  • SSIS Package Architecture Overview
  • Development and Management Tools
  • Source Control for SSIS Packages

   Business Intelligence Development Studio (Sql server data tools)

  • Launching SSDT
  • Project templates
  • The package designer
  • The Toolbox
  • Solution Explorer
  • The Properties window
  • The Variables window
  • The SSIS menu

   Introduction to Control Flow

  • Control Flow Overview
  • Control Flow Tasks
  • Precedence Constraints
  • The Execute SQL Task
  • The Bulk Insert Task
  • The File System Task
  • The Send Mail Task

   Advanced Control Flow

  • Containers – grouping and looping
  • The Execute Process Task
  • The Execute Package Task
  • Other Control Flow Tasks

   Introduction to Data Flow

  • Data Flow Overview
  • Data Sources
  • Data Destinations
  • Data Transformations
  • The Copy Column Transformation
  • The Derived Column Transformation
  • The Data Conversion Transformation
  • The Conditional Split Transformation
  • The Aggregate Transformation
  • The Sort Transformation
  • Data Viewers
  • Other Dataflow tasks

   Variables and Configurations

  • Variables Overview
  • Variable scope
  • SSIS system variables
  • Using variables in control flow
  • Using variables in data flow
  • Configuration Overview
  • Configuration options

   Debugging, Error Handling and Logging

  • SSIS debugging overview
  • Breakpoints in SSIS
  • SSIS debugging windows
  • Control Flow: The On Error event handler
  • Data Flow: Error data flow
  • Configuring Package Logging
  • Built-in log providers

   Advanced Data Flow

  • Revisiting data sources and destinations
  • The Lookup Transformation
  • Getting Fuzzy: The Fuzzy Lookup and Fuzzy Grouping Transformations
  • The Multicast Transformation
  • The Merge and Merge Join Transformations
  • The Slowly Changing Dimension Transformation

   Extending SSIS through Custom Code

  • Introduction to SSIS scripting
  • Script in Control flow: The Script Task
  • Script in Data flow: The Script Component

   SSIS Package Deployment

  • Configurations and deployment
  • The deployment utility
  • Deployment options
  • Scheduling packages with Sql Server Agent

SQL Server Analysis Services (SSAS)

Introduction to SQL Server Analysis Services

  • Introduction to OLTP ,OLAP,DWH
  • Basics of Multidimensional Architecture

   OLAP Modeling

  • Modeling source schemas—stars and snowflakes
  • Understanding fact(measures) and cube modeling

   Using SSAS in SSDT

  • Understanding the development environment
  • Creating Data Sources and Data Source Views
  • Creating cubes – using Cube Build Wizard
  • Defining Dimensions and Measures in BIDS

   Intermediate SSAS

  • KPIs
  • Perspectives
  • Translations – cube metadata and currency localization
  • Actions – regular, drill-through and reporting

   Advanced SSAS

  • Using multiple fact tables
  • Modeling intermediate fact tables
  • Modeling M:M dimensions, Fact (degenerate) dimensions, Role-playing dimensions, write back
  • dimensions

   Cube Storage and Aggregation

  • Storage topics – basic aggregations, MOLAP
  • Advanced Storage Design – MOLAP, ROLAP, HOLAP
  • Partitions – relational and Analysis Services partitions
  • Customizing Aggregation Design – Processing Design
  • Rapidly changing dimensions / ROLAP dimensions
  • Cube processing options

   Beginning MDX

  • Basic syntax
  • Using the MDX query editor in SQL Server Management Studio
  • Most-used Functions & Common tasks
  • New MDX functions

   Introduction to Data Mining

   Tabular Model

SQL Server Reporting Services (SSRS)

Introduction to SQL Server Reporting Services

  • Reporting Services Architecture
  • Reporting Services Terminology
  • Reporting Services Editions
  • Reporting Life cycle
  • Types of Reports

   Creating Reports

  • Shared Data Sources
  • Creating reports with wizard
  • Creating Tabular ,Matrix Reports from Scratch

   Grouping and Sorting

  • Creating Groups
  • Calculating Totals and Percentages
  • Interactive Sorting
  • Creating Drill-Down Reports

   Report Parameters

  • Creating Report Parameters
  • Creating Drop-Down Parameters
  • Multi-Valued Parameters
  • Debugging Parameter Issues

   Publishing and Executing Reports

  • Publish reports and execution from report server
  • Creating Cached Instances
  • Creating Snapshots and Report History
  • Administering reports

   Creating Matrix Reports and Charts

  • Creating a Basic Matrix Report
  • Matrix Subtotals
  • Creating a Basic Chart
  • Exploring the Charting Possibilities

   Calculations and Formatting

  • Creating Expressions
  • Using the Global Collections
  • Formatting Items
  • Conditional Formatting

   Managing Reporting Services

  • Deploying Reports and Data Sources
  • Exporting Reports and Printing
  • Using the Web-Based Report Manager

   Reporting Services Security

  • Two Parts to Reporting Services Security
  • Securing Access to Reports
  • Data Source Security

   Ad-Hoc Reporting with Report Builder

  • The Report Builder Architecture
  • Creating Report Model Projects

Running Report Builder