Training

Home > Training > Microsoft > Sharepoint (MOSS & WSS) >

WA1778 Accelerated Microsoft Business Intelligence: From SQL 2008 through SharePoint and Office 2007 Training and Courseware

This course provides students with the knowledge and skills to develop Microsoft End-to-End business solutions using SQL Server 2008 in an integrated environment with SharePoint and Office 2007. The course introduces the students to Microsoft Unified Data Model, SQL 2008 Analysis Services, Integration Services, Reporting Services, Report Builder 2.0, SharePoint 2007 Integration, Reporting Services Content Types, Business Data Catalog, and Business Intelligence with Excel 2007 and Visio 2007.

Course Outline

Module 1: Course Overview

• Introductions
• Course Materials
• Course Outline
• Facilities

Module 2: The Microsoft Business Intelligence Stack

• Overview
• The Goal of Business Intelligence
  • Informed and Timely Decisions
  • The Microsoft BI Stack
• Key Concepts
  • Data Warehouse
  • Data Mart
  • Cubes
  • Dimensions
  • Measures
  • Fact Table
• Key Components
  • SQL Server Analysis Server
  • SQL Server Integration Server
  • SQL Server Reporting Services
  • Data Mining
  • MDX
• SharePoint Report Center
  • Office 2007
  • Common Business Challenges in BI
  • Slow to Execute Queries
  • OLTP Source Runs Slow
  • Manual Query Writing
  • Disparate Data Sources
  • Invalid or Inconsistent Report Data
  • Data is Not Available to all Users
  • Too Much Data
  • Lack of Common Top Level Metrics
• Discussion
  • What can cause a BI project to fail?

Module 3: Unified Data Model

• Overview
• UDM Defined
  • Definition
  • Structure
  • Data Sources
  • Data Views
  • Proactive Caching
  • XML Definitions
• User Experience
  • Relational Querying the Data Sources Directly
  • UDM and Simplified Access
• Advantages
  • OLAP Built on Transactional Data
  • Extremely Low Latency
  • Ease of Creation and Maintenance
  • Design Versioning with Source Control
• Trouble in River City
  • When not to use the UDM
  • Not an appropriate OLE DB Provider
  • Data Not stored in a RDMS
  • No Full Time Connection to the Data Base
  • Custom Transport of Data like FTP Tape etc
  • Dirty Data
  • Needing cleansed by a ETL process
• Discussion
  • Considering the Pros and Cons of the UDM is it feasible in your environment?
• Demonstration
  • SQL Management Studio
  • BIDS
  • Files
• Lab
  • Creating a Data Mart using Business Intelligence Development Studio.
  • Enabling Table Compression
• Review

Module 4: SharePoint and Business Intelligence

• Overview
• What is SharePoint?
  • Collaboration
  • Portals
  • Enterprise Search
  • Content Management
  • Business Process and Forms
  • Business Intelligence (Huh? I’m listed last?)
• What Does SharePoint Bring to BI?
  • Excel Services
  • External Connection Management
  • Report Management
  • Report Center
  • Business Data Catalog
  • Dashboards
  • Workflows
  • Key Performance Indicators
  • And more but we have only so much time
• Key SharePoint Concepts
• SharePoint or WSS?
• SharePoint Architecture
  • MOSS adopts a typical 3-tier model
  • The Shared Service Provider
• Content Types
  • Document Classification System
  • Metadata
  • Document Templates
  • Forms Associated With
  • Workflows Associated with
  • Supports Inheritance
• Solutions
  • Features
  • Features with a Capitol “F”
  • Features in SharePoint mean Capabilities
  • Can be targeted to a level such as Site or Farm
  • Can be deployed with Solutions
  • Can be built with Dependencies
  • Think of them like DLLs that can be combined
• 12 Hive
  • Program Files\Common Files\Microsoft Shared\web server extensions\12
  • The 12 hive is where everything in SharePoint happens.
  • Directories and What They Contain
• Discussion
  • How is SharePoint being used in your environment now?
  • What plans do you have for expanding SharePoint and the BI interface
  • How Does the Performance Point Roll in affect your future Implementations?
• Demonstration
  • The Central Administration Site
  • Setting up a Report Center
  • Shared Service Provider
• Lab
  • Setting up A Report Center
  • Setting up A Report Center
• Review

Module 5: New BI Features in SQL 2008

• Overview
• Integration Services
  • Performance Enhancements
  • Programmability Enhancements
  • Usability Enhancements
• Analysis Services
  • Performance Enhancements
  • Programmability Enhancements
  • Data Mining
• Reporting Services
  • Architectural Enhancements
  • Management Enhancements
  • Development Enhancements
  • New Teradata Data Source
  • New Report Designer
  • Enhanced Rendering Extensions
  • Report Builder 2.0
• Discussion
  • Which enhancements may have the most affect on your environment?
• Lab
  • Explore Some Of The New Features
• Review

Module 6: SQL Server Integration Services Major Components

• Overview
• Major Components
  • Data Import/Export Wizard
  • ETL Tool
  • Control Flow Engine
  • Application Platform
  • High Performance Data Transformation Data Pipeline
• New or Changed Features
  • Script Task (VB 0r C#)
  • MERGE operation in an SQL statement.
  • Change Data Capture
  • Dataflow task Improvements
  • New Data Profiling Task and Data Profile Viewer
  • Enhanced Performance and Caching for the Lookup Transformation
  • New Debug Dump Files
• Code Samples
  • The SSIS Object Model
  • Downloading a File Using HTTP
  • Loading the Contents of a Text File into a Variable
  • Simple Custom Logging
  • Handling Exceptions
• Demonstration
  • BIDS Integration Project
  • Control Engine
  • Data Engine
• Lab
  • Create A SSIS Project
  • Add a Data Source
  • Create A Data Flow
  • Create Another Data Flow
  • Load Data via a SQL Statement
• Lab

Module 7: SQL Server Integration Services Extract/Transform/Load

• Overview
• SSIS and ETL
  • ETL Life Cycle
  • Extract
  • Transform
  • Load
• Transformers
• Best Practices for Deployment
  • Scope
  • Deploying Packages
  • Testing Packages
  • Data Flow Practices
  • Package Design
  • SSIS is an in-memory pipeline, so ensure that all transformations occur in memory.
• Best Practices for Design
  • Optimize the SQL data source, lookup transformations, and destination.
  • Use data types – yes, back to data types! –Oh NO!.
  • Change the design.
  • Minimize logged operations.
  • Schedule and distribute it correctly.
• Discussion
  • What Real World Problems Might Compromise a Deployed ETL Package?
• Lab
  • Populating the Fact Table
  • Loading Manufacturing Facts
  • Handling Errors
  • Inventory Fact Flow
• Review

Module 8: SQL Server Analysis Server Part 1

• Overview
• Key Concepts
  • Cubes
  • Multi Dimension Space
• Schemas
  • Star
  • Snowflake
• Measures
  • Measures in a Multidimensional Cube
  • Measure Groups
  • Measure Group Dimensions
• Dimensions
  • Hierarchies
  • Time Dimension
  • Slowly Changing Dimension
• Discussion
  • For a Schema Why Might You Choose A Star, A Snowflake or a Mix?
• Lab
  • Define an OLAP Cube
  • Working with Measures and Measure Groups
  • Time and Hierarchies
  • Relating Dimensions
  • Build Deploy and Browse the Cube
• Review

Module 9: SQL Server Analysis Server Part 2

• Overview
• Cube Processing
  • ProcessFull
  • ProcessData
  • ProcessIndex
  • ProcessAdd
  • ProcessClear
• Partitions
  • MOLAP
  • ROLAP
  • HOLAP
• Proactive Caching with
  • MOLAP
  • ROLAP
  • HOLAP
  • Notification Settings
• Aggregations
  • Definition
  • Design of Aggregations
  • Monitoring
• Lab
  • Multiple Partitions
  • Set Storage
  • Design Aggregations
  • Default Members
• Review

Module 10: SQL Server Reporting Services

• Overview
• Integration with SharePoint
  • SharePoint Integrated mode provides access to report server content and operations through a SharePoint site.
  • Integrated Install
• Report Builder 2.0
  • Tablix
  • Charts
    • New Chart Types
    • Advanced Chart Functionality
    • Parameters
    • Interactive Sorting
    • Actions
    • Document Maps
    • Sub-Reports
  • Interactive Features
• Differences Between Environments
  • Developers
  • Basic Users (Need SMDL Models by Developers)
  • Power Users (My Man!!!!) Does not need Developer effort!
• Discussion
  • How Far Will The New Features In Report Builder 2.0 Go Towards The Goal OF Information Workers and Team Generating Their Own Reports?
• Lab
  • SSRS Projects Report Wizard
  • SSRS SMDL
  • Report Builder 2.0
• Review

Module 11: SharePoint Business Data Catalog

• Overview
• Why A Business Data Catalog
• Special BDC Only Web Parts
  • Business Data Web Parts
  • Business Data List
  • Business Data Item Web Part
  • Business Data Related List Web Part
  • Business Data Item Builder
• Application Definition Files
  • Line of Business
  • Entity
  • Method
  • Parameter
  • Filter Descriptor
  • Association
  • Action
• Authentication and Security
  • PassThrough
  • RevertToSelf
  • WindowsCredentials/RDBCredentials
  • Credentials
• Demonstration
  • The Application Definition File
  • The BDC Web Parts
• Lab
  • Use a BDC Application
  • Explore BDC Web Parts
  • Use a BDC List
• Review

Module 12: SharePoint Integrated Security and Report Management

• Overview
• Understanding SharePoint Security
  • Groups
  • Securable Items
  • Securable Items
  • Who Can See What
  • All Those Groups!
• Report Content Types
  • Enable Content Type Management
  • Add the Report Specific Content Types
  • If not Enabled Enable the Report Server Integration Feature
• Report Subscriptions
  • Scheduled Delivery
  • Rendering Type
  • Where to Deliver
  • SharePoint
  • File Share
  • Other
• Workflows
  • Out Of Box
  • SharePoint Designer
  • Visual Studio
• Reports Library
  • Part of The Report Center Template
  • Default Content Types
  • Can be Created in Any Site
  • Default Launch is Excel
• Business Web Parts
  • What is A Web Part?
  • Many Out of The Box
  • Can be Interconnected
  • Can be Custom Developed
  • SmartPart
• Demonstration
  • SharePoint Groups
  • Permission Levels
  • Workflows
• Lab
  • Examine Permissions
  • Set Shared Schedule for Subscriptions
  • Manage Data Connections
  • Set up a Subscription
• Review

Module 13: Excel 2007 and Data Mining

• Overview
• Notable Uses Of Data Mining
  • Pattern Mining
  • Subject Based Mining
  • Games
  • Business
• Key Terms in Data Mining
  • Data-Mining Model
  • Columns
  • Cases
  • Case Tables and Nested Tables
• Types of Data Mining Algorithms
  • Classification Algorithms
  • Regression Algorithms
  • Segmentation Algorithms
  • Association Algorithms
  • Sequence Analysis Algorithms
• The Algorithms
• Excel and The BI Extensions
  • Add ins
  • Forecast Table Analysis Tool
  • Prediction Calculator Tool
  • Shopping Basket Analysis Tool
  • Detect Categories Table
  • Analyze Key Influencers
• Visio 2007
  • Enhanced to Diagram BI
  • Not included with Office
• Discussion
  • In You Environment Which Algorithm Appears To Have The Most Potential?
• Lab
  • Utilize the Excel Data Mining Tools
• Review

Buy on-site and customized class

Attend a public class