CODEWITHSUNDEEP
ssasrelationshipbusiness-intelligenceolapdimension
minh-hieu.pham
minh-hieu.pham

Reputation: 1079

Attribute relationship from Hierarchy in SSAS 2012

I have a misunderstood problem when trying to work with SSAS in creating and configuring Dimension. The problem is: My dimension named Author have three attributes (AuthorKey, AuthorID, AuthorState), which AuthorKey is the primary key of my dimension.

With Visual studio 2013, I have created a user-hierarchy like: 

Hierarchy name: AuthorByState
 + AuthorState
 + AuthorKey
   - AuthorID
   - AuthorState

enter image description here

When I switched to Attribute Relationship tab, i have seen an auto-generated relationship like: 

AuthorKey(AuthorID) --> AuthorState

enter image description here

My questions is: I understand that SSAS automatically relates every attribute in a dimension to the dimension key, but why does the AuthorID attribute is "inside" the AuthorKey attribute instead of "outside" as normally like AuthorState? What it means the attribute relationship?

Thanks for any explanation and sorry for my bad English!

Upvotes: 1

Views: 1693

Answers (2)

Hila DG
Hila DG

Reputation: 738

The Problem

Attributes in the hierarchy are not being defined correctly.

Symptoms

When you define a hierarchy, attributes which are not related are being dragged in

Solution

If you dimension has one key (the "Natural Key" - the key for business use only) - then that should be defined as your key (It clearly says that "Author" is your key, and I don't know what that field stand for). If your dimension has two keys, a natural Key and surrogate key. The Natural key is the key with the business meaning (as every author has an id) and the surrogate key is the key that we allocate (an identity int or whatever). Put the Surrogate key in the dimension. Define it as a key, and choose the "visible attribute" as the Name/Natural key. Once you do that the Hierarchy wouldn't drag unnecessary attribute inside that section.

A comment: if Author Key is your substitute Key and Author Id is your natural key you can hide Author Key; make it display Author Id instead in Properties->NameColumn

Please note the use of "date key" in the following post by Microsoft: https://www.mssqltips.com/sqlservertip/3414/sql-server-analysis-services-attribute-relationships/ As without it the Hierarchy just wouldn't be set.

Upvotes: 0

GregGalloway
GregGalloway

Reputation: 11625

When you build a user-defined hierarchy (the thing you called AuthorsByState) then that causes the Attribute Relationship tab diagram to render differently. Basically any attribute that appears in a user-defined hierarchy has it's own bubble. If you were to build a second user-defined hierarchy with AuthorId in it, then AuthorId would have it's own bubble in the Attribute Relationships tab diagram.

Upvotes: 1

Related Questions