MSBI (SSIS, SSAS, SSRS)

SQL Server Integration Services- SSIS

Introduction to SQL Server Integration Services

SSIS Framework/Architecture
SSIS Package Architecture Overview
Development and Management Tools
The Import and Export Wizard

Introduction to Data Flow

Data Flow Overview
Data Sources
Data Destinations
Data Flow Transformations
Data Viewers

Data Sources

Excel Source
Flat File Source
OLE DB Source
XML Source

Data Flow Transformations

Aggregate Transformation
Audit Transformation
Character Map Transformation
Conditional Split Transformation
Copy Column Transformation
Derived Column Transformation
Data Conversion Transformation
Multicast Transformation
OLE DB Command Transformation
Percentage Sampling Transformation
Row Count Transformation
Sort Transformation
Union All Transformation

Advanced Data Flow Transformations

Lookup Transformation
Merge Transformation
Merge Join Transformation
Slowly Changing Dimension Transformation
Pivot Transformation
Export Transformation
Import Transformation
Unpivot Transformation

Data Flow Destinations

Data Reader Destination
Excel Destination
Flat File Destination
OLE DB Destination

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
Bulk Insert Task
Data Flow Task
Execute DTS 2000 Package Task
Execute Package Task
Execute Process Task
Web Service Task
XML Task

Advanced Control Flow

For Loop Container
For Each Loop Container
Sequence Container

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
Configuring Package Logging
Breakpoints in SSIS
Event handler in SSIS
Configure Error output in Data Flow
Data Viewers
Configure Check Points
Transactions in SSIS
SSIS Security

SSIS Deployment

Create Configuration Files
Implement Logging
Different Deployment Options
Executing packages using utilities – DTExec and DTExecUI
Schedule SSIS Packages with SQL Server Agent Jobs
Monitoring Jobs in SQL Server
Call SSIS Packages in Stored Procedures
Call SSIS Packages in command prompt

SQL Server Reporting Services (SSRS)

SQL Server Reporting Services (SSRS) Architecture

Architecture of SSRS
Components in SSRS
Phases of Report Development Life Cycle

Report types and formats
SSRS Web Services
Report Server
Report Builder
Model Designer
Data sources: SQL Server, Oracle, OLE DB

Creating basic reports

Tabular
List
Matrix
Chart
Constructing data sources and Datasets
Inserting a data region, fields and images

Report Authoring

Table Reports and Matrix Reports
Report Designer Paging Options
Drill-down Reports
Chart Reports, Indicator, Gauge Control Reports
List Reports
Use Page Header and Page Footer
Drill-Through or Linked Reports
Sub Reports
Create Report Template and use it in new Reports
Parameter Reports
Single Parameter
Multiple Parameters
Cascading Parameter
Create Calendar in Parameters
Default Values and Drop Down list options
Expressions in SSRS
Understand conditional formatting
Adding images
Grouping and Sorting
Page Header and Page Footer
Aggregate Functions
Sub Totals and Grand Totals
Document Map Reports
Create Reports with Stored Procedures
Create Reports with SSAS Cube

Deploying reports to the server

Report Server Configuration
Configure Report Server details in SSRS Project
Report Deployment to Report Server

Creating reports with Report Builder

Deploying Report Builder to users
Dragging and dropping entities onto charts and tabular reports
Navigating data with infinite drill through

Report Manager and Delivery

Report Builder to create ad-hoc Reports
Create Linked Reports
Create Subscriptions
Standard Subscriptions
Data Driven Subscriptions
Cache a Report
Snapshot a Report
Report Logging

Data Warehouse Concepts with Mini Project

Intro to OLTP and OLAP
Intro to Data Warehouse
Dimension Modeling
Star and Snowflake Schema
Dimensions and Measures/Facts
Data Warehouse Vs Data Mart
Build the DWH from Source System (OLTP)
Loading the data from OLTP to DWH using SSIS Packages
Understand Time Table in DWH

SQL Server Analysis Services (SSAS)

Introduction to SSAS

Brief Introduction to SSAS
Using SSAS in BIDS
Understanding BIDS
Creating Data Sources
Creating Data Source Views
Creating a Cube Using the Wizard
Refining Dimensions and Measures

Intermediate SSAS

Creating KPIs
Creating Perspectives
Creating Translations
Creating Actions
Advanced SSAS
Working with Multiple Fact Tables
Using Advanced Dimension Types
Working with Changing Dimensions
Using the Business Intelligence Wizard

Cube Designing and development

Different Ways to Create CUBE
Measures and Measure Groups
Diff between BUILD, DEPLOY and PROCESS
Calculated Columns

Hierarchies

Creating and Managing Hierarchies
Translations

Beginning MDX

Understanding MDX
About Members, Tuples and Sets
Common MDX Functions Explained
New or Updated MDX Functions or Keywords

Intermediate MDX

Understanding the Calculations Sub tab
Adding Calculated Members
Adding MDX Scripts
Adding Named Sets

SSAS Administration

Implementing SSAS Security
Implementing XMLA Scripts
Understanding SSAS Backup and Restore
Understanding Performance Optimization

Introduction to SSAS Clients

Using Excel 2007 Pivot Tables
Using SQL Server Reporting Services

Partitions-Aggregations

What are Partitions and How to create Partitions?
On what basis a Partition are created?

Actions, Calculations, Perspectives and KPIs

What are Actions and How to create IT?
Understand KPIs and Create KPIs
Understand Calculations and Create Calculations
Creating Perspectives

Deploying the Cube

Generate XMLA Script and Deploy a Cube in SQL Server
Create Jobs to process the cube in SQL Server
Different Approaches to Deployment
Client Interactions with Excel