Problem with multi-valued Dimension
Page 1 of 1
Problem with multi-valued Dimension
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.
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.
Gast- Guest
Re: Problem with multi-valued Dimension
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.
Re: Problem with multi-valued Dimension
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.
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.
Gast- Guest
Re: Problem with multi-valued Dimension
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.
Similar topics
» Multi-valued dimension with distinct attributes
» Multi valued dimension attributes, weighting factor and cognos
» Challenging Scenario with multi valued dimensions...
» Multi-valued attribute that has fact type data
» too many dimension problem
» Multi valued dimension attributes, weighting factor and cognos
» Challenging Scenario with multi valued dimensions...
» Multi-valued attribute that has fact type data
» too many dimension problem
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum