Analysis Services Fundamentals

This course is for business intelligence developers and database professionals responsible for preparing data for multidimensional analysis and assumes no prior experience with Analysis Services
Course info
Rating
(1092)
Level
Beginner
Updated
Aug 22, 2011
Duration
6h 31m
Table of contents
Introduction to Analysis Services
Dimensional Model Development
Dimension Development I
Dimension Development 2
Cube Development I
Cube Development 2
Calculations 1
Calculations 2
Database Processing
Security
Description
Course info
Rating
(1092)
Level
Beginner
Updated
Aug 22, 2011
Duration
6h 31m
Description

This course explains how to develop Analysis Services databases, beginning with the dimensional modeling process and continuing with the development of dimensions and cubes. It also covers the integration of business logic into the cube by adding calculations, named sets, and key performance indicators. This course explains how to deploy and process Analysis Services databases and how to implement security for both users and administrators. The features and demonstrations in this course focus on the SQL Server 2008 R2 release, although most topics also apply to earlier versions of Analysis Services.

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

Introduction to Analysis Services
Hello! I'm Stacia Misner from Pluralsight and I'll be presenting Introduction to Analysis Services. In his module, we'll start learning about SQL Server Analysis Services covering some basic concepts that you need to understand before beginning to develop your own databases. We'll start by reviewing the type of queries that are common in Business Intelligence. Then we'll look at various ways that we can incorporate Analysis Services into a Business Intelligence architecture. Next, we'll look at the steps involved in the Analysis Services database development process. And we'll conclude with a review of the tools that we use to develop Analysis Services databases to manage databases that we've deployed to an Analysis Services server and the variety of tools available in the Microsoft BI stack for users to access data from Analysis Services.

Dimensional Model Development
Hello, I'm Stacia Misner from Pluralsight and I'll be presenting Dimensional Model Development. In this module, our focus is on developing a Dimensional Model that we can use as the foundation for the dimensions and cube that we need to build to answer specific business questions. We'll start by learning how to create a Data Source and then we'll learn about two different ways to approach the development of our Dimensional Model, Top Down versus Bottom Up. We'll learn how to use the Cube Wizard in this module, but only as part of our Top Down Design for our Dimensional Model. In a separate module in this course we'll learn how to use the Cube Wizard for Bottom Up Design. And then we'll use the Dimension Designer to add more information to the dimensions that were initially built by the Cube Wizard. When we've finished with these Wizards we'll use the Schema Generation Wizard to build the physical tables that support our Dimensional Model. And then last, we'll review the Data Source View that gets generated as a part of this process of working through a Top Down Design and we'll learn how to create a Data Source View in preparation for working through a Bottom Up Design.

Dimension Development I
Hello! I'm Stacia Misner from Pluralsight and I'll be presenting Dimension Development Part 1. In this module, we're going to cover the development tasks that need to be performed during the development of dimensions for an Analysis Services database. We'll start by using the Dimension Wizard to build a dimension using the bottom-up approach rather than the top-down approach that we learned about in the previous module. Then, we'll learn about the Dimension Designer. This is the interface in Business Intelligence Development Studio where we view the dimension design. We can access the various objects that are part of a dimension and adjust the properties of each of these objects as needed. Each dimension contains one or more attributes which correspond to a column in the source table. We'll learn how to add attributes after we view the wizard to create a dimension. Each attribute contains members. Members correspond to row values in a particular column; however, our dimension might not include something called the Unknown Member. So we'll learn how analysis services can add an unknown member to a dimension and why we would need an unknown member in a dimension. And last, after we have all of or attributes added to a dimension, we'll learn how to configure the properties for each of those attributes.

Dimension Development 2
Hello, I'm Stacia Misner and I'll be presenting Dimension Development Part II. We're going to continue building on the concepts that we started in the previous module by learning more about development tasks related to our dimensions. First, we'll take a closer look at the properties related to Date dimension. And then we'll see how BIDS helps us avoid common problems by displaying Best Practice Warnings, and how to disable the warnings on a case-by-case or global bases. We'll also learn how to arrange attributes and hierarchies to facilitate drill down into optimize performance in our cubes. And we'll learn how to properly configure attribute relationships, another performance optimization technique. And if we have to support multiple languages in our cube, we can configure translations for the dimension.

Cube Development I
Hello, I'm Stacia Misner and I'll be presenting Cube Development Part One. In this module, we'll continue learning about building out an Analysis Services database by building a cube. We'll start by learning how to work with the Cube Wizard. We were first introduced to the Cube Wizard in an earlier module, but at that point, we were learning how to build a cube using the top-down approach. This time, we're going to use the bottom-up approach where we have an existing table that we're going to use as the basis for building out our cube. Then we're going to spend some time in the Cube Designer. The Cube Designer in Business Intelligence Development Studio has many different tabs available for working with our cube. In this module, we'll learn how to work with the cube designer to fine tune our cube by managing the appearance and behavior of our measures. And we can even use the Cube Designer to browse the results of our changes. ( Pause )

Cube Development 2
Hello. I'm Stacia Misner. And I'll be presenting Cube Development Part II. In this module, we'll continue building on the cube that we began in the previous module. Having data in a cube is useful for analysis, but analysis gets even better when we add aggregations to our cube. Aggregations are the precalculated totals that enable fast-query performance. Aggregations are not added by default, so we'll use the cube designer to create the necessary aggregations for our cube. When we have a cube that contains a lot of information, sometimes that information can be overwhelming for users. So we can simplify it using perspectives which allow us to show only a subset of the information available in a cube at a given time. And then last, whenever we need to provide support for multiple languages, just as we can do with dimensions, we can also add translations into our cube.

Calculations 1
Hello! I'm Stacia Misner from Pluralsight and I'll be presenting Calculations Part I. One of the benefits of using an analysis services database for reporting an analysis instead of using a relational database is the ability to store business logic as calculations directly in the cube. That way, we can ensure that a particular business metric is calculated the same way every time, no matter who is asking to view the metric and no matter what's skill level they have in building the report. We'll begin by learning how to add basic calculations to the cube. We'll use the Calculation Designer and BIDS which gives us a variety of options for adding our calculations. Users can access these calculations just like any other measure that comes from aggregating fact table rows. The Calculation Designer includes several tools to help screen expressions from finding the right names of members that we want to reference to functions that we can use and also templates for common types of calculations. We'll learn how to create calculations that perform basic mathematical operations and calculations that compare a value from one point in time to a previous point in time. And we'll learn how to create a ratio calculation to determine profitability of sales. And last, we'll learn about expressions that we can use to change the appearance of calculation results, so that we can tell at a glance whether a number is above or below a specified threshold.

Calculations 2
Hello I'm Stacia Misner and I'll be presenting Calculations Part II. This module continues to build on what we learned in the previous module about calculations. In the previous module, we learned about calculations that were added to the measures dimension. But in this module, we'll learn about calculations that create members in other dimensions, we call these Calculated Members or sometimes Custom Members. We'll also use the Calculation Designer to develop Named Sets. A Named Set is a special type of calculation that produces a collection of Dimension Members rather than a specific value like a calculated measure. Another type of calculation that we can add to the cube is a Key Performance Indicator or KPI. Actually, a KPI is a set of calculations in addition to some graphics to indicate whether we are achieving our goals or not.

Database Processing
Hello. I'm Stacia Misner from Pluralsight and I'll be presenting Database Processing. Up to this point in the course, our focus has been on the development process. Periodically, we would use the Deploy command in BIDS which would deploy and process the Analysis Services Database. And we saw the end result of those steps but we haven't yet explored what it really means to deploy and process a database. And that's what we're going to cover in this module. We'll start with an overview of deployment in general and how to use the Deployment Wizard to move an Analysis Services Database from development to production. Then we'll look more closely at what happens during processing and how the selection of a storage mode for the data in our Analysis Services Database affects processing.

Security
Hello, I'm Stacia Misner from Pluralsight and I'll be presenting Basic Analysis Services Security. Throughout this course, we've been building a cube and testing it and getting it ready for our end-users but the database and its contents, the cubes and the dimensions, are all secured by default. Nobody can query the cube except the developer of the cube. So in this module, we'll learn the steps that we need to perform in order to make the cube accessible to others. We'll start by learning about user security in Analysis Services and then we'll look at how to setup administrator security in Management Studio. Administrators can have access to the database and to cubes in order to convict your security or to process the objects in the database without necessarily having access to read the contents of our cubes and dimensions. So we'll learn more about how to set that up. And then lastly, the Windows account that runs the analysis service's service account needs to have specific permissions in order for Analysis Services to work correctly. So we'll review the various types of permissions that this account needs.