DimCustomers and DimGeography - How to Measure Customers?

View previous topic View next topic Go down

DimCustomers and DimGeography - How to Measure Customers?

Post  ohmycamote on Thu Jul 14, 2011 10:44 am

DimCustomers:
- CustomerKey
- JoinDate (when they became a customer)

DimGeography

How do I create a cube to how many people became customers (based on JoinDate) at certain points in time and show them by country, region, etc?

What would be my Fact table at this point?

Thank you for your help!

ohmycamote

Posts : 14
Join date : 2011-07-05

View user profile

Back to top Go down

Re: DimCustomers and DimGeography - How to Measure Customers?

Post  hang on Fri Jul 15, 2011 8:28 am

Your fact table is the periodic snapshot on the DimCustomers. If DimCustomers is not a big dimension, you may flatten out DimGeography inside DimCustomer or snowflake the DimCustomer by DimGeography so that the Customer-Geography hierarchy may be formed through the dimensions, and the entry point in the fact is CustomerKey, as follows:

FactCustomer
-SnapshotDateKey,
-CustomerKey,
-CustomerCount (default to 1)

You may use the following pseudo script to periodically load the fact table:

insert into FactCustomer
select SnapshotDateKey, CustomerKey, 1 as CustomerCount
from DimCustomers
where JoinDate<=SnapshotDate


hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: DimCustomers and DimGeography - How to Measure Customers?

Post  hellovineet on Thu Jul 28, 2011 12:58 pm

hang wrote:

insert into FactCustomer
select SnapshotDateKey, CustomerKey, 1 as CustomerCount
from DimCustomers
where JoinDate<=SnapshotDate


You mean SnapshotDateKey will join the dateDim for the date when the load is run.

Also will this be full refresh of the fact table or an incremental with the history preserved?


hellovineet

Posts : 2
Join date : 2011-07-28

View user profile

Back to top Go down

Re: DimCustomers and DimGeography - How to Measure Customers?

Post  hang on Thu Jul 28, 2011 2:52 pm

hellovineet wrote:You mean SnapshotDateKey will join the dateDim for the date when the load is run.
That's right, you need calendar attributes to analyse your customer counts by drilling down from general to specific period.

hellovineet wrote:Also will this be full refresh of the fact table or an incremental with the history preserved?
Incremental with capability to rebuild the fact history by looping through parameterised (date) load process.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: DimCustomers and DimGeography - How to Measure Customers?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum