Sunday, October 04, 2015

What is attribute relationships, why we need it?


COPIED From  great article : https://www.toadworld.com/platforms/sql-server/w/wiki/9584.defining-attribute-relationships


Defining correct attribute relationships is the key to an optimal MSAS solution. In MSAS 2005, the dimension architecture was overhauled to become significantly different from previous versions. The new model uses the attribute paradigm as opposed to levels, which used to be the primary building block for dimensions. Levels worked well for hierarchical analysis of data, but didn't allow much flexibility for those data elements that aren't hierarchical in nature; for example address, phone number or square footage. These types of attributes must be exposed as member properties in earlier versions of MSAS. Querying member properties is slow and difficult to optimize.
The attribute model resolves this problem because MSAS 2005 attributes no longer have to be related to each other in hierarchical fashion.
MSAS 2005 supports three types of hierarchies:
  1. Natural user-defined hierarchy: Exposes multiple attributes. Each attribute is "related" to the attribute immediately above itself in the hierarchy. In addition, each attribute may also be related to other attributes. With previous versions of MSAS, this was the only type of hierarchy you could build, even though you did not have to explicitly specify attribute relationships. In a natural hierarchy, the top attribute in the hierarchy does not need to be related to any other attribute within the same hierarchy.
     
  2. Attribute hierarchy: Exposes a single attribute (and optionally "ALL" level summarizing data for a single attribute). MSAS creates an attribute hierarchy for every dimension column you include as an attribute. Attribute hierarchies present a fine alternative for columns such as address, square footage, color, weight, etc which previously had to be implemented as member properties. Now you can easily examine sales by product color or store square footage, if necessary.
     
  3. Un-natural user-defined hierarchy or "reporting" hierarchy: Exposes multiple attributes that are not directly related to each other. Such hierarchies are useful for defining a common drill-path used for navigating the data. For example, you could use a reporting hierarchy to show product sales by color, by weight and by shipping cost. However, reporting hierarchies perform significantly slower than natural hierarchies do.
When you create a dimension, each attribute is by default directly related to the dimension key attribute. After you define additional relationships between non-key attributes, BIDS warns you that the dimension contains "redundant" relationships. This is a warning, not an error. BIDS shows this warning because such redundant relationships could impose additional processing overhead on MSAS. As a best practice, remove relationships established by default once you create natural hierarchies. For example, let's suppose we have a date dimension with calendar year, calendar semester, calendar quarter and month attributes. We also have a key attribute called date_key. When we first create the dimension each non-key attribute is related directly to date_key. Next, we define the attribute relationships in "Calendar" hierarchy as shown on the screenshot:
<img border="0" src="ASOQ_250.gif]]
Now BIDS will report a warning about redundant relationships within the dimension. To avoid this warning, remove the direct relationships Calendar Year -> Date_key, Calendar Semester -> Date_key, Calendar Quarter -> Date_key, Month -> Date_key.
If you accidentally remove an indirect relationship from calendar quarter to calendar semester, BIDS will not allow you to proceed with deployment. This is because each attribute MUST be related to the key attribute, either directly or through another attribute.
During processing, MSAS materializes the relationship between attributes on disk in a form of hierarchy stores. Queries that retrieve the children of a particular member are considerably quicker after attribute relationships have been defined. A typical analytical query drills down from a parent to its children to focus on the area of interest once an interesting data element is found. For example, if a manager sees that sales in South East United States are falling, she can drill down to the states of North Carolina, South Carolina, Tennessee, Kentucky, and Georgia. If she subsequently finds that sales are dramatically falling in Georgia she can further drill down to cities of Atlanta, Albany, Valdosta, etc. After you setup correct attribute relationships between region, state and city in this example you could see performance improve 50-100 times or more compared to running the same queries against an un-natural hierarchy. This type of improvement far outweighs the benefits of any aggregations or partitioning.
Although not directly relevant for performance tuning, you should realize that attributes can only be related using many-to-one relationship. For example, each month is associated with many dates, each quarter has multiple months, each year has multiple quarters and so forth. You should only define a relationship between attributes if you can guarantee that such relationship exists. If you create a relationship between attributes but MSAS detects many-to-many or one-to-one relationship between attributes your solution will display incorrect results.
Analysis Services has to have a way to uniquely identify each attribute and a way to rollup each attribute to the corresponding parent - the related attribute. If you try to relate month names to years in a date dimension, you'll see incorrect results because the month of July can belong to multiple years. To avoid such issues, set the value of KeyColumn property to a combination of columns month and year. Alternatively, you can add another column to the dimension which uniquely identifies each month (perhaps month_id) and use that column as the KeyColumn for the month attribute.

No comments:

Post a Comment