MSBI Online Training

MSBI ONLINE TRAINING

Microsoft BI Stack consists of different tools for different operations to be carried out when they developing the business solution.

SSRS : Sql Server Reporting Services , Used for reporting purposes.

SSIS: Sql Server Integration Services , Used for integration of information as ETL.

SSAS : Sql Server Analysis Services , Used for Analysis OLAP cubes to do analytic s over the information.

In a period of growing BI domain, Microsoft evolved these MSBI tools to meet the challenges they developer come across while development.

SQL Server 2005 , SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 (Denali) are the different versions in market.

Tools versions usually enhanced from lower version to higher version due to adding new features in it.

MSBI ONLINE TRAINING COURSE CONTENT

What Is Microsoft BI?
Core concept – BI is the cube or UDM
Example cube as seen using Excel pivot table
MS BI is comprehensive – more than Analysis Services on SQL Server
Demonstration of SQL Reporting Services with cube as data source

OLAP Modeling

Modeling source schemas—stars and snowflakes
Understanding dimensional modeling— Dimensions (Type 1, 2, or 3) or rapidly changing
Understanding fact (measures) and cube modeling
Other types of modeling—data mining etc…

Using SSAS in BIDS

Understanding the development environment
Creating Data Sources and Data Source Views
Creating cubes – using the UDM and the Cube Build Wizard
Refining 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
Modeling changing dimensions – Dimension Intelligence w/ Wizard
Using the Add Business Intelligence Wizards – write-back, semi-additive measures, time intelligence, account intelligence

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
Welcome to the Real Time – Proactive Caching
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

Intermediate MDX

Adding calculated members
Adding scripts
Adding named sets
.NET Assemblies

SSAS Administration

Best practices – health monitoring
XMLA scripting (SQL Mgmt Studio)
Other Documentation methods
Security – roles and permissions
Disaster Recovery – backup / restore
Clustering – high availability

Introduction to Data Mining

What and why?
Examples of using each of the 9 algorithms (MS Clustering, MS Decision Trees, Naïve Bayes, MS
Sequence Clustering, MS Time Series, MS Association Rules, MS Neural Network)
Data Mining dimensions
Data Mining clients
Processing mining models

Introduction to Reporting Clients

Excel 2003 Pivot Tables
SQL RS & Report Builder
SPS RS web parts & .NET 2.0 report viewer controls
Business Scorecards 2005 & ProClarity

Future Directions – Integration with Office 12

SharePoint 12 and AS
Report Center (type of dashboard) uses KPIs, Reports, Excel Web, Filter
Excel Services 12 and AS (Web Services)

SQL Server 2005 Integration Services
Introduction to SQL Server Integration Services

Product History
SSIS Package Architecture Overview
Development and Management Tools
Deploying and Managing SSIS Packages
Source Control for SSIS Packages

SSIS for DBAs: Using SQL Server Management Studio

The Import and Export Wizard
Importing and Exporting Data
Working with Packages
Database Maintenance Plans
Creating Database Maintenance Plans using SSMS
Scheduling and Executing Plans
Examining Database Maintenance Plan Packages
Changes in SQL Server 2005 Service Pack 2

Business Intelligence Development Studio

Launching BIDS
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
Precedence Constraints
The Execute SQL Task
The Bulk Insert Task
The File System Task
The FTP Task
The Send Mail Task

Advanced Control Flow

Containers – grouping and looping
The Web Service Task
The WMI tasks
The Analysis Services tasks
The Execute Process Task
The Execute Package Task

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

Variables and Configurations

Variables Overview
Variable scope
SSIS system variables
Using variables in control flow
Using variables in data flow
Using variables to pass information between packages
Property expressions
Configuration Overview
Configuration options
Configuration discipline

Debugging, Error Handling and Logging

SSIS debugging overview
Breakpoints in SSIS
SSIS debugging windows
Control Flow: The OnError 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 Data Mining Query Transformation
The Data Mining Model Training Destination
The Slowly Changing Dimension Transformation

Extending SSIS through Custom Code

Introduction to SSIS scripting
The SSIS script editor
The SSIS object model
Script in Control flow: The Script Task
Script in Data flow: The Script Component
Introduction to SSIS component development

SSIS Package Deployment

Configurations and deployment
The deployment utility
Deployment options
Deployment security
Executing packages – DTExec and DTExecUI

SSIS Package Management

The SSIS Service
Managing packages with DTUtil
Managing packages with SQL Server Management Studio Scheduling packages with SQL Server Agent

SQL Server 2005 Reporting Services
Introduction to SQL Server Reporting Services

Tour of Features
Reporting Services Architecture
Reporting Services Terminology
Reporting Services Editions
Taking Reporting Services for a Spin

Creating Reports

Deconstructing Reporting Services
Shared Data Sources
Creating Reports from Scratch

Calculations and Formatting

Creating Expressions
Using the Global Collections
Formatting Items
Conditional Formatting

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

Creating Matrix Reports and Charts

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

Managing Reporting Services

Deploying Reports and Data Sources
Exporting Reports and Printing
Using the Web-Based Report Manager
Using SQL Server Management Studio-based Reporting Services Manager

Reporting Services Security

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

Programming Reporting Services

The Many Reporting Services APIs
Integrating Reporting Services into Applications using URL Access
Using the Report Viewer Controls

Advanced Reporting Services Programming

Using the Reporting Services Web Service
Working with Custom Assemblies

Snapshots and Subscriptions

Caching and Snapshots
Creating Standard Subscriptions
Creating Data-Driven Subscriptions
Managing Subscriptions

Ad-Hoc Reporting with Report Builder

The Report Builder Architecture
Creating Report Model Projects
Running Report Builder

We also provide other Data Warehousing Classes like ETL Testing