Problem with multi-valued Dimension

View previous topic View next topic Go down

Problem with multi-valued Dimension

Post  FallenCipher on Sat Mar 12, 2016 7:30 pm

I'm currently designing a star schema for a business process called "Activities".
Employees of my company have to keep track of their work for customers by writing activity reports.
First they have to select the specific customer and one of the projects assigned to the customer.
Then they have to enter the amount of time (in minutes), the detailed activity text (what he was doing), activity type etc.
So one activity report (one row in the activity table of the OLTP system) is bound to a specific project (a project is assigned to only one customer).
The tricky part is, that the employee can select up to three contacts (persons) that should be assigned to the activity for reference purposes.
Contacts are assigned to a specific customer but not to a specific project.

Currently i have the following dimensions and facts:

DimCustomerProject
-- CustomerProjectSurrogateKey
-- CustomerNaturalKey
-- CustomerName
-- CustomerProjectNaturalKey
-- CustomerProjectName
-- ...

DimCustomerContact
-- CustomerContactSurrogateKey
-- CustomerNaturalKey
-- CustomerName
-- CustomerContactNaturalKey
-- CustomerContactName
-- ...

FactActivity
-- ActivitySurrogateKey
-- CustomerProjectSurrogateKey
-- CustomerContactSurrogateKey
-- ActivityDuration
-- ActivityText
-- ...

What would be the best way to model the Customer, Project and Contact dimension (can i consolidate all of them?) and how should i connect them to the fact table to prevent multiple fact rows if the activity has more than one contact assigned to it?

Please ask if you need more info.

FallenCipher

Posts : 2
Join date : 2016-03-12

View user profile

Back to top Go down

Re: Problem with multi-valued Dimension

Post  ngalemmo on Sat Mar 12, 2016 10:18 pm

You need a bridge table to store the additional references.  The bridge table would contain the project key (or maybe activity?) and the contact key.  You can have as many rows for a given project as you like.  You can store other information in the bridge if needed to assist allocation of measures.  Join to the fact on project key whenever you need to include these additional references.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Problem with multi-valued Dimension

Post  FallenCipher on Mon Mar 14, 2016 9:45 am

Thanks for the answer.
The bridge table seems to be the best choice in my case. Downside is that i can't use a foreign key relationship for this table because the activity key exists multiple times in the bridge table.

FallenCipher

Posts : 2
Join date : 2016-03-12

View user profile

Back to top Go down

Re: Problem with multi-valued Dimension

Post  ngalemmo on Mon Mar 14, 2016 3:35 pm

Why? The bridge contains one row with two keys. Each key has a M:1 relationship to whatever it references. This is normal for any foreign key.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Problem with multi-valued Dimension

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