dcsimg

Introduction to SQL Server 2000 Analysis Services: Working with Dimensions - Page 7

September 10, 2002


Examining Property Setpoints

We can examine various setpoints at this stage to see if we need to adjust them to fit the realities of the data (upon which we can easily perform ad hoc browses, courtesy of Analysis Services). Several key setpoints exist in the Properties pane. Let's examine some of these.

8. Highlight the Category level in the Dimension tree.
9. Click the Basic tab in the Properties pane.
10. Review the setpoints, together with the explanations, for a standard dimension level, in Table 1 below.


 

 

Basic Tab Properties

 

 

 

 

(Standard Dimension Level)

 

 

Name

 

Explanation

 

Notes / Comments

 

 

 

 

 

Description

 

The level description

 

Fixed Text Value

 

 

 

 

 

Member Key Column

 

The name of the column that contains member names

 

Editable -- Syntax must be preserved

Can be used as a sort basis, in conjunction with Member Name Column or otherwise, among other purposes

Contains integer keys for the member

Default value -- same as Member Name Column

Set as a unique column in the source table, such as a unique ID field, the Key setpoint prevents grouping of members with identical names, etc. (but we would first have to set Dimension Member Names Unique property to allow for this -- See Table 4)

Values can consist of more than a single column name, including any SQL expression that is understood by the relational data source, provided that the expression returns a number or a string. (Concatenations of column values are a common example.)

 

 

 

 

 

Member Name Column

 

The name of the column that contains member keys

 

Editable -- Syntax must be preserved

Identifies names of table and column that provide member names for the level.

Can be used as a sort basis, in conjunction with Member Key Column or otherwise, among other purposes

Typically appears in heading row or columns on a report -- use meaningful terms!

Default value -- same as Member Key Column

Values can consist of more than a single column name, including any SQL expression that is understood by the relational data source, provided that the expression returns a number or a string. (Concatenations of column values are a common example.)

 

 

 

 

 

Table 1: Level Member Properties (Standard Level) - Basic Tab


11. Click the Advanced tab in the Properties pane.

Review the setpoints, together with the explanations, for a standard dimension level, in Table 2 below.

 

 

Advanced Tab Properties

 

 

 

 

(Standard Dimension Level)

 

 

Name

 

Explanation

 

Notes / Comments

 

 

 

 

 

Member Count

 

The estimated number of level members

 

 

 

 

 

 

 

Member Keys Unique

 

Indicates whether member keys are unique throughout the entire level

 

Disabled for a top dimension

 

 

 

 

 

Member Names Unique

 

Indicates whether member names are unique throughout the entire level

 

False allows identical names, but Keys would have to be unique to use (and in any case for a top dimension)

Keep in mind that a False setpoint also means groups of members with identical names are treated as a single members for purposes of numerous properties / operations (i.e., the group would appear as a single member in the hierarchical tree of the Schema pane - Data tab).

 

 

 

 

 

Level Type

 

Indicates the level type

 

 

 

 

 

 

 

Key Data Size

 

The size of the key column in bytes

 

 

 

 

 

 

 

Key Data Type

 

The key column data type

 

 

 

 

 

 

 

Hide Member If

 

Determines how placeholder members that should be hidden are identified

 

 

 

 

 

 

 

Visible

 

Determines whether the level is visible

 

 

 

 

 

 

 

Order By

 

Property base on which members will be ordered

 

Controls sort basis -- options are Key or Name, but other options can be handled through various means

 

 

 

 

 

Custom Rollup Formula

 

MDX expression used to override the default rollup of values in the level

 

 

 

 

 

 

 

Custom Members

 

Determines whether custom formulas can be created for individual members

 

 

 

 

 

 

 

Custom Member Options

 

Determines whether calculation options can be defined for custom members on this level and unary operators on the following level

 

 

 

 

 

 

 

Grouping

 

Determines whether members in the level are used individually or are grouped together into group members

 

 

 

 

 

 

 

Unary Operators

 

Determines whether members have unary operators that control how individual member values are aggregated into their parent member's value

 

Great potential for allocation and other business uses

 

 

 

 

 

Table 2: Level Member Properties (Standard Level) - Advanced Tab


Page 8: Examining Property Setpoints (Continued)









The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers