Is there anyway around unique dimension key columns and individual Hierarchies

Question

Is there anyway around unique dimension key columns and individual Hierarchies

ttdeveloper

SSCrazy

Points: 2370
More actions
March 13, 2015 at 8:33 am

#145665

Good Day (Why do i feel like i'm asking a question here everyday)
Consider the attribute relationship for my dimension dimdataorg (org_rel) and the hierarchies I am trying to build (hierarchies).
I have the following key columns for my attributes
AD_NM attribute ----> GVP_nm + AD_nm
SSSM_NM ---------> GVP_NM + AD_nm + SSSM_nm
GVP_nm ----------> GVP_NM
AVP_NM ----------> AVP_nm
My question here is - with the individual hiearchies I am trying to build that have multiple columns as keys (AD_NM and SSSM_NM), viewing these in excel is causing multiple (duplicate) names to appear in the same hierarchy naturally because these names can belong to more than one parent.
Eg AD - Joe Shmo reporting Billy Blanks and Billy Crystal. If i pull up the AD hierachy only Joe Shmo appears twice.
Is there any way around this? Naturally if I use only the one column foras my key column, SSAS complains during processing that it found dups.
My only other option right now is to drop those individual hierarchies however there are instances where business doesnt care about the org drilling and just want to see how all ADs are doing without the unsightly dups

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Not sure I follow what you *want* to see....

If Joe reports to Billy *and* Bob (somewhat difficult to serve two masters, but whatever), then why would you *not* want to see Joe under both Billy and Bob?

If the problem is Joe Schmoe (as a full name) appears > 1 time, you can either live with it (because again, it is a true reflection of your business) or modify the name (maybe using the mgr level name) to produce uniqueness. This comes with it's own issues though, as Joe::Bob and Joe:Billy are treated as two separate items, so trying to see the total whatever (sales? tickets? whatever) for Joe means you need to add them together.

Steve.

ttdeveloper SSCrazy Points: 2370 More actions · Answer 2

Hey Steve

Thanks for getting back. What I am trying to see is this. I pull up the AD hierarchy where Joe is and I see only one entry of Joe not two. I currently see his name twice due to the way the key columns are built for this attribute ie GVP + AD.

Unfortunately - business rules in my company dictate that and an AD can report to more than one GVP. The excel drill works fine when i pull up the Data_org_hcy which shows the GVP - AD structure. At least that way one can decipher the two entries. However pulling just the individual column, shows this AD (or whoever) twice if they report to multiple GVPs.

Hope my explanation was clear.

Martin Schoombee SSCoach Points: 19115 More actions · Answer 3

ttdeveloper (3/13/2015)
Hey Steve
Thanks for getting back. What I am trying to see is this. I pull up the AD hierarchy where Joe is and I see only one entry of Joe not two. I currently see his name twice due to the way the key columns are built for this attribute ie GVP + AD.
Unfortunately - business rules in my company dictate that and an AD can report to more than one GVP. The excel drill works fine when i pull up the Data_org_hcy which shows the GVP - AD structure. At least that way one can decipher the two entries. However pulling just the individual column, shows this AD (or whoever) twice if they report to multiple GVPs.
Hope my explanation was clear.

There is a way around it:

Leave your user hierarchies as they are. These define a drill-down path only, and really have nothing to do with the attribute's key or relationships.
Change the keys of your AD_NM and SSSM_NM attributes to their respective fields only, i.e. AD_NM's key will be AD_NM only and not the composite key as you have it now.
Change the attribute relationships so that AD_NM and SSSM_NM is directly associated with the lowest level (Data Org Id)

You may get some warnings in the designer when doing this, but it does work and will get you around this issue.

Hope this helps.

ttdeveloper SSCrazy Points: 2370 More actions · Answer 4

Martin

Spot on - redesigned the composite keys and the relationship (attached). Hierarchies are now fixed. This is awesome.

Thanks for your help as always.

So - I guess just for my curiousity, the attribute relationships do not necessarily have to line up with business logic. Just getting it to work is the trade off?

Martin Schoombee SSCoach Points: 19115 More actions · Answer 5

ttdeveloper (3/17/2015)
Martin
Spot on - redesigned the composite keys and the relationship (attached). Hierarchies are now fixed. This is awesome.
Thanks for your help as always.
So - I guess just for my curiousity, the attribute relationships do not necessarily have to line up with business logic. Just getting it to work is the trade off?

They do, but the business logic as defined by the cube and the way your end users want to browse the data. In an ideal world, users would use "properly" designed hierarchies only as a means to view the cube data...but we both know that it doesn't always work out like that.

The trade-off here is that your hierarchies are not optimized, and performance will suffer a little. In the right situation though, this is a feasible trade-off in my opinion.

ttdeveloper SSCrazy Points: 2370 More actions · Answer 6

Thanks Martin - for a DW with 5 Million rows, SSAS seems to be handling it quite well. So far, no performance issues noticed (argh - i think i just jinxed it).

Thanks for all your help. You're right..users' requests trumps the more sensible cube logic - in this case at least.