CODEWITHSUNDEEP
data-warehousebusiness-intelligencedimensional-modeling
user3220129
user3220129

Reputation: 501

Dimension Model for New customers in current month

I have started to work on a dimension model to know the count of new customers who visited a shop. I'm little bit confused in identifying the facts and dimensions for this purpose. Can someone help me in this regard?

As per my understanding, I have identified Customer, Product, Invoice, Time, Payment as dimensions (as per level 0). But, I'm not sure of how to identify fact in this regard. I know that facts are those data which are measurable. The measure or the result that I want to achieve post data model is how to get count of new customers that visited in current month.

Upvotes: 0

Views: 122

Answers (1)

Jesse Dyson
Jesse Dyson

Reputation: 81

Does each visit result in an invoice? How is "new customer" defined (i.e., their first invoice, some time period after their first invoice?)

If so, one option would be to create a "factless fact table" to capture each invoice event and create a dimension to indicate that the invoice is the first invoice for that customer (i.e., New Customer). You could then use a distinct count of patients where the New Customer dimension indicates that they are a new customer.

FactVisit(TimeKey,CustomerKey,InvoiceKey,ProductKey,PaymentKey,NewCustomerIndicatorKey) DimNewCustomerIndicatorKey(NewCustomerIndicatorKey, ...) {"Y", "N")

Another possibility would be a separate fact table that captures a row the first time a customer is seen.

One additional option would be to include an attribute in the Customer dimension that holds the date that the customer was first seen.

A lot depends on what/if any OLAP front-end you're using and what tool you're using to report the results.

Upvotes: 1

Related Questions