MSBI SQL Server

Introduction to Data warehousing & Business Intelligence SQL Server 2012 Business

Intelligence

Course Agenda:

 SQL Server 2012 Integration Services (SSIS)

 SQL Server 2012 Analysis Services (SSAS)

 SQL Server 2012 Reporting Services (SSRS)

SQL Server 2012 Integration Services

Introduction to SQL Server Integration Services

 SSIS Package Architecture Overview

 Development and Management Tools

 The Import and Export Wizard

Guided tour of Business Intelligence Development Studio

Introduction to Data Flow

 Data Flow Overview

 Data Sources

 Data Destinations

 Data Flow Transformations

 Data Viewers

Data Sources

 Excel Source

 Flat File Source

 OLE DB Source

 XML Source

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

Advanced Data Flow

 Lookup Transformation

 Merge Transformation

 Merge Join Transformation

 Slowly Changing Dimension Transformation

 Pivot Transformation

 Export Transformation

 Import Transformation

 Unpivot Transformation

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 Control Flow

 For Loop Container

 For Each Loop Container

 Sequence Container

Variables and Configurations

 Variables Overview

 Variable scope

 SSIS system variables

 Using variables in control flow

 Using variables in data flow

 Using variables to pass information between 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

SSIS Package Deployment

 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 2012 Analysis Services

What Is Microsoft Business Intelligence?

 Defining Microsoft Business Intelligence

 Understanding the 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 Multiple 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 MDX

 Understanding the Calculations Sub tab

 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 2012 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/detail reports

 Linking sub reports

Presenting data with appropriate detail

 Revealing report detail with drill down

 Displaying related data with drill through

 Navigating with document maps

Deploying and Delivering Reports

Deploying reports to the server

 Preparing reports for publishing

 Configuring project properties

 Units of deployment: Individual reports, projects, solutions, data sources

 Checking results with Report Manager

Securing Reporting Services

Securing the RS environment

 Assigning users to roles

 Setting permissions on report items

 Granting and revoking system-level permissions

Creating reports with Report Builder

 Deploying Report Builder to users

 Dragging and dropping entities onto charts and tabular reports

 Navigating data with infinite drill through

Note:

  1. Real-time scenario’s
  2. Providing the daily notes.
  3. Daily session 1 hrs.
  4. After finish the course providing to Real-time Interview Q&A