Business Intelligence with SQL Server 2005 / 2008 Training Course UK

Business Intelligence with SQL Server 2005 / 2008 Training Course Overview

Course Code:	SQL0080
Price:	£1995
Duration:	5 Days - custom / on-site options available - please call.
DATE:
Who should attend:	Anyone interested in getting raw data to the end user as information that has integrity, is retrieved quickly and requires little or no programming. Developers and analysts who need to design and build reports. Administrators who wish to deploy and manage reports.
Prerequisite Skills:	Delegates should have some basic knowledge of SQL language queries. Please see our associated course as a great way to get up to speed.Introduction to SQL primer training course

Business intelligence, also referred to as BI - is the collective term for technologies and software applications which are used to gather, provide access to, and analyse data and information about operations throughout the enterprise.

Business intelligence systems allow companies to understand factors affecting their business, such sales metrics, production, internal operations, which can help organisations make well-informed business decisions.

This course has been written to cover all the main aspects of designing and building, and reporting on databases, data warehouses, and cubes using SQL Server and the Microsoft Business Intelligence suite of tools. These include SQL Server Reporting Services, SQL Server Analysis Services, and SQL Server Integration Services.

We will be running the course in version 2008 and a short session will be available at the end of the SSRS module to demonstrate some of the differences in the Interface between the 2005 and 2008 versions. The SSAS/IS interface has not changed significantly.

Course Content

This course is the combination of these three courses:
SQL Server 2005 Reporting Services (SSRS) - 2 Days, £995+vat
SQL Server 2005 Analysis Services (SSAS) & Integration Services (SSIS) - 3 Days, £1395+vat

Available as a public or closed course at our London training centre, delivered on-site as a bespoke, customised training course.

This course has been developed for real-world, commercial scenarios by our expert instructors. See below for detailed syllabus. If you have a technical question, please email

What You Will Learn

An understanding of Business Intelligence
The need for Star Schemas and Cubes
Simple Cube design
Some advanced Cube design features
Data Mining overview
MDX – introductory level
An understanding of Extract Transform Load (ETL)
How to use SSIS for ETL and Cube processing
Simple Package design
Advanced package design features
Introduction to Fundamental data
Transformation techniques

Business Intelligence with SQL Server 2005 Training Course Outline

Introduction to SQL Server 2005 / 2008 Reporting Services

Architecture

SSRS – Reporting Services
Report Server
Report Designer
Report Manager
Report Viewer
Report Models
Report Builder

Designing a simple report

Report Wizard
Introduction to Report Designer
Using templates
Basic formatting
Previewing reports
Introduction to deploying reports
Exporting reports to Excel and PDFs

Working with Data / Information / Intelligence

Third normal form relational databases in SQL Server/Oracle/DB2
Star schema relational databases in SQL Server/Oracle/DB2
SSAS Cubes
SSAS data mining
SQL queries and stored procedures
MDX queries
DMX queries

Advanced report design

Tables
Matrices
Charts
Grouping
Conditional formatting with SSRS Expression
Language
Conditional formatting with VB.NET (or C# - SQL Server 2008 only)
Creating KPIs

Interactive reports

Sub reports
Drilldown
Parameterised reports

Scheduling automatic report delivery

Using SMTP email
Using Windows file share

Deploying and accessing reports

Using URLs
Using Report Viewer in WinForms and
WebForms
To Report Manager
To SharePoint – WSS
To SharePoint – MOSS
To PerformancePoint - PPS

Introduction - SSAS

Database design

Business Intelligence and the Enterprise Information Flow
Revision of Third Normal Form OLTP
Relational Database structure
Introduction to Star Schema OLAP
Relational Database design
Introduction to Cubes

Building a Star Schema

Dimension tables
Fact table
Designing the Extract Transform Load (ETL) from OLTP to OLAP
Using SQL stored procedures for the ETL
Using SSIS for the ETL to populate the Star Schema

Designing a simple cube

Data sources
Data source views
Cube wizard
Adding dimensions and facts

Designing an advanced cube

Multiple fact table Cubes
Designing hierarchies
Sorting information in the Cube
Cube perspectives
MDX expressions for creating calculated measures

Introduction to Data Mining

What is Data Mining?
Forecast demonstration
Cluster demonstration
Decision tree demonstration

Browsing cubes and Data Mining (Visualisation)

Cubes in SQL Server Management Studio (SSMS)
Cubes in Business Intelligence Development Studio (BIDS)
Cubes in Excel 2003/2007
Cubes in web pages
Data Mining in Excel 2007

Introduction to MDX and DMX queries

Simple MDX queries
MDX reports in SSRS
Simple DMX queries

Administration

Introduction to Cube security
Backing up and restoring OLTP and OLAP
Relational Databases
Backing up and restoring SSAS Cubes
Scheduling backups as jobs
DMX reports in SSRS

What is ETL?

Unnormalised stage
OLTP normalised stage
OLAP denormalised stage
Cube denormalised and aggregated stage
Data Mining stage

SSIS Package Designer

Control flow and Workflow
Control flow toolbox tour
Data flow and transformations
Data flow toolbox tour
Introduction to package design using the main toolbox tools
Script Task using VB.NET (or C# - SQL Server 2008 only)

Deploying Packages

Deploying as files
Deploying to SQL Server
Packages in SSMS

Running Packages

Executing packages from the command shell
Executing packages from VB.NET and C#
Executing packages with xp_cmdshell and
sp_start_job in SQL Server Stored Procedures