November 7, 2014 at 2:34 am
I have to show the count of new customers. I have a customer dimension and related fact table. My question - Should I add a dimension attribute naming, NewCustomer, and use this attribute with customer count measure OR I add a column in the fact table and make NewCustomer a measure ?
Thanks !
November 7, 2014 at 2:40 am
How do you determine new customers? And when does a customer stop being "new"?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 7, 2014 at 2:50 am
Thanks for swift reply!
My fact is periodic snapshot and contains monthly snapshot record of customers. A customer is the new customer of any month when he enters the system for the first time and then stops being the new customer after that month. But if he exits the system and comes back again, he is new customer again for the month he comes back. So in this particular case, Grand Total New customer would be 1 but for each month when he enters the system, it should show 1
Thanks
November 7, 2014 at 3:09 am
The way we did at our current project (where we also have a snapshot fact table) is to add a measure to the fact table: NumberOfNewCustomers.
This simply contains 1 if the customer was new in that month, 0 otherwise.
This has the advantage that you never need to update the customer table directly.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 7, 2014 at 3:35 am
Yes, adding a column in fact table and setting its value to 1 can be a solution when the customer is counted new only for once in the lifetime. In my case, same customer can come back again and then new customer is set to 1. That will then make the grand total = 2, which is not desired.
November 7, 2014 at 3:53 am
diwakarjha.80 (11/7/2014)
Yes, adding a column in fact table and setting its value to 1 can be a solution when the customer is counted new only for once in the lifetime. In my case, same customer can come back again and then new customer is set to 1. That will then make the grand total = 2, which is not desired.
It all depends on viewpoints.
If a customer can come back as new, he has been new twice in his lifetime. So 2 is technically the correct answer.
It all depends on requirements and how you calculate it in your ETL. In our case, we have a measure called NbrOfReturningCustomers. This deals with this case pretty well 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 7, 2014 at 7:14 am
Can you explain what you mean by grand total?
From what you are explaining you have a monthly snapshot and by adding a new column in your fact table this should be fine. But when you say grand total, would not all of your customers have been new customers at some point? Do you mean grand total by year, quarter? You could just add a new column for each of these date heirarchies but then the question is how is your DimDate setup.
If its the life of the company why not just create a measure that counts the unique companyids, company number, what ever makes your accounts unique? Or you can add two columns to your fact, one for when the company first joined and then the other for everytime it returned. That way if your company ever wanted to know how many times a client has returned you could provide that with something that is already built.
You could also probably do it using MDX but it all depends what you are looking for.
November 7, 2014 at 7:25 am
Sergio, thanks for your suggestions. Indeed there are different ways to show the new customer count for different scenarios. What I am unsure is should NewCustomer column be in fact table ? I can also create it as a attribute of customer dimension. Which is better practice and how to decide on this ...
November 7, 2014 at 7:28 am
diwakarjha.80 (11/7/2014)
Sergio, thanks for your suggestions. Indeed there are different ways to show the new customer count for different scenarios. What I am unsure is should NewCustomer column be in fact table ? I can also create it as a attribute of customer dimension. Which is better practice and how to decide on this ...
Personally I prefer the fact table, as this can give you the most clear view on when a customer was new and if he came back or not.
If you put it into the dimension, you are forced to update your dimension every day or month. And when would you update the dimension? At the start of the month? What about customers who just joined at the end of the previous month? Are they already discarded from the segment "new customers"?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 7, 2014 at 7:34 am
My suggestion would be a new column in the fact table. My reason behind this is that where I work we don't only use the datamart to feed the cube but to also feed reports directly so I prefer to keep the code logic in the datamart. We use the mentality that the cube is more for self serve and the datamart for the more complex reports. We don't have days here to create reports so when something complex comes up I have about a day to produce the report. I know its not the best practice but neither do I live in a perfect world.
I just finished having a presentation done by a DBA on best practices and from what he showed us all I could think was he has never worked with an MS CRM database.:-)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply