MSBI on 2nd Jan (Friday) 9:00AM-10:30AM at Kphb(Hyd) by Mr.Bhaskar

SQL Server:
 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)
SSRS Course Content
 Data Warehouse Concepts
 Introduction to BI
 Introduction to SSRS
 SSRS Architecture
 Tools in SSRS
ü Report Designer
ü Report Manager
ü Report Builder
 Tools available in the Market to create Reports
 Scenarios to use SSRS
 Lifecycle of the Report (Phases in the Report)
 What is a Report
 Data Sources
 Data Sets
 Shared Vs Embedded Data Sources/Data Sets
 Steps to create a Report
 SSRS Data Regions
ü Table
ü Matrix
ü List
ü Chart
ü Gauge
ü Image
ü Nested Data Regions
ü Sub Reports
ü Indicator etc
 Types of Reports
ü Tabular and Matrix Reports
ü Drill Down Reports
ü Drill 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 SSRS
ü Use of Row Group and Column Groups
ü Add Report Header and Report Footer
ü Add Page Numbers, Execution Time etc in the Footer
ü Expressions in the Reports
ü Conditional Formatting
ü Date and Currency Format
ü Using Sorting
ü Add Page Breaks Before and After
ü Add Background Images in the Report
ü Report Format - Changing Style, Font, Color etc
ü Report Performance Tuning
 Build and Deploy a Report to the Report Server
 Report Server Configuration
 View Reports On Line
 Export Reports to different formats – Excel, PDF, Tiff, CSV etc,.
 Introduction to Report Manager
 Managing Reports
ü Subscriptions (for Report Delivery)
 Push (Thru E-mail)
 Pull (Thru Shared Folder)
ü Linked Reports
ü Snapshot of a Report
ü Report Security
ü Cache a Report
ü Report Builder to Create Ad-hoc Reports
SSIS - COURSE CONTENT
ü Introduction to Data Warehouse Concepts
o What is Data Warehouse
o Need for Data Warehouse
o OLTP vs. OLAP
o What is BI
o Tools for BI
o Goals of BI
o Dimension and Fact Tables
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 Control Flow Items
o Containers
§ ForLoop Container
§ ForEachLoop Container
SQL SERVER INTEGRATION SERVICES (SSIS)
2
§ Sequential Container
o Predecessor 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
§ Aggregate
§ Audit
§ Sort
§ Merge
§ Merge Join
§ Union All
§ Lookup
§ Fuzzy Lookup
§ Fuzzy Grouping
§ Conditional Split
§ Export Column
§ Import Column
§ Slowly Changing Dimensions (SCD)
§ Derived Column
§ Copy Column
§ Cache Transformation etc,.
ü SSIS Variables and Expressions
o System Variables
o Local Variables
o Expressions
o Transactions and Check Points in SSIS packages
SQL SERVER INTEGRATION SERVICES (SSIS)
ü Create, Build and Deploy a package
o Create Package
o Build Package
o Deploy package
§ Add config files
§ Add Logging
§ Add Event Handlers
§ Add Error Handling and Debugging
§ Types of Deployments
® SQL Server
® File System
ü Schedule SSIS Packages
o Introduction to SQL Server Agent Service
o Create a Job to Schedule the Package Execution
o Introduction to MSDB database
o Connect to Integration Server and Execute the Package
o Run the package using DTEXEC cmd
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.

Ameerpet branch
Batch Date: Jan 2nd @5:00PM to 6:3PM
Faculty: Mr.Bhaskar Jogi
Duration: 60 days
Fee: 12000 (INR)
Location: Ameerpet, Hyderabad
Address:
#101, First Floor,
Revathi Apartments,
Opp. Annapurna Block,
Ameerpet, Hyderabad.