This article describes a bizar behavior of root elements in Parent Child dimensions. A Parent Child dimension is a dimension based on a self-referencing table, where each record (member) points to its parent. It doesn’t require you to be stuck to fixed levels, if your business is not organised this way (e.g. organisational reporting structure), but builds all the levels behind the scenes.
Parent Child dimension
Based on AdventureWorkDW, this is a typical table layout for a parent child dimension. It has a (Primary) Key column OrganizationKey, and a Parent column ParentOrganizationKey to specify the parent of the member.
The top member (also called root) has no Parent, hence its value is NULL.
Note: make sure to set the MembersWithData to NonLeafDataHidden, if you don’t want to repeat the parent in it’s child members.
Root elements
A quick detour on what makes a member a root element. On the attribute you specify with Usage “Parent”, the property “RootMemberIf” lets you specify the way you want to define if a member is root. There are 4 possibilities
Parent Is Blank
If you specify ParentIsBlank, then the only members that will be roots will have their parent attribute = NULL.
When no Parents are NULL, you either
- supplied an non-existing parent and processing will fail with:
Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: ‘dbo_DimOrganization’, Column: ‘ParentOrganizationKey’, Value: ‘100’. The attribute is ‘Parent Organization’. - supplied an existing parent and processing will fail with:
Errors in the OLAP storage engine: A loop involving the member with the key 1, was detected in the parent-child relationship between the ‘Organization’ attribute and the ‘Parent Organization’ attribute.
So, you need to provide at least one member with Parent reference = NULL.
Parent Is Self
Sometimes you don’t want to allow NULLs in a column on database level. Leaving a column Nullable, might unintentionally create NULL for members you didn’t mean to be root. In this case you can specify ParentIsSelf, to indicate that you want to define a root when the value of Parent is the same as the member itself.
When you have no records referring to themselves, you either:
- supplied a NULL as parent, processing will fail with error:
Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: ‘dbo_DimOrganization’, Column: ‘ParentOrganizationKey’, Value: ‘0’. The attribute is ‘Parent Organization’.
Here you can see already a glimpse of the unexpected behavior which is the goal of this article. The NULL seems to be internally converted to a 0, as in the error message becomes clear. - supplied a member that exists lower in the hierarchy, processing will fail with error:
Errors in the OLAP storage engine: A loop involving the member with the key 1, was detected in the parent-child relationship between the ‘Organization’ attribute and the ‘Parent Organization’ attribute. - supplied a non-existing member, processing will fail with error:
Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: ‘dbo_DimOrganization’, Column: ‘ParentOrganizationKey’, Value: ‘100’. The attribute is ‘Parent Organization’.
Parent Is Missing
Less interesting from a data integrity point of view is to choose that a member is the root, when the parent it refers to doesn’t exist. It’s not NULL, but simply is not in the ParentChild table.
When you have no missing values for the Parent attribute, you either:
- specified NULL as parent. NULL is converted internally to 0 and as such is a missing value. This touches again on the unexpected behavior which is the initial goal of this article.
No error in processing occurs and you created a root element with NULL as parent. - specified an existing value as parent, which will result in a processing error:
Errors in the OLAP storage engine: A loop involving the member with the key 1, was detected in the parent-child relationship between the ‘Organization’ attribute and the ‘Parent Organization’ attribute.
ParentIsBlankSelfOrMissing
The last option is to allow any of the 3 previous definitions to create a root element. This is the default setting.
Unexpected treatment of 0
Let’s assume we use the default ParentIsBlankSelfOrMissing for the RootMemberIf property.
You can create additional top-level members, by adding records with ParentOrganizationKey = NULL.
E.g. let’s add a record with OrganizationKey 15 and ParentOrganizationKey = NULL.
No surprises there, this created a new member directly underneath All, with no children.
We can add children, by creating records with ParentOrganizationKey = 15 (the Key of Top Level 1).
Again, no surprises, that’s the way the Parent-Child dimension should work.
Let’s try to add a Member with 0 as Key and NULL as Parent. Again, it becomes part of our dimension.
When trying to add members to this new root, it seems that all new members become roots themselves.
This is because the parent = 0. Any other value would work as we saw above, but linked to a parent that has value 0 confuses Analysis Services and treats it link if the value 0 would be a NULL.
A workaround for this is to never use a record with Key 0 in the Parent Child table and never reference a Parent with value 0.
I hope you were able to find this article quicker then spending many hours on figuring out what part of this table was setup incorrectly.
Read More
If you’re interesting in BI related articles, make sure to read some of the following: