Attributes, UDAs, and Alternate Hierarchies

I'm often asked what the best practice is regarding implementation of Attribute Dimensions, Alternate Hierarchies, and User Defined Attributes. While there are no hard and fast rules, these guidelines outline the strengths and weaknesses of each.

Attribute Dimensions
Attribute dimensions are created by tagging members in a sparse dimension with an attribute tag. Members tagged with a member from an attribute dimension must be at the same Level as all other members tagged with an attribute from the respective attribute dimension. In theory, as long as members are at the same level they may be assigned attributes. In practice I recommend avoiding utilizing attributes for members that are not level zero members. This is due to difficulties ensuring members are at the same level. While this often is apparent in ragged hierarchies (also known as unbalanced hierarchies), symmetrical hierarchies can also have members that appear to be at the same level (i.e. they are at the same generation), but are in fact at different levels due to implied sharing where a parent only has one child. Also, this can create situations where a member is at multiple levels, and the resulting data value utilized by an attribute is not what a user expects.

User Defined Attributes (UDAs)
UDAs are flags that are placed on an Essbase member. A given UDA can be placed on any dense or sparse member in the outline. In addition, unlike Attribute dimensions, UDAs are not linked to the level of the members they are tagged to. In practice, this flexibility is most often leveraged to identify member sets for use in a calculation, especially since UDAs can be tagged to Dense members. While it is possible to refer to UDAs in most reporting and analysis packages, however, I do not recommend deploying UDAs for use by end users.

Alternate Hierarchies
Alternate hierarchies are secondary rollups of members within the same dimension. The level zero members in the alternate hierarchy are called Shared Members, and point back to an identically named real member elsewhere in the dimension. It is important to note that the real members do not have to be level zero members, or at the same level. One of the key advantages of Alternate Hierarchies is the ability for upper level summary members to have a completely unique structure (as opposed to utilizing an Attribute dimension, which merely re-totals the same hierarchy with only a subset of members). While there are many uses for this functionality, this often is utilized to build Management and Legal organizational hierarchies from the same source data, as well as facilitate financial reporting with different standards (i.e. IFRS vs. US GAAP). Conversely, Alternate Hierarchies typically require more maintenance, and increase the size of database. Therefore, they should be carefully deployed only in circumstances that justify the additional expense.

In summary, Attribute Dimensions, User Defined Attributes, and Alternate Hierarchies all have advantages and disadvantages. A typical deployment will often leverage all three areas of functionality to solve specific business problems, but will pay particular attention to the negatives of each option to prevent creating an overly difficult to support and expensive database.

Data Analysis/Validation

Data Analysis/Validation:

Data is the heart of all Business Intelligence projects, but it is often the most missed step in many Business Intelligence projects. Most often the data analysis/validation steps are preformed in the testing phase after the design and build are already completed. Often when the dashboards and reports are compared to their existing reports there are differences between the values on the two reports. Hence, a long, detail data analysis/validation process starts which often cause the project time lines be project budget to be exceeded.
A better approach is to start the data analysis/validation in the project requirements gathering phase. Once access is provided to the application database, it is very beneficial to write queries against the source system to validate the measures on the existing reports. Also by writing queries against the source system the data calculation can be checked and validated. Many of the existing user’s reports may have been built by a resource that is no longer with the organization. Additionally be doing the data analysis/validation early in the project you can get a feel for the data quality. Small data quality errors in a transaction system will be magnified many times in the data warehouse. A small error on one record will greatly be magnified when you are looking at millions of records.

There is often an argument that there is no time in the project requirements phase to perform the data analysis/validation steps. However, if it is not performed in the early phases of the project the time and cost to perform the data analysis/validation are more expensive. There is an old project truism that puts data analysis/validation in perspective: if it is preformed in the requirements phase is cost $1, if it is performed in the design phase it costs $10, If it is performed in the build phase it costs $100, if it is performed in the testing phase it cost $500, and if it is done in the production phase it costs $1000. From this perspective it is more effective and cost efficient to perform the data analysis/validation in the early part of the project where the cost and time constraints and less costly.