Fact table design question RRS feed

  • Question

  • I have a fact table which has customer visits data. One customer visit per row. It looks like following.


    Distinct Customers is a measure. Store, Product, Date are dimensions.

    Now people are interested at customer numbers who make multiple visits. Is there a way to make a calculated measure to find out return customers? Or I should put this as a new field in fact table?


    Thursday, April 24, 2008 4:06 PM

All replies


    I am not able to get your point correctly. What does Distinct Customers refer to. IF i have got it right then you want the count of customers. then you should have a customer dimension and then slice this fact table by the dimension. Here measure will be count of the Customer ID (which will give all the customer number), then slicing it by customer dimension you can get the number of visits of the customers. You can also slice with both date and customers to get the customer visits number for a particular day.



    Girija Shankar 

    Thursday, April 24, 2008 4:31 PM
  • Sorry I didn't make my question clear. There is no customer dimension. We only care about total number of customers on month, store, product, et al.

    Now I have one measure which is distinctcount of CustomerID. I need another measure which gives distinctcount of CustomerID who makes multiple visits, not the number of visits of each customer.

    Thanks for your help.
    Thursday, April 24, 2008 6:08 PM
  • You can go for a query for your fact table so that it will have a another column having the count of customer who make multiple visits for a particular Time, produst store etc.


    The sample query will be :


    SELECT T,S,P,Expr1 from (SELECT T,S,P,C,distinct Count(*),Expr1 from <TableName> group by T,S,P,C) where <Your condition.


    The above you will have to append to your existing query so that you get the count of customers who make multiple visits for a certain combition of Time, product, store.


    Hope this helps...



    Girija Shankar


    Friday, April 25, 2008 10:51 AM