SQL Server 2005 Analysis Services (SSAS) Training (4 days)

SQL Server 2005 Analysis Services (SSAS) Training Overview

In this SSAS training course, you will learn how to use Microsoft SQL Server 2005 Analysis Services (SSAS) to design and implement OnLine Analytical Processing (OLAP) cubes and data mining models to support Business Intelligence (BI) solutions.This SSAS course includes concepts, procedures and practices based on real-world experience giving both the novice and experienced SQL Server 2005 developer the tools to build data warehousing and decision support system solutions. This course also provides information on end-user tools including Microsoft Excel 2003/2007 and Microsoft SQL Server 2005 Reporting Services. Implementing SSAS solutions have shown to boost data retrieval and report generation from SQL Server by up to 1000%.

SQL Server 2005 Analysis Services (SSAS) Training Course Goals

• Learn the core concepts in building solutions using Microsoft Business Intelligence with SSAS.
• Understand OLAP (cube) modeling and data mining.
• Learn how to develop solutions using Business Intelligence Development Studio (BIDS).
• Understand advanced storage design including MOLAP, ROLAP, and HOLAP.
• Learn the fundamentals of MultiDimensional Expressions (MDX).
• Learn best practices for SSAS administration using SQL Server Management Studio (SSMS) and XMLA (XML for Analysis).
• Learn to work with OLAP reporting clients including Microsoft Excel and SQL Server Reporting Services.
• Learn to integrate with Microsoft Office 2007 and Microsoft SharePoint 2007.

SQL Server 2005 Analysis Services (SSAS) Training Course Prerequisites

Experience in the following areas is required:

• Basic relational database concepts
• SQL Server 2000 or 2005

Experience in the following areas would be beneficial.

• Data transformation services
• Excel
• SharePoint Server

SQL Server 2005 Analysis Services (SSAS) Training Course Outline

1. What Is Microsoft BI?
   1. Core concept - BI is the cube or UDM
   2. Example cube as seen using Excel pivot table
   3. MS BI is comprehensive - more than Analysis Services on SQL Server
   4. Demonstration of SQL Reporting Services with cube as data source
2. OLAP Modeling
   1. Modeling source schemas-stars and snowflakes
   2. Understanding dimensional modeling- Dimensions (Type 1, 2, or 3) or rapidly changing
   3. Understanding fact (measures) and cube modeling
   4. Other types of modeling-data mining etc...
3. Using SSAS in BIDS
   1. Understanding the development environment
   2. Creating Data Sources and Data Source Views
   3. Creating cubes - using the UDM and the Cube Build Wizard
   4. Refining Dimensions and Measures in BIDS
4. Intermediate SSAS
   1. KPIs
   2. Perspectives
   3. Translations - cube metadata and currency localization
   4. Actions - regular, drill-through and reporting
5. Advanced SSAS
   1. Using multiple fact tables
   2. Modeling intermediate fact tables
   3. Modeling M:M dimensions, Fact (degenerate) dimensions, Role-playing dimensions, writeback dimensions
   4. Modeling changing dimensions - Dimension Intelligence w/ Wizard
   5. Using the Add Business Intelligence Wizards - write-back, semi-additive measures, time intelligence, account intelligence
6. Cube Storage and Aggregation
   1. Storage topics - basic aggregations, MOLAP
   2. Advanced Storage Design - MOLAP, ROLAP, HOLAP
   3. Partitions - relational and Analysis Services partitions
   4. Customizing Aggregation Design - Processing Design
   5. Rapidly changing dimensions / ROLAP dimensions
   6. Welcome to the Real Time - Proactive Caching
   7. Cube processing options
7. Beginning MDX
   1. Basic syntax
   2. Using the MDX query editor in SQL Server Management Studio
   3. Most-used Functions & Common tasks
   4. New MDX functions
8. Intermediate MDX
   1. Adding calculated members
   2. Adding scripts
   3. Adding named sets
   4. .NET Assemblies
9. SSAS Administration
   1. Best practices - health monitoring
   2. XMLA scripting (SQL Mgmt Studio)
   3. Other Documentation methods
   4. Security - roles and permissions
   5. Disaster Recovery - backup / restore
   6. Clustering - high availability
10. Introduction to Data Mining
    1. What and why?
    2. Examples of using each of the 9 algorithms (MS Clustering, MS Decision Trees, Naive Bayes, MS Sequence Clustering, MS Time Series, MS Association Rules, MS Neural Network)
    3. Data Mining dimensions
    4. Data Mining clients
    5. Processing mining models
11. Introduction to Reporting Clients
    1. Excel 2003 Pivot Tables
    2. SQL RS & Report Builder
    3. SPS RS web parts & .NET 2.0 report viewer controls
    4. Business Scorecards 2005 & ProClarity
12. Future Directions - Integration with Office 12
    1. SharePoint 12 and AS
    2. Report Center (type of dashboard) uses KPIs, Reports, Excel Web, Filter
    3. Excel Services 12 and AS (Web Services)

SQL Server 2005 Analysis Services (SSAS) Training Course Materials

Each student will receive a comprehensive set of materials, including course notes and all the programming examples.

SQL Server 2005 Analysis Services (SSAS) Training Technical Requirements

1. Microsoft SQL Server 2005 with SP2
   • Download - Full installation (including all services) required of developer or enterprise edition.
2. Microsoft SQL Server Sample Databases
   • Download - Once the sample database scripts have been extracted, double-click each of the two .sql files to open it in Microsoft SQL Server Management Studio Express and execute it using the toolbar button. This will install the sample database.
3. SQL Server Management Studio
   • Download - - Express Edition
   • System Requirements
4. Microsoft Office Professional Edition 2007 (includes Microsoft Access 2007)
5. Excel 2007 Data Mining Add-in
   • Download
6. Microsoft Visio 2007

Class Files for SQL Server 2005 Analysis Services (SSAS) Training

The class files for this course are included on a DVD that is shipped with the manual.