How to model a FACT where its relationship to multiple dimensions has changed from M:1 to M:M

Go down

How to model a FACT where its relationship to multiple dimensions has changed from M:1 to M:M

Post  aw-dm-md on Thu Oct 03, 2013 11:14 am

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.


Posts : 1
Join date : 2013-10-03

View user profile

Back to top Go down

Re: How to model a FACT where its relationship to multiple dimensions has changed from M:1 to M:M

Post  ngalemmo on Thu Oct 03, 2013 1:28 pm

#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.

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

View user profile

Back to top Go down

Back to top

- Similar topics

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