SQL Server – Analysis Services / BIDS – Dimension – Error – “Create hierarchies in non-parent child dimensions” & “Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies”

Introduction

When trying to build an SSAS project, I am running into this warning – Create “hierarchies in non-parent child dimensions“.

Error Description

The specific dimension referenced is the Date Dimension.

Here is the Dimension Structure:

DimensionStructure

And, here is the error message:

error

Error Identification

If we look at the middle pane, specifically the Hierarchies pane, we can see that we have no Hierarchies.

Resolution

Let us go create hierarchies.

Add Hierarchies

  • Select the Dimension (dimDates)
  • Double-click on your selection and the “Dimension Structure” tab is displayed
  • The following sub tabs are shown (Attributes, Hierarchies, Data Source View)
  • We will start from the most attribute; in this case Year and move it from the Attributes panel unto the Hierarchy panel
  • Next, move the Semester attribute from the Attributes panel unto the Hierarchies panel
  • Next, move the Quarter Number from the Attributes panel unto the Hierarchies panel

Added Hierarchies

Once we added the Hierarchy (Year \ Semester \ Quarter Number \ Month Name), here is where we ended up:

DimensionStructure-ShowingUserHierarchy

We should note the yellow exclamation icon that is shown in the Hierarchy panel.

Added Hierarchies – Build

We do a build, and we made progress and here is where we are:

Image:

PostHierarchiesDeclaration - Error

Texual:

Warning 1 Hierarchy [DimDates].[Hierarchy] : Attribute relationships do not exist 
between one or more levels of this hierarchy. This may result in decreased query 
performance. 0 0

Warning 2 Dimension [DimDates] : Avoid visible attribute hierarchies for attributes 
used as levels in user-defined hierarchies. 0 0

Review Attribute Relationship – Before Changes

Select the Dimension and Access the Attribute Relationship panel:

Here is the our screen shot:

AttributeRelation-before

We can see the attributes that are part of our User Hierarchies are shown outside of our Date Dimension.

Add Attribute Relationship

Let us define the appropriate Date Hierarchy.

  • From the solution explorer, access the Dates Dimension
  • Double-click on the Dimension
  • In the diagram, right-click on the Month Name attribute and then select New Attribute Relationship.
  • In the Create Attribute Relationship dialog box, the Source Attribute is Month Name. Set the Related Attribute to “Quarter Number”. In the Relationship type list, set the relationship type to Rigid.
  • Click OK.
  • In the diagram, right-click the “Quarter Number” attribute and then select New Attribute Relationship.
  • In the Create Attribute Relationship dialog box, the Source Attribute is Calendar Quarter. Set the Related Attribute to “Semester”. In the Relationship type list, set the relationship type to Rigid.
  • Click OK.
  • In the diagram, right-click the Calendar Semester attribute and then select New Attribute Relationship.
  • In the Create Attribute Relationship dialog box, the Source Attribute is Calendar Semester. Set the Related Attribute to Year. In the Relationship type list, set the relationship type to Rigid.
  • Click OK.

Review Attribute Relationship – Post Changes

Diagram

AttributeRelationDiagram-After

Diagram – Attribute List

AttributeRelationAttributes-After

Diagram – Error List

AttributeRelationErrorList-After

Review Attribute Relationship – Hide Attribute Hierarchies

So we now have a new error warning stating:


Warning 1 Dimension [DimDates] : Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies. 0 0

Let us go fix it, by hiding Attribute Hierarchies where corresponding User Hierarchies exist!

Review User Hierarchies

Here is our User Hierarchies:

UserHierarchiesReview

It shows Year -to- Semester – Quarter Number

Here is our Attribute Relationship:

AttributeRelationship-BeforeVisibilityChanges

Attribute Relationships & Corresponding User Hierarchy

Attribute Relationship User Hierarchy (Corresponding)
Date ID -> Day
Date ID -> Day Number of Year
Date ID -> Month Name
Month Name -> Quarter Number Yes
Quarter Number -> Semester Yes
Semester -> Year Yes

And, so it appears that we should fix hide Attribute Relationships for Month Name -> Quarter Number, Quarter Number -> Semester, and Semester -> Year.

To fix:

  • Access the Dimension
  • Click on the “Attribute Relationship” tab
  • Access the “Attribute Relationship” panel
  • For each of the Attribute Relationship noted, please do the following : Select Attribute, right click on the selection, glance to the properties panel and toggle the visibility property – Set visibility to false.

Visibility Change : Before

AttributeRelationshipVisibilityMarker

Visibility Change : After

Once Attribute Relation visibility is set to false:

AttributeRelationshipVisibilityMarker-After

Thanks goodness for Microsoft’s marksmanship; Attribute relationships that are set to false are color coded as blue.

Duplicate Hierarchies – Made invisible

Post hiding duplicate Hierarchies and rebuilding, the warnings remain as there are still some Attributes that flat-lined with our primary key.  Will not worry about them.

Dimension Attribute KeyColumns

We have one more thing to do, set key columns for our Attributes.

Because of our data, we need to go set KeyColumns for Month, Quarter, and Semester.

If we try to deploy without making this essential modification, we will get:

Warning 3 Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_dimDates', Column: 'Semester', Value: '1'. The attribute is 'Semester'. 0 0

Here are the relevant Attribute Names and corresponding key Columns:

Attribute Name Key Columns
Month Name YearMonthName
Quarter Number YearQuarterNumber
Semester YearSemester

To effect change, please do the following:

  • Access the Solution Explorer panel
  • Choose the Dimension, in our case Dimension – DimDate
  • Double-click on your selection
  • In the “Attributes” panel, access each affected Attribute
  • And, access the properties, specifically the KeysColumn
  • Choose the proper key columns based on your data

Dimension Attribute KeyColumns

We yet have one more thing to do, set Name columns for the Attributes that have composite Key Columns; composite key columns are columns that have two or more columns as their Key name.

If we build without making this change, we will get error:


Error 1 DimensionAttribute [DimDates].[Month Name] : The NameColumn should be defined because there are multiple KeyColumns. 0 0

Error 2 DimensionAttribute [DimDates].[Quarter Number] : The NameColumn should be 
defined because there are multiple KeyColumns. 0 0

Error 3 DimensionAttribute [DimDates].[Semester] : The NameColumn should be defined 
because there are multiple KeyColumns. 0 0

Here are the relevant Attribute Names and corresponding key Columns:

Here are the relevant Attribute Names and corresponding Name Columns:

Attribute Name Name Column
Month Name MonthName
Quarter Number QuarterNumber
Semester Semester

To effect change, please do the following:

  • Access the Solution Explorer panel
  • Choose the Dimension, in our case Dimension – DimDate
  • Double-click on your selection
  • In the “Attributes” panel, access each affected Attribute
  • And, access the properties, specifically the Name Column
  • Choose the proper key columns based on your data

Column-NameColumn-MonthName

Final Warnings


Warning	1	Dimension [DimDates] : Avoid visible attribute hierarchies for 
attributes used as levels in user-defined hierarchies.		0	0	

Warning	2	Dimension [DimFactUsage] : Create hierarchies in non-parent child dimensions.		0	0	

We are left with two warnings:

  • Warning 2 is due to the fact that we have not created Hierarchies for our second Dimension (DimFactUsage)
  • And, Warning 1 because we yet have a couple of attributes in the DimDate Dimension that are not part of Attribute Relationship nor User Hierarchies

Summary

For some dimensions such as Date, Hierarchy relations are well known and broadly defined.  You might have two sets of Hierarchy relationships; one for Calendar Years, and another for Fiscal Year.

The Fiscal Year obviously covers those organizations for which Fiscal Years do not line up firmly with the Calendar Year.

In the case of other Dimensions, you have to think quite a bit more to recognize Hierarchy relationships.

Nevertheless, designing with Hierarchies makes your system infinitely more usable as  it will help guide your users; especially when the relationships are natural and business based.

Hierarchies also help direct the Analysis Services Engine per indexes and summarized data.  If SSAS knows ahead about Hierarchies, it will much build those indexes and summarized data during the deploy process; and they will be there for you to use when you need them.

References

References – User-Defined Hierarchies

References – Tutorial

Leave a Reply

Please log in using one of these methods to post your comment:

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