MSBI Online Training

SQL Server Integration Services (SSIS)

Microsoft SQL Server Integration Services(SSIS)

• Introduction to SQL Server 2008
  
• Overview of integration services solutions
  
• SSIS Package Architecture Overview
  
• Integration Services Tools
  
• Development and Management Tools
  
• Deploying and Managing SSIS
  

Developing Integration Services Solutions

• Creating an Integration Services Solution
  
• Using Variables
  
• Building And Running a Solution
  
• Development and Management Tools
  
• The Import and Export Wizard
  
• Importing and Exporting Data
  
• Working with Packages

Implement Control Flow

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

Control Flow Precedence Constraints

Control Flow Containers

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

Implement Data Flow

Data Flow Sources

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

Date Destinations

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

Data Flow Transformations

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

The slowly Changing Dimentions
Change Data Captures
Data Flow Pipeline
Data Viewers

Variables and Configurations

• Variables Overview
  
• SSIS system variables
  
• Using variables in control flow
  
• Using variables in data flow
  
• Property expressions
  
• Configuration Overview
  
• Configuration options
  
• Configuration discipline

Implement Logging in SSIS

Overview of Integration Services Logging
Implementing Logging

Explore Debugging and Error Handling

• SSIS Debugging overview
  
• Debugging a Package
  
• Implement error handling
  
• Configuring Package Logging
  
• Built in log providers

Implement Checkpoints AND Transactions

Implement Checkpoints
Implement Transactions

SSIS Administration

Deploying Packages

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

Managing Packages

• Managing packages with DTUtil
  
• Managing packages with SQL Server Management Studio
  
• Scheduling packages with SQL Server Agent

Securing Packages

• SSIS Package Security Options
  
• Encrypting SSIS Packages
  
• SSIS Package Store and SSISDB
  
• Security Audits Reports and Limitations
  
• SSIS Folder Level Security Options
  
• Project Level Security and SSIS Roles
  
• Container and Package Logging

Scheduling a Package

SQL Server Analysis Services SSAS

Introducing SSAS

• Defining Microsoft Business Intelligence
  
• Understanding the UDM
  
• Differences between Data Warehouse database and OLTP database
  different types of OLTP
  
• Understanding Realworld Databases
  
• Need for OLAP Databases and Mining
  
• Need for Cubes and Faster Reports
  

SSAS Architecture

• SSAS Architecture introduction
  
• Cubes Analysis Services Tools
  
• Dimension Tables Fact tables

SSAS Installation and Configuration

• Introduction to SSAS 2012 & 2014
  
• Multidimensional Mode Configuration
  
• Tabular Mode Configuration, In-Memory
  
• Testing SSAS Workspace Servers in SSDT
  

Cube Design Process and Concepts

• Dimensions Measure groups
  
• Creating Simple Calculations
  
• Aggregations and Hierarchies
  
• Dimensions Relationships
  
• Transactions Perspectives
  
• 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

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

OLAP Modeling

• Modeling source schemas-stars and snowflakes
  
• Understanding dimensional modeling Dimensions (Type 1, 2, or 3) or rapidly changing
  
• Understanding fact (measures) and cube modeling
  

Advanced Cube Design Process

• Understanding MDX
  
• About Members, Tuples, and Sets
  
• Common MDX Functions Explained
  
• New or Updated MDX Functions or Keywords
  
• Queries Implementing
  
• KPI Implementing
  
• Actions Using Write back
  
• Intermediate MDX
  
• Understanding the Calculations Sub tab
  
• Adding Calculated Members
  
• Adding MDX Scripts
  
• Adding Named Sets

SSAS Administration

• Apply Security Roles
  
• Levels of Security 
  
• OLAP Processing
  
• Monitoring Cube activity Deployment
  
• Planning for Disaster Recovery
  
• Performance Tuning SSAS

Microsoft SQL Server Reporting Services(SSRS)

Introduction to SSRS & Usage

• Overview of SQL Server Reporting Services
  
• Installing Reporting Services
  
• Reporting Services Tools
  
• Three-Phase Report Life Cycle
  
• Reporting Services Configurations

SSRS Architecture

• Report types and formats
  
• SSRS Web Services
  
• Report Designer Usage and Scope
  
• Report Builder Usage and Scope
  
• Report Manager and Options

Report Planning and Design

• Report Authoring Options
  
• RDSConnections
  
• Credentials and Security Options
  
• Table Reports 
  
• Matrix Reports
  
• Report Images, Textbox and Globals
  
• Dynamic Expressions and Options
  
• Reporting Functions and Formatting
  
• Report Headers and Report Footers
  
• Aliasing & Expressions
  
• Queries with Many-to-Many Relations
  
• Enterprise Reports
  
• Syntax

Creating a Basic Table Report

Formatting Report Pages
Grouping and Sorting

Report Parameter

• Report Parameters & Properties
  
• Dynamic Report Parameters and Filters
  
• Dependent Parameters & Usage
  
• Multi Valued Nullable Parameters
  
• Formatting Options & Expressions
  
• Report Parts and Reusability
  
• Report Filters - Options and Types
  

Creating Matrix Reports and Charts

• Map Reports 
  
• Indicators with Map Reports
  
• Designing Reports using OLAP DBs
  
• Identifying KPIs and Measures
  
• MDX Queries 
  
• MDX Filters 
  
• Accessing and Controlling Reports
  
• Tuning MDX Reports in SSRS

Managing Reporting Services

• Report Manager Configurations
  
• SSRS Report Builds and Deployment
  
• Managing Data Source Security
  
• Snapshot Management & Schedules
  
• ReportServerTempdb Usage & Tuning
  
• Report Caching and Memory Refresh
  
• Managing Reporting Engine & Tuning
  
• Report Execution Audits & Events

Reporting Services Security

• Data Source Security Credentials
  
• Report Server Security Levels
  
• Security Options and Report Users
  
• Report (RDL) Configuration Options
  
• Managing Configurations Files
  
• RSConfig & Encryption Keys
  
• SSRS Reports Upgradations
  

Programming Reporting Services

• Reporting services APIs
  
• Using the Reporting Services Web Service
  
• Working with Custom Assemblies

Ad-Hoc Reporting with ReportBuilder

• Creating reports with Report Builder
  
• Deploying Report Builder to users
  
• Dragging and dropping entities onto charts tabular reports
  

Publishing and Executing Reports

• Preparing reports for publishing
  
• Configuring project properties
  
• Units of deployment: Individual reports, projects, solutions, data sources
  
• Checking results with Report Manager

Administering SSRS

• Server Administration Performance 
  
• Reliability monitoring
  
• Administering Report Server 
  
• Databases Security