MSBI

Introduction to MSBI

• What is MSBI
• the life cycle of BI/DWH
• List of ETL and OLAP Tools 
• Features of MSBI

SQL SERVER INTEGRATION SERVICE(SSIS)

• Introduction to SQL Server Integration
•  Services SSIS Architecture Overview
• Development and Management  Tools 
• SQL Server Import and Export Wizard
•  Business Intelligence Development Studio

Introduction to Data Flow

• Data Flow Overview
• Data Sources
• Data Destinations
• Data Flow Transformations Data Viewers

Introduction to Data Sources

• Excel Source
• Flat File Source
• OLE DB Source
• XML Source

Introduction to Data Flow Transformations

• Aggregate Transformation
•  Audit Transformation
•  Character Map Transformation
• Conditional Split Transformation
•  Copy Column Transformation 
• Derived Column Transformation
•  Data Conversion Transformation 
• Multicast Transformation
• OLE DB Command Transformation
•  Percentage Sampling Transformation 
• Row Count Transformation
• Sort Transformation
• Union All Transformation

Advance use of Data Flow

• Lookup Transformation
•  Merge Transformation 
• Merge Join Transformation
• slow Changing Dimension Transformation
•  Pivot Transformation
• Export Transformation
•  Import Transformation
• Unpivot Transformation

Explain Data Flow Destinations

• Data Reader Destination 
• Excel Destination
• Flat File Destination
•  OLE DB Destination

Introduction to Control Flow

• Control Flow Overview 
• Precedence Constraints 
• The Execute SQL Task 
• The Bulk Insert Task 
• The File System Task 
• The FTP Task
• The Send Mail Task
•  Bulk Insert Task 
• Data Flow Task
• Execute DTS 2000 Package Task 
• Execute Package Task
• Execute Process Task
• Web Service Task XML Task

Advanced use of Control Flow

• For Loop Container
• For Each Loop Container 
• Sequence Container

Explain Variables and Configurations

• Variables Overview 
• Variable scope
• SSIS system variables
• Using variables in the control flow 
• Using variables in data flow
• Using variables to pass information between

Overview of packages

• Property expressions 
• Configuration Overview
•  Configuration options
• Configuration discipline

Debugging, Error Handling and Logging

• SSIS debugging overview 
• Breakpoints in SSIS
• Control Flow: The On Error event handler
•  Data Flow: Error data flow
• Configuring Package Logging 
• Built-in log providers

Extending SSIS through Custom Code Scripting

• Introduction to SSIS scripting 
• The SSIS script editor
• The SSIS object model
• Script in Control flow: The Script Task 
• Script in Data flow: The Script Component
• Introduction to SSIS component development

Package Deployment of SSIS

• Configurations and deployment 
• The deployment utility 
• Deployment options 
• Deployment security
• Executing packages DTExec and DTExecUI

SSIS Package Management

• The SSIS Service
• Managing packages with DTUtil
• Managing packages with SQL Server Management
• Studio
• Scheduling packages with SQL Server Agent

SQL SERVER ANALYSIS SERVICES

• Introduction to SQL Server Analysis Services
•  SSAS Architecture Overview
• Explain Microsoft Business Intelligence 
• What is UDM
• Building and Viewing a Sample Cube 
• Viewing a Cube Using Excel
• Cubes in Reporting Services 
• Using SSAS in BIDS Understanding BIDS 
• Creating Data Sources 
• Creating Data Source Views
• Creating a Cube Using the Wizard
• Refining Dimensions and Measures

Intermediate SSAS

• Creating KPIs
•  Creating Perspectives 
• Creating Translations 
• Creating Actions 
• Advanced SSAS
• Working with Many Fact Tables
•  Using Advanced Dimension Types 
• Working with Changing Dimensions
• Using the Business Intelligence Wizard

Cube Storage and Aggregation

• Basic Storage: MOLAP 
• Customized Aggregations
• Advanced Storage: MOLAP, HOLAP, or ROLAP 
• Implementing Proactive Caching
• Using Partitions: Relational or SSAS
• Cube and Dimensions Processing Options

Beginning MDX

• Understanding MDX
• About Members, Tuples, and Sets 
• Common MDX Functions Explained
• New or Updated MDX Functions or Keywords

Intermediate MD

• Understanding the Calculations Subtab 
• Adding Calculated Members
• Adding MDX Scripts 
• Adding Named Sets

SSAS Administration

• Implementing SSAS Security 
• Implementing XMLA Scripts
• Understanding SSAS Backup and Restore 
• Understanding Performance Optimization

Introduction to SSAS Clients

• Using Excel 2007 Pivot Tables
• Using SQL Server Reporting Services

SQL SERVER REPORTING SERVICES

• SQL Server Reporting Services (SSRS) Architecture 
• Report types and formats
• SSRS Web Services
• Report Server 
• Report Builder
• Model Designer
• Data sources: SQL Server, Oracle, OLE DB
•  Authoring Reports

Creating basic reports

• Tabular 
• List
•  Matrix 
• Chart
• Constructing data sources and Datasets
•  Inserting a data region, fields, and images

Leveraging expressions

• Calculating fields
• Integrating expressions in properties 
• Applying conditional formatting 
• Adding images

Grouping and sorting data

• Grouping to multiple levels 
• Sorting the results
• Employing aggregate functions

IMPLEMENTING PARAMETERS AND FILTERS BUILDING PARAMETERS INTO REPORTS

• Returning subsets of data with query parameters 
• Mapping report parameters to query parameters
•  Building cascading report parameters
• Passing parameters to stored procedures 
• Exploiting multivalve parameters
• Restricting report data with filters
• Enhancing performance with filters Filters vs. query parameters
• Localizing tables and queries
• Leveraging Advanced Reporting Features Combining data regions in one report
• Utilizing sequential and nested regions 
• Producing master/details reports
• Linking sub-reports
• Presenting data with appropriate detail
• Revealing report details with drill-down
• Deploying Report Builder to users
• Dragging and dropping entities onto charts and tabular reports
• Navigating data with infinite drill-through