DimCustomers and DimGeography - How to Measure Customers?

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!

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 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 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.