Designing OLAP Dimensions

1
Designing OLAP Dimensions
2
Enabling Various Views
Analysis Server
Finance
Operations
Profit by Divisionby Country by Monthby
Actual/Budget

Volume by Plant by Shift by Productby Day
Marketing
Sales
Revenue by Productby Regionby Sales Rep by
Quarter
Revenue by Customerby Industryby Channel by Week
3
Understanding Levels and Members
Product Dimension

• Four Levels
• All
• Category
• Sub-Category
• Product
• Category Members
• Bread
• Dairy
• Meat

4
Reviewing Analysis Services Limits
5
Working with Standard Dimensions
Country
State
City

• Each Level Corresponds to a Dimension Table
  Column
• All Members at a Given Level Have the Same Number
  of Ancestors
• Can Be Star or Snowflake Dimensions

6
Working with Ragged Dimensions
Country
No States
State
City

• Variable Depth in Branches
• Level Property Hide Member If

7
Assigning Member Keys and Names

• Defining the Member Key Column
• Determines the members included in a level
• Usually comes from a single dimension table
  column
• Defining the Member Name Column
• Provides names for members at a level
• Can be different from the Member Key Column

8
Creating Members from Expressions

• Add Flexibility When Defining Levels
• Are Created from One or More Columns in a Single
  Table
• Are Defined in the Member Key Column and Member
  Name Column in the Dimension Editor
• Act as RDBMS Pass-Through Functions
• Must be Valid RDBMS Syntax

9
Using Member Properties

• Why Member Properties?
• Information Needed for Analysis that Does Not
  Make Sense as a New Dimension or Level
• A Starting Point for Creating Virtual Dimensions
• Used in MDX Queries for Analysis
• Impact of Member Properties
• Do Not Affect Cube Size
• Do Not Significantly Affect Cube Processing Times
• Are Stored in Dimension Structure Files

10
Creating Time Dimensions

• Using the Dimension Wizard
• Contains Built-In Intelligence
• Defines Entire Hierarchy From a Single Date/Time
  Column
• Uses Appropriate Functions Depending on Data
  Source
• Using a Separate Date Table
• Contains Additional Date Properties
• Reduces Storage Space
• Can be 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
Working with Shared Dimensions

• Created Once and Shared by One or More Cubes in a
  Database
• Cannot Be Changed to Private
• Maintained in Dimension Editor
• Administered in One Place
• Cause All Cubes Using that Dimension to be
  Unavailable for Querying After Rebuilding
  Structure
• Identified by a Sharing Hand Icon

13
Working with Private Dimensions

• Created and Used within Single Cube
• Maintained in Cube Editor, Not Dimension Editor
• Cannot Be Changed to Shared
• Rebuilt Automatically with Cube Process
• Identified by Dimension Icon

14
Defining the All Level

• Summarizes All Data at Top Level of Dimension
• Is Included by Default
• Is Named All DimensionName by Default
• For example, All Product
• Can Be Turned Off within the Dimension Editor
• Cannot Be Defined by the Member Key Column or the
  Member Name Column
• Can Be Renamed Using the All Caption Property

15
Specifying a Default Member
16
Defining a Hierarchy

• A Hierarchy Is a Set of Members and Levels within
  a Dimension
• By Default, a Dimension Contains One Hierarchy
• A Dimension Can Contain Multiple Hierarchies

17
Creating Multiple Hierarchies
Department Dimension
Department.Region
Region 1
Department A
Department B
Region 2
Two Hierarchies
Department.Management
18
Overview of Parent-Child Dimensions

• Are Based on a Two Column Dimension Table
• Contain Levels Created by Parent-Child
  Relationships
• Contain Unbalanced Levels
• Are Created with the Dimension Wizard
• Can Slow Queries that Reference Them

19
Structure of a Parent-Child Dimension
20
Loading Data To Non-Leaf Members
Steve
3000
John
Diana
Eric
1500
1500
1500
Mike
Corey
Susan
Beth
500
500
500
500
21
Members with Data

• In Standard Dimensions, Only Leaf Members Can
  Correspond to Fact Table Data
• In Parent-Child Dimensions, Leaf and Upper Level
  Members Correspond to Fact Table Data
• The Members with Data Property Has Three Possible
  Settings
• Leaf Members Only
• Non-leaf Data Hidden
• Non-leaf Data Visible

22
Reviewing Analysis Services Limits
23
Creating a Grouping Level
Large Level
Grouping Level
24
Grouping Members into Levels Two Solutions

• Analysis Services Auto-grouping
• Create Intermediate Parents
• Approx. Square Root of Members
• Can Hide Using Visible Property
• Custom Grouping Using Expressions
• Custom Intermediate Parents
• Use SQL Expressions