|Attribute relationships explained (by Richard Tkachuk)|
|Written by Richard Tkachuk|
|Tuesday, 01 May 2007 15:22|
The purpose of this document is to define how attribute relationships affect calculations. Attribute relationships have other impacts such as query performance that are only briefly mentioned here.
In Analysis Services 2005, Attribute relationships define functional dependencies between attributes. In other words, if A has a related attribute B, written A è B, there is one member in B for every member in A, and many members in A for a given member in B. More specifically, given an attribute relationship City è State, if the current city is Seattle, then we know the State must be Washington.
A quick definition of terms: if B is related to A, then AèB and A is said to be a relating attribute to B. For example, given City è State, State is related to City and City is relating to State.
Given this definition, consider the query:
Select measures.sales on columns
What cell is actually being queried? There are two choices; it’s either:
(measures.sales, Customer.Name.Richard, Customer.Gender.All, Customer.City.All, Customer.State.All, Customer.Country.All)
(measures.sales, Customer.Name.Richard, Customer.Gender.Male, Customer.City.Sammamish, Customer.State.Washington, Customer.Country.USA)
The two choices reflect whether or not attributes not specified in the query are left alone or whether they are moved to the members that exist with those specified in the query. If there are no attribute relationships defined, the answer is the first option. But if the query is executed in the context of the following attributes and attribute relationships:
Then the answer is the second of the two choices. When the engine resolves the coordinates of a cell, it uses the tuple specified by the query (Customer.Name.Richard, measures.Sales) and then uses the attribute relationships to get the coordinates of related and relating attributes. Here’s the rule:
This is pretty straightforward. If you tell me you live in Seattle, I know you live in Washington, USA (yes, yes, many cities have the same name – let’s assume not for now). Analysis Services 2005 behaves the same way – if you indicate that a city can only exist in one and only one state by defining the attribute relationship, it moves the state coordinate to that state when the city is defined.
When you create a dimension in Analysis Services 2005, an attribute relationship is automatically created between the key attribute and all the other attributes. For example, if you create the customer dimension, it will look something like this:
It’s up to you to know the relationships in your data and define them. You should do this – not only do you get behavior that makes sense, you’ll get a significant performance boost as well whenever the dimension is queried.
Attribute Relationships and Granularity Attributes
Most of the time measures are associated with the dimension by its key. Defining or not defining the attribute relationships affects performance and overwrite behavior. But there are situations where defining attribute relationships incorrectly can result in errors in query results.
Sometimes the granularity of a measuregroup is at something other than the dimension’s key attribute. Consider actual and budgeted sales against a time dimension. Actual sales can have a finer granularity than budgeted sales. Actual sales can be by day and budgets can be by week. Now consider attribute relationship defined this way:
The dimension and measure groups might be best visualized as:
I’ve drawn the attribute relationships as arrows and the relationship between measure groups and the dimension as lines. Now note that there is no relationship between week and month – so querying the budget measures by month can result in incorrect values. So structures like these should be avoided if possible.
Attribute Relationships and Calculations
Attribute relationships are used in calculations and effect query results. Whenever an attribute is overwritten, its related and relating attributes are overwritten as well.
Perhaps some explanation is required as to what exactly is an overwrite: whenever a calculation specifies a member of a hierarchy, this is an overwrite of the currentmember. As the simplest example, consider the following statement in a script:
Create member measures.Profit as (measures.Revenue – measures.Expense);
The first part of the expression overwrites the current member in the measures dimension with Revenue. The second part overwrites the current member with Expense. Virtually all calculations overwrite one or more hierarchies.
For example, consider a query with and without attribute relationship defined to a customer dimension. The customer dimension contains attributes Name, City, State and Country and a user hierarchy Geography containing these attributes. Now consider the expression:
with member measures.CityName as
One would expect the name of the city but, without attribute relationships defined, that’s not the result. In fact, one would see something like this
What’s going on?
The explanation for the unexpected result is how the currentmember in the Geography hierarchy is determined. For each member in Customer.City the expression attempts to find the member in the Geography hierarchy that corresponds to that specific city, and the other members Name.All, State.All, Country.All (no attribute relationships defined means these attributes are unaffected by changing city). There are no such members in the user hierarchy. In such circumstances the Analysis Services engine applies the so-called “gap rule” and overwrites attributes starting from the lowest level to the top until a valid coordinate is found.
But this is clearly not the intent of the calculation’s author. Generally a customer lives in one city and the city determines the state which then determines the country. But the Analysis Services engine doesn’t know this – to the engine, they’re just attributes. The engine has to be told of the functional dependencies with the attribute relationships: NameèCity, CityèState, and StateèCountry.
With this new information, you’ll see the expected results and the Name in the geography hierarchy will match the name in the user hierarchy:
Explicit vs Implicit Overwrite
When an attribute is overwritten in an expression, it is said to be explicitly overwritten. Related and relating attributes are implicitly overwritten. For example, the following expression is an explicit overwrite of City and an implicit overwrite of State:
With member measures.x as (measures.Sales, City.Seattle)
The query result returns the value for (measures.Sales, City.Seattle, State.Wa). The calculated member explicitly overwrites City from City.All to City.Seattle. State undergoes an implicit overwrite from State.Or to State.Wa; thus the result of (measures.Sales, City.Seattle, State.Wa).
Why the distinction? It is because an explicit overwrite moves directly and indirectly related and relating attributes based on the rules below. An implicit overwrite has no impact on its own related and relating attributes.
For example, in the presence of the following attributes and attribute relationships:
Consider the statement below:
with member measures.x as (measures.Sales, City.Portland)
The result of the calculation yields
(Name.All, Gender.Male, City.Portland, State.Oregon, Country.USA)
Why? The initial coordinate before the calculated member overwrites the coordinates is (assuming Richard is male and lives in Sammamish, Washington, USA) is
(Name.Richard, Gender.Male, City.Sammamish, State.Washington, Country.USA)
The calculation asks for Sales in City.Portland – an explicit overwrite of City which drives an implicit overwrite of its relating attribute (Name) and its related attributes (State and Country) but has no affect on Gender.
Overwriting with the currentmember function
In prior versions of Analysis Services, the currentmember function could be used in calculations with no effect other than improving readability. For example, the contribution of sales of a product to its parent could be written as
(measures.sales, product.hierarchy.currentmember) /
Because a hierarchy’s currentmember is always used unless overwritten, this could be expressed as
In AS2005, specifying the currentmember is an overwrite and has consequences on other attributes and the two expressions might not give identical results in all situations. For example, consider this:
with member measures.x as (measures.sales)
The State attribute’s original coordinate is (Name.All, City.Seattle, State.Washington, Country.USA) and the calculated member does not overwrite any attribute in the customer dimension. The result is sales for the same tuple.
Do the same thing (well, almost the same) but now overwrite the state attribute with the currentmember function:
with member measures.x as (measures.sales, customer.[State].currentmember)
The calculation refers to the state attribute’s currentmember – but this is still considered an overwrite and the City attribute is moved from City.Seattle to City.All. If the calculation did not contain the currentmember function, the state attribute would not get overwritten and the City attribute coordinate would be unchanged.
Detailed Rules and Examples
Assuming the existence of an attribute relationship between two attributes A and B such that AèB or Aè … è B; that is, A and B are directly or indirectly related via a chain of attribute relationships. . The examples use City and State as attributes A and B, respectively with example data below (in the examples the shorthand “..” sometimes indicates the level directly below the all level.)
The table below illustrates the effect of an explicit overwrite of A or B has on the other attribute:
Take care when defining your attribute relationships – they impact performance, accuracy and query semantics:
- overwriting a member overwrites its related and relating attributes
Latest Author Articles
- Analysis Services Partition Size
- MDX Performance Hits - v2
- Handling Begin and End Times in SQL Server Analysis Services 2008
- IIF Function Query Hints in SQL Server Analysis Services 2008
- Richard Tkachuk discusses the SQL CAT team, working with Analysis Services