Hi everyone,

I am not sure how to design what I am trying to do so want to get your
ideas.

Basically I have a consumer fact table with different demographic and
geographic dimensions.

I have another fact table that stores consumer complaints. This fact
table can have many complaints for one consumer. Multiple complaints
from same consumer can come at different time periods. So assume an
example below.

Consumer 1 Complained 3 times in last week and 2 times in a week before
that. So if someone is analyzing complaints for last week then for that
particular consumer it should only show 1 complaint instead of 3.

Consumer fact has fields: Consumer Name, Demographic Fields
Complaint fact has fields: consumer name, complaint date

They have 1 to many relation between them.

I would appreciate any ideas...

Thanks,
/R

The best way to solve your specific problem would be a regular star
schema with a dimension (or simply a measure...) WEEK that you can use
to calculate the distinct count of weeks. If you have a Time Dimension,
you could also use it as a Fact Dimension and a Distinct Count measure
on the Week key (you should use a key that is year-month, if you only
use week number you would override same weeks on different years).

In a strict way, you don't need the many-to-many dimension
relationships. Anyway, I would suggest you to take a look to my paper
(http://www.sqlbi.eu/manytomany.aspx) for the Distinct Count model just
to have another perspective on how you can model your data.

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

rahulchudiwal (AT) yahoo (DOT) com wrote: