Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Dimension with multivalue attributes

3 posters

Go down

Dimension with multivalue attributes Empty Dimension with multivalue attributes

Post  kmh030 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

Back to top Go down

Dimension with multivalue attributes Empty Re: Dimension with multivalue attributes

Post  kmh030 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

Back to top Go down

Dimension with multivalue attributes Empty Re: Dimension with multivalue attributes

Post  LAndrews 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

Back to top Go down

Dimension with multivalue attributes Empty Re: Dimension with multivalue attributes

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Dimension with multivalue attributes Empty Re: Dimension with multivalue attributes

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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