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:
- What are the Different types of Security roles
- How to assign User Security
- How to Create Database level Security
- What are the difference between administrator and user security
- How to provide cube and dimension level Security
- How to Deploy the cube and browser with new Role
- How to create cell level security
