Module 5: Using Advanced Dimension Settings

1
Module 5 Using Advanced Dimension Settings
2
Overview

• Working with Levels and Hierarchies
• Working with Time Dimensions
• Creating Custom Rollups
• Introducing Member Properties
• Understanding Virtual Dimensions

3
Working with Levels and Hierarchies

• Applying Level Settings
• Defining a Hierarchy
• Creating Multiple Hierarchies
• Grouping Members into Levels

4
Applying Level Settings

• Member Count
• Key Data Size
• Key Data Type
• Level Type
• Visible

5
Defining a Hierarchy

• A Hierarchy Is a Set of Members and Levels within
  a Dimension
• By Default, a Dimension Contains One Hierarchy
• Analysis Services Supports a Variety of
  Hierarchies
• Regular hierarchies, which are balanced
• Parent-child hierarchies, which are unbalanced
• Ragged hierarchies, which can occur in both
  regular and parent-child hierarchies
• A Dimension Can Contain Multiple Hierarchies

6
Creating Multiple Hierarchies
Department Dimension
Department.Region
Region 1
Department A
Department B
Region 2
Two Hierarchies
Department.Management
7
Grouping Members into Levels
Large Level
Grouping Level
8
Working with Time Dimensions

• Using the Dimension Wizard to Create Dates
• Using a Separate Table for Dates
• Setting Time Dimension Properties
• Creating a Fiscal Date Hierarchy
• Building a Date Hierarchy from a String

9
Using the Dimension Wizard to Create Dates

• When Possible, It Is Best Practice to Use the
  Dimension Wizard When Creating Time Dimensions
• Contains Built-In Intelligence
• Defines Entire Hierarchy From a Single Date/Time
  Column
• Uses Appropriate Functions Depending on Data
  Source
• Is Available for Modification in the Dimension
  Editor After Initially Created

10
Using a Separate Table for Dates

• Contains Additional Date Properties
• Reduces Storage Space
• Is Used with Multiple Fact Tables

11
Setting Time Dimension Properties

• Some MDX Functions Use Time Dimension Properties
• Third-Party Products Use Time Properties
• Several Time Dimension Level Properties Exist
• The Type Property Has No Effect on the Analysis
  Server

12
Creating a Fiscal Date Hierarchy

• May Not Begin on January 1
• Is Used for Internal Reporting Purposes
• Causes the Need for Multiple Hierarchies within a
  Time Dimension
• Fiscal date hierarchy
• Calendar date hierarchy

13
Building a Date Hierarchy from a String

• May Not Be Able to Create New Tables within the
  Data Warehouse
• Can Use Dates Stored as Strings
• Can Use Expressions to Define Time Dimensions

14
Lab A Creating Time Dimensions

15
Lab B Creating Hierarchies from Single Columns

16
Creating Custom Rollups

• Using Custom Rollup Operators
• Creating Custom Rollups

17
Using Custom Rollup Operators
18
Creating Custom Rollups

• Change Unary Operators Property to True
• Define a Dimension Table Column that Provides
  Custom Rollup Operators
• Order Members to Define the Calculation Order
• Update Unary Operator Column with Custom Rollup
  Operators
• In Dimension Editor, if write-enabled
• In source database, if not write-enabled
• Can Be Enabled within Shared and Private
  Dimensions
• Do Not Work in a Cube Using the Distinct Count
  Aggregate Function

19
Lab C Creating a Custom Rollup
20
Introducing Member Properties

• Defining Member Properties
• Describing Member Properties
• Using Member Properties

21
Defining Member Properties

• Dimension Member Attributes
• Information Needed for Analysis that Does Not
  Make Sense as a New Dimension or Level
• A Starting Point for Creating Virtual Dimensions

22
Describing Member Properties

• Come from a Column in the Same Dimension Table as
  the Members
• Do Not Affect Cube Size
• Do Not Significantly Affect Cube Processing Times
• Do Not Affect Query Performance for Queries that
  Do Not Reference Them
• Are Used in MDX Queries for Analysis
• Are Stored in Dimension Structure Files

23
Using Member Properties

• Associating Member Properties with Levels
• Creating Member Properties
• Previewing Member Properties
• Using SQL Expressions

24
Lab D Creating Member Properties

25
Understanding Virtual Dimensions

• Defining Virtual Dimensions
• Placing Virtual Dimensions in Cubes
• Using Virtual Dimensions

26
Defining Virtual Dimensions

• Are Based on Levels and Member Properties of
  Other Dimensions
• Appear as Regular Dimensions to Users
• Can Contain Multiple Levels
• Can Contain Member Properties
• Do Not Have Aggregations
• Do Not Increase Cube Storage and Processing Time
• Increase Query Times for Queries that Reference
  Them

27
Placing Virtual Dimensions in Cubes

• The Member Property Must Exist Prior to Creating
  the Virtual Dimension
• The Source Dimension Must Exist within the Cube
• Virtual Dimensions Are Added in the Same Way as
  Regular Dimensions

28
Using Virtual Dimensions

• When to Create a Virtual Dimension
• Cube processing times are a problem
• Cube storage is a problem
• A minority of users require a dimension
• When to Create a Regular Dimension
• Cube processing times are not a problem
• Cube storage is not a problem
• Query times must be fully optimized
• The dimension requires features not available in
  virtual dimensions

29
Demonstration Creating Virtual Dimensions
30
Lab E Creating Virtual Dimensions
31
Review

• Working with Levels and Hierarchies
• Working with Time Dimensions
• Creating Custom Rollups
• Introducing Member Properties
• Understanding Virtual Dimensions