1:M relationship between Dims?

View previous topic View next topic Go down

1:M relationship between Dims?

Post  Vaire on Thu Jan 05, 2012 3:46 pm

This is probably an elementary scenario but it's one I have not encountered before and I would appreciated thoughts/input on ways to handle it. We are designing a data mart for a business process of an insurance company. One of the things that happens in this business process is making calls to or receiving calls from policyholders. Each of these calls can cover one or more topics. The business wants to be able to query along numerous attributes of the policy, call, and call topic. We envisioned a fact table where the grain is one row per topic per call per policy. Our confusion is that while a policy is the subject of many calls/call topics over time, a call/call topic is only for a single policy thereby violating the rule of fact tables expressing M:M relationships between dimensions. The attribute data for a call and call topic will never change as the information enters the data mart only after completion of all topics for a call (even though multiple physical phone calls may take place over time to accomplish this). So, it seems wrong to have call and call topic as separate dimensions even though they have attributes that apply to them. Almost none of the attributes are measurable (except premium amount) and so are not fact table attributes. Things such as customer disposition for a call topic (happy, angry, etc.) and reason for call and issue resolution and numerous dates, while useful, are certainly not additive. So, we have an (almost) factless fact table which leads back to call and call topic being dimensions but then we are back to the "but a row in the call topic dim would only be used once in our stated grain" issue. Maybe we have the grain wrong but cannot see another one that gives the business the ability to query as they need. Or maybe a dimension only in 1:M relationship is not indicative of us missing something. Tired of thinking in circles, I volunteered to see how others have approached this situation. Thanks for any thoughts.

Vaire

Posts : 2
Join date : 2012-01-04

View user profile

Back to top Go down

Re: 1:M relationship between Dims?

Post  ngalemmo on Thu Jan 05, 2012 4:50 pm

Our confusion is that while a policy is the subject of many calls/call topics over time, a call/call topic is only for a single policy thereby violating the rule of fact tables expressing M:M relationships between dimensions.

I don't follow. Where did M:M come from and why the confusion?

What is the grain of the fact?

What is the nature of the call and call topic dimensions? Is call topic free form notes about the call or a standardized list of topics?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: 1:M relationship between Dims?

Post  Vaire on Thu Jan 05, 2012 5:51 pm

Confusion is arising because I've not dealt with a dimension that appears to be a 'child' of another dimension. In our case, a policy will have many call made to its policyholder over time and each of those calls will cover one or more call topics. However, the call/call topic is for one policy hence a 1:M relationship between policy and call/call topic rather than a M:M. Since a call/call topic can relate to only one policy it seems odd to relate them through a fact table since I've always worked with fact tables relating dimensions M:M.

The grain of the fact table is one row per call topic per call per policy which is essentially the same level as the call topic dimension itself, another thing that feels odd.

The policy dim obviously has many attributes. The call dim has attributes which describe it, such as whether the call was initiated inbound or outbound, the person initiating the call, when the call was initiated, the primary reason for the call. Attributes are either date, person, or code/description from a selection list - no free form text. A call can cover one or more call topics and each topic has attributes which describe it such as customer attitude (satisfied, angry, etc.), resolution code, status, comments. Attributes are date, person, code/decription from a selection list, one that is free form text, and for one type of call topic one numeric amount.

Not having encountered a situation before where a dimension (call topic) is seemingly at the same level as the fact table grain buthas numerous non-numeric attributes and having a dimension row, the call topic, used only once in the fact table seems as if we've missed something or gone awry in our design.

Vaire

Posts : 2
Join date : 2012-01-04

View user profile

Back to top Go down

Re: 1:M relationship between Dims?

Post  ngalemmo on Thu Jan 05, 2012 11:54 pm

You have a problem with the attributes. Why does a call topic have person? Why isn't person a dimension of its own? Also put comments in a separate dimension. Topic is now reduced to attitude, status and resolution, which could be handled by a mini (junk) dimension.

Since a call/call topic can relate to only one policy it seems odd to relate them through a fact table

That may seem odd, but you are not relating policy with call topic. You are providing context to an event (the call). A star schema is a self-contained model. The fact represents an event and the dimensions provide context.

As far as relationships go, the relationship between fact and dimension is always many (fact) to one (dimension). When you have a many-to-many relationship, you need to implement a bridge to support it. It does not appear you have a M:M relationship given the grain of your fact.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: 1:M relationship between Dims?

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