SSIS: 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. SSAS : 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. SSRS : 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 Covered

SSIS Course Objectives: 16Hrs Module 1: Architecture and Introduction to SQL Server Integration Services Need for SSIS SSIS and DTS Comparison SSIS Architecture Control Flow Architecture Data Flow Architecture Data Pipeline Usage Precedence Constraints SSIS Event Handler SSIS Components 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, SSIS Variables, Connection Managers Data Flow Tasks usage SSIS Expressions Module 3: Designing and Implementation of Control Flow & Data Flow. Control Flow For Loop Container For each Loop Container Sequence Container Bulk Insert Task Data Flow Task Execute Package Task Execute SQL Task File System Task FTP Task Script 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 Rowset Transformation: Aggregate Transformation, Sort Transformation, Percentage Sampling Transformation, Row Sampling Transformation, Pivot Transformation, Unpivot Transformation Other 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 SSIS Expression Data Sources Module 4: Debugging and Managing Package Execution Debugging Control Flow Debugging Data Flow Debugging Script Task Packaging Configuring XML File Configuring Multiple Configuring File, Package Logging Module 5: Detecting and Handling Processing Errors Error Handling 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, Securing Package Role – Based Security, Deployment Option 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 SSAS Architecture 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, MDX Functions 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 Verification Techniques 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 Reporting Services SSRS three phase Lifecycle . SSRS Architecture 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 SSRS Expression, 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, Tabular Reports Exporting Reporting data Module 3: Use report models to implement reporting for business users Understanding of Report models Designing Report models Identifying Entities Identifying different Attributes Numerical Aggregation, Time Aggregation Module 4: Configure report publishing and execution settings Deployment Option Build and Deploy, publishing reports Configuration Option 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 attend

Any graduates, Masters, working professionals, looking for career in business intelligence..

Pre-requisites

Knowledge of SQL Server

What you need to bring

Note book, Laptop

Key Takeaways

Best knowledge on all the concepts of MSBI, integration , Reporting and Analysis, Real time hand on challenges handling..