Which set of relationships should you use?

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?

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.

 



Leave a Reply 0

Your email address will not be published. Required fields are marked *