Design Cube Architecture

Practice Labs Module
Time
21 minutes
Difficulty
Intermediate

The "Design Cube Architecture" module provides you with the instructions and devices to develop hands on skills in the following topics: Creating a Data Source View in SSAS, Creating Measures, Measure Groups and Aggregate Functions, Using Drillthrough Action, Define an Index for the View.

Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *
or

Already have an account? Sign In »

Overview

Introduction

The Design Cube Architecture module provides you with the instructions and devices to develop hands on skills in the following topic:

  • Creating a Data Source View in SSAS
  • Creating Measures, Measure Groups and Aggregate Functions
  • Using Drillthrough Action
  • Define an Index for the View

Exercise 1 - Creating a Data Source View in SSAS

You create data source views in Analysis Services multidimensional projects to contain the logical model of the schema used by the database objects such as cubes and dimensions. The data source views allow you to work independently without directly modifying the original data source. An Analysis Services multidimensional model allows you to create multiple data source views based on one or more data sources, thereby to cater to the requirements for a different solution.

In this exercise, you will create a data source view that defines the AdventureWorksDW2012 database as the data source for an Analysis Services cube.

Exercise 2 - Creating Measures, Measure Groups and Aggregate Functions

A Measure is an aggregation of numeric data values, such as sum, count, minimum, maximum, average, or a custom MDX expression that you create. A Measure Group is a container for one or more measures. All measures exist in a measure group. A cube must have at least one measure and measure group.

In this exercise, you will define an Analysis Services cube using the Cube Wizard. You will then assign an aggregate function to a measure in the cube.

Exercise 3 - Using Drillthrough Action

Using fact dimension without filtering the data can affect the efficiency of the query. Defining a Drillthrough action restricts the total number of rows returned and thus improves the query performance.

Exercise 4 - Define an Index for the View

Views allow you to create a virtual table, when you query one or more table. The results of the query are stored in the virtual table instead of the database. You can extract the result by querying the view. Indexing a view increases the query performance. In this exercise, you will create a clustered index on a view.

Learning Partner
Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.