Is this a Correct Periodic Snapshot Fact Table?

View previous topic View next topic Go down

Is this a Correct Periodic Snapshot Fact Table?

Post  ohmycamote on Thu Aug 04, 2011 2:09 pm

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)
SKCustIDNameCustStatusStartDTEndDTStatus
21 123JohnA 1/1/2010 12/31/2010Expired
22333PabloA 1/1/2010 Current
23888EllaA 1/1/2010 Current
- -- - - - -
24 123John I 1/1/2011 Current

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

DimDate
DateKey Year QuarterMonth Day
51 2010 1 1 1
52 2010 1 1 2
-----
145 2010 241
146 2010 242
-----
235 2010 371
236 2010 372
-----
325 2010 4101
326 2010 4102
-----
415 2011 111
416 2011 112

FactMembers
[tr][tr][tr][tr]
SKCustKey JoinedDTKey ReportingDTKey
1 21 51 51
2 22 51 51
3 2351 51
----
4 21 51 145
5 22 51 145
6 2351 145
----
7 21 51 235
8 22 51 235
9 2351 235
----
10 21 51 325
11 22 51 325
12 2351 325
----
13 22 51 415
14 2351 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.

ohmycamote

Posts : 14
Join date : 2011-07-05

View user profile

Back to top Go down

Re: Is this a Correct Periodic Snapshot Fact Table?

Post  Dave Jermy on Mon Aug 08, 2011 7:31 am

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):

MonthKeyClubKeyNewMembershipCountRenewedMembershipCountLapsedMembershipCountTotalMembershipCount
5113003
14510003
23510003
32510003
41510212

Dave Jermy

Posts : 33
Join date : 2011-03-24
Location : London, UK

View user profile

Back to top Go down

Re: Is this a Correct Periodic Snapshot Fact Table?

Post  VHF on Mon Aug 08, 2011 8:57 am

ohmycamote wrote:
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.
ohmycamote wrote:
DimCustomers (SCD)
SKCustIDNameCustStatusStartDTEndDTStatus
21 123JohnA 1/1/2010 12/31/2010Expired
22333PabloA 1/1/2010 -Current
23888EllaA 1/1/2010 - Current
- -- - - - -
24 123John 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.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Is this a Correct Periodic Snapshot Fact Table?

Post  ohmycamote on Mon Aug 08, 2011 10:55 am

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.

ohmycamote

Posts : 14
Join date : 2011-07-05

View user profile

Back to top Go down

Re: Is this a Correct Periodic Snapshot Fact Table?

Post  Dave Jermy on Mon Aug 08, 2011 11:13 am

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.

Dave Jermy

Posts : 33
Join date : 2011-03-24
Location : London, UK

View user profile

Back to top Go down

Re: Is this a Correct Periodic Snapshot Fact Table?

Post  ohmycamote on Tue Aug 09, 2011 5:52 pm

ohmycamote wrote:Summary
FactMembers
[tr][tr][tr][tr]
SKCustKey JoinedDTKey ReportingDTKey
1 21 51 51
2 22 51 51
3 2351 51
----
4 21 51 145
5 22 51 145
6 2351 145
----
7 21 51 235
8 22 51 235
9 2351 235
----
10 21 51 325
11 22 51 325
12 2351 325
----
13 22 51 415
14 2351 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?

ohmycamote

Posts : 14
Join date : 2011-07-05

View user profile

Back to top Go down

Re: Is this a Correct Periodic Snapshot Fact Table?

Post  Demitri on Sat Aug 20, 2011 9:54 pm

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

Demitri

Posts : 9
Join date : 2010-07-27

View user profile

Back to top Go down

Re: Is this a Correct Periodic Snapshot Fact Table?

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