4.5 sort attribute members by secondary attributes

Sort attribute members by secondary attributes :

In Lesson 3rd, you learned how to sort attribute members by their names or key values. You also learned how to use the composite member key to influence attribute members and sort order. For more information, see . However, you may have to sort attribute members by Secondary attribute to obtain the desired sorting order. For example, when the attribute name or attribute key cannot provide the required sorting order. To sort attributes by Secondary attribute name or key, you must use secondary attributes related to this attribute.

Attribute relationship defines the relationship or dependency between attributes. In dimensions based on a single relational table, all attributes are usually related to each other through key attributes. This is because all attributes of a dimension provide information about the members who use the key attribute to link to fact data in the fact data table of each correlation value group. In Dimensions Based on Multiple tables, attributes are usually linked Based on the join keys between tables.

However, you may also want to learn more about the special-level members in the hierarchy. With the dimension designer, you can define other relationships between attributes or change the default relationships to improve performance. When creating an attribute relationship, the main constraint is to ensure that the referenced attribute contains only one value for any member of its related attribute. When defining the relationship between two attributes, you can define the relationship as rigid or flexible based on whether the relationship between members changes over time. For example, employees may move to different sales areas, while cities and counties may not move to different States, provinces, and autonomous regions. If a link is defined as a rigid link, attribute aggregation is not recalculated each time a dimension is incrementally processed. However, if the relationship between Members does change, the dimension must be fully processed. For more information, see , , , And .

In the task of this topic, a new attribute is defined in the "time" dimension based on the existing columns in the basic dimension table. You will use this new attribute to sort calendar month members in chronological rather than alphabetic order. You will also define a new attribute in the "customer" dimension based on the name calculation, which is used to sort members of the "commuting distance" attribute. In the task of the next topic, you will learn how to use Attribute relationships to improve query performance.

Define attribute relationships and sorting order in the "time" dimension
Define attribute relationships and sorting order in the "time" dimension

2. Open the dimension designer for the "time" dimension, and view"Month name"FeatureOrderbyAttribute.

   Note,"Month name"Feature members are sorted by their key values.

4. Switch"Browser"Tab, make sure that"Hierarchy"Selected in the list"Calendar time"And then expand the level in the User-Defined hierarchy to view the order of calendar months.

   Note: Members of the Attribute Hierarchy are sorted Based on the ASCII values of their member keys (I .e. Month and year. In this case, calendar months are not sorted by attribute names or keys in chronological order. To solve this problem (MonthnumberofyearAttribute) sorts the members of The Attribute Hierarchy. This new attribute is created based on the columns in the dimtime dimension table.

6. Switch to the "time" dimension"Dimension Structure"Tab, right-click"Data Source view"In the paneMonthnumberofyearAnd then click"Create attribute from column".

8. In"Attribute"In the pane, selectMonth number of yearIn the "properties" windowAttributehierarchyenabledSet propertyFalse, SetAttributehierarchyoptimizedstateSet propertyNotoptimized, AndAttributehierarchyorderedSet propertyFalse.

   These settings will hide this property to the user and save processing time. Since this new property will only be used to sort members of other properties, you should set it in this way.

This task can be simplified by arranging properties in alphabetical order in the "properties" window, because these three properties are adjacent to each other after sorting.

 

2. In"Attribute"Expand in the pane"Date".

   Note: All attributes in the "time" dimension are directly related to the "date" attribute. The "date" attribute is the member key that associates a Dimension member with the fact data in the relevant measurement group.

4. Expand"Month name".

   Note that"Month name"Attribute and"Month number"No relationship is defined between attributes.

6. Set"Month number"Drag properties"Month name"Under"<New property relationship">Mark.

   If you define other relationships, moving existing relationships can effectively improve processing performance and reduce redundancy. Now in"Month number"Attributes and"Month name"The relationship between attributes is defined. In the "properties" window, you can note that"Month number"Feature relationshipRelationshiptypeThe default value of the attribute isFlexible.

8. In the "properties" window, SetRelationshiptypeAttribute Value changedRigid.

   "Month name"Attributes and"Month number"The relationship between members of the attribute does not change with time. Therefore, analysis services does not delete the aggregation of this link during incremental processing. If a change does occur, a processing error occurs during the incremental processing. You need to complete the process on the dimension. You are now ready to set"Month name".

   Display"Month name"Feature"Month number"Feature relationshipRelationshiptypeThe property has been setRigid.

10. In"Features"Select"Month name"In the "properties" windowOrderbyAttribute Value changedAttributekey, AndOrderbyattributeAttribute Value changed"Month number".

    These property changes are displayed in the "properties" window.

12. In"Generate"Click"Deployment Analysis Services tutorial".
14. When the deployment is completed successfully, switch to"Browser"Tab, click"Reconnect"And then browse the "calendar time" and "accounting time" User hierarchies to ensure that the months are now sorted in chronological order.

    Note that the month is now sorted in chronological order, as shown in.

Define attribute relationships and sorting order in the "customer" dimension
Define attribute relationships and sorting order in the "customer" dimension

2. In the "customer" dimension designer, switch"Dimension Structure"Tab.

4. In"Features"In the pane, select"Commuting distance"Feature, and then view it in the "properties" WindowOrderbyAttribute.

6. switch to the "Browser" tab, And Then browse the members of the "commuting distance" Attribute Hierarchy.

   note: the members of this Attribute Hierarchy are sorted Based on the ASCII value of the member key. In this case, sort by attribute name or key will not sort the commuting distance in ascending order. In this task, members of the Attribute Hierarchy are sorted Based on the commutedistancesort name (the corresponding sorting number is assigned to different values in the column. This name calculation has been added to the customer table in the adventure works DW Data Source view to save time. You can switch to this data source view to view the SQL script used in this name calculation. For more information, see .

   Members of the "commuting distance" Attribute Hierarchy are displayed. These members are sorted by the ASCII value of the member key.

8. In the "customer" dimension designer, switch"Dimension Structure"Tab, right-click"Data Source view"PaneCustomerTableCommutedistancesortAnd then click"Create attribute from column".

10. In"Features"In the pane, select"Sort commuting routes"In the Properties windowAttributehierarchyenabledSet propertyFalse, SetAttributehierarchyoptimizedstateSet propertyNotoptimized, AndAttributehierarchyorderedSet propertyFalse.

    These settings will hide this property to the user and save processing resources. Since this new property will only be used to sort members of other properties, you should set it in this way.

12. In"Features"In the pane, expand"Full name".

    Note that all attributes derived from the customer table in the "customer" dimension from the analysis services tutorial Data Source view are related to this attribute.

14. Expand"Region".

    Note that all attributes derived from the analysis services tutorial Data Source view in the "customer" dimension are related to the "region" attribute.

16. Select"Region"In the "properties" windowAttributehierarchyvisibleSet propertyFalse, SetAttributehierarchyoptimizedSet propertyNotoptimized, AndAttributehierarchyorderedSet propertyFalse.

    Because this attribute is not used for browsing, these settings will hide this attribute and save processing time. If an Attribute Hierarchy contains a member attribute, this attribute hierarchy must be enabled.

18. In"Features"In the pane, expand"Commuting distance".

    Note that the attribute corresponds"Sort commuting routes"No relationship is defined between attributes.

20. Set"Sort commuting routes"Drag properties"Commuting distance"Under"<New property relationship">Mark.

    You have"Commuting distance"Attribute and"Sort commuting routes"The relationship between attributes is defined. In the "properties" window, note that"Commuting distance"Feature"Sort commuting routes"Member'sRelationshiptypeThe property isFlexible.

22. In the "properties" window, SetRelationshiptypeAttribute Value changedRigid.

    "Commuting distance"Attributes and"Sort commuting routes"The relationship between members of the attribute does not change with time. You are now ready to set"Commuting distance"The order of attributes.

24. In"Features"Select"Commuting distance"In the "properties" windowOrderbyAttribute Value changedAttributekey, AndOrderbyattributeAttribute Value changed"Sort commuting routes".

26. In"Generate"Click"Deployment Analysis Services tutorial".

28. When the deployment is successful, switch to the dimension designer of the customer dimension."Browser"Tab, click"Reconnect"And then browse"Commuting distance"Attribute Hierarchy.

    Note: The members of the Attribute Hierarchy are now sorted in logical order based on the increasing distance, as shown in.