Microsoft SQL Server 2005: Analysis & Integration Services (SSAS & SSIS) - (Business Intelligence, BI, Data Warehouse, Mining)

Scheduled Dates (sort by: location | date)

I'd like a different date/location

Please click above and we will take care of the rest.

I’d like an onsite/bespoke course

Please click above and we will be happy to discuss the benefits of onsite and bespoke courses with you.

If suitable dates or locations are not available, click this link and we will arrange one for you

Course Overview

This course has been written to cover all the fundamentals of designing and building data warehouses and cubes using SQL Server 2005 Analysis Services (SSAS), and building high performance data integration solutions with SQL Server Integration Services, including extraction, transformation, and load (ETL) packages for data warehousing.

Our course demonstrates how to build star schemas in SQL Server and cubes in Analysis Services. In addition, it shows how to use Integration Services to migrate and transform data. This is applied to populating and updating OLAP star schemas and cubes from traditional normalised databases.

This course has been classified as IT Technical Training.

Audience

Existing Database specialists who need to get more out of SQL Server 2005 Analysis Services for Data Warehousing and Data Mining.

Skills Gained

By completing this course, delegates will be familiar with:

• Cube design best practises and security
• A deep understanding of MOLAP / ROLAP / HOLAP
• MDX Queries and Scripts
• Learn how to use XMLA to create, process and back-up Cubes
• Effective Data Mining

Prerequisites

• You should be able to understand table, column, data type design, and write SQL Select statements within any relational database
• also to write SQL Server stored procedures. You should have experience of populating a Star Schema
• and of designing, building and deploying simple SSAS cubes with the Cube Wizard.

Course Outline

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

Scheduling Packages

• Scheduling a package as an SSIS job
• Scheduling a package as a stored procedure job

Microsoft SQL Server 2005: Analysis & Integration Services (SSAS & SSIS) - (Business Intelligence, BI, Data Warehouse, Mining) / SQL0060