MSBI Online Training

MSBI Online Training

MSBI Online Training

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...Loading...

MSBI Online Training Concepts :

 

Unit 1: What Is Microsoft BI?

Core concept – BI is the cube or UDM
Example cube as seen using Excel pivot table
MS BI is comprehensive – more than Analysis Services on SQL Server
Demonstration of SQL Reporting Services with cube as data source
OLAP Modeling

Unit 2: Modeling source schemas—stars and snowflakes

Understanding dimensional modeling— Dimensions (Type 1, 2, or 3) or rapidly changing
Understanding fact (measures) and cube modeling
Other types of modeling—data mining etc…
Using SSAS in BIDS

Unit 3: Understanding the development environment

Creating Data Sources and Data Source Views
Creating cubes – using the UDM and the Cube Build Wizard
Refining Dimensions and Measures in BIDS
Intermediate SSAS

Unit 4: KPIs

Perspectives
Translations – cube metadata and currency localization
Actions – regular, drill-through and reporting
Advanced SSAS

Unit 5: Using multiple fact tables

Modeling intermediate fact tables
Modeling M:M dimensions, Fact (degenerate) dimensions, Role-playing dimensions, write back dimensions
Modeling changing dimensions – Dimension Intelligence w/ Wizard
Using the Add Business Intelligence Wizards – write-back, semi-additive measures, time intelligence, account intelligence
Cube Storage and Aggregation

Unit 6: Storage topics – basic aggregations, MOLAP

Advanced Storage Design – MOLAP, ROLAP, HOLAP
Partitions – relational and Analysis Services partitions
Customizing Aggregation Design – Processing Design
Rapidly changing dimensions / ROLAP dimensions
Welcome to the Real Time – Proactive Caching
Cube processing options
Beginning MDX

Unit 7: Basic syntax

Using the MDX query editor in SQL Server Management Studio
Most-used Functions & Common tasks
New MDX functions
Intermediate MDX

Unit 8: Adding calculated members

Adding scripts
Adding named sets
.NET Assemblies
SSAS Administration

Unit 9: Best practices – health monitoring

XMLA scripting (SQL Mgmt Studio)
Other Documentation methods
Security – roles and permissions
Disaster Recovery – backup / restore
Clustering – high availability
Introduction to Data Mining

Unit 10: What and why?

Examples of using each of the 9 algorithms (MS Clustering, MS Decision Trees, Naïve Bayes, MS
Sequence Clustering, MS Time Series, MS Association Rules, MS Neural Network)
Data Mining dimensions
Data Mining clients
Processing mining models
Introduction to Reporting Clients

Unit 11: Excel 2003 Pivot Tables

SQL RS & Report Builder
SPS RS web parts & .NET 0 report viewer controls
Business Scorecards 2005 & ProClarity
Future Directions – Integration with Office 12

Unit 12: SharePoint 12 and AS

Report Center (type of dashboard) uses KPIs, Reports, Excel Web, Filter
Excel Services 12 and AS (Web Services)
SQL Server 2005 Integration Services
Introduction to SQL Server Integration Services

Unit 13: Product History

SSIS Package Architecture Overview
Development and Management Tools
Deploying and Managing SSIS Packages
Source Control for SSIS Packages
SSIS for DBAs: Using SQL Server Management Studio

Unit 14: The Import and Export Wizard

Importing and Exporting Data
Working with Packages
Database Maintenance Plans
Creating Database Maintenance Plans using SSMS
Scheduling and Executing Plans
Examining Database Maintenance Plan Packages
Changes in SQL Server 2005 Service Pack 2
Business Intelligence Development Studio

Unit 15: Launching BIDS

Project templates
The package designer
The Toolbox
Solution Explorer
The Properties window
The Variables window
The SSIS menu
Introduction to Control Flow

Unit 16: Control Flow Overview

Precedence Constraints
The Execute SQL Task
The Bulk Insert Task
The File System Task
The FTP Task
The Send Mail Task
Advanced Control Flow

Unit 17: Containers – grouping and looping

The Web Service Task
The WMI tasks
The Analysis Services tasks
The Execute Process Task
The Execute Package Task
Introduction to Data Flow

Unit 18: Data Flow Overview

Data Sources
Data Destinations
Data Transformations
The Copy Column Transformation
The Derived Column Transformation
The Data Conversion Transformation
The Conditional Split Transformation
The Aggregate Transformation
9.The Sort Transformation
10.Data Viewers
1 Variables and Configurations

Unit 19: 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
9.Configuration discipline
10.Debugging, Error Handling and Logging

Unit 20: SSIS debugging overview

Breakpoints in SSIS
SSIS debugging windows
Control Flow: The OnError event handler
Data Flow: Error data flow
Configuring Package Logging
Built-in log providers
Advanced Data Flow

Unit 21: Revisiting data sources and destinations

The Lookup Transformation
Getting Fuzzy: The Fuzzy Lookup and Fuzzy Grouping Transformations
The Multicast Transformation
The Merge and Merge Join Transformations
The Data Mining Query Transformation
The Data Mining Model Training Destination
The Slowly Changing Dimension Transformation
Extending SSIS through Custom Code

Unit 22: 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

Unit 23: Configurations and deployment

The deployment utility
Deployment options
Deployment security
Executing packages – DTExec and DTExecUI
SSIS Package Management

Unit 24: The SSIS Service

Managing packages with DTUtil
Managing packages with SQL Server Management Studio Scheduling packages with SQL Server Agent
SQL Server 2005 Reporting Services
Introduction to SQL Server Reporting Services

Unit 25: Tour of Features

Reporting Services Architecture
Reporting Services Terminology
Reporting Services Editions
Taking Reporting Services for a Spin
Creating Reports

Unit 26: Deconstructing Reporting Services

Shared Data Sources
Creating Reports from Scratch
Calculations and Formatting

Unit 27: Creating Expressions

Using the Global Collections
Formatting Items
Conditional Formatting
Grouping and Sorting

Unit 28: Creating Groups

Calculating Totals and Percentages
Interactive Sorting
Creating Drill-Down Reports
Report Parameters

Unit 29: Creating Report Parameters

Creating Drop-Down Parameters
Multi-Valued Parameters
Debugging Parameter Issues
Creating Matrix Reports and Charts

Unit 30: Creating a Basic Matrix Report

Matrix Subtotals
Creating a Basic Chart
Exploring the Charting Possibilities
Managing Reporting Services

Unit 31: Deploying Reports and Data Sources

Exporting Reports and Printing
Using the Web-Based Report Manager
Using SQL Server Management Studio-based Reporting Services Manager
Reporting Services Security

Unit 32: Two Parts to Reporting Services Security

Securing Access to Reports
Data Source Security
Programming Reporting Services

Unit 33: The Many Reporting Services APIs

Integrating Reporting Services into Applications using URL Access
Using the Report Viewer Controls
Advanced Reporting Services Programming

Unit 34: Using the Reporting Services Web Service

Working with Custom Assemblies
Snapshots and Subscriptions

Unit 35: Caching and Snapshots

Creating Standard Subscriptions
Creating Data-Driven Subscriptions
Managing Subscriptions
Ad-Hoc Reporting with Report Builder

Unit 36: The Report Builder Architecture

Creating Report Model Projects
Running Report Builder

Lessons

  1. Core concept – BI is the cube or UDM

    Example cube as seen using Excel pivot table

    MS BI is comprehensive – more than Analysis Services on SQL Server

    Demonstration of SQL Reporting Services with cube as data source

    OLAP Modeling

  2. Understanding dimensional modeling— Dimensions (Type 1, 2, or 3) or rapidly changing

    Understanding fact (measures) and cube modeling

    Other types of modeling—data mining etc…

    Using SSAS in BIDS

  3. Perspectives

    Translations – cube metadata and currency localization

    Actions – regular, drill-through and reporting

    Advanced SSAS

  4. Modeling intermediate fact tables

    Modeling M:M dimensions, Fact (degenerate) dimensions, Role-playing dimensions, write back dimensions

    Modeling changing dimensions – Dimension Intelligence w/ Wizard

    Using the Add Business Intelligence Wizards – write-back, semi-additive measures, time intelligence, account intelligence

    Cube Storage and Aggregation

  5. Advanced Storage Design – MOLAP, ROLAP, HOLAP

    Partitions – relational and Analysis Services partitions

    Customizing Aggregation Design – Processing Design

    Rapidly changing dimensions / ROLAP dimensions

    Welcome to the Real Time – Proactive Caching

    Cube processing options

    Beginning MDX

  6. Using the MDX query editor in SQL Server Management Studio

    Most-used Functions & Common tasks

    New MDX functions

    Intermediate MDX

  7. XMLA scripting (SQL Mgmt Studio)

    Other Documentation methods

    Security – roles and permissions

    Disaster Recovery – backup / restore

    Clustering – high availability

    Introduction to Data Mining

  8. Examples of using each of the 9 algorithms (MS Clustering, MS Decision Trees, Naïve Bayes, MS

    Sequence Clustering, MS Time Series, MS Association Rules, MS Neural Network)

    Data Mining dimensions

    Data Mining clients

    Processing mining models

    Introduction to Reporting Clients

  9. SQL RS & Report Builder

    SPS RS web parts & .NET 0 report viewer controls

    Business Scorecards 2005 & ProClarity

    Future Directions – Integration with Office 12

  10. Report Center (type of dashboard) uses KPIs, Reports, Excel Web, Filter

    Excel Services 12 and AS (Web Services)

    SQL Server 2005 Integration Services

    Introduction to SQL Server Integration Services

  11. SSIS Package Architecture Overview

    Development and Management Tools

    Deploying and Managing SSIS Packages

    Source Control for SSIS Packages

    SSIS for DBAs: Using SQL Server Management Studio

  12. Importing and Exporting Data

    Working with Packages

    Database Maintenance Plans

    Creating Database Maintenance Plans using SSMS

    Scheduling and Executing Plans

    Examining Database Maintenance Plan Packages

    Changes in SQL Server 2005 Service Pack 2

    Business Intelligence Development Studio

  13. Project templates

    The package designer

    The Toolbox

    Solution Explorer

    The Properties window

    The Variables window

    The SSIS menu

    Introduction to Control Flow

  14. Precedence Constraints

    The Execute SQL Task

    The Bulk Insert Task

    The File System Task

    The FTP Task

    The Send Mail Task

    Advanced Control Flow

  15. Data Sources

    Data Destinations

    Data Transformations

    The Copy Column Transformation

    The Derived Column Transformation

    The Data Conversion Transformation

    The Conditional Split Transformation

    The Aggregate Transformation

    The Sort Transformation

    Data Viewers

    Variables and Configurations

  16. 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

  17. Breakpoints in SSIS

    SSIS debugging windows

    Control Flow: The OnError event handler

    Data Flow: Error data flow

    Configuring Package Logging

    Built-in log providers

    Advanced Data Flow

  18. The Lookup Transformation

    Getting Fuzzy: The Fuzzy Lookup and Fuzzy Grouping Transformations

    The Multicast Transformation

    The Merge and Merge Join Transformations

    The Data Mining Query Transformation

    The Data Mining Model Training Destination

    The Slowly Changing Dimension Transformation

    Extending SSIS through Custom Code

  19. 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

  20. The deployment utility

    Deployment options

    Deployment security

    Executing packages – DTExec and DTExecUI

    SSIS Package Management

  21. Managing packages with DTUtil

    Managing packages with SQL Server Management Studio Scheduling packages with SQL Server Agent SQL Server 2005 Reporting Services

    Introduction to SQL Server Reporting Services

  22. Reporting Services Architecture

    Reporting Services Terminology

    Reporting Services Editions

    Taking Reporting Services for a Spin

    Creating Reports

  23. Using the Global Collections

    Formatting Items

    Conditional Formatting

    Grouping and Sorting

  24. Calculating Totals and Percentages

    Interactive Sorting

    Creating Drill-Down Reports

    Report Parameters

  25. Creating Drop-Down Parameters

    Multi-Valued Parameters

    Debugging Parameter Issues

    Creating Matrix Reports and Charts

  26. Exporting Reports and Printing

    Using the Web-Based Report Manager

    Using SQL Server Management Studio-based Reporting Services Manager

    Reporting Services Security

  27. Integrating Reporting Services into Applications using URL Access

    Using the Report Viewer Controls

    Advanced Reporting Services Programming

  28. Creating Standard Subscriptions

    Creating Data-Driven Subscriptions

    Managing Subscriptions

    Ad-Hoc Reporting with Report Builder