MSBI on 24th Feb (Tuesday) 9:30AM at Kphb (Hyd) by Mr.Bhaskar

SQL SERVER INTEGRATION SERVICES (SSIS)

 SSIS - COURSE CONTENT
 Introduction to Data Warehouse Concepts

o What is Data Warehouse
o Need for Data Ware house
o OLTP vs. OLAP
o What is BI
o Tools for BI
o Goals of BI
o Dimension and FactTables
o Cube
o Measure
o Surrogate Keys
o Data Warehouse Schemas

 Star Schema
 Snow Flake Schema

o Cube Storage

 ROLAP
 MOLAP
 HOLAP

o Data Warehouse vs. Data Marts

 Introduction to SSIS

o Understand BIDS Components
o DTS vs. SSIS
o Control Flow
o Data Flow
o Event Handlers
o Package Explorer
o Connection Managers etc

 Introduction to ControlFlow Items

o Containers

 ForLoop Container
 ForEachLoop Container
 Sequential Container

o Predecesor Constraints
o Control Flow Tasks

 Data Flow
 Execute SQL Task
 File System Tasks
 DBA Related Tasks etc

 Data Flow Items

o Data Flow Sources
o Data Flow Destinations
o Data Flow Transformations

 Agregate
 Audit
 Sort
 Merge
 Merge Join
 Union Al
 Lokup
 Fuzy Lokup
 Fuzy Grouping
 Conditonal Split
 Export Column
 Import Column
 Slowly Changing Dimensions (SCD)
 Derived Column
 Copy Column
 Cache Transformation etc,.

 SIS Variables and Expresions

o System Variables
o Local Variables
o Expresions
o Transactions and Check Points in SIS packages

 Create, Build and Deploy a package

o Create Package
o Build Package
o Deploy package

 Ad config files
 Ad Loging
 Ad Event Handlers
 Ad Eror Handling and Debuging
 Types of Deployments

 SQL Server
 File System

 Schedule SIS Packages

o Introduction to SQL Server Agent Service
o Create a Job to Schedule the Package Execution
o Introduction to MSDB database
o Conect o Integration Server and Execute the Package
o Run the package using DTEXEC cm

SRS Course Content

 Data Warehouse Concepts
 Introduction to BI
 Introduction to SRS
 SRS Architecture
 Tols in SRS

 Report Designer
 Report Manager
 Report Builder

 Tols available in the Market o create Reports
 Scenarios to use SRS
 Lifecycle of the Report (Phases in the Report)
 What is a Report
 Data Sources
 Data Sets
 Shared Vs Embeded Data Sources/Data Sets
 Steps to create a Report
 SRS Data Regions

 Table
 Matrix
 List
 Chart
 Gauge
 Image
 Nested Data Regions
 Sub Reports
 Indicator etc

 Types of Reports

 Tabular and Matrix Reports
 Dril Down Reports
 Dril Thru Reports
 Parameterized Reports

 Single value Parameters
 Multiple Value Parameters

 Customized Reports using List Control
 Nested Data Region Reports
 Document Map Reports
 Chart, Gauge, Indicator, Image Reports

 More Features in SRS

 Use of Row Group and Column Groups
 Ad Report Header and Report Foter
 Ad Page Numbers, Execution Time etc in the Foter
 Expresions in the Reports
 Conditonal Formating
 Date and Curency Format
 Using Sorting
 Ad Page Breaks Before and After
 Ad Background Images in the Report
 Report Format - Changing Style, Font, Color etc
 Report Performance Tuning

 Build and Deploy a Report o the Report Server
 Report Server Configuration
 View Reports On Line
 Export Reports to diferent formats – Excel, PDF, Tif, CSV etc,.
 Introduction to Report Manager
 Managing Reports

 Subscriptions (for Report Delivery)

 Push (Thru E-mail)
 Pul (Thru Shared Folder)

 Linked Reports
 Snapshot of a Report
 Report Security
 Cache a Report
 Report Builder to Create Ad-hoc Reports

SSAS Course Content

What Is Microsoft Business Intelligence?

Define Business Intelligence.
Understand the Cube Structure.
Deploy and View a Sample Cube.
View a Cube by using Excel.
View a Cube by using SQL Reporting Services.

OLAP Modeling

Understand Basic OLAP Modeling (star schema).
Understand Dimensional Modeling (stars and snowflakes)
Understand Measure (fact) and Cube Modeling.
Model with SQL Server Business Intelligence Development Studio (BIDS).

Intermediate SSAS

Learn how to Create Key Performance Indicators (KPIs).
Discover how to Create Perspectives.
See how to Create Translations for Cubes and Dimensions.
Review the three SSAS Action Object Types: Regular, Drillthrough, and Reporting.

Cube Storage and Aggregation

View Aggregation Designs.
Customize Aggregation Designs.
Implement Proactive Caching.
Understand Writeback in Dimensions.
Review Semi-Additive Measures in OLAP Cubes.

Understand Basic MDX Syntax

Understand Basic MDX Syntax.
CUse the MDX Query Editor in SSMS.
Understand Common MDX Functions and Tasks.
Understand Writeback in Dimensions.

MDX Expressions

Understand the Calculation Subtab.
Learn how to add Calculated Members.
Understand Common MDX Functions and Tasks.
Explore how to add MDX Script Commands.

SSAS Administration

Implement SSAS Security .
Implement XMLA Scripts in SSMS.
Deploy and Synchronize Databases.
Understand SSAS Database Backup and Restore.

SQL SERVER 2008 R2 for DEVELOPERS

 Introducing SQL Server 2008 R2

 What's New in SQL Server
 SQL Server History and Editions

 Basics of Database Design

 Introduction to RDBMS
 ERD Modelling
 Normalization and Demoralization
 Business Integrity
 Constraints in SQL Server
 Data Types
 SQL Server 2008 R2 Architecture
 Files and File Groups
 Transaction Logs

 T-SQL

 DDL and DML Commands
 Operators
 Variables

 Local
 Global

 Programming Constructs
 System Functions

 SELECT Statements

 Basic Select Statements

 Using Where Clause
 Using GROUP BY and HAVING

 Advanced Select Statements

 Joins

 Inner Join
 Outer Join

1. Left outer join
2. Right outer Join
3. Full Outer Join

 Cross Join
 Self Join

 Sub Queries, Correlated Sub Queries
 Case Statements
 UNION and UNION ALL

 EXCEPT and INTERSECT
 Cube Operator
 Rollup Operator
 Triggers

 Need
 Features
 Types of Triggers

After
 Instead of

 Creating, Altering, Dropping DML Triggers
 Magic Tables
 Recursive and Nested Triggers
 Views

 Need
 Features
 Types of views
 View Maintenance

 Stored Procedures

 Need
 Features
 Types of Stored Procedures

 User Defined Stored Procedures
 System Stored Procedures

 Functions

 Need
 Features
 Types of Functions

 Indexes

 What Is An Index?
 Types Of Indexes
 Features
 Tuning Queries for improve Performance
 Tuning Indexes for improve Performance

 Transactions

 Need
 Features
 Transaction Properties (ACID)
 Transactions Types
 Transaction Logs
 Checkpoint
 Save Point
 Distributed Transactioning
 Nested Transactions

 Concurrency and Locks

 Problems Related to Concurrency
 Transaction Isolations
 Locking
 Lock Hints
 Lock Escalation
 Live locks
 Deadlocks
 Blocking

 Advanced Transact-SQL

 Ranking Functions
 Cursors
 Common Table Expression(CTE)
 PIVOT and Unpivot
 Exception Handling (Error Handling)
 Temp Tables