MSBI Training

  • Introduction to Data warehousing & Business Intelligence SQL Server 2012 BusinessIntelligenceCourse 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&