About us   Registration   Site map    
      New user|Forgot password  
Select Your Country to check the Schedule for this Course
Time Zone:
Course Name Course Code
Course Contents


BI and Microsoft SQL


Before attending this course, students must have:

·         Basic knowledge of SQL Server

1.      BI Introduction

ü  Measurement, Metrics and Analysis

ü  Fact

ü  Dimensions

ü  Dimension Modeling

ü  ETL

ü  Simple Pivot

ü  Cubes introduction

ü  Analytical reporting


2.      Dimension Modeling with Microsoft Tools

ü  Logical modeling using Visio

o   Identifying Facts and Dimensions

o   Shared dimension

o   Time dimension

o   Star Schema

o   Snow flake  Schema

ü  Dimension Types (Type 1, Type 2, Type 3)

ü  Typical BI Architecture

ü  Deploying the model into Ms SQL

ü  Managing change

ü  ErWin Introduction


3.      ETL with Microsoft Tools

ü  Native ETL in MS SQL

o   BCP

o   Bulk Insert





ü  SQL Server Integration Services (SSIS)

o   SQL Server Integration Services Basics

o   Exploring the need for migrating diverse data

o   SSIS Architecture

o   Preparing for Installation

o   Utilizing basic SSIS objects

·         Configuring connection managers

·         Adding data flow tasks to packages

·         Reviewing progress with data viewers

·         Assembling tasks to perform complex data migrations

ü  Operating system level tasks

o   Copying, moving and deleting files

o   Transferring files with the FTP task

o   Reading system information with WMI query language (WQL)

ü  Communicating with external sources

o   Sending messages through mail

o   Detecting system events with WMI

ü  Processing XML

ü  Extending Capabilities with Scripting

o   Writing expressions

o   Making properties dynamic with variables

o   Building expressions in Expression Builder

o   Script Task

o   Extending functionality with the Script Task

o   Debugging, breakpoints, watches

ü  Transforming with the Data Flow Task

o   Performing transforms on columns

o   Converting and calculating columns

o   Transforming with Character Map

o   Combining and splitting data

o   Merge, Union, Conditional Split

o   Multicasting and converting data

o Aggregate, sort, audit and look up data  

ü  Manipulating row sets and BLOB data

ü  Reading and writing binary files

ü  Importing and exporting BLOB data

§  Redirecting error rows

§  Performing database operations

§  Executing a SQL task

§  Bulk inserting data from text files

§  Error Handling, Logging and Transactions

§  Organizing package work flow

ü  Defining success, failure, completion and expression precedence constraints

§  Adapting solutions with package configurations

§  Auditing package execution results

§  Managing and securing packages

ü  Storing packages in Package Store and msdb

§  Encrypting packages with passwords and user keys


4.      Cubes with Microsoft Tools

ü  Simple Pivot using Excel

ü  SQL Server Analysis Services (SSAS)

o   SQL Server Business Intelligence Development Studio

o   Adding Data Source

o   Adding  Data view

o   Adding Dimension

o   Create the cube

·         Measures

·         Calculated members

·         Actions

·         KPI

·         Partition

·         Perspectives

·         Translations

ü  MDX introduction.

ü      XMLA introduction. 

5.      Analytical reporting with Microsoft Tools

ü  Simple report using Excel

ü  SQL reporting Services  (SSRS)

o   Reporting Services Architecture

o   SQL Server Business Intelligence Development Studio

o   Building a report

·         Simple Report

·         Calculations and Formatting

·         Grouping and Sorting

·         Creating Drill-Down Reports

·         Report Parameters

·         Multi-Valued Parameters

·         Creating Matrix Reports and Charts

·         Managing Reporting Services

·         Reporting Services Security

ü  Introduction to ProClarity

ü  Introduction to PPS


6.      Introduction to Data Mining


 At Course Completion

After completing this course, students will be able to:

·         Explain the elements of SQL Server Business Intelligence.

·         Input and output data, script to branch control, and configure and deploy packages.

·         Create an account and work with measures.

·         Scale Analysis Services.

·         Use Client Reporting Tools and the web service.

·         Create custom OLAP clients.

·         Create queries using Analytical MDX.

·         Create and execute a package using Integration Services.