Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

DimCustomers and DimGeography - How to Measure Customers?

3 posters

Go down

DimCustomers and DimGeography - How to Measure Customers? Empty DimCustomers and DimGeography - How to Measure Customers?

Post  ohmycamote 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

Back to top Go down

DimCustomers and DimGeography - How to Measure Customers? Empty Re: DimCustomers and DimGeography - How to Measure Customers?

Post  hang 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

Back to top Go down

DimCustomers and DimGeography - How to Measure Customers? Empty Re: DimCustomers and DimGeography - How to Measure Customers?

Post  hellovineet 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

Back to top Go down

DimCustomers and DimGeography - How to Measure Customers? Empty Re: DimCustomers and DimGeography - How to Measure Customers?

Post  hang 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

Back to top Go down

DimCustomers and DimGeography - How to Measure Customers? Empty Re: DimCustomers and DimGeography - How to Measure Customers?

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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