SSAS Relationships and Granularity


The creation of measure groups at varying levels of granularity is used when two related measures differ by some dimensional level. A classic example of this occurs in budget comparisons to actual reporting. In this situation, the budget data is typically stored at a higher level of granularity. For example, it may exclude product information (or be set at high level product classes) and be targeted at monthly totals. Actual data is presumed to be recorded at a detail sale level (where { date x product x store x ref# } gives { revenue & quantity sold }). This post looks at how attribute relationships determine how data is aggregated in dimensions where measure groups are related to dimensions through non key attributes.

Situation

To illustrate the significance of relationships in dimension design and the effect they have on aggregation, the examples shown in this post use a very simple cube structure. There are two measure groups (one for actual and one for budget) and only one dimension (product). We assume that actual data is recorded at the product level and budgets are set at subcategory. Note that the related attribute for the Actual measure group is [Product] and the related attribute for the budget measure group is [Sub Category] (as below);

The product dimension has similar characteristics to that of adventure works but includes only three attributes (a product, sub category and category). The natural (business) hierarchy for product is [product] à [sub category] à [category]. That is, that a group of products will role-up to a [sub category] and several [sub categories] will role-up into a [category].

In this example, we consider only two subcategories which are [Mountain Bikes] and [Forks]. [Mountain Bikes] maps to the category [Bikes] and [Forks] maps to [Components]. This can be seen in the following hierarchy browse and script;

To illustrate how the use of relationships effect aggregations, we will assume that the [Mountain Bikes] sub category has a budget of 100 and the [Forks] sub category has a budget of 200. With these values, the budget for the [Bikes] category should be 100 and [Components] should be 200.

These examples uses MDX update statements to update the underlying fact data however, this is only for simplicity. The examples in this blog will work the same had the underlying fact been updated by other means.


Defined Relationships

When relationships are correctly defined between attributes according to the business definition (below), values at the [Sub Category] attribute are correctly aggregated to the parent attribute. For example, when the [mountain bikes] subcategory is updated, the value is aggregated to the parent (Bikes). This is demonstrated in the script below and screen shots below. When [Mountain Bikes] is updated and category [Bikes] is updated by the same amount. This can be seen in the following example;

Relationship :
Update Script : /* updating script */
update [Granularity Example] set

([Product].[Product By Category].[Mountain Bikes],[Measures].[Bgt Amount])=100,
([Product].[Product By Category].[Forks],[Measures].[Bgt Amount])=200
;

Query / Result select [Measures].[Bgt Amount] on 0,
{ [Product].[Product By Category].[Bikes]
,[Product].[Product By Category].[Components]
} on 1
from [Granularity Example];

Non Defined Relationships

When relationships are not defined between product attributes, both parent categories (Bikes and Components) contain the same value. The value shown is the total for all members and can be seen below.

Relationship :
Query / Result

Clearly, without the relationship between the parent and child, the values for the [sub category] level are not aggregated to [category] as expected.

The reason for this is that a correct relationship (as it was displayed in the first example) specifies how members are aggregated to higher levels in the hierarchy. In this case, how a [category] is made up of several [sub categories]. Without an ‘upstream’ relationship to the [sub category] attribute, the [category] attribute can only show total values regardless of the member data is displayed through the hierarchy. In a previous post on aggregation usage, we showed how lower level aggregations could be used when higher levels of the relationship were required. This is exactly the same (except we are not concerned with aggregation use). Note that I have used the words relationship and hierarchy interchangeably. In this situation, we assume that a hierarchy mimics the relationship structure (business hierarchy).

Although aggregations and relationships improve performance, the problem does not present itself when the fact is joined to the key of the dimension. This is because there is an explicit relationship between the dimension key and every other attribute (hierarchy).

Conclusion

The ability to include measure groups from varying levels of aggregations is an important feature of SSAS because it permits the ability to combine data that would otherwise be disparate. However, this flexibility comes with caution because measure groups joined to dimension non-key attributes aggregates data only to the extent that relationships are defined in the dimension structure. If this structure is poorly defined, not understood by the user or not managed through perspectives, data may be misinterpreted where hierarchies are provided without supporting relationships and non-key attributes are used.

Advertisements

One thought on “SSAS Relationships and Granularity

  1. Pretty nice post. I just stumbled upon your blog and wanted to
    say that I’ve really enjoyed surfing around your blog posts. After all I’ll be subscribing
    to your rss feed and I hope you write again soon!

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