1

I am pretty new to data warehousing, so I'm a little unclear on some aspects of design. My business sells memberships. People join to become a member, and of course resign to no longer be a member. We have join date and the resign date as dimensions. Would we have one fact table or two for memberships? I am thinking that 'members joining' would be a fact table, and 'members resigning' would be another fact table. Or do we have it all in one fact table encompassing all Membership joins and resigns?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user2125820
  • 11
  • 2
  • 1
    What are the reports you want to run? That is a very important question. Can the same member join and leave over and over again?. It might actually fit better into a slowly changing member dimension. When selecting metrics to go into facts, then if the granularity is the same, put them in the same fact. You only use a different fact if there are quite different dimensions or if it is at a different granularity (i.e. by month instead of day) – Nick.Mc Oct 22 '15 at 09:51

1 Answers1

1

Fact and Dimension tables in a data warehouse are more about foriegn key relationships. So you might have a fact table like:

 FactMemberStatus:
 MemberId      JoinDate         ResignDate

Then Dimension tables like:

 DimMember
 MemberId      MemberName       MemberPhone        MemberAddress    Etc.

 DimDate
 PKDate       WeekOfYear        MonthOfYear        FiscalMonthOfYear    Etc.

Then you could join on JoinDate->PKDate, or ResignDate->PKDate, you could also query on if a member was joined or resigned, if either joindate was null, or resigndate was null.

Without knowing much else, those would be my first thoughts.

russds
  • 845
  • 5
  • 25
  • 48