MSSQL Analysis Services - Attribute Member Names - Page 4

October 13, 2008

The Collation property affords us a way to specify the rules we wish to invoke for text data string comparisons. While collation in general has multiple purposes, it is often used to support the determination of whether the members of a given pair of strings are alike or different. Several Sort Orders are also available, with the Designator and Sort Order selections defaulting to server settings (in effect here, where the Collation property appears empty within the Properties pane.

Because Collation can have an impact upon the uniqueness of attribute member Names, the setting becomes particularly significant when we are specifying Names. Examples given in various books and other documentation surrounding this consideration often cite the fact that treatment of capitalized and non-capitalized letters (some Collation settings ignore capitalization entirely, whereas others treat capitalized letters, etc., as different characters than the same, non-capitalized letters). Collation also impacts sorting, so when information consumers dictate, say, in a report they create in Reporting Services that they wish to retrieve a group of attribute members in order of their respective Names (versus in order of the attribute member Key), results might not match expectations if the Collation property setting is not taken into consideration .

NOTE: We always have the option of using another attribute entirely as a basis for our sort orders, if Key or Name is somehow unsatisfactory. For more information on this versatile option, see my article Alternatively Sorting Attribute Members in Analysis Services 2005.

15.  Click the downward arrow selector button to the right of the box labeled Collation designator, to expose the collations available for selection, as partially depicted in Illustration 16.

Illustration 16:  Available Collation Options (Partial View)
Illustration 16: Available Collation Options (Partial View)

16.  Leaving the settings in the Collation Designator dialog at their previously established values, click the Cancel button to dismiss the dialog.

17.  Click the Format label, just beneath the Collation property label, simply to rest it there.

We have noted in other articles of this series that the Format property purports (via the Books Online and other documentation) to allow us to specify - using Visual Basic (Format function) format types - the conventions used in transforming numeric data to text, if such a transformation is required. The reality is that the only member formatting supported within the Unified Dimension Model (UDM) is the Trimming setting that we discuss below. (We can, of course, employ named calculations or column calculations (at the data source view level) within the cube to achieve our formatting ends, as alternative approaches.

18.  Leaving the Format property blank, click the box to the immediate right of the InvalidXmlCharacters label, just beneath the Format property, once again to enable the downward-pointing selector button.

19.  Click the downward arrow selector button, to expose the three selection options for InvalidXmlCharacters, as shown in Illustration 17.

Illustration 17:  Selection Options for InvalidXmlCharacters
Illustration 17: Selection Options for InvalidXmlCharacters

The InvalidXmlCharacters property is applicable in cases where we expect data to be received in the XML format, and where we wish to dictate the handling of such data, if, and when, invalid characters appear. The values are explained in Table 2.

Value

Explanation

Preserve

Analysis Services preserves (e.g., does not change) invalid characters.

Remove

Analysis Services removes invalid characters.

Replace

Analysis Services replaces invalid characters with a question mark (“?”)


Table 2: Options for InvalidXmlCharacters Selection

20.  Leaving the InvalidXmlCharacters property at its previously established setting, click the MimeType label, just beneath the InvalidXmlCharacters property label, simply to rest it there.

The MimeType property allows us to specify the binary data type, where necessary to meet our needs.

21.  Leaving the MimeType property blank, click the box to the immediate right of the Trimming label, just beneath the MimeType property, as before, to enable the downward-pointing selector button.

22.  Click the selector button, to expose the four options for Trimming selection, as depicted in Illustration 18.

Illustration 28: Trimming Property Value Selection Options
Illustration 28: Trimming Property Value Selection Options

The Trimming property allows us to specify the desired treatment of trailing spaces at the beginning / end of a string. As we see in Illustration 18 above, the options are self-explanatory.

23.  Leave the Trimming setting as it currently exists.

NOTE: Please consider saving the project we have created to this point for use in subsequent related articles of this subseries, so as to avoid the need to repeat the preparation process we have undertaken initially, to provide a practice environment.

24.  Select File -> Save All to save our work, up to this point, within the originally chosen location, where it can be easily accessed for our activities within subsequent articles of this subseries.

25.  Select File -> Exit to leave the design environment, when ready, and to close the Business Intelligence Development Studio.

Conclusion

In this article, we examined the attribute member Name property, which we briefly touched upon in Dimensional Attributes: Introduction and Overview Part V. We examined the details of this property, and shed some light on how it can most appropriately be used without degrading system performance or creating other unexpected or undesirable results.

We focused, throughout our examination of attribute member Name, on the general nature of the property, and its possible roles in helping to meet the primary objectives of business intelligence. We reviewed its roles from a technical perspective, including its purpose within its containing dimension within Analysis Services.

In gaining hands-on exposure to attribute member Names, we discussed, at appropriate points, some of the differences between attribute Name and Key properties. We also mentioned significant differences between Analysis Services 2000 and Analysis Services 2005, particularly regarding the use of expressions within our Name column references. Finally, we performed a detailed review of the settings associated with the attribute member Name property, based upon the examination of a representative dimension attribute within our sample UDM.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Introduction to MSSQL Server Analysis Services Series
Introduction to Security in Analysis Services
Cube Storage: Planning Partitions from a SQL Server Management Studio Perspective
Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective)
Cube Storage: Introduction to Partitions
Introduction to Cube Storage
Attribute Discretization: Customize Grouping Names
Attribute Discretization: Using the "Clusters" Method
Attribute Discretization: Using the "Equal Areas" Method
Attribute Discretization: Using the Automatic Method
Introduction to Attribute Discretization
More Exposure to Settings and Properties in Analysis Services Attribute Relationships
Attribute Relationships: Settings and Properties
Introduction to Attribute Relationships in MSSQL Server Analysis Services
Attribute Member Values in Analysis Services
MSSQL Analysis Services - Attribute Member Names
Attribute Member Keys - Pt II: Composite Keys
Attribute Member Keys - Pt 1: Introduction and Simple Keys
Dimension Attributes: Introduction and Overview, Part V
Dimension Attributes: Introduction and Overview, Part IV
Dimension Attributes: Introduction and Overview, Part III
Dimension Attributes: Introduction and Overview, Part II
Dimension Attributes: Introduction and Overview, Part I
Dimensional Model Components: Dimensions Part II
Dimensional Model Components: Dimensions Part I
Manage Unknown Members in Analysis Services 2005, Part II
Manage Unknown Members in Analysis Services 2005, Part I
Alternatively Sorting Attribute Members in Analysis Services 2005
Introduction to Linked Objects in Analysis Services 2005
Distinct Counts in Analysis Services 2005
Positing the Intelligence: Conditional Formatting in the Analysis Services Layer
Administration and Optimization: SQL Server Profiler for Analysis Services Queries
Mastering Enterprise BI: Time Intelligence Pt. II
Mastering Enterprise BI: Time Intelligence Pt. I
Design and Documentation: Introducing the Visio 2007 PivotDiagram
Actions in Analysis Services 2005: The URL Action
Actions in Analysis Services 2005: The Drillthrough Action
Mastering Enterprise BI: Introducing Actions in Analysis Services 2005
Mastering Enterprise BI: Introduction to Translations
Mastering Enterprise BI: Introduction to Perspectives
Introduction to the Analysis Services 2005 Query Log
Mastering Enterprise BI: Working with Measure Groups
Mastering Enterprise BI: Introduction to Key Performance Indicators
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. II
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I
Process Analysis Services Objects with Integration Services
Usage-Based Optimization in Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Named Sets Revisited
Introduction to MSSQL Server Analysis Services: Migrating an Analysis Services 2000 Database to Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Introducing Data Source Views
Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More ...
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Create Aging "Buckets" in a Cube
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube
Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS
Introduction to MSSQL Server Analysis Services: Presentation Nuances: CrossTab View - Same Dimension
Introduction to MSSQL Server Analysis Services: Point-and-Click Cube Schema Simplification
Introduction to MSSQL Server 2000 Analysis Services: Manage Distinct Count with a Virtual Cube
Introduction to MSSQL Server 2000 Analysis Services: Distinct Count Basics: Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Semi-Additive Measures and Periodic Balances
Introduction to MSSQL Server 2000 Analysis Services: Performing Incremental Cube Updates - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Partitioning a Cube in Analysis Services - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Basic Storage Design
Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures
Introduction to MSSQL Server 2000 Analysis Services: Creating a Dynamic Default Member
Introduction to MSSQL Server 2000 Analysis Services: Another Approach to Local Cube Design and Creation
Introduction to MSSQL Server 2000 Analysis Services: Introduction to Local Cubes
Introduction to MSSQL Server 2000 Analysis Services: Actions in Virtual Cubes
Introduction to MSSQL Server 2000 Analysis Services: Putting Actions to Work in Regular Cubes
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Part II
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Professional, Part I
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services , Part II
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Toward More Sophisticated Analysis
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Simple Cube Usage Analysis
Introduction to MSSQL Server 2000 Analysis Services: Build a Web Site Traffic Analysis Cube: Part II
Build a Web Site Traffic Analysis Cube: Part I
Reporting Options for Analysis Services Cubes: Cognos PowerPlay
Reporting Options for Analysis Services Cubes: MS FrontPage 2002
Reporting Options for Analysis Services Cubes: MS Excel 2002
Introduction to MSSQL Server 2000 Analysis Services: Drilling Through to Details: From Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Custom Cubes: Financial Reporting - Part II
Introduction to MSSQL Server 2000 Analysis Services Custom Cubes: Financial Reporting (Part I)
Introduction to SQL Server 2000 Analysis Services: Exploring Virtual Cubes
Introduction to SQL Server 2000 Analysis Services: Working with the Cube Editor
Introduction to SQL Server 2000 Analysis Services: Parent-Child Dimensions
Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions
Introduction to SQL Server 2000 Analysis Services: Working with Dimensions
Introduction to SQL Server 2000 Analysis Services: Creating Our First Cube








The Network for Technology Professionals

Search:

About Internet.com

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