Attribute relationships and cube space in SSAS


Defining attributes on your dimension will improve processing and querying performance but might also affect the results of your queries, sometimes leading to unexpected results.

This has already been discussed by Jeffrey Wang and other bloggers but I figured a new treatment of this topic might be helpful (at least to me).

Without attribute relationships.

Let us take the Date dimension as an example. It has [Year],[Month], and [Date] attributes and contains data for the years 2011-2013. No attribute relationship has been defined yet.

The cube has a measure called [# Days] which represents the number of days for a given period.

In the following, I will not explicitly mention the dimension when writing tuples. This is for the sake of readability. This is of course not recommended in production.

The following table shows what happens, when  you overwrite the context in derived measures.

 
Year Month [# Days] ( [# Days], [Month].[All]) ( [# Days], [Year].[All]) ( [# Days], [Month].&[2013/01]) ( [# Days], [Year].&[2013])
2013 2013/01 31 365 31 31 31
2013 All 365 365 1096 31 365
All All 1096 1096 1096 31 365
All 2013/01 31 1096 31 31 31

The first two columns describe what members are being queried on rows, the other columns show the result of the corresponding measure definition.

The rule in the case where no relationship has been defined is quite simple:  overwriting an attribute has no impact on unrelated attributes.

Attribute relationships modify cube space

Now, suppose we define a relationship [Month]->[Year] in SSAS. [Month] is the source attribute, and [Year] the related attribute.

This relationship tells SSAS there is a n:1 relationship between [Month] and [Year]. In other words, one month can only belong to one year.

Setting [Month] to a specific member, say [2013/01] in your query, will effectively set [Year] to the matching member (in this case, [2013]).

The table shows the effective context that results from what you specified in your query.

Query Effective
Year Month Year Month
All All All All
All 2013/01 2013 2013/01
2013 All 2013 All
2013 2013/01 2013 2013/01
2012 All 2012 All
2012 2012/02 2012 2012/02

When you specify the tuple ( [Year].[All], [Month].&[2013/01] )  in your query, SSAS will actually evaluate ( [Year].[2013], [Month].&[2013/01] )  even though you explicitly requested [Date].[Year].[All].

This might not seem worth mentioning, but it is. It becomes quite important, when you add further attributes and relationships to the [Month] attribute.

Partial tuples in measures.

Overwriting context in a measure would be simple, if you only ever provided complete tuples. However, providing complete tuples would make measures hard to maintain and read. When you specify a partial tuple in a measure, you only specify a few attributes. SSAS implicitly provides members for the attributes you did not include in your tuple.

Overwriting source attributes.

When  you set an attribute to a specific member (e.g. [2013/01]) , its related attributes are implicitly set to their corresponding member (e.g. [2013]).

The rule when setting an attribute to its [All]member,  is less straightforward, and can be expressed this way:

  1. if the current member is [All], current members of related attributes will be kept
  2. otherwise related attributes will be set to [All]

In the table below, I stressed the case where case #1 applies.

Effective ( [Month].[All]) ( [Month].[2013/01])
Year Month Year Month Year Month
All All All All 2013 2013/01
2013 All 2013 All 2013 2013/01
2013 2013/01 All All 2013 2013/01
2012 All 2012 All 2013 2013/01
2012 2012/02 All All 2013 2013/01

Overwriting related attributes.

Setting a related attribute (e.g.  [Year]) in a measure, while omitting the source attribute, will modify the context by implicitly setting its source attribute  (e.g.  [Month]) to its[All] members.

This simple rule can be visualized in the table below.

Effective ( [Year].[All] ) ( [Year].[2013] )
Year Month Year Month Year Month
All All All All 2013 All
2013 All All All 2013 All
2013 2013/01 All All 2013 All
2012 All All All 2013 All
2012 2012/02 All All 2013 All

Context propagation.

Although it might look so at first sight, writing ( [Year].[All] ) in a measure is not the same as writing ( [Year].[All], [Month].[All] ).

Suppose we add new attributes to our date dimension ([Month In Year] and [Day In Month]), and add the appropriate relationships.

Our attribute relationships now look like this:

  • [Date] –> [Day In Month]
  • [Date] –> [Month] –> [Year]
  • [Date] –> [Month] –> [Month In Year]

Explicitly overwriting an attribute in a measure implicitly overwrite its source and related attributes. Implicitly overwriting an attribute, however, only implicitly overwrites its source attributes.

So, if we write ( [Year].[All] ) in a measure, this will not overwrite [Month In Year], or [Day In Month].

The table below summarizes what we obtain for different tuples:

Tuple Year Month Month In Year Date Day In Month
( [Year].[All] ) All All Current member All Current member
( [Year].[All],  [Month].[All] ) All All All All Current member
( [Year].[All], [Month].[All], [Date].[All] ) All All All All All

Also, I mentioned earlier relationship modify cube space.  Suppose you have the following query.

WITH MEMBER [Measures].[My Measure] AS
    (
        [Measures].[# Days], 
            [Date].[Year].[2012]
    )
SELECT  
  [My Measure] ON COLUMNS
 , ([Date].[Month].[2013/01]) ON ROWS 
FROM [My Cube]

The result will be 31.

The reason behind this, is that writing ([Date].[Month].[2013/01]) on rows, is the same as writing:

( [Year].[2013], [Month].[2013/01], [Month In Year].[1], [Date].[All], [Day In Month].[All]  )

Since overwriting [Year] will not overwrite [Month In Year], the measure [# Days] will be calculated for:

( [Year].[2012], [Month].[All],     [Month In Year].[1], [Date].[All], [Day In Month].[All]  )

Conclusion

When overwriting attributes in a measure, you had better not rely too much on implicit members. Rather use the ROOT function wherever appropriate. Check your relationship tree. Check your measures against every sibling attribute in the relationship tree (or other descendants of ascendant nodes).

If you want to override any filter on the [Date] level, including any filter through [Day In Week] or other related attributes then include the [Date].[Date].[All] member in your measure.

If you want to override the filter on the [Month] attribute, as any filter on the [Date] attribute, but keep current filters on [Year] and [Month In Year], then use:

( [Year].CURRENTMEMBER, [Date].[All] )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: