Is this a Correct Periodic Snapshot Fact Table?

[ohmycamote]

Summary
We have a pool of customers.
From that pool, customers could be part of a membership club wherein each membership term is 1 year and renewable every year.

Each year has four quarters and members only get inducted to the club at the start of each quarter so their "Join Date" is basically 1/1/YYYY, 4/1/YYYY, 7/1/YYYY and 10/1/YYYY.

Objective
To be able to report how many total members were there during 2010 Q1, 2010 Q2, and so on.

DimCustomers (SCD)

SK	CustID	Name	CustStatus	StartDT	EndDT	Status
21	123	John	A	1/1/2010	12/31/2010	Expired
22	333	Pablo	A	1/1/2010		Current
23	888	Ella	A	1/1/2010		Current
-	-	-	-	-	-	-
24	123	John	I	1/1/2011		Current

Possible Solution?
I am exploring the use of a Periodic Snapshot Fact table.

DimDate

DateKey	Year	Quarter	Month	Day
51	2010	1	1	1
52	2010	1	1	2
-	-	-	-	-
145	2010	2	4	1
146	2010	2	4	2
-	-	-	-	-
235	2010	3	7	1
236	2010	3	7	2
-	-	-	-	-
325	2010	4	10	1
326	2010	4	10	2
-	-	-	-	-
415	2011	1	1	1
416	2011	1	1	2

FactMembers
[tr][tr][tr][tr]

SK	CustKey	JoinedDTKey	ReportingDTKey
1	21	51	51
2	22	51	51
3	23	51	51
-	-	-	-
4	21	51	145
5	22	51	145
6	23	51	145
-	-	-	-
7	21	51	235
8	22	51	235
9	23	51	235
-	-	-	-
10	21	51	325
11	22	51	325
12	23	51	325
-	-	-	-
13	22	51	415
14	23	51	415

My Questions
1.) Is this how a Periodic Snapshot Fact table is done? Have a FK to the actual report date (ReportDTKey)? Since we are doing Quarter reporting, I linked ReportDTKey to the First day of each quarter (e.g., 415 being 1/1/2011 or 2011Q1)

2.) DimCustomers is an SCD: CustStatus field.
Is my approach on FactMembers correct on how it shows that on the Reporting Period 2011Q1, John is no longer a member and there are only two members in that reporting quarter?

Thank you for any insights you can share and I appreciate all your time.

[Dave Jermy]

Given your stated objective, I would say not. There doesn't appear to be any need for the grain of the periodic snapshot to include the customer.

I would model this with two fact tables; the first would be either a transactional fact table which holds a row for whenever a customer becomes a member, renews a membership, cancels a membership etc. or an accumulating snapshot, with a row per customer membership and columns for all the dates. I'd probably go with the transactional fact table.

The second table would then be the periodic snapshot and it would contain MonthKey, ClubKey (if you've got multiple clubs), NewMembershipCount, RenewedMembershipCount, LapsedMembershipCount, TotalMembershipCount. The snapshot would then look like this (using your data):

MonthKey	ClubKey	NewMembershipCount	RenewedMembershipCount	LapsedMembershipCount	TotalMembershipCount
51	1	3	0	0	3
145	1	0	0	0	3
235	1	0	0	0	3
325	1	0	0	0	3
415	1	0	2	1	2

[VHF]

Objective
To be able to report how many total members were there during 2010 Q1, 2010 Q2, and so on.

This can be done directly off your dimension without any fact table.

DimCustomers (SCD)

SK	CustID	Name	CustStatus	StartDT	EndDT	Status
21	123	John	A	1/1/2010	12/31/2010	Expired
22	333	Pablo	A	1/1/2010	-	Current
23	888	Ella	A	1/1/2010	-	Current
-	-	-	-	-	-	-
24	123	John	I	1/1/2011	-	Current

For example, to query the number of members that were current as of 2010 Q4:

SELECT COUNT(*)
FROM DimCustomers AS c
WHERE StartDT <= '10/01/2010'
AND (EndDT >= '10/01/2010' OR EndDT IS NULL)

I used the first day of the quarter, but you could use any date that falls within the quarter. If you used a far-future date (such as 12/31/2999) instead of NULL for your current members the query would be even simpler.

However, even though this query can be satisfied directly off the dimension table, to make it more user-friendly and benefit from month or quarter dimension attributes, building a periodic snapshot fact table is probably a good idea.

[ohmycamote]

Dave Jermy,

I didn't mention on my original post that we do require the ability to report on the member's other attributes in a given reporting quarter.
E.g.,
1.) For 2010Q1 there were 3 members and of those 3 members 2 are from USA and 1 from Canada, etc. (DimGeography table)
2.) Compare the number of members from USA on 2010Q1 to 2010Q2 and so on...

So the number of members per reporting quarter aren't just for the total count but also their attributes in that given quarter as stated above.

Given that granularity, would you recommend any better way to do this?

Thank you for your responses.

[Dave Jermy]

Not a problem; make the grain of the periodic snapshot whatever you need to to satisfy the requirements. Add in the Geography Key and away you go. If the requirement includes being able to analyse it by other customer demographics (say Age Band), then fine. If the need is to have a monthly snapshot for each customer then so be it.

The point is, whatever the grain, include additive metrics that will allow you to satisfy any reasonable question an end user may have about the data.

[ohmycamote]

Summary
FactMembers
[tr][tr][tr][tr]

SK	CustKey	JoinedDTKey	ReportingDTKey
1	21	51	51
2	22	51	51
3	23	51	51
-	-	-	-
4	21	51	145
5	22	51	145
6	23	51	145
-	-	-	-
7	21	51	235
8	22	51	235
9	23	51	235
-	-	-	-
10	21	51	325
11	22	51	325
12	23	51	325
-	-	-	-
13	22	51	415
14	23	51	415

Given the above, what is the best way to track how many are new members in that reporting period and how many are not?
Definition of "new members" being that their JoinDate matches the reporting period (e.g., JoinedDTKey=ReportingDTKey)

So in the example above, during ReportingDTKey=51, there were 3 new members.
During ReportingDTKey=145, all 3 are old members.
And so on...

Possible Solutions?
Should I put new columns in the Fact table to show NewMemberCount=1 or OldMemberCount=0?
But both has to be mutually exclusive.

Is that the right way to do it?

[Demitri]

You can create a single MembershipNewOrRenewFlg attribute, assuming 'N' for new or 'R' for renewed memberships.

[Sponsored content]