Dimension with multivalue attributes

View previous topic View next topic Go down

Dimension with multivalue attributes

Post  kmh030 on Tue Aug 03, 2010 3:16 pm

I can't seem to wrap my brain around this one, need some help. Data model is simplified for the example given here:

fact: Interview
dimension: applicant

The grain is obviously interviews, however the users want to be able to report interview counts on the following Applicant attributes:

1) Undergrad school(s)
2) Grad school(s)
3) Language(s)
4) Previous Employer(s)

The problem is many-to-many, each applicant can have many of the above attributes and vice versa. I've been reading about bridge tables, helper tables etc. but am not sure which situation whould be best here. I looked into seperate physical columns for each but that is not a viable solution (i.e. Major_1, Major_2, Lang_1, Lang_2 etc).

Can someone please point me in the right direction? thanks!

kmh030

Posts : 2
Join date : 2010-08-03

View user profile

Back to top Go down

Re: Dimension with multivalue attributes

Post  kmh030 on Thu Aug 05, 2010 8:00 am

ok I've tried everything and really need some help here. totally stuck. I build a POC using a helper table, however trying to get accurate fact table counts with many attributes in a helper table is nearly impossible. I also revisited the grain, and instead of 'interview' I built a fact table called Applicant_Interview. It's a factless fact that will include all of the combinations an applicant can have for a single candidacy, however I just realized that this will violate the project requirements: the users want to drill through the Excel SSAS pivot reports to view candidacy record detail. In this case the users would see multiple records per cadidacy and it would cause much confusion obviously.




kmh030

Posts : 2
Join date : 2010-08-03

View user profile

Back to top Go down

Re: Dimension with multivalue attributes

Post  LAndrews on Thu Aug 05, 2010 1:25 pm


Here's a good article on helper tables : http://intelligent-enterprise.informationweek.com/010810/412warehouse1_2.jhtml

What you need to remember is that helper tables are designed to manage the M:M relationship between two dimensions. So in your example, you would have helper table(s) for

Fact --> Applicant Dimension --> Applicant-Language Helper --> Language Dimension
Fact --> Applicant Dimension --> Applicant-Grad-School Helper --> Grad School Dimension
Fact --> Applicant Dimension --> Applicant-Undergrad-School Helper --> Undergrad School Dimension
Fact --> Applicant Dimension --> Applicant-Prev-Employer Helper --> Employer Dimension

Its one of the few areas where snowflaking is required.

Hope this helps

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Dimension with multivalue attributes

Post  ngalemmo on Thu Aug 05, 2010 1:46 pm

...and it is important to note the operative word in LA's response: "manage"

It's still a many-to-many relationship. The helper (aka bridge) table doesn't change that. But it does give you the ability to control how measures are evaluated, so long as the queries are properly structured.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Dimension with multivalue attributes

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum