Module 4: Building Working with Standard Dimensions Dimensions - PDF document

Overview � Understanding Dimension Basics � Shared vs. Private Dimensions Module 4: Building � Working with Standard Dimensions Dimensions Using the � Basic Level Properties � Working with Parent-Child Dimensions Dimension Editor � Understanding Dimension Basics Enabling Various Views Analysis Server � Enabling Various Views � Understanding Levels and Members Finance � Describing Familial Relationships Operations Profit Volume � Reviewing Analysis Services Limits by Division by Plant by Country by Shift by Month Marketing Sales by Product by Actual/Budget Revenue Revenue by Day by Product by Customer by Region by Industry by Sales Rep by Channel by Quarter by Week Understanding Levels and Members Describing Familial Relationships Dimension Ancestors of IL and MO Region Parents Central Descendants IL of Region MO Siblings East � Product Dimension Cousins Children NY � Four Levels: All, Category, Sub-Category, Product MA � Category Members: Bread, Dairy, Meat 1

� Shared vs. Private Dimensions Reviewing Analysis Services Limits Items Limits Dimensions per database 65,535 � Working with Shared Dimensions Levels per database 65,535 � Working with Private Dimensions Dimensions per cube 128 Levels per cube 256 Levels per dimension 64 Members per parent 64,000 Length of dimension name 24 characters Working with Shared Dimensions Working with Private Dimensions � Created Once and Shared by One or More Cubes in a � Created and Used within Single Cube Database � Maintained in Cube Editor, Not Dimension Editor � Cannot Be Changed to Private � Cannot Be Changed to Shared � Maintained in Dimension Editor � Rebuilt Automatically with Cube Process � Administered in One Place � Identified by Dimension Icon: � Cause All Cubes Using that Dimension to be Unavailable for Querying After Rebuilding Structure � Identified by a Sharing Hand Icon: � Basic Level Properties Working with Standard Dimensions Country USA � Assigning Member Keys and Names � Identifying Uniqueness of Members California Colorado Illinois State � Creating Members from Expressions � Working with Ragged Dimensions San Jose La Jolla Denver Chicago Peoria Springfield City � Understanding Snowflake Dimensions � Each Level Corresponds to a Dimension Table Column � Defining the All Level � All Members at a Given Level Have the Same Number of � Specifying a Default Member Ancestors 2

Assigning Member Keys and Names Identifying Uniqueness of Members � Defining the Member Key Column � Member Keys Unique � Determines the members included in a level � Works with the Member Key Column � Usually comes from a single dimension table column � Is set as a dimension or level property � Defining the Member Name Column � Affects cube processing performance with True setting � Provides names for members at a level � Cannot be set within a parent-child dimension � Can be different from the Member Key Column � Member Names Unique � Sorting Members in a Level � Works with the Member Name Column � Order by key � Is set as a dimension or level property � Order by name � Affects member naming in MDX with True setting � Order by member property Creating Members from Expressions Working with Ragged Dimensions All � Add Flexibility When Defining Levels Country USA Israel � Are Created from One or More Columns in a Single Table No California Illinois State States � Are Defined in the Member Key Column and Member Name Column in the Dimension Editor Tel Aviv Haifa City San Jose La Jolla Chicago � Act as RDBMS Pass-Through Functions � Variable Depth in Branches � Must be Valid RDBMS Syntax � Level Property Hide Member If Understanding Snowflake Dimensions Defining the All Level Dimension � Summarizes All Data at Top Level of Dimension Tables � 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 Fact Table 3

Specifying a Default Member Lab A: Creating a Standard Dimension � Working with Parent-Child Dimensions Lab B: Creating a Snowflake Dimension � Overview of Parent-Child Dimensions � Structure of a Parent-Child Dimension � Members with Data � Parent-Child Level Management � Skipped Levels Column Overview of Parent-Child Dimensions Structure of a Parent-Child Dimension Employee Manager Smith Smith <none> � Are Based on a Two Column Dimension Table Jones Smith � Contain Levels Created by Parent-Child Jones White Relationships White Smith Block Jones � Contain Unbalanced Levels Block Hart Knight Hart Jones � Are Created with the Dimension Wizard Knight Jones � Can Slow Queries that Reference Them Fox Hart Fox Hunt Hunt Hart Smart Hunt Smart 4

Members with Data Parent-Child Level Management � 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 Skipped Levels Column Lab C: Creating a Parent-Child Dimension � Creates Ragged Hierarchies within Parent-Child Dimensions � Uses a Column in the Dimension Table that Contains the Number of Levels to Skip � Is Accessed from the Advanced Tab of the Properties Pane Review � Understanding Dimension Basics � Shared vs. Private Dimensions � Working with Standard Dimensions � Basic Level Properties � Working with Parent-Child Dimensions 5