Advanced SQL SSAS

Course Description

Duration: 5 days – 35 hrs.

Overview

This training course enables you to analyze large quantities of data. With it, you can design, create, and manage multidimensional structures that contain detail and aggregated data from multiple data sources. To manage and work with Online Analytical Processing (OLAP) cubes, you use SQL Server Management Studio. To create new OLAP cubes, you use Business Intelligence Development Studio.

Objectives

Work with Data Source and data Source View.
Develop Cube using Cube Wizard.
Create Dimensions using Wizard and Designer.
Work with Regular, Snow Flake and Time Dimension.
Work with attribute relationship, Role playing dimension.
Work with measure group, adding removing measures.
Work with Calculation, Named Query and Named Calculation.
Work with KPI, Perspective and Translation.
Work with Partition, Aggregation.
Understand different storage modes like ROLAP, MOLAP and HOLAP.
Work with Security, Roles.
Understand Processing and Deployment.
Learn MDX (Multidimensional Expressions Language)
Work with actual clients, technology architects, or team leads on a large-scale SQL Server Analysis Services / MDX / Data Mining Project.
Upon completion of this course, successful participants will be able to use SSAS to deploy an Analysis Services database with multiple levels of security for data mining.

Audience

Database Administrators
Programmers
BI (Business Intelligence) professionals
Data Analysts
Database and data warehousing professionals

Pre- requisites

At least one month experience using SQL
Intermediate Microsoft Excel experience recommended
An understanding of data analysis
Experience with Microsoft SQL Server
SQL: Fundamentals of Querying or equivalent knowledge.

Course Content

OLAP Introduction

In this module, you will learn about Basic understanding of OLAP, OLTP and Data ware house. How an OLAP database can be created from a data mart or data warehouse and how Analysis Services handles different types of fact/dimension relationships. The benefits to end users who consume an OLAP database:

What is OLAP and OLTP system
Advantages of OLAP and OLTP system
What is Data ware house
What are the key elements of data ware house
What is fact table and dimension tables
What is Attribute, Member, Measure
What is cube and examples
What is Dimension Model
Different types of approaches for designing Dimension Model

Introduction to SSAS

In this chapter, you will learn about Introduction to SSAS, how it is useful, development tools, how to create an

Analysis Services in multidimensional analysis solution, and how to create data sources, data source views:

What is SSAS
Why SSAS
What are the core services present in SSAS
What is UDM(Unified Dimension Model)
What are the different tools we will use in SSAS
How to install and use the Adventure Works DW database.
How to create a SSAS project and create a data source which will connect to this database.
How to create a star schema using a Data Source View.
How to create a Snow flake Schema using a Data Source View
How to Design a Data warehouse

Data Source View and Cube Wizard

In this chapter, you will learn about Data Source View, Cube Wizard and how to use filters how to create cube

wizard. How to use data mining techniques to extract data from collections of multiple data sources and applications:

What is cube and advantages of cube
What is Data Source View
How to create Data Source View
How to Use of filter
How to Add related tables
How to Remove unwanted tables
How to use Named Calculations
Using Cube Wizard, how to create Cube
How to Add Required Measure Groups and Measures
How to Add dimensions, attributes to the cube

MDX (Multi – Dimensional expressions) and Cube Browser

In this module, you will learn about the Multidimensional Expression (MDX) fundamentals, cube browser, how to

implement calculated members and named sets in an Analysis Services cube, querying a Cube by Using MDX. The power of MDX in extending OLAP databases:

What is MDX
How to use MDX
Different distinct modes In Analysis Services MDX
How to develop cube objects
How to write a query using MDX
What is a cell, tuple, set, slice
What are the Different we can use in MDX queries
What is Members and Children
How to write a query by using functions that Non Empty, cross Join, filter and Top Count
What is Cube Browser
Advantages of Cube Browser
How to use Cube Browser

Calculations

In this module, you will learn about How to define calculations, which are Multidimensional Expressions (MDX) expressions or scripts. Calculations let we will define calculated members, name sets, and execute other script

commands to extend the capabilities of a Microsoft SQL Server 2005 Analysis Services (SSAS) cube:

What is Calculation
How to Create Calculations
What is Calculation Designer
How to Create Calculation for Gross profit margin using templates
How to use expressions in Calculations
How To define the percent of total calculations
How To browse the new calculated members
What is Script View
Advantages of Script View

Working with Dimensions

In this chapter, you will learn about the dimensions, hierarchy and how to create hierarchy using attribute relationship for optimization. Advantages of creating hierarchy in SSAS dimension and how different it is from other dimension attribute in the case of performance:

What is Dimension
Advantages of Dimension
How to Create Dimension Using Dimension wizard
How to Create Snow Flake Dimension Using Dimension Designer
What is Time Dimension
What is Attribute Hierarchies and User Defined Hierarchies
What is Attribute Relationship
Importance of using composite key for identifying attributes uniquely
What is parent-child hierarchy
How to Create parent-child hierarchy
What is Sorting Dimension Attribute
What is Named Query
How to Create Named Query
How to Handle Warning

Working with Cube Design

In this module, you will learn about what are the advantages of using SSAS Cubes over a regular data warehouse for reporting, why do you need OLAP cubes, Measures. What are the different types of relationships are there in SSRS:

How to Create Cube using dimensions
How to include dimension in Cube
What is Fact Relationship, Reference Relationship
How to Define Relationship
What is Measure
How to add Measures
How to use Aggregate Functions
How to Add Measure Group

Translation, Perspective and Actions

In this chapter, you will learn about Defining and Browsing Perspectives, how to Define perspective and how to browse the cube through each perspective, defining and Browsing Translations. How to enables administrators to create views of a cube:

What is Translation
How to use Translation in cube
What is perspective
How to create perspective
What is Action
What are the Different types of Actions

KPI (Key Performance Indicator)

In this module, you will learn about KPI (Key Performance Indicator) how effectively a company is achieving key business objectives. Why do we use key performance indicators, how do key performance indicators work and how do you set key performance indicators:

What is KPI
Advantages of KPIs
What are the Properties of KPI
How to Create KPIs
How to add Goal, value, status, trend expression to KPIs
How to deploy KPI in KPI Browser
What is Parallel Period

Deployment and Processing

In this module, you will learn about how to Deploy, how to Processing the Application, advantages of Deployment, what are the different ways to deploy and advantages of deployment and processing. What are the different types of processing options:

What are the Deployment Options
What are Processing Options for complete Solution
What are the Processing Options for Individual Dimensions
How to deploy using SSDT
How to Deploy using Deployment wizard
How to Deploy using XMLA Script

Partitions

In this chapter, you will learn about how to optimize the dimension security performance using partitioning in SSAS Multidimensional and their characteristics and considerations surrounding their use, as well as their impact on data storage and cube processing:

What is Partition
How to use Partition
How to Create partition using SSDT
How to Create partition using Management studio, XMLA script
What are the Different Types of Binding in Partition
What are the Different types of storage modes
How to use Different types of storage modes
What is Proactive Caching
How to set Cache Settings

Aggregation

In this chapter, you will learn about what is Query Processing, what is Aggregation Wizard and how it is use full. How to run Queries faster and how to design for each partition independently. Why aggregation:

What is Aggregation
Advantages of Aggregation
Understanding Query Processing
How to Create Aggregation design for any Partition
What is Aggregation Design Wizard

Security

In this module, you will learn about Security the basic concepts and considerations surrounding implementation of security in Analysis Services. How many ways we can provide security and different types of security roles: