SQL Server 2005 Analysis Services Tutorial

Welcome to the Microsoft SQL Server 2005 Analysis Services (SSAS) Tutorial. Data warehouse developers use Business Intelligence Development Studio to develop and deploy Analysis Services projects, and use SQL Server Management Studio to manage the Analysis Services databases instantiated from these projects. This tutorial describes how to use BI Development Studio to develop and deploy an Analysis Services project, using the fictitious company Adventure Works Cycles for all examples. For more information about SQL Server Management Studio, see SQL Server Management Studio Tutorial. For more information about Analysis Services objects, see Analysis Services Concepts and Objects.

Terms are explained in context throughout this tutorial. You can also see the Glossary in SQL Server 2005 Books Online for specific terms and their definitions.

What You Will Learn

In this tutorial, you will learn how to define data sources, data source views, dimensions, attributes, hierarchies, and cubes in an Analysis Services project within BI Development Studio. You will also learn how to view cube and dimension data by deploying the Analysis Services project to an instance of Analysis Services and then processing the deployed objects to populate them with data from the underlying data source. You will also learn how to modify the measures, dimensions, hierarchies, attributes, and measure groups in the Analysis Services project, and how to then deploy the incremental changes to the deployed cube on the development server. Finally, you will learn how to define calculations, Key Performance Indicators (KPIs), actions, perspectives, translations, and security roles within a cube.

Requirements

The following components, samples, and tools are required to complete this tutorial:

• Microsoft SQL Server 2005 Database Engine
• Microsoft SQL Server 2005 Analysis Services (SSAS)
• Business Intelligence Development Studio
• AdventureWorks DW sample database
• The updated Analysis Services Tutorial sample projects. For more information about how to obtain these updated sample projects, see Installing Samples.

Note

SQL Server 2005 Service Pack 1 is also recommended. This tutorial is based on SQL Server 2005 with Service Pack 1 installed.

For information about how to install these components, samples, and tools, see Obtaining Updated Samples in Installing SQL Server 2005 Components and Installing Samples.

Additionally, the following prerequisites must be met to successfully complete this tutorial:

• You must be a member of the Administrators local group on the Analysis Services computer or be a member of the Server role in the instance of Analysis Services.
• You must have Read permissions in the SQL Server 2005 AdventureWorks DW database.

Note   Business Intelligence (BI) Development Studio is not supported on Itanium-based computers. However, support for BI Development Studio is available for x64-based computers. If the SQL Server sample databases have been deployed on an Itanium-based computer, use BI Development Studio on either an x86-based or x64-based computer to modify and run the samples.

Contents

This tutorial is divided into ten lessons. Each lesson is intended to require approximately one hour to complete. A completed version of each lesson is included with the updated samples folder. For information about obtaining the updated samples, see Installing Samples.

Note

When you start Lesson 4, you must use an enhanced version of the cube that you define in Lessons 1 through 3. You must use the updated version of the sample project for this lesson in order to match the content of this tutorial.

• Lesson 1: Defining a Data Source View within an Analysis Services Project
  In this lesson, you define a data source view within an Analysis Services project by using BI Development Studio.

• Lesson 2: Defining and Deploying a Cube
  In this lesson, you define a cube and its dimensions by using the Cube Wizard, and then deploy the cube to the local instance of Analysis Services.

• Lesson 3: Modifying Measures, Attributes and Hierarchies
  In this lesson, you improve the user-friendliness of the cube and incrementally deploy the related changes, processing the cube and its dimensions as needed.

• Lesson 4: Defining Advanced Attribute and Dimension Properties
  In this lesson, you define a referenced dimension relationship, order attribute members by using composite keys, and define custom error handling.

• Lesson 5: Defining Relationships Between Dimensions and Measure Groups
  In this lesson, you define a fact relationship for a degenerate dimension and define a many-to-many relationship.

• Lesson 6: Defining Calculations
  In this lesson, you define calculated members, named sets, and scripts.

• Lesson 7: Defining Key Performance Indicators (KPIs)
  In this lesson, you define Key Performance Indicators (KPIs).

• Lesson 8: Defining Actions
  In this lesson, you define actions.

• Lesson 9: Defining Perspectives and Translations
  In this lesson, you define views of a cube and translations of metadata.

• Lesson 10: Defining Administrative Roles
  In this lesson, you define administrative and user roles.

Important

When you review tutorials, we recommend that you add Next topic and Previous topic buttons to the document viewer toolbar. For more information, see Adding Next and Previous Buttons to Help.

See Also

Concepts

Analysis Services Tutorials

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release

History

17 July 2006

• New content:

  • Added note on availability for IA64 platform.

14 April 2006

Term

Definition

Changed content:

• Added information about how to obtain updated sample project files.
• Updated tutorial to be based on SQL Server 2005 with Service Pack 1.