Advanced Analysis Services

This course is for business intelligence developers and database professionals who understand how to develop and deploy a basic Analysis Services solution and need to learn advanced design, deployment, and security concepts.
Course info
Rating
(184)
Level
Advanced
Updated
Sep 28, 2012
Duration
4h 32m
Table of contents
Advanced Design Techniques Part I
Advanced Design Techniques Part II
Writeback
Aggregations
Partitions
Deployment
Advanced Security Topics
Description
Course info
Rating
(184)
Level
Advanced
Updated
Sep 28, 2012
Duration
4h 32m
Description

This course covers advanced design topics related to dimensions and partitions. It explains how to utilize Analysis Services features for parent-child hierarchies, financial analytics, referenced dimensions and many-to-many relationships. It also demonstrates how to work with write back in Analysis Services for both dimensions and cells. In addition, this course provides guidance for managing Analysis Services performance and storage through the implementation of aggregations and partitions. Furthermore, this course expands topics introduced in the Analysis Services Fundamentals course by explaining how to manage design and data changes following initial implementation and how to implement alternative security scenarios. The features and demonstrations in this course focus on the SQL Server 2008 R2 release, although most topics also apply to all versions of Analysis Services (SQL Server 2005 and later).

About the author
About the author

Stacia Misner Varga is a consultant, instructor, author, mentor, BI Partner for SQLSkills, and principal of Data Inspirations specializing in Microsoft business intelligence technologies for over 10 years. She is a frequent speaker at the PASS Summit, IT/Dev Connections conferences, and various SQL Saturday and Microsoft-related events worldwide.

More from the author
Building Your First Power BI Report
Beginner
1h 37m
Jun 3, 2019
Modeling Data in Power BI
Beginner
4h 57m
Mar 29, 2019
More courses by Stacia Misner Varga
Section Introduction Transcripts
Section Introduction Transcripts

Advanced Design Techniques Part I
Hello. I'm Stacia Misner from Pluralsight, and I'll be presenting Advanced Design Techniques Part 1. In this module, we're going to review some more properties that we can use to manage the behavior of parent-child hierarchies. We'll start by discussing how a financial parent-child hierarchy is different from the simple type of parent-child hierarchy introduced in the Analysis Services Fundamentals course. There's several additional settings we can use to configure a parent-child hierarchy to change the default aggregation behavior to work with financial reporting, for example. First, we'll look at account intelligence to see how we can control additivity, that is how we control how values rollup in combination with the date dimension. We'll also look at the use of unary operators for changing how values rollup within the parent-child hierarchy. Then we'll review the purpose of the IsAggregatable property for a hierarchy, and we'll learn how to manage the level names within a parent-child hierarchy by using a naming template. We can also configure a default member to manage the values a user sees when not including the parent-child hierarchy directly in a query, and we'll review the use of custom rollup formulas to compute a value at query time when we can't use the fact table data directly. Last, we'll review the pros and cons of working with a parent-child hierarchy, and we'll look at an alternative approach to this type of dimension design.

Advanced Design Techniques Part II
Hello. I'm Stacia Misner from Pluralsight and I'll be presenting Advanced Design Techniques Part II. In this module, we're going to continue to learn about two more advanced design techniques. First, we'll look at referenced relationships that we can use to relate a dimension to a fact table through an intermediate dimension table. And second, we'll look at many-to-many relationships for handling more complex data scenarios such as when we need to associate a fact record with multiple dimension records. We'll also review the pros and cons of each technique.

Writeback
( Noises ) Hello, I'm Stacia Mysner and I'm presenting Write-back. Write-back is one of the less commonly used features of analysis services for a couple of reasons. First it requires a client application that supports Write-back, and second Write-back is used by a relatively small number of users of need to plan, budget, or forecast as compared to a much larger number of users who are performing analysis only. Before we see how to implement Write-back we'll review how it works. Then we'll look at Dimension Write-back which allows users to add Dimension members directly to the analysis services databases through the client application. After that we'll learn about Cell Write-back which allows users to modify the measure values in a cube.

Aggregations
Hello, I'm Stacia Misner and I'll be presenting Aggregations. In this module, I'll be expanding on the topic of aggregations, first introduced in the analysis services fundamentals course. We'll start by reviewing aggregation concepts to get a clear understanding of what aggregations do and what they look like. Then we'll look at the aggregation usage settings that we can configure to help the aggregation wizard analyze the data model and create aggregation designs. We'll also review the other steps that we can take to optimize the time it takes to run the aggregation wizard as well as to produce better aggregations. Then we'll see how to review and even modify aggregation designs if necessary by using the aggregation designer. And then finally, we can use usage based optimization to find tune aggregations to better support actual query patterns.

Partitions
Hello, I'm Stacia Misner and in this module I'll be discussing Partitions. In this module, we'll learn about how Analysis Services manages the physical data that we see as a cube. Analysis Services stores fact data in an object called the Partition. If we have Enterprise edition we can use multiple partitions as a way to optimize the user experience and manage the administrative tasks on the server, but we need to understand how to develop a proper partitioning strategy to achieve our goals. The main reason to partition is to manage physical storage, so we'll explore the different options that we have. Next, we'll review the steps required to design a partition. We'll also review how to combine partitions if we later need to change the physical structure.

Deployment
Hello I'm Stacia Misner with Pluralsight and I'll be presenting Deployment. In this module, we're going to review various aspects of working with an analysis services database and production. We'll start with the deployment process, that's the process where we move our files from the development environment to a server and we'll talk about what happens if things change after that initial deployment. Then we'll look at how to automate the partitioning process so that we don't have to manually create a new partition each time we need a new one. Next, we'll review what happens during the processing and how to choose a processing operation to suit our conditions. Last, we'll look at alternate ways to move a database from one server to another.

Advanced Security Topics
Hello, I'm Stacia Misner and in this module, I'm presenting Advanced Security Topics. In the analysis services fundamentals course, I explained how security and analysis services works. In this module, I'll review some additional topics related to security. First, we'll look at an alterative way to manage user security through a table driven approach rather than relying exclusively on rules. Next, well learn how dimension security affects the parent-child hierarchy differently than a regular hierarchy and we'll learn how to adapt dynamic security to work with the parent-child hierarchy. And last, we'll review how security works with the writeback feature.