MSBI (SSIS, SSRS,SSAS) + SQL Server Development

Courses

MSBI (SSIS, SSRS,SSAS) + SQL Server Development

Learning Objectives

In this module, you will understand the need for an Integration tool and its significance in real world. You will learn the importance of SSIS as an integration tool and get a birds-eye-view of the overall architecture.

  • SSIS Introductions
  • Different Components in MSBI (SSMS/SSDT/Services )
  • Control Flow
  • Data Flow
  • Precedence constraint & Expressions
  • Annotation
  • Sequence Container
  • Data Flow Components
  • Audit]
  • SQL TO FF  â€” Done
  • Data viewer
  • Copy
  • OLEDB Source
  • OLDB Destination
  • Character Map
  • Row sampling
  • Percentage sampling
  • Multicast  â€” Done
  • Sort
  • Aggregate
  • Merge
  • Union All
  • Flat File Destination
  • Excel Destination — DONE
  • Conditional Split
  • Merge join  
  • Data conversion / Type Cast — Done
  • Derived  â€“
  • Lookup
  • OLEDB Command —DONE
  • Cache transformation (target is cashed)
  • SCD (type 1, type 2)  â€” DONE
  • Control Flow Components
  • Sequence Containers  
  • Dynamic loading of files into SQL target —Done
  • For loop
  • For each loop
  • Variables with default values
  • Variables with dynamic values through Expressions
  • Script task
  • Debugging / Break Points
  • Execute SQL Task
  • Execute SQL task with parameter and with result       
  • For Each Looping Table — Done
  • Row Count
  • Send Mail Task , RC Mail — Done
  • Execute package
  • Execute Process
  • Bulk Insert Task ( sending FF – SQL)
  • File system Task
  • FTP Task
  • Event Handler
  • ERROR Output — DONE
  • Checkpoints (restart ability)
  • Logging
  • Configurations
  • Deployment
  • Jobs (Scheduling)

SSRS (SQL Server Reporting Services)

Learning Objectives

In this module you will understand Reporting Services, its Architecture and how to develop and configure different kinds of reports and their functionalities along with best practices to be followed. You will also learn about the environment where reports are created and about server where the reports are deployed.–                                                 

TOPICS:

  • Introduction to SSRS
  • Architecture of SSRS
  • What is Report Server
  • What is Report Manager
  • Creating reports using SSDT
  • What are Data sources
  • Basic Table Report
  • What are Shared Data sources
  • HEADERS AND EXPRESSIONS
  • FOTTER
  • Report FILTER
  • Matrix Report
  • Page break concept
  • Drill Down report
  • Report Wizard / Adhock Report /Ad-Hock Report / Wizards
  • Parameterized report
  • Multi-valued Parameterized Reports
  • Lable and Value Fields in Parameters
  • Cascading Parameters Report    
  • Dynamic Title based on selection
  • Expressions , Show or hide based on IF ELSE Expressions
  • Total , Subtotal, Add Row
  • Chart report and its properties & expressions    
  • Drill through Report / hyperlink report / jump report / navigation report / go to report/ Action report
  • Sub report — Done
  • List Report with Image
  • Rectangle Report
  • Indicators
  • Gauge
  • Deployment
  • Edit Report
  • Report Manager Operations
  • Snap short and History comes under this report
  • Report Subscription (Email and Windows File Share) and Scheduling
  • Move / Delete Report
  • Linked Report
  • Report Buider

SSAS (SQL Server Analysis Server

Learning Objectives

This module will help you in understanding SQL Server Analysis Services Overview and its Architecture. It also covers various types of SSAS models along with designing dimensions and measure groups.

  • Introduction to SSAS
  • Schemas
  • Star Schema
  • Snowflake Schema
  • Data source
  • Data source views
  • Options in DSV
  • Dimension
  • Measures
  • Cube Creation
  • Build
  • Deploy
  • Process
  • How to Brows the CUBE   
  • Types Of Relationships inside CUBE
  • No relationship
  • Regular Relationship
  • Fact Relation
  • Referenced Relationship
  • None
  • Many to Many (Fact less Fact) — Done
  • Hierarchies inside a Dimension  
  • Roll Playing Dimensions
  • How to use Cube in Excel
  • What are KPI’s and How to Create KPI’s in SSAS
  • What are Actions, Drill through
  • What are Translations — Done
  • What are Calculations
  • Aggregations
  • What are Perspective and how to create
  • What are Partitions
  • Storage design – ROLAP, MOLAP , HOLAP
  • Cubes in Report

Fundamentals of MDX

Learning Objectives

This SQL Server Module will help you to create database objects like Tables, Views, Stored procedures & Functions and triggers in SQL Server and also gives idea about writing Queries & Sub-queries, working with Joins, etc. \MSSQLNEW

SQL Server Development

Topics:

  • SQL Server Management Studio Installation and Services.
  • Connecting To Server
  • Server Name
  • Authentication Modes
  • SQL Server Authentication Mode
  • Windows Authentication Mode
  • Login and Password
  • Object Explorer Details   â€”- Done.
  • Working with Data Types
  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)  â€”— Done
    • Identity
    • Unique
    • Check
    • NOT NULL
    • Default
  • Column aliases
  • Table Alias
  • Between, And
  • In
  • Like
  • Operators (= , <, >, in , like, between )
  • Calculate information across result sets using aggregate queries (sum, min, max, avg, etc.)
  • Group by
  • Having Clause
  • Select
  • Where clause
  • Order By Clause
  • Distinct Keyword
  • TOP — Done
  • Is null() function, Case
  • Predefine Functions
  • Date functions
  • Conversion Functions
  • String Functions
  • Variables
  • IF
  • IF ELSE
  • Table Variables (Local and Global)
  • Control and Looping Functions
  • While +IF  â€” Done
  • Union
  • Union all
  • Exception handling and Transaction
  • Joins — Done
  • Views  
  • CTE
  • Cursor
  • Ranking Functions
  • Row_Number()
  • Ntile()
  • Rank()
  • Dense_Rank() — Done
  • User Defined Functions
  • Scalar Functions
  • Table value / Inline  Function
  • Multi value Function
  • Stored Procedures
  • SP with Input Parameters
  • SP with Output Parameter
  • Triggers