Dimension with multivalue attributes
3 posters
Page 1 of 1
Dimension with multivalue attributes
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!
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
Re: Dimension with multivalue attributes
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
Re: Dimension with multivalue attributes
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
Re: Dimension with multivalue attributes
...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.
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.
Similar topics
» Multivalue attributes
» dimension table design question for around 100 attributes and higher level calculated attributes
» Dimension Attributes and Fact attributes storing same data in multiple data marts??
» Status attributes on main dimension or as separate dimension
» How to Handle Data that serves as both a dimension and attributes of another dimension
» dimension table design question for around 100 attributes and higher level calculated attributes
» Dimension Attributes and Fact attributes storing same data in multiple data marts??
» Status attributes on main dimension or as separate dimension
» How to Handle Data that serves as both a dimension and attributes of another dimension
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|