You design a Business Intelligence (BI) solution by using SQL Server 2008. You create a SQL Server 2008 Analysis Services (SSAS) solution by using SQL Server 2008. The solution contains a dimension named DimProduct. The DimProduct dimension contains attributes named Product, Color, Sub-Category, and Category. The Product attribute is the key attribute for DimProduct.
A sample data set of the solution is as shown in the following table.
Product Color Sub-Category Category
A001 Blue Jeans Clothing
A002 Red Jeans Clothing
A003 Yellow Couch Furniture
A004 Red T-shirt Clothing
A005 Black Chair Furniture
You discover that the DimProduct dimension has performance issues. You need to design attribute relationships on the DimProduct dimension for optimal performance.
Which set of relationships should you use?
A.
Source Attribute Related Attribute
Product Color
Product Category
Product Sub-Category
B.
Source Attribute Related Attribute
Product Color
Product Sub-Category
Sub-Category Category
C.
Source Attribute Related Attribute
Product Color
Color Sub-Category
Sub-Category Category
D.
Source Attribute Related Attribute
Product Color
Product Category
Product Sub-Category
Sub-Category Category
Explanation:
Tip: “attribute relationships … optimal performance” = “Product -> Color and SubCat / SubCat -> Cat”Attribute Relationships
Before we examine the new attribute relationship designer, lets take a minute to define the term attribute relationship. We know that attributes represent aspects of dimensions.
Hierarchies are roll-up groupings of one or more attributes. Most often, measure data is aggregated (usually summed) in hierarchies.
Measure data is loaded into the cube via rows in the fact table. These are loaded at the lowestlevel of granularity.
Its important to understand that the SSAS query processing engine is designed to use or calculate aggregate values of measure data at intermediate levels in dimensional hierarchies.
If you are creating natural hierarchies, the SSAS query engine can use intermediate aggregations if and only if you define the attribute relationships between the level members. These intermediate aggregations can significantly speed up MDX queries to the dimension. To that end, the new Attribute Relationships tab in the dimension editor lets you visualize and configure these important relationships correctly
(Smart Business Intelligence Solutions with Microsoft SQL Server 2008, Copyright 2009 by Kevin Goff and Lynn Langit)http://msdn.microsoft.com/en-us/library/ms166553.aspx
Specifying Attribute Relationships Between Attributes in a User-Defined Hierarchy
As you have already learned in this tutorial, you can organize attribute hierarchies into levels within user hierarchies to provide navigation paths for users in a cube. A user hierarchy can represent a natural hierarchy, such as city, state, and country, or can just represent a navigation path, such as employee name, title, and department name. To the user navigating a hierarchy, these two types of user hierarchies are the same.
With a natural hierarchy, if you define attribute relationships between the attributes that make up the levels, Analysis Services can use an aggregation from one attribute to obtain the results from a related attribute. If there are no defined relationships between attributes, Analysis Services will aggregate all non-key attributes from the key attribute. Therefore, if the underlying data supports it, you should define attribute relationships between attributes. Defining attribute relationships improves dimension, partition, and query processing performance.
When you define attribute relationships, you can specify that the relationship is either flexible or rigid. If you define a relationship as rigid, Analysis Services retains aggregations when the dimension is updated. If a relationship that is defined as rigid actually changes, Analysis Services generates an error during processing unless the dimension is fully processed. Specifying the appropriate relationships and relationship properties increases query and processing performance.http://msdn.microsoft.com/en-us/library/ms174878.aspx
Defining Attribute Relationships
In Microsoft SQL Server Analysis Services, attributes are the fundamental building block of a dimension. A dimension contains a set of attributes that are organized based on attribute relationships.
For each table included in a dimension, there is an attribute relationship that relates the table’s key attribute to other attributes from that table. You create this relationship when you create the dimension.
An attribute relationship provides the following advantages:
– Reduces the amount of memory needed for dimension processing. This speeds up dimension, partition, and query processing.
– Increases query performance because storage access is faster and execution plans are better optimized.
– Results in the selection of more effective aggregates by the aggregation design algorithms, provided that user-defined hierarchies have been defined along the relationship paths.
Attribute Relationship Considerations
When the underlying data supports it, you should also define unique attribute relationships between attributes. To define unique attribute relationships, use the Attribute Relationships tab of Dimension Designer.
Any attribute that has an outgoing relationship must have a unique key relative to its related attribute. In other words, a member in a source attribute must identify one and only one member in a related attribute. For example, consider the relationship, City -> State. In this relationship, the source attribute is City and the related attribute is State. The source attribute is the many side and the related side is the one side of the many-to-one relationship. The key for the source attribute would be City + State.