Microsoft SQL Server: Analysis Services: Hands-On

Course Overview

With the current explosion of data in today's enterprise environment, traditional methods of querying and reporting on information are no longer sufficient. This course provides the knowledge and skills to analyse and discover trends in your data warehouse. You learn to create On-Line Analytical Processing (OLAP) cubes using Business Intelligence tools and leverage the Analysis Services administrative tools to better manage and maintain your data.

Delegates that attend a Microsoft training course are twice as motivated than those that don't?

Related Courses

• Microsoft SQL Server: Optimisation / Performance Tuning - Advanced, Optimization
• Microsoft: Introduction to Microsoft Tools (SQL Server, Excel SharePoint) for Business Intelligence: Hands-On
• Microsoft SQL Server: Introduction to SQL Server Business Intelligence
• Microsoft SQL Server: Introduction to Microsoft Business Intelligence with SQL Server
• Microsoft SQL Server: Expert Business Intelligence with SQL Server
• Microsoft SQL Server Upgrade Training Course
• Microsoft SQL Server: Advanced Queries & Transact SQL Programming
• Microsoft SQL Server: Advanced SQL, Stored Procedures & Triggers for Microsoft SQL Server
• Microsoft SQL Server: Introduction to Transact SQL
• Microsoft SQL Server: SQL Introduction For SQL Server

Audience

Those designing, creating or developing analysis cubes from a database. Familiarity with Microsoft Business Intelligence tools or Course 146, "Microsoft Tools for Business Intelligence", is helpful.

Skills Gained

You Will Learn How To

• Leverage SQL Server Analysis Services to produce Business Intelligence solutions
• Create and deploy multidimensional data cubes
• Extend hierarchies and exploit advanced dimension relationships
• Slice and dice your data with advanced dimension hierarchies
• Make smarter business decisions with data mining techniques
• Implement Key Performance Indicators (KPIs) to monitor business objectives

Course Outline

Hands-on Training

Throughout this course, you gain extensive experience with SQL Server Analysis Services. Practical exercises include:

• Building a data source view
• Creating and deploying a cube
• Modifying cube dimensions
• Navigating hierarchies
• Establishing relationships in the data model
• Creating and using a perspective for browsing
• Implementing a security policy
• Forecasting trends with data mining techniques

BI Studio for Analysis Services

• Building data sources and views in the Unified Dimensional Model (UDM)
• Creating data source views

Building and Modifying an OLAP Cube

Defining fact tables

• Identifying and selecting available measures
• Adding new measure groups and creating custom measures
• Determining foreign key dependencies with dimensions

Creating dimensions

• Implementing a Star and Snowflake Schema
• Managing Slow Changing Dimensions (SCD)
• Identifying role-play dimensions

Fact dimension

• Building a dimension on a fact table
• Relating the fact dimension to other dimensions
• Browsing fact dimension data

Partitioning for optimal performance

• Choosing between ROLAP, MOLAP and HOLAP for performance and storage requirements
• Configuring incremental updates
• Defining aggregate storage
• Reviewing partitioning best practices

Modifying dimension attributes

• Using dimension properties for specific needs
• Implementing stored procedures for Analysis Services
• Improving dimension usability
• Changing granularity in a measure group

Extending the Cube with Hierarchies

Parent-child relationships

• Declaring hierarchies
• Grouping related attributes
• Equal Areas
• Clusters
• Buckets

Creating hierarchies

• Building hierarchies on multiple dimensions
• Utilising the dimension Attribute Relationship tab
• Taking advantage of the Aggregate Transformation Editor
• Editing grouping properties
• Choosing between ragged, balanced and unbalanced hierarchies

Expanding the BI Data Model

Referencing relationships

• Converting a dimension to a measure
• Building referenced dimensions

Resolving many-to-many relationships

• Identifying relationship anomalies
• Implementing intermediate fact and dimension tables

Creating cube perspectives

• Filtering business-related information
• Slicing and dicing data
• Working with local languages

Retrieving data with MDX

• Composing simple MDX queries
• Writing MDX expressions
• Navigating hierarchies with parent, child, cousin and ancestor

Adding Attributes

Moving attributes

• Importing attributes from the data source view
• Removing and hiding attributes

Managing attribute properties

• Creating composite keys
• Building calculated members
• Setting the sort order and sorting on an alternate key

Gaining Business Advantage with Data Mining

Finding patterns in your data

• Correlating business trends
• Predicting future trends with algorithms

Determining the correct model

• Choosing between discrete and continuous attributes
• Analysing various data mining algorithms

Validating models

• Training algorithms for optimal results
• Exploring results with data mining viewers

Monitoring KPIs for Better Business Decisions

Reporting on business objectives with KPIs

• Selecting critical performance indicators
• Implementing KPIs with expressions
• Running reports based on Analysis Services

Browsing the cube data with Excel Services

• Viewing dashboard gauges of analysis data
• Encapsulating business trends into a single view