To speak to a training advisor please call

0800 40 848 40

training@coursemonster.com

All Courses / Microsoft Courses / Microsoft SQL Server 2008: Business Intelligence End to End with SQL Server 2008 R2 and SharePoint 2010

Share: Follow us:

Bookmark with:

Print Page:

Microsoft SQL Server 2008: Business Intelligence End to End with SQL Server 2008 R2 and SharePoint 2010

Online Training

Time stretched?

If you would prefer something more flexible, we can now offer Online Training related to this topic.

Click to see a list of courses »

Course Overview

Business intelligence solutions provide the infrastructure that enables users at all levels of a business to make better decisions based on more accurate and up-to-date information. This workshop focuses on teaching IT professionals the best practices and skills required to successfully design, build and operate a business intelligence solution using SQL Server 2008 R2 Integration Services, Analysis Services, Reporting Services, performancePoint Monitoring/Analytics and ProClarity.

CourseMonster are unbiased and "vendor neutral" which means that we can find the right course for you or your company by looking at our entire database of Microsoft training providers.

Related Courses

Audience

This workshop - style course is designed for IT professionals who are interested in learning how to implement Business Intelligence solutions on the Microsoft BI Stack. Basic knowledge of common business requirements for a BI solution is assumed and some experience with SQL Server is required.

Prerequisites

Basic knowledge of Business Intelligence
Knowledge of relational database systems
Delegates will gain the most from this course if they are experienced with SQL Server database to the level of Microsoft course M2779 or M6232. However, as minimum requirement, delegates should have a good working knowledge of T-SQL to level of M50027 or M2778

Course Outline

Module 1 : Business Intelligence Overview
Module 2 : Designing Business Intelligence Solution
Module 3 : Creating the Data Warehouse (SQL)
Module 4 : Loading the Data Warehouse (SSIS)
Module 5 : Creating the Analysis Cube (SSAS)
Module 6 : Enhancing the Analysis Cube (SSAS)
Module 7 : Producing the User Interface
Module 8 : Creating reports with Reporting Services (SSRS)
Module 9 : Creating dashboards with Excel PowerPivot
Module 10 : Creating dashboards with PerformancePoint Services

Module 1 : Business Intelligence Overview

This module will introduce the course concepts and the Microsoft Business Intelligence stack (Line-of-business -> SSIS -> Data Warehouse -> SSAS -> SSRS / ProClarity / PerformancePoint / SharePoint).

Includes a discussion of the BI burger.

Sections

Define terms and products

Business intelligence (BI)
Extract Transform and Load (ETL)
SQL Server Integration Services (SSIS)
SQL Server Analysis Services (SSAS)
SQL Server Reporting Services (SSRS)
PerformancePoint Services for SharePoint
ProClarity
SharePoint
Master Data Management (MDM)

Evaluating BI requirements

Phased delivery

Documenting requirements

High level requirements i.e. reports / scorecards / charts / analytical data

Demonstrations

Examine a sample requirement and identify which products are best suited to the requirement.
Examining a sample requirement
- Identify source data systems
- Identity collisions / overlap from source data systems
- Discuss ways of joining data (i.e. MDM)
- Identify destination requirements i.e. reports, scorecards and charts
- Identify transformations required for making data the correct format and data type.

Labs

Identify software requirements for solutions

Three business requirements that the delegates need to identify which Microsoft products are needed to fulfil requirement
- Group exercise (2-3 per team)
- Notes about assumptions and decisions to be made during exercise
- Show and tell (if appropriate to class)

Module 2 : Designing Business Intelligence Solution

Sections

Reviewing requirements
- Identify each products role in the BI solution
Discuss whether to use a staging database
Discuss use of slowly changing dimensions (SCD)
Introduce temporal / rapidly changing dimensions (RCD)
Discuss use of surrogate keys
Discuss data warehousing techniques
Discuss timely delivery of data from source to destination (daily, hourly, real-time)
Discuss data warehouse schema design with advantages and disadvantages
- Star
- Snowflake
- Normalised
- Combination
Discuss designing a time dimension
- Surrogate key

References

Ralph Kimball
- Data Warehouse Design
Richard T Snodgrass
- Temporal Databases / RCD

Demonstrations

Design a simple data warehouse using 4-5 tables (1 fact)
Design a time dimension

Labs

Documentation of requirements
Map source data onto output requirements
Flow of data from line of business to user interface

Module 3 : Creating the Data Warehouse

Sections

Table design
- Partitioned tables
- Primary and foreign keys
- Data types
- Logging and error tables
Index considerations
- Clustered
- Non-clustered
- Disabling and rebuilding indexe

References

Ralph Kimbal

Demonstrations

Create base objects
Create partitioned tables (using multiple file groups for the fact table)
Index maintenance

Labs

Create data warehouse database
Create the dimension tables
Create the fact tables with partitioning
Create logging and error recording tables
Create indexes on the fact and dimension tables

Module 4 : Loading the Data Warehouse

Sections

SSIS basics
- Packages
- Control Flow
- Data Flow
- Transformations
- Data Sources
- Data Destinations
Configurations
Logging
Lookups
Fast-load and table partitioning
Using switch and merge
Late arriving data
Change Data Capture (CDC - SQL 2008 sources only)
Package design
Transactions
Checkpoints

Demonstrations

SSIS package basics
- Control flow
- Data flow
- Logging
- Control flow
- Execute SQL task
- Caching task
- Data flow task
- Transformations
- Change data capture (CDC)
- Logging
- Configuration
- Variables
- o Lineage

Labs

Design and create a SSIS package structure
Design the dimension load process using slowly changing dimension transformation (SCD)
Add control and data flow items
Use SSIS for lineage, logging, configurations and variables
Create data flow for each dimension table
Create data flow for each fact table

Module 5 : Creating the Analysis Cube

Sections

Data source views
- Friendly names
- Relationships
- Calculated columns
- Named queries
Dimensions
- Time
- Parent-Child
- Multiple table (snowflake)
- Unary Operator
Dimension Attributes
- Key values and display values
- Attribute relationships
Measures
- Data type design
- Aggregations (Additive / Semi-Additive / Non-Additive)
Cubes
- Producing the base cube
- Dimensions usage and relationships
Best practice warnings

Demonstrations

Create a catalog database
Create a data source (DS)
Create a data source view (DSV)
- o Add named query
- o Add calculated column (full name, quarter with year, month with year)
Create dimensions
- Time
- Product-> Product Subcategory -> Product Category
- Geography
- Customers
- Resellers

Labs

Designing dimensions for usability to required design
Design cube using dimensions
Set properties for measures

Module 6 : Enhancing the Analysis cube

Sections

Cubes
- Calculated measures
- Calculated members
- Names sets (Dynamic / Static)
- Key Performance Indicators (KPI)
- Perspectives
- Actions
- Partitions
- Storage design
- Designing aggregations
- User-based optimisation (UBO)
- Partition slice alignment
- Security Roles
- o Processing design

Demonstrations

One per item in section above

Labs

Create calculated measures (profit, profit%)
Create calculated members (total sales amount)
Create named sets (Europe : {[France],[UK],[Germany]})
Create KPI (profit%> 30%)
Create actions (Drillthrough)
Design partitions to correlate to the table partitioning and slices
Design perspectives for Internet and Reseller sales departments
Implement security roles for Internet and Reseller departments
Update SSIS control flow to include processing for SSAS dimensions and cubes

Module 7 : Producing the User Interface

Sections

Best Tool for the job
Infrastructure design
Where to use SSRS , PAS, PerformancePoint

Module 8 : Creating reports with Reporting Services

Sections

Show differences between BIDS and Report Builder 2.
Creating a report
Tools
- Tablix
- Gauges
- Charts
Related reports
Parameters
Deploying
- Standalone SSRS
- SharePoint Integrated

References

Stephen Few
- Show Me The Numbers
Edward Tufte
- Quantitative Evidence

Demonstrations

Create a report using a tablix that shows sales by product category for a particular region (parameterised).
Create a report that holds a bar chart showing sales by regions
Link bar chart report to tablix report passing the selected region as a parameter.

Labs

Create a report to show sales for regions compared to last year
- Allow drillable (region -> country -> city)
- Use groups and Tablix
- Create simple gauge report for sales this year against last year's sales (+10%) by region
- Create a report for sales per product by country (parameterised) for link to SSAS action and implement as action (PDF format)

Module 9 : Creating dashboards with Excel PowerPivot

Sections

Describe positioning of Microsoft PowerPivot
Designing pages
Saving and publishing PowerPivot views
Introduce slicers

References

Stephen Few
- Show Me The Numbers
Edward Tufte
- Quantitative Evidence

Demonstrations

Produce a variety of views such as chart, grid, Performance map, perspective and decomposition tree.
Demonstrate creating a slicer using the background

Labs

Excel Powerpivot working with multiple sources (SSAS, text file and Access)

Module 10 : Creating dashboards with PerformancePoint Services

Sections

Dashboard design best practice
Data Sources
Reports
- Analytic Chart
- Analytic Grid
- SSRS Report
- ProClarity Page
- Scorecard
- Strategy Map
Dashboard
- Filters
- Relating published elements
- Deploying to SharePoint Services (WSS or MOSS)

References

Stephen Few
- Dashboard Design
- Show Me The Numbers
Edward Tufte
- Quantitative Evidence

Demonstrations

Create each of the elements and deploy to SharePoint

Labs

Show dashboard design to best practice (ref. Stephen Few "Information Dashboard Design")
Create a data source from SSAS cube created earlier
Create an analytic chart and grid, include parameters, for sales over time
Create a KPI for sales against last year
Create a scorecard using the KPI and include multiple target columns for target, score and indicator
Create a summary dashboard holding overview reports and scorecard
Introduce filters into the dashboards and relate published reports to filters
Publish dashboards to SharePoint

Related Online Training

If you would prefer the flexibility of online training, use the link(s) below:

More Online Training>>

Top Selling Brands