Composite Keys on Dimensions

I have a question about Composite Keys on Dimensions. I run the Dimension Health Checker that comes with BIDS Helper and it tells me I have many to many relationships happening, so I change key columns to multiple columns to get rid of the many to many, which is good.

Now, when I browse my cube, I pull in a Dimension property , say the lowest level of a heirarchy, and I see duplicates where before it was rolled up.

Is there an easy way to get rid of the many to many with composite keys yet still have the data rolled up to my name column?
i am guessing CustomRollupColumn is the answer?
|||

Instead of using the key as the keycolum you could use the name of the attribute e.g.

key_size name_size

1 big

2 big

3 small

4 medium

if your keycolumn is key_size you will get four values if the keycolumn is name_size you will only get three where keys 1 and 2 are pointed to by the name_size big.

Hope that helps

Matt

|||that is how it was set up, but with the attribute relationships and heirarchies, the dimension health checker comes back with many to many relationships, so i have to make the key composite to get uniques across..

the dimensions are slowly changing collapsed snoflake dimensions..so there are possiblities for dupes
|||

The composite keys only really need to exist in the DSV to join the tables together, do they not? Each individual attribute may not need composite keys. Think I must have missed something here, any chance of a more detailed example, perhaps two of the tables.

Cheers

Matt

|||i have one dimension.. called Source

there is a heirarchy Dept->Account->Campaign

since the dimension is slowly changing there are duplicates when campaigns change

for my Campaign i have the keycolumn as Campaign and Account, name column as Campaign as the BIDS dimension health checker said there were many to many relationships occuring. When i changed the keycolumn to the two columns, then reran the health checker, the warning/error went away...

before i had the composite key column, i could drag campaign and say revenue onto a pivot table and it would rollup to campaigns, now when i do that, it looks like there are duplicates because really it is rolling up to the keycolumn (Campaign and Account).

What i want to do is still roll up to the campaign name even though I have the key column as campaign+account
|||

Hi,

Ok i think i understand, it should look ok in the hierarchy? yes. But if you browse the campaign attribute, it looks like there are duplicates, this is as expected. Two solutions, remove the composite key and do not make an attribute relationship between the attributes - thus giving you a little yellow triangle warning in the hierarchy.

OR, rename campaign to campaign2, create another attribute Campaign and don't use a composite key on that one, just the key campaign. If you want to browse the campaign list, just use campaign attribute but if you want to use the hierarchy use campaign2. In effect creating four attributes:

-Campaign

-Campaign2

-Account

-Dept

A hierarchy

-Dept

--Account

Campaign2 (change its name to Campaign to reduce confusing the users)

And perhaps in the perspectives hide the individual attribute campaign2 so no one ever see it. Bit of a work around, but removes the duplicates.

Not to sure if there is another way of doing it. Maybe someone else has a better idea.

Hope that helps

Matt

|||yes , for now i just removed the composite key and left the little warning in the heirarchies, but i would like to fix that as i want to design my aggregations and get best performance

your workaround might just work as well, something I can probably try...I dont know if there is another solution or something else to try, but you would think there would be a way to roll it up on a column instead of the key, i dunno..

thanks for all your help on this