Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Many to many relations in natural hierarchies

Posted by thomasivarssonmalmo on March 26, 2008

This blog entry is about a problem with attribute relations that can appear when you migrate cubes from AS2000 or have issues whith many to many relations in natural hierarchies. Natural hierarchies can only have one-to-many or one-to-one relations from a parent to the next lower level. The problem is that you can have many-to-many relations, a child level points to more than one parent on the next level, since this is not detected by primary key constraints and this problem occur above the primary key. If this was the case in AS2000 the engine will put its own unique keys for children that points to more than one parent. In SSAS2005 this unique key creation is not present and I will show an example of this here.
You will have to create the same cube as used in the previous blog post. This example will carry on from where we ended previously.
Run the following TSQL script in management studio on the previously created database and tables. We vill create an additional record in the fact table(icecream sales) and create a many-to-many relation, in the product dimension,  between the "Other" product group that will point, on it’s own own "Fresh food" parent member, and be an alternate pointer to the "Stock food" parent in the product segment top level(that we had before).  Run a select * on the product dimension and  you will see that Other will point to two parents after you have made the change below.
Insert Into MyFact(MyDate, MySales,ProductName) Values(‘2007-11-22′,250,’IceCream’)
Insert Into DimProduct (ProductName, ProductGroup, ProductSegment) Values(‘IceCream’,’Other’,’Fresh food’)
Open the cube project in BIDS(BI-Dev Studio) and make the following changes in the two dimensions.
Create a natural hierarchy in the product dimension like this.
Skapar naturliga hierarkin
Do the same thing in the time dimension like this.
Tidsdimensionen efter ändringar
 Process the two dimensions separately first and check their natural hierarchies. After this is finished you can process the cube and execute the same MDX select that we have used in the previous blog post.
MDX Result
You will see that the Other product group have moved entirely to the fresh food segment and the stock food segment values for Other have disappeared. However in the source tables you will have the original pattern that we have designed in the TSQL scripts above. The cube and the source tables will differ and this is not a bug. Natural hierarchies only allow one-to-many and one-to-one relations in natural hierarchies.
I have seen the same behaviour with parent-child hierarchies that also only allows one-to-many relations from the parent to the child.
If you have a many-to-many relation in a natural hierarchy with attribute relations SSAS2005 will pick one of these parents and add all child records to it. In AS2000 the engine used it’s own internal unique keys so this issue would never show up. When you migrate from AS2000 you will have to be careful about this but since primary keys will not enough you should review your dimensions even in an existing SSAS2005 solution. The totals will be correct with many-to-many relations but the distribution of values will be random and not correct.
The BIDS-Helper have a dimension health check tool that will detect this issue in both natural hierarchies and parent-child hierarchies.
(If you look carefully you will also see some additional records for meatballs that were not present in the previous blog post but you can disregard them.)

One Response to “Many to many relations in natural hierarchies”

  1. Vidas said

    Pingback – link to this post was added in SSAS  Articles / Design section of our website:

Leave a Reply

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

You are commenting using your 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: