The many names of attribute relations

The reason for writing about this is that I need to find some good practical examples of how attribute relations work and implications on calculations.

Richard Tkatchuk has written a white paper on this topic here, that you can download. My ambition is to follow Richards examples and apply them on the Adventure Works cube(like always). There are not that many examples of people in this business that have followed up Richards work on this topic but as a MDX rookie I am trying to explain what he writes about.

 

I have only tried a few examples and some of them can be questioned from not having a complete picture of what is going on. Still I have given this subject much thought and have checked the best SSAS2005 books several times.  I have also built a small and simple cube without attribute relations and it seem to confirm what I am supposing here. The rest of my experience comes from working in a large company that use SSAS 2005 cubes heavily.

 

Anyway, this is my current opinion.

 

Sometimes attribute relations also are refered to as strong hierarchies. A third alternate term is autoexist.

 

In SSAS2005 you have relations between each attribute in a dimension and the dimension key column. This is the default attribute relation that exists if you have not created any user or natural hierarchies in a dimension. Each attribute hierarchy only has its members and an all member at the top. I assume that this also is the explanation for the autoexists behaviour. Autoexist means that you will only see the same combinations of attributes in a SSAS dimension that also exists in the data source table. So if you only have two customers in a customer dimension(George and Mary) and a Gender attribute(Male, Female) you will only see the combination of (George,Male) and (Mary,Female) in the dimension. You will not see (George,Male), (George, Female), (Mary, Female),(Mary, Male).

 

SSAS2005 also includes the option to create user or natural hierarchies. In the customer dimension you can create a Geography hierarchy with country as the top level followed by state or region  and the individual customer at the leaf level. In the hierarchy editor you put city as the related attribute for the individual customer. Finally you have a natural hierarchy like this.

 

HierarchyLevel RelatedAttribute

Country
State/Region Country
City  State/Region
Customer City

 

When you have built this hierarchy you will get a message that you have redundant attribute relations because the individual attributes(attribute hierarchies) are still connected to the dimension key. Because of this you remove the attribute relations with the dimension key that have been created in the natural hierarchy.

Richard continues with discussing how attribute relations, existing or not existing can affect calculations in MDX. If you have the default attribute relations between the dimension key and all other attributes in a dimension you will never have to worry about that fact table measures will be uncorrect, unless your data model is wrong. If the attribute relations in a natural hierarchy is wrong, mostly because you have failed to recognize a many-to-many relation between to levels, the totals will still be correct ,but the distribution of data in this hierarchy will be wrong or random.

 

So lets start with the calculation or MDX part of this discussion. I have used management studio to run the following MDX selects.
 
I have choosen the first example where you have attribute relations in the user hierarchy that will match members in the attribute hierarchy.

 

With Member Measures.CityName as
[Customer].[Customer Geography].CurrentMember.Name
Select Measures.CityName On Columns,
[Customer].[City].Members On Rows
From [Adventure Works];

 

The city names on the rows will match the calculated members city names beacuse City is related to the [Customer Geography] hierarchy.

In the customer dimension Education is not part of a natural hierarchy. If we change the MDX above to use this attribute you will have an MDX Select like this.

 

With Member Measures.EducationName as
[Customer].[Customer Geography].CurrentMember.Name
Select Measures.EducationName On Columns,
[Customer].[Education].Members On Rows
From [Adventure Works];

 

The calculated member Measures.EducationName now returns the member All Customers i all the cells. [Customer].[Education] have no relation to the members in the natural hierarchy [Customer].[Customer Geography]. Will this mean any problem for the cube measures or a calculated member? Try this MDX and see yourself. Both measures will be correct.

 

With Member Measures.EducationSales as
([Customer].[Customer Geography].CurrentMember,[Measures].[Internet Sales Amount]),format_string = "currency"
Member Measures.EducationName as
[Customer].[Customer Geography].CurrentMember.Name
Select {Measures.EducationSales,[Measures].[Internet Sales Amount],Measures.EducationName}  On Columns,
[Customer].[Education].Members On Rows
From [Adventure Works];

 

The last part of Richards paper discusses coordinate overwrite with the currentmember function. Remember that a overwrite happens all the time when you choose members in dimensions or build calculated members so this is nothing strange.

 

The following examples are related to attribute relations because of the autoexist behaviour in SSAS2005 dimensions and between the slicer and the axises in a MDX select. What you put in the slicer will decide what you will see on rows and columns in a client.  But there are some exceptions with calculated members. 

 

Lets start with a simple MDX select to see the correct values before making changes.

 

With Member Measures.x as [Measures].[Internet Sales Amount]
Select {Measures.x , [Measures].[Internet Sales Amount]} On Columns
From [Adventure Works]
Where( [Customer].[City].&[Seattle]&[WA]);

It will return the Internet Sales amount for All Periods and Seattle.

 

Now, let us see an example where the calculated member will overwrite the slicer but without the currentmember function.

 

With Member Measures.x as ([Measures].[Internet Sales Amount],[Customer].[City].&[Portland]&[OR])
Select Measures.x On Columns
From [Adventure Works]
Where ([Customer].[City].&[Seattle]&[WA]);

 

Seattle is still in the slicer but the calculated member points to Portland in Oregon and the value is for All Periods and Portland.

If you try this MDX Select in ProClarity it will return nothing or an empty grid.

 

Finally, if I use the natural hierarchy with the currentmember function both measures will return the same value. From what I can see the currentmember function will accept the slicer and without it the calculated member will overwrite the slicer.

 

With Member Measures.x as ( [Measures].[Internet Sales Amount],
[Customer].[Customer Geography].CurrentMember),format_string = "currency"
Select {Measures.x , [Measures].[Internet Sales Amount]} On Columns
From [Adventure Works]
Where ( [Customer].[City].&[Seattle]&[WA]);

 

This will also return a correct result for the city Seattle and All Periods.

 

With Member Measures.x as ([Measures].[Internet Sales Amount],[Customer].[City].CurrentMember),format_string = "currency"
Select Measures.x On Columns
From [Adventure Works]
Where ([Customer].[City].&[Seattle]&[WA]);

 

Summary: I have not found any problems with combining attributes in the same dimension that have no attribute relations except for the ones with the dimension key. Natural hierarchies and their attribute relations will help the currentmember function to return correct results when a calculated member overwrites the slicer. If you use attribute hierarchies in a calculated member you can overwrite the member in the slicer and get strange results. More important than the results is ,perhaps , to show practical examples of some MDX terms running around in different discussions.