Dimension Attributes

Question

Hello,

I have a dimension (providers) with a few attributes that includes the State, County and the City where the provider resides.

I need to have the capablity to pull a State and County and get all the providers in this location.

But when I browse the dimension I get a seperate row for each provider.

Do I need to create a seperate dimension for the location and have a key to the Provider dimension (I tried to avoid the snow flake design) or it can be done the way it is set up and how?

Thanks,

Itzhak

---

Itzhak

Answer 1

In the brower tab of the dimension you can choose the hierarchy you want to browse. If you choose a user hierachy, you will be able to drill down members to the lowest level.

Attributes that have the AttributeHierarchyEnabled property set to true, can also be choosen and will only list the attribute members.

Philip,

Answer 2

Thank you for your reply.

What I was trying to do is to create a user hierarchy of State-County-City but when I created it when I tried to process the dimension it gave me an error of duplicate key. The reason I have duplicate key is that I can have different providers within the same location so the location can repeat itself.

When I tried to add something that will make it unique, my measure (count) gave me one per location and repeated the location.

So I ended up removing the hierarchy I created and modified the attributes of the State, County and City.

Now when you select the count measure and then select the State attribute it will give the count in each state when dragging the county after the state again it will give the correct count by state and county and so on. 

Thanks,

Itzhak

---

Itzhak

Answer 3

 The reason I have duplicate key is that I can have different providers within the same location so the location can repeat 

Dimension members ( as a member at the most granulous level in the dimension and mostly associated with the dimension key) having the same attribute is rather the rule than the exception. You probably mean you have the same provider having different locations.

or a level member having different parents in a hierarchy. 

SSAS does not support multiple parents hierarchies and the workaround will produce different level members with the same name belongs to different parents. The Adventure Works has about 5 "Berlin" cities belonging to differents states, but there are effectively different cities with the same name.

Philip

Answer 4

Hi Itzhak,

Philip's explanation is correct. Your hierarchy should be like this: Country -> State -> City. So they can presever 1 to many relationships e.g. 1 Country can have many states etc.

Thanks, Khilit
http://www.bigator.com

Answer 5

My hierarchy was State -> County -> City but the problem that I had is that in this dimension of Providers what is unique is the provider ID and the Surrogate key but the different providers can have the same location (they reside in the same state, county and city).

Thanks,

Itzhak

---

Itzhak

Answer 6

Hi Itzhak,

In your case, I suggest you that refer to the following article about handling Ragged/Unbalanced hierarchies in SSAS. Please see:
http://iman77.blogspot.com/2009/09/handling-raggedunbalanced-hierarchies.html

Regards,
Bin Long

---

Bin Long

TechNet Community Support