Hierarchical Metadata Relationships in Essbase – Pro’s and Con’s

A question that often has to be considered during design of an Essbase database is to establish a relationship between metadata points that could possibility be two separate dimensions. For instance, combining Entity and Project into a single entity dimension with Projects rolling into the entities that are responsible for managing those Projects. The first litmus test that needs to be passed is whether your project has a direct correlation to a specific entity. Essentially, does a one for one relationship exist? Can the Project only be managed by one Entity?

In instances where a Project may roll to many different Entities the debate as to whether combining the dimensions is often eliminated since the size of the dimension could eventually become very large due the numbers of combinations of metadata points that would need to be supported. In earlier versions, member uniqueness would be a constraining factor, that at that time, could only be addressed through member concatenation. Duplicate member names capabilities have since eliminated that constraint.

If the project is only managed by a single entity then Pro's and Con's need to be evaluated.

The Single Dimension Approach

• Con's relating to a Single Entity dimension:
  
  • What-if Modeling would need to be a function of an administrator since they would need to stage the alternate roll-up based upon end-user feedback
    
• Pro's with a Single Entity dimension:
  
  • Relationships between contracts and divisions could be clearly understood by viewing the hierarchical structure
    
  • No need to copy, clear, input or move data by end-users when What-If modeling is being performed since organizational changes could be modeled via alternate hierarchies, leveraging shared members.
    
  • Sparseness of the database is reduced allowing for better performance
    
  • One less dimension for users to have to navigate, reducing the possibility of user getting lost in their data
    
  • One less dimension is necessary to aggregate enhancing application performance and promoting a more dynamic application
    
  • Easier administration of security
    

The Separate Dimension Approach

• Pro's relating to the multiple dimension approach:
  
  • End-users would need no administrator involvement in modeling What-if analysis since they would leverage separate slices of the database through scenario and version combinations to model.
    
• Con's with the multiple dimension approach:
  
  • Relationships between contracts and divisions cannot be inferred by viewing the hierarchical structure
    
  • Data would need to be copied, cleared, input or moved by end-users to stage the what-if.
    
  • Sparseness of the database increases which could lead to diminished performance
    
  • Another dimension for users to have to navigate, increasing the possibility of user getting lost in their data
    
  • One more dimension necessary to aggregate reducing application performance
    
  • More complex administration of security. Flat dimensions don't readily allow the use of relationship functions when administering security.
    

So what is the right approach? Either approach is acceptable as long as the Pro's and Con's of each approach is understood.