How to model a FACT where its relationship to multiple dimensions has changed from M:1 to M:M
2 posters
Page 1 of 1
How to model a FACT where its relationship to multiple dimensions has changed from M:1 to M:M
We have an existing model for a call center application, with a FACT recording the individual calls made to the call target.
The current fact has several foreign keys to dimensions, 2 of which are a) the reason the person called refused to answer, and b) the method we can use to follow up
The requirement has now changed, and these 2 dimensions both can now have multiple values.
As an example: A single call target may be called on multiple occasions over a period of time, on any call, the recipient may respond in any number of ways, but let's say in one call instance, the responses are 1. I am not feeling well, 2. I am going about to start my lunch and 3. I am going out soon. In addition, the recipient may also give us reason to call again by saying: 1. I am free after 10pm, and 2. if you send me the details by mail I will look at it
Assume the reasons for not answering now, are all defined possible values in the non response dimension, and the possible future actions are already defined in another dimension, and also assume there can be 30 or more of each of these dimensions for any single call.
The inbound data source is structured with a single record for each contact, with 2 delimited strings for the non-response reasons, and for the future actions
What this means is we have a many to many relationship between the Fact and 2 dimensions.
In a normal relational model, this would be resolved using 2 intersection tables, but how can we do this in a Data Warehouse Model?
To complicate the requirement, there are attributes of the contact which need to be measured against the quantity and types of both the non-response and the future actions to determine for example how the duration of a call, and time of a call correlate to the number and types of combinations or the non-response and future actions.
Solutions we see are:
1. Use complex data elements on the fact to store lists of each of the dependent item foreign keys
2. Define Child facts where they have the foreign key to the dimension and also a foreign key to the fact (yes we know this violates rules)
3. Define 3 different facts one for contacts, one for contact non-response and another for contact future actions
The first option is probably the least flexible, but the most efficient when it comes to the end reporting needs, and closely matches to the inbound data source structure
The second seems to violate DW modeling rules, is not so efficient as the first when it comes to reporting and maintaining the data.
The last option probably satisfies the DM/DW rules, but is the least efficient when we need to repeatedly report across these 3 facts and also when maintaining the data.
Does anyone have any thoughts about these options, or other options we are have not considered.
aw-dm-md- Posts : 1
Join date : 2013-10-03
Re: How to model a FACT where its relationship to multiple dimensions has changed from M:1 to M:M
#2 is called a bridge table and is the standard mechanism for resolving M:M dimension relationships. There are different ways to implement a bridge: you can either assign a unique key on the fact and have a fact key/dimension key relationship; or you can create groups of unique combinations of reasons and store a group key on the fact and the group key/dimension key relationship in the bridge.
If the reasons are not free form text, but rather an enumerated list of standard reasons, using the group method will result is a much smaller bridge table. Even if they are text, if they are being entered by customer service personnel, they often use the same wording, so grouping will also reduce the overall size of the bridge.
If the reasons are not free form text, but rather an enumerated list of standard reasons, using the group method will result is a much smaller bridge table. Even if they are text, if they are being entered by customer service personnel, they often use the same wording, so grouping will also reduce the overall size of the bridge.
Similar topics
» Handling many-many relationship between fact and dimensions.
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» How to Model Fact table having 1:1 relationship with key Dimension attributes
» Do I need multiple fact tables or dimensions
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» How to Model Fact table having 1:1 relationship with key Dimension attributes
» Do I need multiple fact tables or dimensions
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum