Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

MSAS: Understanding Hierarchies

By Exforsys | on April 3, 2005 |
MSAS
A hierarchy defines the relative positions of members in a dimension. Hierarchies are sometimes represented as pyramidal structures. The members in this structure are arranged in an expansive order—from the most summarized to the most detailed. For instance in a geography dimension the country may the most summarized and the individual cities and localities may be the most detailed members of the hierarchy.

Analysis Services supports three kinds of hierarchies. Balanced, unbalanced and ragged. A balanced hierarchy is a hierarchy in which all branches descend to the same level and each member’s logical parent is the level immediately above the member. Examples of balanced hierarchies are regular and virtual dimensions. In unbalanced hierarchies, the branches of the hierarchy descend to different levels as in parent child dimensions. Ragged hierarchies are hierarchies in which one members’ logical parent is not in the level immediately above the member. As a result the branches of the hierarchy descend to different levels. Ragged hierarchies are supported in regular and in parent child dimensions. The ragged hierarchy becomes an unbalanced hierarchy in the parent child dimension. The Hide Member If property in the property pane is used to make a hierarchy ragged or the Skipped Levels column property of a level is used to support a ragged structure. These properties are not supported in virtual dimensions.

Analysis services supports dimensions with multiple hierarchies. These provide an alternate view of the cube data. Dimensions with multiple hierarchies can be defined as two or more dimensions having names that share the same prefix followed by a period but having different suffixes. Example:– Time Calendar and Time Fiscal. The only condition to this is that the suffix should not be equal to any current or future level name or member name in the dimension. This rule is imposed to ensure that queries using the dimension do not get ambiguous results. The process of creating hierarchies for a time dimension is illustrated in the section Creating a Time Dimension.

How to create a dimension with multiple hierarchies in Dimension Editor

In the Analysis Manager tree pane, expand the database in which you want to create the dimension with multiple hierarchies.

Right-click the Shared Dimensions folder, point to New Dimension, and then click Editor.

In the Choose a Dimension Table dialog box, expand a data source, click the dimension table for the dimension, and then click OK. Dimension Editor appears with the dimension table showing in the Schema tab.

To add more tables to the dimension, on the Insert menu, click Tables, and then use the Select Table dialog box.

If the dimension contains multiple tables, ensure each is joined to another. To join two columns, in the Schema tab, drag one column to the other.
Create levels in the dimension. It is easiest to create the highest, most general level first and then create progressively lower, more specific levels; otherwise, if member counting is enabled, a confirmation dialog box appears each time you add a level. For each level you want to create:
On the Insert menu, click Level.
In the Insert Level dialog box, select the column on which the level is based, and then click OK. As you create each level, it appears in the tree pane.
(Optional.) In the properties pane, modify the properties of the dimension and its levels.
To save the dimension, on the File menu, click Save, type a name for the dimension in the New Dimension Name dialog box. To define a hierarchy for the dimension, this name must be of the form dimensionname.hierarchyname, where dimensionname is unique if there are no existing hierarchies or the same as another if it is another hierarchy of a dimension; hierarchyname is unique for each hierarchy being defined. Click OK.
Adding Hierarchies to Existing Dimensions
Hierarchies can be added to a dimension in two ways. If a hierarchy has already been defined for a dimension then the Dimension Editor can be invoked to edit the dimension. If the hierarchy is to be added to a dimension that has no defined hierarchies, then, new hierarchies can be created by renaming the dimension with a new dimension name part, a period and a hierarchy name part. Further hierarchies can be created on similar lines with the hierarchy name being different. It should be noted that when creating dimensions with multiple hierarchies, the hierarchy part of the name should not be equal to any current or future level name or member name in the dimension as queries will derive ambiguous results.
How to add a hierarchy to an existing dimension
In the Analysis Manager tree pane, expand the database in which you want to create the dimension with multiple hierarchies, and then expand the Shared Dimensions folder.
Right-click the shared dimension with an identified hierarchy in which you want to add a hierarchy, and then click Edit. Such dimensions have names where a period is used to separate the dimension name part from the hierarchy name part.
In Dimension Editor, edit the dimension by adding or deleting levels or changing its schema.
After editing the dimension, to save the dimension with a new hierarchy, on the File menu, click Save As to display the Dimension: New Name dialog box. To add a new hierarchy, change the hierarchy part of the name following the period in the New name box. (Do not change the dimension name part before the period.) Click OK.
Please note that when additional hierarchies are created for dimensions, the hierarchy part of the name should not equal any current or future level name or member name in the dimension because queries using the dimension may be ambiguous.

To add a hierarchy to a dimension without a previously identified hierarchy

In the Analysis Manager tree pane, expand the database in which you want to create the dimension with multiple hierarchies, and then expand the Shared Dimensions folder.
Right-click the shared dimension with an unidentified hierarchy in which you want to add a hierarchy, and then click Edit. The names of dimensions with unidentified hierarchies are those that do not contain periods. The dimensions without periods are marked in red and those with periods are marked in blue in the picture below.


In Dimension Editor, to create a new dimension with a new hierarchy based on the selected dimension, on the File menu, click Save As to display the Dimension: New Name dialog box. In the New name box, provide a different dimension name followed by a period and a hierarchy name. Click OK.
Please Note that when creating dimensions with multiple hierarchies, the hierarchy part of the name should not equal any current or future level name or member name in the dimension because queries using the dimension may be ambiguous.

(Optional.) In Dimension Editor, edit the hierarchy of the dimension by adding or deleting levels or changing its schema.
After editing the dimension and its hierarchy, on the File menu, click Save to save changes.
(Optional.) To create additional hierarchies for the dimension, on the File menu, click Save As to display the Dimension: New Name dialog box. To add a new hierarchy, change the hierarchy part of the name following the period in the New name box. (In this case, do not change the dimension name part before the period.) Click OK.
Repeat Steps 4 through 6 to edit and save the new hierarchy.



« « MSAS: Dimension Storage Modes and Levels
MSAS: Creating a Time Dimension » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • MSAS : Working with Standard Dimensions

    March 31, 2005 - 0 Comment
  • MSAS – Writebacks

    April 28, 2005 - 0 Comment
  • Microsoft Analysis Services Installation

    March 5, 2005 - 0 Comment
  • MSAS: Defining Cube Properties

    April 9, 2005 - 0 Comment
  • MSAS : The Data warehousing framework of SQL Server 2000 – Part 2

    March 18, 2005 - 0 Comment
  • MSAS – Introduction and Managing Partitions

    April 19, 2005 - 0 Comment
  • MSAS : Basics of Levels

    March 31, 2005 - 0 Comment
  • MSAS – Introducing Analysis Services Security

    May 2, 2005 - 0 Comment
  • MSAS : Introducing Analysis Manager Wizards

    March 7, 2005 - 0 Comment
  • MSAS: Analysis Server Cube Storage

    April 12, 2005 - 0 Comment
  • MSAS – Browsing the Dependency Network

    May 6, 2005 - 0 Comment
  • MSAS – Building a Relational Decision Tree Model

    May 6, 2005 - 0 Comment
  • MSAS – Introduction to Data Mining

    May 6, 2005 - 0 Comment
  • MSAS – Applying security to a Dimension

    May 5, 2005 - 0 Comment
  • Tutorial 65: MSAS – Managing Cube Roles

    May 5, 2005 - 0 Comment
  • MSAS – Understanding Database Roles

    May 5, 2005 - 0 Comment
  • MSAS – Securing User Authentication

    May 2, 2005 - 0 Comment
  • MSAS – Introducing Analysis Services Security

    May 2, 2005 - 0 Comment
  • MSAS – Writebacks

    April 28, 2005 - 0 Comment
  • MSAS – Defining and Creating Drillthrough

    April 26, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • MSAS – Browsing the Dependency Network
  • MSAS – Building a Relational Decision Tree Model
  • MSAS – Introduction to Data Mining
  • MSAS – Applying security to a Dimension
  • Tutorial 65: MSAS – Managing Cube Roles

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT