I have design a data model where I am using a bridge table in the RDBMS to capture the number of time a customer attended a company event. As you will see below, I capture the Event Type, Event, From Date and To Date. My fact table captures the charges/fees for every trade date for a particular customer. When I add the Event Type shared dimension in Analysis Service, I get duplicate records also.
The Customer and Event relationship looks something like this:
CUSTOMER --< COMMUNICATION EVENT >-- EVENT TYPE
When I add the dimension to the OLAP Cube, it incorporates the structures
automatically, which then I a get duplicate records.
Basically, I am interested in knowing how the customer charges and/or fees
(commission) increased or descreased since a customer attended the company's
Here are the following dimensions in the cube:
The EVENT_TYPE consist of various events held through out the year. It also
has a dummy record with a description "No Event
I have created a bridge table to track the various events that customer
Here are the following facts
Although the CHARGE_ALLOCAITON_FACT has a record for every type of charge,
period key is associated with the end of month key or the last business day
in the month. However, I can still extract or identify the actual trade date
to show the daily charges. This is need because the business user wants to
select a range date to analyze the generate revenue from charges/fees daily.
Initially, my thoughts on the design,I will need to create another fact
table that will incorporate the EVENT_TYPE dimension, which would look like
--FROM_DATE (Based on the trade date)
--TO_DATE (Base on the trade date)
However, I will probably run into problems when a customer attends more than
one event during the year or does not have any charges during that event but
afterward. I initially thought of just assigning the event key to the fact
records that fall under the From Date and To Date.
I realize that MS SQL Server does not like snowflak schemas.
Your feedback and insight is greatly appreciated.
quote:Originally posted by robprell
Please post a description of what your trying to store if you want some suggestions. For example employees to ??? or sales to ??? etc.
and sorry for my topo's above. In SQL you do a many to many relationship by going from table a to b with a many to one relationship then from table b to c with a one to many relationship thus the net between a and c is a many to many. But you never have a direct many to many relationship between tables.