Introduction to MSBI and Data warehouse concepts

Definition of  Data Warehouse

Why Data Warehouse

DWH Architecture

Master Data

Transaction Data

Dimension Table

Fact Table

OLTP(Online Transaction Processing System)

OLAP(Online Analytical Processing System)

SQL Server

SQL Basics

Constrains

Joins

Working with Data

String Functions

Date Functions

Aggregate Functions

Identity

Temp Table, Table Variable

CTE(Common Table Expression)

Sub Queries

Operators

Case Statements

While Loop and If else Statements

Views

Stored procedures

Functions

Cursor

Triggers

Indexes

Exception Handling

SSIS (SQL Server Integration Services)

Introduction of SSIS

BIDS(Business Intelligence Development Studio)

Components of SSIS

Control Flow Items

For Loop Container

For each Loop Container

Sequence Container

Bulk Insert Task

Data Flow Task

Execute SQL Task

Execute Package Task

Execute Process Task

FTP Task

File System Task

Script Task

Send Mail Task

Data Flow Items

Data Flow Sources

OLE DB Source

Flat File Source

Excel Source

Data Flow Transformations

Aggregate

Audit

Character Map

Conditional Split

Copy Column

Data Conversion

Derived Column

Lookup

Fuzzy Lookup

Fuzzy Grouping

Merge

Merge Join

Multicast

SCD(Slowly Changing Dimension)

Sort

Union All

Data Flow Destinations

OLE DB Destination

Excel Destination

Flat File Destination

SQL Server Destination

Variables

Loggings

Configurations

Package Deployment

Scheduling SSIS Packages

SSIS Package Tuning

SSRS (SQL Server Reporting Services)

Core Components of SSRS Reports

Types of Reports

Basic SSRS Report

Data Sources and Shared Data Sources

Datasets and Shared Datasets

Report Parameters

Cascading parameters

Drill down Report.

Report Formatting

Custom Expressions

Drill Through Report

Ad-Hoc Reporting with Report Builder

Report Model Projects

Subscriptions

Report Manager and Report Server

Report Security

Improving Report Performance

SSAS (SQL Server Analytical Services)

Definition of Cube

Advantage of Cube

Star and snowflake schema

Dimensions and Dimension Types

Measures and Measure Types

Fact Table and Types

Data Source

DSV(Data Source View)

Hierarchy and Types

Cube Structure

Dimension Usage and Relationships

Calculations

Partitions

KPIs

Actions

Aggregations

Perspectives

Translations

Browser

Storage Models (ROLAP,MOLAP and HOLAP) 

MDX (Multi Dimensional Expressions)

Retrieving Cube data using MDX

Hierarchy

Sets

Tuples

Calculated Member

Basic MDX Functions

PARANT and CHILDREN Functions

LAG and LEAD Functions

TIME Functions

PARALLELPERIOD Function

YTD , QTD , MTD and WTD

MDX Calculations.