Posts Tagged ‘attribute relationship’

MDX #26 – SSN can only be referenced as a member property in MDX

February 21, 2013 Leave a comment

I’ve blogged about the Properties()  function before.

MDX #11 – How to get number of cars each customer owned using Properties() function?

MDX #12 – Do not forget the TYPED flag in the Properties() function 

The Properties()  function is used to explore the attribute relationships in a dimension.

If an attribute is also enabled as an attribute hierarchy, then of cause, we can also reference it as Attribute Hierarchy in MDX.

AttributeHierarchyEnabled = False

In the Employee dimension in the Adventure Works cube, employees’ SSN numbers is not enabled as an Attribute Hierarchy. Its property AttributeHierarchyEnabled is set to False.

Here are what will happen in SSAS, in MDX, and in client applications when AttributeHierarchyEnabled is set to False.

In SSAS: A disabled attribute hierarchy cannot be be used as a level in a user defined hierarchy

However, it can still be used to order the members of another attribute.

In MDX: 1) A disabled attribute hierarchy cannot be referenced as Attribute Hierarchy

This query referenced the SSN as attribute hierarchy, and no result is returned.


In MDX: 2) A disabled attribute hierarchy can only be referenced as a member property

This query referenced SSN as the property of current member of the employee. It shows the correct SSN for each employee.


In Client App: 1) Will not be used for browsing in client application

In Client App: 2) Will only be visible to client applications as a member property

In SSAS: still makes sense to order and optimize the disabled attribute

Because the members of these attribute hierarchies are still used for dimensioning fact data, ordering and optimizing the members of these attribute hierarchies can still improve performance.

Therefore, the following properties of these attributes can still be enabled.

  • AttributeHierarchyVisible = True: (the attribute will be visible to the attribute hierarchies list in client applications)
  • IsAggregatable = True: (it will still have the All top level member)
  • AttributeHierarchyOptimizedState = FullyOptimized
  • AttributeHierarchyOrdered = True
  • GroupingBehavior = EncourageGrouping

SSAS #24 – Implement Attribute Relationship in SSAS 2008

June 13, 2011 Leave a comment

Do you still remember the blue squiggly from my previous blog post, SSAS #23 – Implement dimensional hierarchy in SSAS 2008?

On the Hierarchies tab in the Dimension Designer, the blue squiggly says:

“Attribute relationships do not exist between one or more levels in this hierarchy. This may result in decreased query performance.”

You’ve already know that this is a Best Practice Warning and it is telling us to create attribute relationships.

Fortunately, in SSAS 2008, attribute relationships are no longer a “hidden” feature, as in SSAS 2005 where there is no graphic to show the relationships. Now we have a dedicated tab with graphics. How cool can it get?

After the drag-and-drop of the two attributes, TreatmentType and Treatment, to the Hierarchies tab, the initial graphics on the Attribute relationships shows:


This is not exactly correct.

The correct graphics should be:


The trick here is to create the attribute relationships in reverse, that is, lower-order objects (many) then higher-order objects (one), instead of in the order of one-to-many.

There are two ways to get the relationships into the correct order.

One way –drag the lower-order object and drop in onto the higher-order object

Give it a try. Drag Treatment and drop it onto TreatmentType.

Another way – use the Attribute Relationships pane at the lower right corner.

Right click on the relationship. Select Edit Attribute Relationship…


Make sure these:

  • The One side of the relationship = Source Attribute
  • The Many side of the relationship = Related Attribute
  • Relationship type = Rigid (in my example, the relationship will not change over time)


The blue squiggly on the Hierarchies tab disappeared

Here is the proof.


Closing Remark

In SSAS #22, 23 and 24 posts, I made the following assumptions:

  • you will use the star schema in your data mart design, and
  • there are natural one-to-many relationships among dimensional attributes, therefore,
  • the dimensional hierarchical structures are stored in one single dimensional table, and
  • the dimensional table is a non-parent-child table
    There are many discussions about Snowflake schema verses Star schema. If you use a Star schema, the Dimension Wizard will not be able to detect the natural hierarchies that exist.
%d bloggers like this: