About the Course
Architecture and Introduction to SQL Server Integration Services.
Designing and Implementation of SSIS packages.
Designing and Implementation of Control Flow & Data Flow.
Debugging and Managing Package Execution.
Detecting and Handling Processing Errors.
Securing and Deploying SSIS Packages.
Understanding BI and Data warehousing
Architecture and Understanding Analysis Services
Designing and Creating Dimensions.
Designing Measures, Measure Group and Aggregations.
Implementing Security and Managing Deployment.
Architecture and Introduction to SQL Server Reporting Services.
Designing and Implementation of Reporting Services Projects.
Use report models to implement reporting for business users.
Configure report publishing and execution settings.
Implementing subscription for reports.
Topics CoveredSSIS Course Objectives: 16Hrs
Module 1: Architecture and Introduction to SQL Server Integration Services
Need for SSIS
SSIS and DTS Comparison
Control Flow Architecture
Data Flow Architecture
Data Pipeline Usage
SSIS Event Handler
SSIS Development Studio - BIDS
SSIS Runtime Services
SSIS Package Deployment
SSIS Folders and File Structure
Module 2: Designing and Implementation of SSIS packages
SSIS Data Types,
Connection Managers Data Flow Tasks usage
Module 3: Designing and Implementation of Control Flow & Data Flow.
For Loop Container
For each Loop Container
Bulk Insert Task
Data Flow Task
Execute Package Task
Execute SQL Task
File System Task
Send Mail Task
Transfer Database Task
WMI Data Reader Task WMI Event Watcher Task XML Task
Maintenance Plan Task Backup Database Task Shrink Database Task. Row Transformation:
Character Map Transformation
Copy Column Transformation
Data Conversion Transformation Derived Column Transformation
Script Component Transformation OLEDB Command transformation
Aggregate Transformation, Sort Transformation, Percentage Sampling Transformation,
Row Sampling Transformation, Pivot Transformation, Unpivot Transformation
Export Column, Import Column, Audit, Row Count,
Slowly Changing Dimension
Data Flow Task Split and Join Transformation
Understanding of Extraction, Transformation, Loading Conditional Split, Multicast, Merge, Merge Join, Union All, Lookup
Data Flow Sources Data Quality Transformation
ADO NET Source, Excel Source
Flat File Source,
OLE DB Source,
XML Source Fuzzy Lookup, Fuzzy Grouping
Data Mining Transformation
Data Mining Query, Term Extraction, Term Lookup
Synchronous and Asynchronous Transformation
Module 4: Debugging and Managing Package Execution
Debugging Control Flow
Debugging Data Flow
Debugging Script Task
Packaging Configuring XML File Configuring
Multiple Configuring File,
Module 5: Detecting and Handling Processing Errors
Basic Error Detection and Handling
Understanding of Event Handler Maintaining Data Consistency with Transactions
Checkpoint, Checkpoint Restarts and Transaction
Module 6: Securing and Deploying SSIS Packages
Creating Deployment Utility,
Role – Based Security,
Monitoring Package Execution and Event Logs
Optimizing SSIS Packages
Best Practices in SSIS, Real Time Scenario Discussion, Mock Project - 1 – Automation
Legend: Students/Trainees should know/Aware before attending this course
Basic SQL Server knowledge, SQL Server Run Time Services, SQL Server Agent
After completion of this tool, Students will able to Handel the ETL Process, Design SSIS Package etc
SSAS Course Objectives 16hrs
Module 1: Understanding BI and Data warehousing
Data warehouse concepts,
Data warehouse Architecture
Difference between Ralph Kimball and Bill Inmon’s
What is Business Intelligence?
Difference between OLTP vs. OLAP
Module 2: Architecture and Understanding Analysis Services
Understanding of OLAP, MOLAP, ROLAP
What is SSAS? Need for SSAS
Understanding of CUBE Structure
Understanding of Schemas (Star, Snowflake)
Data Source usage, Data Source View
Computed and Named Queries
Module 3: Designing and Creating Dimensions
Understanding of Dimension Table
Creating Dimension Table
Understanding of Parent – Child Dimension
Building Levels, Understanding of Fact Table
Creating Fact Table
Module 4: Designing Measures, Measure Group and Aggregations
Measures and Additives, Semiadditive Measures
Understanding of Aggregation
Designing User Hierarchies
Optimizing Aggregation, Query Log
Usage Based Optimization
Cube Operations – Dimension usage and Flexible cubes
Building Fact-Dimension, Creating Calculation,
MDX Scripting for Calculation
Defining KPI’s and usage
Partitioning, Perspective and usage
What is MDX? Need of MDX
MDX Basics, Complex MDX Queries,
What is Data mining? Need for Data Mining
Data mining structures and Algorithm
Data mining levels, operating predictions
Prediction Functions and Analysis
Module 5: Implementing Security and Managing Deployment
Using Role based security, Object level access
Drill through Security, Windows Level Authentication, Security Audits.
Deployment phases: OLAP Database Backups, Compression and Encryptions
Restore Option, Write – Backs in Cube
Automating Database Processing using SSIS
Managing Deploy Process
Best Practices in SSAS, Real Time Scenario Discussion, Mock Project - 1
After completing of this course students will able to do: Design CUBE -OLAP DB
SSRS Course Objectives 8hrs
Module 1: Architecture and Introduction to SQL Server Reporting Services.
Understanding of Reports,
Introduction to SSRS,
Need for SSRS
SSRS three phase Lifecycle
Reporting components, Tools, Web Service Requirements and usage
SSRS and IIS, and SQL Server Web Services
Requirement for Virtual Directories
Module 2: Designing and Implementation of Reporting Services Projects
Report planning, Data Sources,
Enterprise and Adhoc-reports
Optimizing Data Sources
Configuring connection and Data providers
Parameter and Multi valued Parameter
Filters in Data source Report Designer: Wizard based reports, custom reportsUsing Table and Matrix
Chart based report and images
Sub Reports, Reporting Option,
Exporting Reporting data
Module 3: Use report models to implement reporting for business users
Understanding of Report models
Designing Report models
Identifying different Attributes
Module 4: Configure report publishing and execution settings
Build and Deploy, publishing reports
Managing Configuration Files
Module 5: Implementing subscription for reports
Creating Standard Subscription
Data Driven Subscription
Subscription Logging options
Benefits of SSRS Logging
Server Level Logging Options
Report level logging
Audit reports execution
Best Practices in SSRS, Real Time Scenario Discussion, Mock Project – 1
After completing of this course students will able to do: Design Reports, publish reports subscription of the reports.
After completing of MSBI course students will able to give solution to Business and handle the MSBI Projects, will become MSBI Developer
Who should attendAny graduates, Masters, working professionals, looking for career in business intelligence..
Pre-requisitesKnowledge of SQL Server
What you need to bringNote book, Laptop
Key TakeawaysBest knowledge on all the concepts of MSBI, integration , Reporting and Analysis, Real time hand on challenges handling..